CRUD

CRUD — Create, Read, Update, Delete — is the pattern of using one model for both reading and writing, mapped closely onto the shape of your storage. A Customer row, a Customer object, a CustomerRepository, and four endpoints. It is so ubiquitous that most engineers do not think of it as a pattern at all, which is exactly why it deserves a page in a pattern catalog: choices you make unconsciously are the ones you never re-examine.

This article treats CRUD as what it actually is — a deliberate architectural decision with real strengths and specific, predictable failure modes. Almost every system should start here. Many should stay here forever. The ones that should not are identifiable by concrete symptoms rather than by ambition, and knowing those symptoms is worth more than knowing any amount of CQRS theory.

The problem

The problem CRUD solves is the ordinary one: an application needs to persist state and read it back, and every hour spent on data-access machinery is an hour not spent on the thing the software is for.

CRUD's answer is to collapse the distance between the storage schema and the application model. One table, one type, one repository, one set of operations. The schema is the model. There is no translation layer, no separate read store, no synchronization, no eventual consistency, and no question about which representation is authoritative — because there is only one. A developer who understands the table understands the system.

This is not laziness; it is a legitimate optimization for the constraint that dominates most projects, which is the cost of change while the domain is still being discovered. Early on you do not know which invariants matter, which queries will be hot, or which parts of the model will turn out to be separate concepts. A single model that can be reshaped with a migration and a refactor is dramatically cheaper to be wrong with than a write model, a read model, and a projection pipeline that all have to be reshaped together.

The trouble is that CRUD's central simplification — one model for two jobs — is also the assumption that eventually fails. It fails in four specific ways, and they arrive in roughly this order.

Intent is destroyed. An UPDATE records the new state and discards the reason. A customer row whose status changed from active to inactive cannot tell you whether they churned, were suspended for fraud, or were archived by a cleanup job. The business asks "how many customers churned last quarter?" and the honest answer is that the system never recorded churn, only the absence of activity. You can bolt on an audit table, but you are now writing history by hand, and the audit and the state can disagree.

Concurrent updates silently lose data. Two users load the same order, each edits a different field, each saves the whole object. The second write overwrites the first. Nobody sees an error; the first user's change simply evaporates. This lost update is CRUD's most common production bug, and it is invisible in testing because it requires concurrency to reproduce.

Read and write requirements diverge. The write side wants a normalized schema that makes invariants enforceable. The read side wants denormalized, pre-joined shapes that make dashboards fast. One model serves both by compromising both: you add indexes for reports that slow down writes, or you write a seven-table join that a product manager wants to run on every page load.

The model goes anemic. With data access dominating the design, business logic drifts out of the entities and into services and controllers, leaving objects that are little more than typed rows with getters and setters. Rules that should be enforced in one place get duplicated across every code path that touches the data — and then diverge.

When to use it

CRUD is the correct choice — and should be the default — when:

• The domain is genuinely record-oriented. Reference data, catalogs, user profiles, settings, admin panels, content management. When the business really does think in terms of "records that get edited," a model that matches that thinking is not a compromise, it is accuracy.
• Business rules are thin. Validation, required fields, simple relationships. If the interesting logic is "email must be unique" rather than "a policy may only be cancelled if no claim is open in the current period," you do not need a rich model to hold it.
• The domain is still being discovered. Early-stage products change shape weekly. CRUD's cheap reshaping is worth more than any structural purity, and the patterns you would otherwise adopt encode assumptions you have not earned yet.
• Read and write shapes are similar. If the data you display closely resembles the data you store, the primary justification for splitting the models does not apply.
• Strong read-after-write consistency matters. CRUD gives it for free. Every alternative in this catalog trades some of it away, and users notice: saving a form and not seeing the change is a bug report every time, no matter how correct the eventual consistency is.
• The team is small. One model means one thing to learn. A projection pipeline that nobody but its author understands is a liability in a five-person team regardless of its elegance.

The overwhelming majority of software should be CRUD, and much of the software that is not should have been.

When NOT to use it

