CRUD Operations

Why it matters

Many data defects are CRUD defects: a field the form accepts is never saved, an edit changes the wrong record or wipes a field nobody touched, a delete leaves related rows behind or removes too much. The screen often hides them: a green "Saved" message proves only that the request ended without an error, not that the right data reached the right row.

CRUD is also a simple coverage checklist. For every kind of record a feature handles, ask whether all four operations exist, who may perform each one and whether each one was tested. Create and read get exercised by almost every test, because other tests need data; update and delete are tested only when someone plans it, and they are the operations that can lose or expose data.

The four operations and what each must do

Each operation has an expected effect on the stored data, and that effect is what you test. The primary key from the previous lesson ties them together: create gives the record its key; read, update and delete use it to reach exactly one record.

• Create: one new record with a new primary key, every submitted field in the right column, and the values the system sets itself, such as a default status and created_at.
• Read: the stored data is returned as it is, one record by its key or a filtered list, and reading changes nothing.
• Update: the intended fields of the intended record change, everything else stays as it was, and updated_at, if the table has it, moves forward.
• Delete: the record disappears from the product. In the database it is either removed (a hard delete) or kept and marked, for example with a deleted_at timestamp (a soft delete). A hard delete of a row that other rows reference follows the foreign key's delete rule from the previous lesson: the related rows are deleted too (CASCADE), kept with their reference set to NULL (SET NULL), or the delete is refused (RESTRICT or NO ACTION). A soft delete is an update, so no delete rule runs and the application must handle the related rows itself.

One operation, three layers: UI, HTTP and SQL

A click in the UI sends an HTTP request to the API, and the API runs one or more SQL statements. An HTTP request names a method, the action, and a path, the target: GET /contacts/7 asks for contact 7, and the response carries a status code such as 200 OK. PUT sends the complete new record, PATCH only the fields that change. On the SQL side, INSERT adds rows, SELECT reads them, UPDATE changes values in existing rows and DELETE removes rows; a WHERE clause limits the statement to the rows that match its condition.

In a REST API, a common style in which each path names a record or a collection of records, the usual mapping is: create is POST and INSERT, read is GET and SELECT, update is PUT or PATCH and UPDATE, and delete is DELETE in both layers. These four statements are the core of what is usually called SQL's data manipulation language (DML). The lessons on HTTP methods and REST API basics cover the requests in depth, and the next lesson teaches SELECT.

The mapping is a convention, not a rule, and where it differs is where tests should look. One POST /orders can run an INSERT for the order and one for each item. A delete button can run an UPDATE that sets deleted_at; RFC 9110 defines DELETE as removing the link between the URI and the resource, not as a promise to erase data. A cancel button may send POST /appointments/7/cancel, an update in CRUD terms. The API contract, the developer or the database tells you what really happens.

CRUD    UI action        HTTP request        SQL statement
------  ---------------  ------------------  --------------------------------------------
Create  Add contact      POST /contacts      INSERT INTO contacts (...) VALUES (...)
Read    Open contact 7   GET /contacts/7     SELECT * FROM contacts WHERE id = 7
Update  Edit phone       PATCH /contacts/7   UPDATE contacts SET phone = ... WHERE id = 7
Delete  Delete contact   DELETE /contacts/7  DELETE FROM contacts WHERE id = 7

Try it: SQL INSERT Generator

Verify in the product first, then in the database

Perform each operation as a user or client would, through the UI or the API. Writing rows straight into the database skips the validation and logic you are meant to test; direct INSERT statements are for preparing preconditions. Then check the result at the points below. In negative tests, such as invalid input or a user without permission, the database must show no change at all, not even a partial row.

Work safely: use SELECT for checks, and run INSERT, UPDATE or DELETE only in a test environment you are allowed to change, never in production without explicit permission. An UPDATE or DELETE without a WHERE clause changes every row in the table, so write the WHERE first and run it in a SELECT to see which rows it matches.

• The response: the message on screen, or the status code and body from the API.
• A read-back through the product: reload the page or send a separate GET. It catches a screen that only echoes your input and stale cached data; comparing the response before and after an update shows every changed field.
• The database: select the row by its primary key to see what the product may not show: field mapping, defaults, timestamps, soft-delete flags, rounding and the encoding of Arabic text.
• The neighbours: a create added exactly one row; an update left other rows and untouched columns alone; a delete handled related rows as designed, and the record is gone from lists, searches and totals.

Try it: REST API Tester Try it: JSON Diff Try it: SQL Query Validator

Worked example: rescheduling an appointment

Requirement: "A patient can move an upcoming appointment to another free slot with the same doctor. The old slot becomes free again." For the user this is one update; in the database two tables should change.

The tester records the state first: appointment 7 of patient 1203 is in slot 41 at 09:00, and it is the patient's only booked appointment. The tester then reschedules it to 10:30 in the UI, which sends a PATCH, reloads "My appointments", which must show 10:30, and runs the queries at the end of this section. Read each one as "show these columns of the rows that match": IN (41, 58) matches either id, and COUNT(*) returns the number of matching rows. Each query catches a different defect:

• Query 1: if status or patient_id changed, the update touched columns it should not; if slot_id is still 41, the confirmation screen showed only what was sent.
• Query 2: if slot 41 is still booked, nobody can book 09:00 again; if slot 58 is not marked booked, another patient can book 10:30 too. The patient sees nothing wrong; only the database or another patient's view shows it.
• Query 3: if the API inserted a new appointment instead of updating row 7, the count is 2, a double booking the confirmation screen would not show.

-- Before: appointment 7 (patient 1203) is booked in slot 41 at 09:00
-- UI: "Reschedule" to 10:30 -> PATCH /api/appointments/7 {"slot_id": 58} -> 200 OK

-- 1. The appointment changed, and only the intended columns
SELECT id, patient_id, slot_id, status, updated_at
FROM appointments
WHERE id = 7;
-- expected: slot_id 58, patient_id 1203, status 'booked', updated_at later

-- 2. Both slots changed
SELECT id, starts_at, is_booked
FROM slots
WHERE id IN (41, 58);
-- expected: 41 is_booked false, 58 is_booked true

-- 3. No second appointment was created
SELECT COUNT(*)
FROM appointments
WHERE patient_id = 1203 AND status = 'booked';
-- expected: 1, the same as before

Try it: SQL Formatter

Frequently asked questions

References

• RFC 9110: HTTP SemanticsIETF · 2022The meaning of HTTP methods, header fields, status codes and content negotiation, independent of the wire version.
• ISO/IEC 9075-1:2023 — Information technology — Database languages SQL — Part 1: Framework (SQL/Framework)ISO/IEC · 2023The framework part of the international SQL standard, which the dialects of PostgreSQL, MySQL, SQLite and others implement to different degrees.
• SQL As Understood By SQLiteSQLiteThe SQL grammar accepted by SQLite, statement by statement, with railroad diagrams.
• RFC 5789: PATCH Method for HTTPIETF · 2010The PATCH method for partial updates, as opposed to PUT replacing a whole resource.