CRUD

Create, read, update, delete: the four basic things you do to stored records, mapped to SQL’s INSERT, SELECT, UPDATE and DELETE and to HTTP’s POST, GET, PUT or PATCH, and DELETE.

How it works

CRUD treats data as records you manage directly. Create adds a record, read fetches one or many, update changes fields in place, and delete removes it. In SQL each letter is one statement: INSERT, SELECT, UPDATE, DELETE. In an HTTP API each maps to a method on a resource, and RFC 9110 gives the methods rules that matter for retries. GET is safe: it asks for no change on the server. PUT (replace the whole resource, or create it at a URI the client chooses) and DELETE are idempotent: sending the same request twice has the same intended effect as sending it once, so a client can retry automatically when a connection drops before the response arrives. POST (create a resource the server will name, answered with 201 Created and a Location header) is neither, so a retried POST can create a duplicate. PATCH, added by RFC 5789, applies a set of changes to part of a resource and is neither safe nor idempotent unless designed to be. The same four words also describe a style of user interface: forms and lists for adding, viewing, searching, editing and removing records.

Think of it as

A box of index cards. You can write a new card, look one up, cross out a line and write a new value, or throw a card away. Every change is one of those four moves, which keeps things simple, and none of them records why the card changed.

Reach for it when

Users really are editing records: settings, reference data, catalogues, admin screens
Simple services whose model, logic and API fit in one project, such as a product catalogue
Resource-style HTTP APIs where idempotent PUT and DELETE let clients retry safely

Think twice when

The operations are business actions, not field edits: “discontinue product”, “relocate customer”, “book room”
You need to know why and when a value changed, not just its latest state
Many people edit the same record and nothing checks for conflicting changes

A real example

A product catalogue API: POST /products creates product 17 and returns 201 Created with Location: /products/17; GET /products/17 reads it; PATCH /products/17 changes the price from 4.50 to 5.00; DELETE /products/17 removes it. Two admins open product 17 at the same time and both save a new price. Without a check, the second save silently overwrites the first. With an ETag and If-Match on the update, the second request fails with 412 Precondition Failed, and the admin reloads before trying again.

The four operations in SQL and HTTP#

The SQL statements are the same in every relational database. The HTTP column follows RFC 9110 and, for PATCH, RFC 5789.

Operation	SQL	HTTP	Safe	Idempotent	Notes
Create	`INSERT`	`POST` to a collection, or `PUT` to a new URI	No	`POST` no, `PUT` yes	`201 Created` with a `Location` header naming the new resource
Read	`SELECT`	`GET`	Yes	Yes	Caches and crawlers may repeat it freely
Update	`UPDATE`	`PUT` replaces the whole resource, `PATCH` changes part of it	No	`PUT` yes, `PATCH` only if designed to be	Add `If-Match` to refuse the change if someone else changed it first
Delete	`DELETE`	`DELETE`	No	Yes	Removes the URI mapping; the stored data might or might not be destroyed

Where CRUD thinking breaks down#

CRUD fits data that people edit. It strains when the data stands for a business with its own verbs, history and concurrency.

Business actions aren’t updates

lost intent

“Set status to reserved” hides what happened; “book hotel room” says it. With only four verbs, rules end up in the client or in people’s heads, and one task can mean editing several screens. Task-based commands, as in CQRS, name the action.

Lost updates

concurrency

Two people read the same record, both edit it, and the last save wins without either knowing. Optimistic checks catch it: a version column in the WHERE clause, or an ETag with If-Match over HTTP, which returns 412 Precondition Failed when the record has moved on.

History disappears

audit

UPDATE overwrites the old value and the reason for the change. Audit tables or change logs can keep previous states, but they still record what changed, not what happened. Recording events, as an accounting ledger does, keeps both.

Soft deletes

delete rarely means delete

Deleting a catalogue item shouldn’t make past orders and invoices vanish, so teams add an is_deleted flag. Often the user meant something else: orders are cancelled, products discontinued, employees leave. Each of those is a business action with its own rules (a late cancellation fee, stock still to sell off) that a flag can’t carry.

Reads and writes drift apart

one model for both

Screens want joined, reshaped views; updates want validation and rules. As those needs grow, a single record model serves neither well, which is the problem CQRS splits apart.

In code · sql

CREATE TABLE products (
  id      INTEGER PRIMARY KEY,
  name    TEXT    NOT NULL,
  cents   INTEGER NOT NULL,
  version INTEGER NOT NULL DEFAULT 1
);

-- Create
INSERT INTO products (id, name, cents) VALUES (17, 'Tea', 450);

-- Read
SELECT id, name, cents, version FROM products WHERE id = 17;   -- 17 | Tea | 450 | 1

-- Update, but only if nobody changed the row since we read version 1
UPDATE products SET cents = 500, version = version + 1 WHERE id = 17 AND version = 1;  -- 1 row
UPDATE products SET cents = 480, version = version + 1 WHERE id = 17 AND version = 1;  -- 0 rows: stale, reload

-- Delete
DELETE FROM products WHERE id = 17;

Often confused with

CQRS Persistence: state, events and replay Relational databases Web APIs

Where it came from1983–2022

The four operations are as old as stored data: whatever holds data must let you create it, read it, change it and eventually remove it. The acronym came later. Wikipedia credits James Martin’s 1983 book Managing the Data-base Environment with likely first popularising the term. In relational databases each letter matches one SQL statement, and form-based applications built screens around the same four actions.

The web added a second mapping. HTTP methods such as PUT and DELETE manipulate resources directly, so REST-style APIs came to be read as CRUD over URIs. Roy Fielding, who defined REST, wrote in 2009 that his dissertation never mentions CRUD or POST, and that using POST for operations that don’t fit the other methods is fine. In 2010 RFC 5789 added PATCH, because PUT only allows complete replacement. RFC 9110, in 2022, restated which methods are safe and which are idempotent.

CRUD also became the thing other designs define themselves against. In 2009 Udi Dahan argued that users had learned to phrase requirements in CRUD terms, saying “delete” when they meant “discontinue”. Greg Young’s 2010 CQRS documents made the same point about the four verbs, and Martin Fowler’s 2011 note on CQRS started from the CRUD mental model, adding that many systems fit it and should be built that way.

Why it’s built this way

Four generic verbs for every kind of record: The same operations work for any table or resource, so tools, frameworks and APIs can be built once and reused. The cost is that the verbs say nothing about the business meaning of a change.
Update in place: Storage holds the current state, so UPDATE replaces the old value. That is cheap and simple, but earlier states survive only if something else, such as an audit trail or an event log, records them.
Idempotent PUT and DELETE in HTTP: If a connection closes before the response arrives, the client can resend an idempotent request and know the intended effect is the same. POST gets no such promise.
A separate PATCH method: PUT is defined to overwrite a resource with a complete new body, so reusing it for partial changes would confuse proxies, caches, clients and servers. RFC 5789 added a method that sends only a set of changes, applied atomically.

Timeline

1983
James Martin’s Managing the Data-base Environment likely popularises the term
2009
Roy Fielding: “It is okay to use POST”; Udi Dahan: “Don’t Delete: Just Don’t”
2010
RFC 5789 adds the HTTP PATCH method
2011
Martin Fowler’s CQRS note contrasts it with the CRUD mental model
2022
RFC 9110 restates HTTP method semantics, including idempotency

Sources12

Improve this page on GitHub