• Intent and history are business requirements. Regulated finance, healthcare, anything requiring a defensible audit trail or temporal queries ("what did this look like on the 3rd?"). Bolting audit tables onto CRUD reimplements event sourcing badly, without its guarantees.
• The write model must enforce complex invariants. Rules spanning several entities, state machines with illegal transitions, temporal constraints. These need a real domain model with an aggregate boundary — which is a change of modeling, not of storage, and is available without adopting CQRS.
• Read and write load differ by orders of magnitude, in shape or volume. Millions of reads against complex projections and a trickle of writes is the textbook case for splitting the models so each can be optimized independently.
• Reporting is destroying transactional performance. Analytical queries and OLTP workloads on the same tables is the most common practical trigger for splitting — and often the cheapest fix is a read replica, not a new architecture.
• Concurrent editing is normal and conflicts are costly. Where two users routinely touch the same record, last-write-wins is not an acceptable conflict resolution strategy, and the fix — command-level granularity, or explicit merge — pushes past CRUD's whole-object update.

Note what is not on this list: scale alone. Plenty of very large systems are CRUD systems with good indexes, caching, and read replicas. "We might need to scale" is not a reason to leave; a measured bottleneck is.

Architecture

The structure is deliberately unremarkable — that is the point.

flowchart LR
    C[Client] --> A[API / Controller]
    A --> S[Service layer]
    S --> R[Repository]
    R --> D[(Single database)]
    D --> R
    R --> S
    S --> A
    A --> C

One path in, one path out, one store. Compare with the CQRS topology — separate command and query models, a projection process, and an eventually-consistent read store — and the difference in operational surface is stark. CRUD has no projection to lag, no rebuild to run, no consistency window to explain to users.

The one piece of structure worth adding early is optimistic concurrency control, because it closes the lost-update failure without meaningfully complicating anything.

sequenceDiagram
    participant U1 as User A
    participant U2 as User B
    participant DB as Database
    U1->>DB: read order (version 7)
    U2->>DB: read order (version 7)
    U1->>DB: update ... WHERE version = 7
    DB-->>U1: 1 row — now version 8
    U2->>DB: update ... WHERE version = 7
    DB-->>U2: 0 rows — conflict
    Note over U2: reload, re-apply, or surface to user

The version column turns a silent data loss into an explicit conflict the application can handle. It costs one integer and one WHERE clause, and it is the highest-value addition to a CRUD system by a wide margin.

Code walkthrough

Here is CRUD done carefully in TypeScript — not the scaffolded version, but the version that avoids the two bugs that scaffolding leaves in. Start with the model and an explicit version.

interface Order {
  id: OrderId;
  customerId: CustomerId;
  status: "draft" | "placed" | "cancelled";
  lines: OrderLine[];
  version: number;
}

class ConcurrentModificationError extends Error {
  constructor(readonly id: string, readonly expectedVersion: number) {
    super(`Order ${id} was modified by someone else`);
    this.name = "ConcurrentModificationError";
  }
}

version is part of the model, not a hidden ORM detail, because callers need to make decisions about conflicts and cannot do that if the mechanism is invisible.

The repository, with the conditional update that makes concurrency safe:

class OrderRepository {
  constructor(private readonly db: Database) {}

  async findById(id: OrderId): Promise<Order | undefined> {
    return this.db.selectFrom("orders").where("id", "=", id).first();
  }

  async update(order: Order): Promise<Order> {
    const updated = await this.db
      .updateTable("orders")
      .set({ ...order, version: order.version + 1 })
      .where("id", "=", order.id)
      .where("version", "=", order.version)
      .returning();

    if (updated.length === 0) {
      throw new ConcurrentModificationError(order.id, order.version);
    }
    return updated[0];
  }
}

The second where clause is the entire optimistic-locking mechanism. Without it, update is last-write-wins and concurrent edits vanish without a trace. With it, the conflict becomes an exception the caller must handle — which is the correct outcome, because only the caller knows whether to retry, merge, or ask the user.

The second improvement is to resist the generic update(entity) endpoint for operations that carry meaning. Compare the two shapes:

async function updateOrder(id: OrderId, patch: Partial<Order>): Promise<Order> {
  const existing = await repo.findById(id);
  if (!existing) throw new NotFoundError(id);
  return repo.update({ ...existing, ...patch });
}

This is the shape that erodes into an anemic model. patch can set status to anything, so every rule about legal transitions has to be re-checked in every caller — or, more commonly, is not checked at all. It also destroys intent: the audit log records that status became cancelled, not that the customer cancelled and why.

Naming the operation costs almost nothing and fixes both:

async function cancelOrder(
  id: OrderId,
  reason: CancellationReason,
): Promise<Order> {
  const order = await repo.findById(id);
  if (!order) throw new NotFoundError(id);
  if (order.status !== "placed") {
    throw new IllegalTransitionError(order.status, "cancelled");
  }

  return repo.update({ ...order, status: "cancelled", cancellationReason: reason });
}

