Real-World CRUD Operations in SQL
Introduction
CRUD represents the four fundamental data operations used by almost every database-driven application: Create adds data, Read retrieves it, Update changes it, and Delete removes it. In SQL, these responsibilities are usually implemented with INSERT, SELECT, UPDATE, and DELETE. Learning those four statements is necessary, but production CRUD is much broader than syntax.
| CRUD operation | SQL statement | Purpose |
|---|---|---|
| Create | INSERT | Store a new record or business event |
| Read | SELECT | Retrieve an authorized and meaningful result |
| Update | UPDATE | Change valid fields while preserving invariants |
| Delete | DELETE | Remove, archive, or logically retire data |
A real operation must also enforce validation, constraints, ownership, authorization, transactions, relationships, concurrency, auditability, and recovery. An INSERT that accepts duplicate customers, a SELECT that exposes another tenant's order, or an UPDATE that silently overwrites a concurrent edit is not production-quality CRUD even when the statement executes successfully.
REAL-WORLD CRUD = SQL OPERATION
+ BUSINESS RULES
+ DATA INTEGRITY
+ SECURITY
+ CONCURRENCY
+ TRANSACTION SAFETY
CRUD in a Real Application
Consider an e-commerce application. Create operations register customers, add products, place orders, and record payments. Read operations search the catalog, display order history, and track shipments. Update operations reserve inventory, change an address, or advance an order through its lifecycle. Delete operations remove cart items, discard drafts, expire sessions, or retire accounts.
These operations are connected. Placing an order reads product availability, creates an order and line items, updates inventory, and may create an outbox event for another service. Cancelling it checks the current status, updates the order, returns inventory, and records history. CRUD is therefore part of a business workflow rather than four isolated buttons.
Application Architecture and Responsibility
A common request travels from the user interface to an API, through a service layer and repository, and finally to the database. Each layer has a distinct responsibility. The API parses input and authenticates the caller. The service applies use-case rules and chooses the transaction boundary. The repository executes parameterized SQL. The database enforces durable constraints and coordinates concurrent sessions.
Duplicating every rule in every layer creates inconsistency, but trusting only one layer is also unsafe. A user-friendly email validation message belongs in the application, while a UNIQUE constraint remains the final protection against two simultaneous registrations. The service decides whether an order can be cancelled; a current-state predicate makes that transition atomic at the database.
Example Commerce Schema
The examples use customers, products, orders, and order_items. Primary keys identify rows, foreign keys preserve relationships, and constraints express facts that must always be true. Monetary amounts use exact decimal types, timestamps are stored consistently, and status values have a controlled domain.
CREATE TABLE customers (
customer_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email VARCHAR(320) NOT NULL UNIQUE,
display_name VARCHAR(120) NOT NULL,
status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
version_no INTEGER NOT NULL DEFAULT 1,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
deleted_at TIMESTAMP NULL
);
CREATE TABLE products (
product_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
sku VARCHAR(60) NOT NULL UNIQUE,
product_name VARCHAR(200) NOT NULL,
price DECIMAL(12, 2) NOT NULL CHECK (price >= 0),
available_quantity INTEGER NOT NULL CHECK (available_quantity >= 0)
);
An order stores the customer, current status, total, and creation time. Each order item stores the purchased quantity and the price charged at that moment. Keeping the historical unit price is important because a later product-price update must not rewrite the commercial facts of an existing order.
Data Modeling Comes Before CRUD
Reliable CRUD starts with a model that reflects identity, optionality, cardinality, ownership, and history. If the model does not distinguish a customer's current address from a shipping address captured for an order, no query can reconstruct the original shipment accurately after the profile changes.
Before writing endpoints, define primary and business keys, required and optional columns, relationship rules, valid status transitions, delete behavior, audit requirements, and tenant boundaries. Decide whether a record represents current state, an immutable event, or a historical snapshot. These choices determine what CRUD is allowed to do.
Create: Customer Registration
A basic registration inserts normalized values and lets the database generate identity, defaults, and timestamps. The application validates required fields, length, and supported format before sending the statement, but database constraints still protect every writer.
INSERT INTO customers (email, display_name)
VALUES (:normalized_email, :display_name)
RETURNING customer_id, status, created_at;
Parameters keep values separate from SQL structure and prevent injection. RETURNING is supported by several systems; SQL Server commonly uses OUTPUT, while other systems may expose generated-key APIs. Never calculate a new identifier with MAX(id) + 1 or run a later MAX(id) query to discover the row. Concurrent sessions make those patterns incorrect.
Duplicate Create and Unique Constraints
A preliminary query can provide a fast, friendly duplicate warning, but it cannot be the only protection. Two requests can both observe that an email is absent and then both attempt insertion. A UNIQUE constraint serializes that race correctly: one succeeds and the other receives a duplicate-key error.
The application should translate the expected constraint violation into a domain response such as "an account already uses this email." It should not return raw database details. Constraint names help error handling identify which business rule failed without parsing vendor-specific error text.
Creating an Order Atomically
Order creation is a multi-table workflow. The system validates the customer, reserves stock, inserts the order, inserts line items with historical prices, calculates or verifies the total, and records a durable event. Those steps should normally succeed or fail as one transaction.
BEGIN;
UPDATE products
SET available_quantity = available_quantity - :quantity
WHERE product_id = :product_id
AND available_quantity >= :quantity;
-- Require exactly one affected product row.
INSERT INTO orders (customer_id, status, total_amount)
VALUES (:customer_id, 'PENDING', :total_amount);
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (:order_id, :product_id, :quantity, :unit_price);
COMMIT;
The atomic inventory UPDATE avoids a check-then-act race. If its affected-row count is zero, the product is missing or stock is insufficient, so the transaction must stop. A separate SELECT followed by an unconditional UPDATE could let two customers purchase the same last item.
Create Idempotency and Retries
Clients retry when a response times out, even though the server may have committed. Repeating a create without protection can produce duplicate orders or payments. An idempotency key gives one logical request a stable identity. Store the key under a unique constraint with the operation result, and return the prior result when the same request is repeated.
The key must be scoped appropriately, often by tenant or customer, and reused only for the same request payload. Blindly treating every unique violation as success is unsafe because the repeated key could accompany different data. Idempotency is a business protocol supported by schema design, not merely an INSERT option.
Read: Retrieve Only Intended Data
A production read defines its result grain, authorization scope, columns, ordering, and expected cardinality. Avoid SELECT * in application queries because it couples callers to schema growth, transfers unnecessary sensitive data, and can prevent efficient covering indexes.
SELECT customer_id, display_name, status, created_at
FROM customers
WHERE customer_id = :customer_id
AND deleted_at IS NULL;
If exactly one row is expected, handle zero and multiple rows explicitly. A zero result may mean not found, not visible, or deleted. Multiple rows indicate a violated assumption and should not be silently reduced to the first row.
Authorization-Aware and Tenant-Aware Reads
Possessing an identifier does not grant access. An insecure direct object reference occurs when a user changes an ID and receives another user's data. Scope the query by ownership, organization, or tenant, and derive those security values from the authenticated context rather than trusting request fields.
SELECT order_id, status, total_amount, created_at
FROM orders
WHERE order_id = :order_id
AND customer_id = :authenticated_customer_id
AND tenant_id = :authenticated_tenant_id;
Applications sometimes return the same not-found response for absent and unauthorized objects to avoid revealing existence. The policy depends on the product, but SQL must enforce the chosen visibility boundary consistently.
Reading Relationships without Losing Meaning
JOINs should match the intended result grain. An order header joined to five items produces five rows; that is correct for a line-level result but not for counting orders. Accidental duplication is often a modeling or join problem, not something to hide with DISTINCT.
SELECT o.order_id,
o.status,
i.product_id,
i.quantity,
i.unit_price
FROM orders AS o
JOIN order_items AS i ON i.order_id = o.order_id
WHERE o.order_id = :order_id
AND o.customer_id = :customer_id
ORDER BY i.order_item_id;
For APIs, the repository may return header and item rows that the service groups into a nested response, or it may run two bounded queries. Choose based on clarity and performance rather than forcing every shape into one complex statement.
Search, Sorting, and Pagination
Catalog and history pages need controlled filters, deterministic ordering, and bounded page sizes. Dynamic values remain parameters, while dynamic identifiers such as sort columns must come from a server-side allowlist. SQL parameters cannot safely replace arbitrary keywords or column names.
SELECT product_id, sku, product_name, price
FROM products
WHERE product_name LIKE :search_pattern
AND is_active = TRUE
ORDER BY product_name, product_id
FETCH FIRST :page_size ROWS ONLY;
A unique tie-breaker prevents rows from shifting unpredictably when many records share the same name or timestamp. OFFSET pagination is simple for small result sets but becomes slower and less stable at deep pages. Keyset pagination continues after the last ordered values and is usually better for large feeds.
WHERE (created_at, order_id) < (:last_created_at, :last_order_id)
ORDER BY created_at DESC, order_id DESC
FETCH FIRST :page_size ROWS ONLY;
Read Performance and Index Strategy
Indexes should support real predicates, joins, and ordering rather than every column independently. A tenant order-history query may benefit from an index beginning with tenant_id and customer_id followed by created_at. An index that matches neither the filter nor order can still force a large scan or sort.
Indexes accelerate reads but add storage and write cost because INSERT, UPDATE, and DELETE must maintain them. Use execution plans and production-like data distribution to evaluate important queries. Fast sample queries against one hundred rows do not predict behavior at millions of rows.
Update: Change Only Allowed Fields
An update should target a stable identity, enforce ownership, change only authorized mutable fields, and validate the affected-row count. Narrow statements reduce accidental overwrites and make intent easier to review.
UPDATE customers
SET display_name = :display_name,
phone_number = :phone_number,
updated_at = CURRENT_TIMESTAMP
WHERE customer_id = :customer_id
AND tenant_id = :authenticated_tenant_id
AND deleted_at IS NULL;
Do not map every JSON field directly to every table column. Immutable identifiers, account status, role, ownership, price, and audit fields often require separate privileged operations. Mass assignment can turn an ordinary profile endpoint into an authorization vulnerability.
PATCH, Omitted Fields, and NULL
For partial updates, omitted and explicit NULL usually have different meanings. Omitted means leave the value unchanged; NULL means clear it when the schema and business rule permit that. Converting both into one default value destroys the caller's intent.
A service can build a statement from an allowlist of present fields or use separate operation-specific updates. Avoid broad COALESCE patterns when NULL is a legitimate requested value, because they make clearing a column impossible. Validate type, range, format, and authorization before executing the update.
Status Transitions as Business Operations
Changing order status is not a generic field edit. It is a state transition with allowed sources, side effects, and authorization. Encode the expected current state in the predicate so the transition is atomic.
UPDATE orders
SET status = 'SHIPPED',
shipped_at = CURRENT_TIMESTAMP
WHERE order_id = :order_id
AND status = 'PAID';
Require exactly one affected row. Zero means the order is missing, invisible, or no longer PAID; it must not be reported as a successful shipment. When the transition also creates shipment records or history, perform the related writes in the same transaction.
Optimistic Locking and Lost Updates
A lost update occurs when two users read version 4, edit independently, and both write a complete record. The second write silently replaces the first. Optimistic locking adds the expected version to the predicate and increments it on success.
UPDATE customers
SET display_name = :display_name,
version_no = version_no + 1
WHERE customer_id = :customer_id
AND version_no = :expected_version;
If no row changes, the caller's representation is stale. The application can return a conflict and ask the user to reload or perform a deliberate merge. This is safer than silently accepting stale state, especially for collaborative editing and administrative screens.
Atomic Counters and Inventory Updates
Do not read a counter, add in application memory, and write the result when concurrent sessions can act. Express relative changes atomically in SQL and include valid bounds.
UPDATE products
SET available_quantity = available_quantity - :quantity
WHERE product_id = :product_id
AND available_quantity >= :quantity;
The database evaluates the expression against the current locked row. Check the affected count and keep a CHECK constraint as final protection. Similar patterns apply to claim counts, balances where domain rules permit direct arithmetic, and limited-capacity reservations.
Delete: Hard Removal versus Business Retirement
Hard delete physically removes a row. It suits temporary data, cart items, expired sessions, and records whose removal is legally and operationally intended. It should still include ownership and a precise predicate.
DELETE FROM cart_items
WHERE cart_item_id = :cart_item_id
AND cart_id = :authenticated_cart_id;
Soft delete marks a record as inactive, often with deleted_at and deleted_by. It preserves audit and restore options but complicates uniqueness, joins, reporting, and every read query. Use it because the domain requires retention or recovery, not as an automatic default for all tables.
UPDATE customers
SET status = 'DELETED',
deleted_at = CURRENT_TIMESTAMP,
deleted_by = :actor_id
WHERE customer_id = :customer_id
AND deleted_at IS NULL;
Foreign Keys and Delete Behavior
Foreign keys prevent orphaned records. Their referential action must match the domain. RESTRICT can block deletion while dependent orders exist. CASCADE can remove truly subordinate data such as disposable detail records. SET NULL can preserve a record after an optional relationship ends.
Cascade is powerful and must be reviewed for its full path. Deleting one parent can remove a much larger graph than the initiating query suggests. Preview destructive operations, count the target rows, understand dependencies, run inside an appropriate transaction, and verify the result before commit.
Transactions and ACID Boundaries
A transaction groups changes that must be atomic. Atomicity prevents partial workflows, consistency combines application rules with constraints, isolation defines interaction with concurrent work, and durability preserves committed results. The transaction boundary should correspond to one business invariant, not merely one repository method.
Keep transactions short: validate independent input before opening one, lock only needed rows, use a consistent access order, and avoid waiting for remote services while locks are held. Always commit or roll back in a finally-style cleanup path so a connection does not return to the pool with an open transaction.
Concurrency, Locks, and Deadlocks
Optimistic locking works well when conflicts are uncommon. Pessimistic row locking is useful when a workflow must read current state and make several dependent decisions before updating, but it increases blocking and must be kept brief. Atomic conditional statements are often simpler than either explicit strategy.
Deadlocks can occur when transactions acquire resources in different orders. The database resolves them by aborting one participant. Applications should use consistent ordering, small transactions, useful indexes, and bounded retries for errors known to be transient. Retrying a non-idempotent business operation blindly can duplicate effects.
Security and Least Privilege
Parameterized SQL protects values from injection, but complete CRUD security also requires authentication, object-level authorization, tenant scoping, field allowlists, and least-privilege database accounts. A runtime account should not normally alter schemas, disable constraints, or read unrelated sensitive tables.
Passwords require purpose-built hashing and should never be readable or logged. Payment and identity data require strict selection, masking, encryption, and retention controls. Logs should record useful identifiers and outcomes without copying secrets, tokens, full credentials, or unnecessary personal data.
Auditing and Historical State
Important changes need an answer to who changed what, when, from which request, and why. An audit entry can store actor, entity, operation, timestamp, correlation ID, and safe before-and-after fields. Database triggers, change-data capture, application events, or explicit history tables can provide this record, each with different context and operational tradeoffs.
Current status and status history serve different purposes. The current row answers what is true now; an append-only history explains how it became true. Do not overwrite historical prices, addresses, approvals, or policy versions when those facts must remain accurate for prior transactions.
Bulk CRUD and Data Imports
Bulk work should use set-based statements or database batch APIs rather than thousands of independent round trips. Large mutations may need bounded batches to control locks, transaction logs, replication lag, and recovery scope. Batch on a stable key, not OFFSET against a set that changes as it is processed.
Imports benefit from staging tables. Load and parse external data into staging, validate types and duplicates, reconcile business keys, report rejected rows, and then merge valid records into production tables within controlled transactions. This separates unreliable external input from trusted core data.
Upsert and Conflict Handling
Upsert means insert when a conflict key is absent and update when it exists. Syntax differs across PostgreSQL, MySQL, SQL Server, Oracle, and SQLite. The conflict key must correspond to a real unique constraint; otherwise the operation has no reliable definition of "same record."
Upsert is useful for synchronized reference data and repeatable imports, but it does not replace business validation. Decide which columns may change, preserve immutable creation data, and consider concurrency behavior. For commands where duplicate creation should be an error, a normal INSERT with explicit conflict handling is clearer.
Caching, Events, and Distributed Workflows
A database commit does not automatically refresh every cache or publish every event. Updating the database and then publishing to a message broker creates a dual-write failure window: either action can succeed alone. A transactional outbox stores the domain change and an event record in one database transaction; a worker publishes the event later and marks it processed.
In microservices, each service should own its data and expose behavior through APIs or events rather than allowing other services to update its tables. Distributed CRUD often becomes eventually consistent, so consumers need idempotency, versioning, and tolerance for delayed or repeated events.
Mapping CRUD to HTTP APIs
CRUD commonly maps to POST for creation, GET for reads, PATCH or PUT for updates, and DELETE for removal. The mapping is useful but not the business model. Commands such as cancel order, approve refund, or reserve seat are clearer as named operations because they carry transition rules and side effects.
| API action | Typical result | Important checks |
|---|---|---|
| POST /orders | 201 Created | Validation, transaction, idempotency |
| GET /orders/{id} | 200 or 404 | Ownership, tenant, projection |
| PATCH /customers/{id} | 200, 204, or 409 | Field allowlist, version, row count |
| DELETE /cart-items/{id} | 204 or 404 | Ownership, dependency, repeat behavior |
Translate expected database outcomes into stable API errors: validation failure, conflict, not found, forbidden, or dependency violation. Do not expose internal SQL, table names, connection details, or stack traces.
A Complete Order Placement Flow
An order request demonstrates mature CRUD. Authenticate the customer, validate line items, normalize the idempotency key, and begin a transaction. Lock or atomically reserve each product in a consistent order. Insert the order, insert historical line items, calculate the trusted total, and write an outbox event. Commit, then return the generated order representation.
If inventory is insufficient, any earlier reservations must roll back. If the client times out after commit and retries, the idempotency record returns the original order instead of creating another. If event publication is temporarily unavailable, the committed outbox row preserves eventual delivery. Each mechanism addresses a different failure mode.
A Complete Order Cancellation Flow
Cancellation begins with authorization and an allowed current state. An atomic predicate changes PENDING or PAID to CANCELLED according to policy. The same transaction restores inventory where applicable, adds status history, records who cancelled, and writes an outbox event.
A zero affected-row count is not generic success. The order may be absent, belong to another customer, already be shipped, or already be cancelled. The service interprets that state deliberately. Repeating an already completed cancellation may return the current result when the operation is designed to be idempotent.
Testing Create Operations
Test valid creation, required fields, boundary lengths, malformed data, duplicate business keys, generated IDs, defaults, foreign-key failures, and rollback when a later statement fails. Run concurrent duplicate requests to prove the database constraint, not timing luck, determines the result.
For idempotent create, repeat the same key, send the same key with a different payload, simulate a response timeout after commit, and verify that only one business record exists. Assert database state and externally visible behavior.
Testing Read Operations
Test found, missing, deleted, and unauthorized records. Verify tenant isolation, selected fields, NULL representation, relationship cardinality, deterministic order, page boundaries, duplicate sort values, and empty pages. Confirm that sensitive columns never appear accidentally.
Use production-like volume to test execution plans and deep pagination. Verify that search wildcards, case rules, date boundaries, time zones, and optional filters behave as documented. A read that returns correct rows slowly can still fail its production requirement.
Testing Update Operations
Test one-row success, zero rows, stale versions, forbidden fields, explicit NULL, omitted fields, invalid transitions, unique conflicts, check constraints, and rollback of related side effects. Verify that unchanged fields retain their values and historical records remain untouched.
Concurrency tests should let two sessions update the same version and confirm that only one succeeds when conflicts matter. Race inventory reservations and assert that stock never falls below zero. Validate affected-row counts instead of checking only that no exception occurred.
Testing Delete Operations
Test ownership, existing dependencies, restrictive and cascading foreign keys, already-deleted rows, restoration where supported, and retention policies. Verify that soft-deleted records disappear from ordinary reads but remain available to authorized audit or recovery workflows.
For bulk cleanup, test the cutoff boundary, batch restart, transaction-log impact, replication lag, and stop conditions. A cleanup query that is logically correct can still be operationally unsafe when it removes millions of rows in one transaction.
Common CRUD Mistakes
- Executing UPDATE or DELETE without a precise, reviewed WHERE clause.
- Using SELECT * and returning confidential or unstable columns.
- Concatenating request values into SQL instead of using parameters.
- Relying on application checks without database constraints.
- Splitting one business invariant across independent commits.
- Trusting ownership or tenant identifiers supplied by the client.
- Ignoring zero, excessive, or ambiguous affected-row counts.
- Using read-modify-write for counters and inventory.
- Blindly retrying operations that can apply an effect twice.
- Using hard delete everywhere or soft delete without a retention need.
- Paginating without deterministic order and a unique tie-breaker.
- Overwriting historical values with current reference data.
- Assuming an ORM removes the need to understand SQL and transactions.
- Logging credentials, tokens, payment data, or complete sensitive payloads.
Production CRUD Checklist
- Define the business operation, target identity, ownership, and expected cardinality.
- Validate and normalize input while preserving omitted versus explicit NULL semantics.
- Use parameters for values and allowlists for dynamic SQL structure.
- Enforce durable invariants with keys, constraints, and appropriate relationships.
- Choose a transaction boundary that protects the complete business invariant.
- Use atomic predicates, optimistic versions, or locks for concurrent decisions.
- Check affected rows and translate expected conflicts into stable domain outcomes.
- Protect retries with idempotency where duplicate effects are possible.
- Preserve necessary history and audit context without recording secrets.
- Test correctness, authorization, concurrency, rollback, performance, and recovery.
- Monitor latency, errors, lock waits, deadlocks, pool usage, and unusual row counts.
- Document how to stop, resume, verify, and recover high-risk bulk operations.
Interview Questions and Answers
What does CRUD mean? Create, Read, Update, and Delete, commonly implemented with INSERT, SELECT, UPDATE, and DELETE.
Why is CRUD more than SQL syntax? Production operations must also enforce business rules, authorization, integrity, concurrency, transactions, observability, and recovery.
Why is check-then-insert insufficient for uniqueness? Concurrent requests can both pass the check; a UNIQUE constraint is the final atomic protection.
How do you prevent a lost update? Use optimistic version checks, suitable locks or isolation, atomic expressions, or an intentional merge policy.
Why check affected-row counts? They reveal missing targets, stale state, authorization mismatch, or an unexpectedly broad predicate.
What is soft deletion? Marking a row deleted while retaining it, usually for audit, retention, or restoration.
When should operations share a transaction? When partial success would violate one business invariant.
How do you make create retry-safe? Use an idempotency key backed by a unique constraint and persist the prior result.
Why avoid SELECT *? It transfers unnecessary data, exposes sensitive columns, weakens contracts, and couples code to schema changes.
What is the transactional outbox? A pattern that commits business data and a publishable event record in the same local transaction.
Key Takeaway
Real-world CRUD is the disciplined management of business state, not four isolated SQL statements. A mature create is validated, constrained, transactional, and retry-aware. A mature read is authorized, bounded, deterministic, and efficient. A mature update protects allowed fields and concurrent state. A mature delete respects dependencies, retention, audit, and recovery.
PRODUCTION CRUD = PRECISE INTENT
+ VALIDATED INPUT
+ DATABASE CONSTRAINTS
+ AUTHORIZATION SCOPE
+ TRANSACTION CONTROL
+ CONCURRENCY SAFETY
+ VERIFIED RESULTS
+ AUDIT AND RECOVERY
When every operation defines its identity, invariant, security boundary, conflict behavior, and failure response, CRUD becomes a reliable foundation for the application rather than a collection of fragile database calls.