Queries and CRUD, Conceptually

Build the mental model

A query is simply how you ask a database to do something - find, add, change, or remove rows.

A tiny example like `SELECT * FROM users WHERE active = true;` asks the database to return every active user - the full syntax lives in the SQL tutorial.

A single API endpoint might trigger several database operations behind the scenes - creating an order might update stock, write an audit log, and record a notification, all in one request.

CRUD ACROSS THREE LAYERS
------------------------
CONCEPT      SQL STATEMENT      REST API VERB
-------      -------------      -------------
Create   ->  INSERT         ->  POST
Read     ->  SELECT         ->  GET
Update   ->  UPDATE         ->  PATCH / PUT
Delete   ->  DELETE         ->  DELETE

One API call can trigger several DB operations,
e.g. POST /orders may INSERT an order AND UPDATE
stock AND INSERT an audit log row - not just one.

Connect it to a real scenario

Real applications build and send queries through a library or ORM. Read is usually the safest operation, while Create, Update, and Delete change data permanently.

• a filter that is too broad
• a filter that is missing
• a filter that is simply wrong

Any of these can affect far more rows than intended, sometimes silently. Before modifying data, check exactly which rows the filter would match first.

The runnable example below implements create/read/update/delete and includes a safety check that warns when a filter matches more rows than expected.

• A query asks the database to perform an operation, and CRUD maps onto both SQL statements and REST verbs
• An API action and a database operation are related but not identical - one endpoint can trigger several database operations
• Always check exactly which rows a filter matches before running an UPDATE or DELETE

Try the working example

function createRow(table, row) {
  const nextId = table.length ? Math.max(...table.map((r) => r.id)) + 1 : 1;
  const newRow = { id: nextId, ...row };
  table.push(newRow);
  return newRow;
}

function readRows(table, filter) {
  return table.filter((r) => Object.keys(filter).every((k) => r[k] === filter[k]));
}

function updateRows(table, filter, changes, options = {}) {
  const { allowMultiple = false } = options;
  const matches = readRows(table, filter);
  if (matches.length > 1 && !allowMultiple) {
    return {
      ok: false,
      warning: `Filter ${JSON.stringify(filter)} matched ${matches.length} rows; expected at most 1.`,
      updated: 0,
    };
  }
  matches.forEach((r) => Object.assign(r, changes));
  return { ok: true, updated: matches.length };
}

const users = [];
createRow(users, { name: "Aye", status: "active" });
createRow(users, { name: "Bo", status: "active" });
createRow(users, { name: "Cho", status: "inactive" });

console.log("Read active:", readRows(users, { status: "active" }));
console.log("Unsafe update (matches 2):", updateRows(users, { status: "active" }, { status: "archived" }));
console.log("Safe update (matches 1):", updateRows(users, { id: 1 }, { status: "archived" }));

Read active: [ {id:1,name:'Aye',status:'active'}, {id:2,name:'Bo',status:'active'} ]
Unsafe update (matches 2): { ok: false, warning: 'Filter {"status":"active"} matched 2 rows; expected at most 1.', updated: 0 }
Safe update (matches 1): { ok: true, updated: 1 }

The unsafe call is blocked because two rows match the filter; the safe call succeeds because filtering by id matches exactly one row.

Wikipedia: CRUD (create, read, update and delete) — How Databases Work