What Is a CRUD Application? SQL, REST and a Working Example

Web · Published 2026-10-24

Rows of small wooden drawers with brass handles and label holders in a card catalogue — The drawers of a wooden library card catalogue.

A CRUD application is one whose main job is to let users Create, Read, Update and Delete records in a persistent store, usually a database. Admin panels, to-do lists, contact managers, inventory tools and most internal business software are CRUD applications at their core.

The four operations

CRUD is less a technology than a vocabulary. The same four verbs appear at every layer of a typical web application: the database speaks SQL, the API speaks HTTP, and the user interface offers forms, lists and buttons. Understanding how they line up makes it much easier to design endpoints and spot mistakes.

Operation	SQL statement	REST method and path	Typical success status
Create	`INSERT`	`POST /tasks`	201 Created
Read (list)	`SELECT … WHERE …`	`GET /tasks`	200 OK
Read (one)	`SELECT … WHERE id = ?`	`GET /tasks/42`	200 OK, or 404
Update	`UPDATE … SET … WHERE id = ?`	`PUT /tasks/42` (replace) or `PATCH /tasks/42` (partial)	200 OK or 204 No Content
Delete	`DELETE FROM … WHERE id = ?`	`DELETE /tasks/42`	204 No Content

The difference between PUT and PATCH is worth getting right. PUT sends the complete new representation of the resource; fields you leave out are, in principle, reset. PATCH sends only the fields that change. Many APIs implement only PATCH because it is what forms and inline edits actually need.

A minimal CRUD layer in Python

The following module uses only the standard library's sqlite3. Each function is one CRUD operation. All values are passed as query parameters (?), never formatted into the SQL string, which is what prevents SQL injection. The only dynamic SQL is the list of column names in update_task, and those come from code, not from user input.

import sqlite3

def connect(path=":memory:"):
    conn = sqlite3.connect(path)
    conn.row_factory = sqlite3.Row
    conn.execute("""
        CREATE TABLE IF NOT EXISTS tasks (
            id         INTEGER PRIMARY KEY AUTOINCREMENT,
            title      TEXT NOT NULL,
            done       INTEGER NOT NULL DEFAULT 0,
            deleted_at TEXT
        )
    """)
    return conn

def create_task(conn, title):
    title = title.strip()
    if not title:
        raise ValueError("title must not be empty")
    with conn:
        cur = conn.execute("INSERT INTO tasks (title) VALUES (?)", (title,))
    return cur.lastrowid

def read_task(conn, task_id):
    row = conn.execute(
        "SELECT id, title, done FROM tasks WHERE id = ? AND deleted_at IS NULL",
        (task_id,),
    ).fetchone()
    return dict(row) if row else None

def list_tasks(conn):
    rows = conn.execute(
        "SELECT id, title, done FROM tasks WHERE deleted_at IS NULL ORDER BY id"
    ).fetchall()
    return [dict(r) for r in rows]

def update_task(conn, task_id, *, title=None, done=None):
    fields, params = [], []
    if title is not None:
        if not title.strip():
            raise ValueError("title must not be empty")
        fields.append("title = ?"); params.append(title.strip())
    if done is not None:
        fields.append("done = ?"); params.append(int(bool(done)))
    if not fields:
        return False
    params.append(task_id)
    with conn:
        cur = conn.execute(
            f"UPDATE tasks SET {', '.join(fields)} WHERE id = ? AND deleted_at IS NULL",
            params,
        )
    return cur.rowcount == 1

def delete_task(conn, task_id):
    with conn:
        cur = conn.execute(
            "UPDATE tasks SET deleted_at = datetime('now') "
            "WHERE id = ? AND deleted_at IS NULL",
            (task_id,),
        )
    return cur.rowcount == 1

if __name__ == "__main__":
    conn = connect()
    a = create_task(conn, "Write migration")
    b = create_task(conn, "Add validation")
    update_task(conn, a, done=True)
    delete_task(conn, b)
    print(list_tasks(conn))
    print(read_task(conn, b))
    print(delete_task(conn, b))

Running python3 crud.py produces:

[{'id': 1, 'title': 'Write migration', 'done': 1}]
None
False

The second task was deleted, so it no longer appears in the list, reading it returns None, and deleting it again reports that nothing changed.

Exposing it over HTTP

Wrapping those functions in a web framework turns them into a REST API. With Flask (pip install flask), each route maps one HTTP method to one CRUD function and translates outcomes into status codes:

from flask import Flask, abort, jsonify, request
import crud

app = Flask(__name__)
conn = crud.connect("tasks.db")

@app.post("/tasks")
def create():
    data = request.get_json(silent=True) or {}
    try:
        task_id = crud.create_task(conn, str(data.get("title", "")))
    except ValueError as exc:
        return jsonify(error=str(exc)), 422
    return jsonify(crud.read_task(conn, task_id)), 201

@app.get("/tasks")
def index():
    return jsonify(crud.list_tasks(conn))

@app.get("/tasks/<int:task_id>")
def show(task_id):
    task = crud.read_task(conn, task_id)
    if task is None:
        abort(404)
    return jsonify(task)

