The Power of CRUD Operations Explained with SQL

CRUD is an acronym that’s used a lot in web and mobile development. Frontend engineers in particular might wonder what it means and why we use it. In this post, I’ll explain CRUD using PostgreSQL, and talk about why it’s important.

Unfamiliar with SQL or Postgres? No problem! These examples are pretty simple.

What is CRUD?

CRUD is an acronym that stands for ‘create, read, update, and delete.’ In persistence, these are four operations we perform on a record. According to Wikipedia: “[t]he term was likely first popularized by James Martin in his 1983 book Managing the Data-base environment.” Thanks, James!

Each database provider implements these operations however they choose. And then, they’re abstracted by your API, making them language-agnostic, too.

The SQL standard has implemented CRUD via four keywords:

  • INSERT (create)
  • SELECT (read)
  • UPDATED (update)
  • DELETE (delete)

Here’s the full CRUD cycle, in PostgreSQL:

-- create people table
create table people (name varchar);

-- insert (create)
insert into people values (name 'jake');

-- select (read)
select * from people where name = 'jake';

-- update (update)
update people set name = 'jane' where name = 'jake';

-- delete (delete)
delete from people where name = 'jane';

We’ve created a record, read it, updated it, and deleted it! That’s CRUD.

Why CRUD Matters

When you’re designing an application, from the UI to the database, you must consider CRUD. Users expect that when they create something with your software, they can see it again, change it, and delete it.

For example, I enter my last name into a form in your application, which I surmise is the create operation of CRUD. I now expect to be able to:

  • See it again (read)
  • Change it because I mistyped it, changed my name, or just desire to (update)
  • Delete it, when that makes business sense (delete)

Sometimes you might not build every letter of CRUD– I have omitted delete before. But you must consider each operation, because people expect them. And if you leave one out, have a clear reason. Looking at you, Twitter.