This is still CRUD — one model, one store, immediate consistency, no projections. But the invariant lives in exactly one place, and the operation records why. The important observation is that most of what people credit to CQRS is available here: intention-revealing operations and enforced invariants are a modeling choice, not an architectural one. You do not need a separate read model to stop having an anemic domain, and adopting CQRS without making this change simply gives you an anemic model with more infrastructure.

When reads eventually diverge, the first move is still not CQRS — it is a purpose-built query that skips the write model entirely:

async function orderSummariesForDashboard(customerId: CustomerId) {
  return db.raw(`
    SELECT o.id, o.status, o.placed_at, SUM(l.qty * l.unit_price) AS total
    FROM orders o JOIN order_lines l ON l.order_id = o.id
    WHERE o.customer_id = ? GROUP BY o.id
  `, [customerId]);
}

Reading through a tailored query rather than hydrating aggregates relieves most read-side pressure at near-zero architectural cost, and it can run against a read replica. Many teams who believe they need CQRS need this function and an index.

Performance characteristics

The figures above are indicative and describe shape rather than a benchmark.

One store, immediate consistency. CRUD's defining performance property is the absence of a consistency window. There is no projection lag, no "your change will appear shortly," and no class of bug where a user's read hits a stale replica of their own write. Every alternative pattern spends some of this, and the UX cost of spending it is consistently underestimated.

Velocity is the dominant benefit. Time-to-first-feature is measured in hours because there is no pipeline to build. Over a system's life this compounds: every new field is a migration and a form, not a migration, a projection change, and a rebuild. Treating this as merely "developer convenience" undersells it — it is the reason CRUD systems ship and outlive more sophisticated rewrites.

Read scaling has a real ceiling, but it is further away than people think. Indexes, caching, and read replicas take a CRUD system a very long way. The ceiling arrives when read queries need a fundamentally different shape than the write schema — many joins, heavy aggregation — and no index fixes shape. Until then, cache-aside plus a replica is a far better return than restructuring.

Lost updates are the characteristic correctness risk. Without versioning, concurrent writes to the same row silently discard changes at a rate that scales with concurrency and edit overlap. It produces no errors and no logs — users report "my change didn't save" and nobody can reproduce it. The fix is one column.

Write contention grows with model coarseness. Because CRUD updates whole objects, two operations touching unrelated fields of the same row still contend. Finer-grained, intention-revealing updates reduce this without leaving the pattern.

Live demo

An interactive CRUD sandbox is coming soon. You will be able to run concurrent edits against the same record with and without a version column — watching updates silently disappear in the first case and surface as explicit conflicts in the second — then add reporting load to a transactional table and watch write latency climb until a read replica or a tailored query relieves it. The aim is to make the two moments concrete: the bug you should fix immediately, and the threshold where splitting the models finally pays.

Related patterns

CQRS is the direct contrast and the pattern CRUD is most often abandoned for, frequently too early. CQRS splits the single model into a write model optimized for invariants and one or more read models optimized for queries, and accepts eventual consistency between them as the price. The decision hinges on a question worth asking bluntly: are your read and write requirements genuinely in conflict, measurably, today? If reports are slowing down writes, if the read shape needs joins the write schema cannot cheaply provide, if the two sides need different scaling — then splitting pays. If the honest answer is "not yet, but we expect to grow," CRUD plus indexes plus a read replica will serve longer than most teams expect, and the migration to CQRS remains available. Note also that CQRS is a spectrum: separating read queries from write commands within one database gets much of the benefit before any projection pipeline exists.

Event Sourcing is the deeper contrast, addressing CRUD's intent-destruction failure directly. Where CRUD stores current state and forgets how it got there, event sourcing stores the sequence of facts and derives current state. That buys a perfect audit trail, temporal queries, and the ability to build new read models retroactively over history — at the cost of significant complexity in versioning, replay, and snapshotting. The two are frequently combined with CQRS but are independent decisions: you can event-source without CQRS, and adopt CQRS over a CRUD store.

Cache-Aside is the pragmatic first response to CRUD read pressure, and it deserves to be tried before any architectural split. Adding a cache in front of hot reads relieves the load that usually triggers the CQRS conversation, while leaving the single model intact. When the working set is much smaller than the dataset — which is typical — this is the highest-leverage change available and it takes an afternoon.