@app.patch("/tasks/<int:task_id>")
def update(task_id):
    data = request.get_json(silent=True) or {}
    try:
        found = crud.update_task(conn, task_id,
                                 title=data.get("title"), done=data.get("done"))
    except ValueError as exc:
        return jsonify(error=str(exc)), 422
    if not found:
        abort(404)
    return jsonify(crud.read_task(conn, task_id))

@app.delete("/tasks/<int:task_id>")
def destroy(task_id):
    crud.delete_task(conn, task_id)
    return "", 204

Exercising it with Flask's test client (Flask 3.1) returned these status codes and bodies (the 404 body is an HTML error page, so it has no JSON):

POST   /tasks {"title": "Ship CRUD demo"}  -> 201 {'done': 0, 'id': 1, 'title': 'Ship CRUD demo'}
POST   /tasks {"title": "  "}              -> 422 {'error': 'title must not be empty'}
PATCH  /tasks/1 {"done": true}             -> 200 {'done': 1, 'id': 1, 'title': 'Ship CRUD demo'}
DELETE /tasks/1                            -> 204
DELETE /tasks/1                            -> 204
GET    /tasks/1                            -> 404 None

A single shared SQLite connection is fine for a demo but not for a multi-threaded production server; real applications open a connection per request or use a pool, typically through an ORM such as SQLAlchemy or Django's.

What separates a toy from a real CRUD app

Validation

Every create and update must check input before it reaches the database: required fields, lengths, formats, ranges, and references to other records that must exist. The example rejects empty titles with 422 Unprocessable Content. Database constraints (NOT NULL, UNIQUE, foreign keys) are a second line of defense, not a replacement, because their error messages are not fit for end users.

Soft deletes

The example never runs DELETE. It sets deleted_at and filters on deleted_at IS NULL everywhere it reads. Soft deletes make undo and auditing possible and keep foreign keys intact. The costs: every query must remember the filter, unique constraints need to account for deleted rows, and data protection rules may still require a real, permanent delete after some period.

Authorization

Authentication tells you who the user is; authorization decides whether that user may touch this particular record. The most common CRUD security bug is an endpoint like PATCH /tasks/42 that checks the user is logged in but not that task 42 belongs to them (an insecure direct object reference). Scope every query by owner or tenant, for example WHERE id = ? AND owner_id = ?, and return 404 rather than 403 when you do not want to reveal that the record exists.

Idempotency

An operation is idempotent if repeating it has the same effect as doing it once. By the HTTP specification, GET, PUT and DELETE are idempotent and POST is not. That is why the second DELETE /tasks/1 above still returns 204: the end state, "task 1 does not exist", is the same. (Returning 404 the second time is also a defensible choice; what matters is that nothing else changes.) POST is different: a client that retries after a timeout may create two records. APIs that handle payments or orders solve this with an idempotency key, a client-generated unique value sent in a header and stored with the created record so a retry returns the original result.

Lists that scale

"Read all" must become paginated (?page= or cursor-based ?after=), filterable and sortable once a table holds more than a few hundred rows, and the columns used for filtering need indexes.

Concurrency

Two users editing the same record can overwrite each other's changes. Optimistic locking handles this: keep a version column or an updated_at timestamp, require the client to send the value it read, and add it to the WHERE clause of the UPDATE. If zero rows match, someone else changed the record first and the API returns 409 Conflict (or 412 when using If-Match headers).

When CRUD is not enough

CRUD assumes that the current state of a record is what matters. Some domains outgrow that model:

Business actions that are not field edits. "Cancel order" or "approve invoice" involve rules, side effects and notifications. Modelling them as PATCH status=cancelled scatters that logic. Explicit endpoints such as POST /orders/42/cancel are clearer.
CQRS (Command Query Responsibility Segregation). The write model (commands that enforce rules) and the read model (queries optimized for screens and reports) are separated, sometimes into different stores. Useful when reads and writes have very different shapes or loads; overkill for most apps.
Event sourcing. Instead of storing current state, you store the sequence of events that happened ("ItemAdded", "OrderShipped") and derive state by replaying them. You get a complete audit trail and the ability to rebuild views, at the price of significantly more complexity around schema evolution and querying.

Most applications start as CRUD and stay that way successfully. Reach for these patterns when a concrete problem demands them, not in advance.

Common pitfalls

Building SQL with string formatting from user input instead of parameters.
Using GET for actions that change data; crawlers, prefetchers and link previews will trigger them.
Forgetting the soft-delete filter in one query, so "deleted" records reappear in an export or a count.
Returning 200 with an error message in the body, which breaks clients that rely on status codes.
Checking login but not ownership on update and delete endpoints.

Summary

A CRUD application creates, reads, updates and deletes records. Those operations map to SQL INSERT/SELECT/UPDATE/DELETE and, in a REST API, to POST/GET/PUT or PATCH/DELETE. The four functions are easy; the work that makes a CRUD app production-ready is validation, authorization per record, sensible status codes, idempotent retries, pagination and concurrency control. When behavior matters more than state, consider explicit command endpoints, CQRS or event sourcing.