SQL foundations, ACID, and constraints: engineering in practice

CHAPTER 15

DEEP DIVE · FROM BASICS TO CODE

Understanding: SQL foundations, ACID, and constraints

You can read this chapter before running the experiment. Then return to the live trace and match each concept to a real event.

FIRST, IN PLAIN LANGUAGE

≈

A database is not a large JSON file or passive storage. It checks rules, coordinates competing changes, and chooses how to retrieve rows. A good schema does not merely store data; it prevents states that are impossible for the business.

TECHNICAL FOUNDATION

The relational model describes entities as tables, identifies rows with keys, and encodes relationships and valid values with constraints. PostgreSQL parses declarative SQL, evaluates possible plans, chooses one from statistics, and executes it inside a transaction. ACID describes atomicity, consistency, isolation, and durability. Consistency is a joint responsibility of the schema and business logic, not automatic database magic.

Why it mattersProduction bugs frequently live where concurrent code meets shared state. If an invariant exists only in one Nest service, another worker, migration, or manual query can violate it. Constraints and transactions make critical rules apply to every client.

OFFICIAL RESOURCES

Official PostgreSQL documentationThe primary source for SQL, the planner, indexes, MVCC, transactions, and PostgreSQL maintenance.Data definition and constraintsNOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY, and exclusion constraints.TransactionsBEGIN, COMMIT, ROLLBACK, savepoints, and atomic groups of changes.Query planningHow PostgreSQL turns declarative SQL into an executable plan.

WHERE THE WORK RUNS

01APPLICATION CODErepository · use case · transaction

→

02SQL + DRIVERparameters · pool · protocol

→

03POSTGRESQLparser · planner · executor · MVCC

→

04STORAGEheap pages · indexes · WAL · disk

01 · GLOSSARY

Terms used in this experiment

Understand the words first, then the execution order.

Relational model

A data model built from tables, rows, columns, keys, and constraints. SQL describes a desired result rather than a row-by-row algorithm.

Constraint

A rule checked by the database during writes: NOT NULL, CHECK, UNIQUE, PRIMARY KEY, or FOREIGN KEY.

Invariant

A condition that must remain true, such as a positive order amount or an order referencing an existing customer.

ACID

Atomicity, Consistency, Isolation, and Durability: properties of reliable transactional changes.

WAL

Write-Ahead Log: changes are logged before corresponding data pages to support recovery.

SQLSTATE

A stable machine-readable PostgreSQL error code. Applications should not parse localized error text.

02 · MECHANICS

What happens step by step

Each step maps to an observable runtime state.

01
Model the invariants
Define entities, identity, required values, valid states, and relationships before choosing an ORM.
02
Encode rules in DDL
PRIMARY KEY, UNIQUE, CHECK, and FOREIGN KEY protect data regardless of which service performs the write.
03
Send values as parameters
The driver sends SQL and values separately; $1 does not turn user input into SQL syntax.
04
Open a transaction
BEGIN creates one unit of work whose changes are committed or rolled back together.
05
Receive a structured error
A CHECK violation returns SQLSTATE 23514, which the application maps to a domain result.
06
Verify rollback
The runtime inserts a row inside a transaction, rolls back, and confirms that persistent state did not change.

03 · CONTEXT

Where the result needs context

These details explain why similar code can sometimes produce a different trace.

Consistency is not automatic business logic

The database enforces declared constraints but cannot guess missing invariants. ACID does not create a rule that the schema and transaction never expressed.

NULL uses three-valued logic

Comparing to NULL yields UNKNOWN, so use IS NULL. CHECK also accepts UNKNOWN unless NOT NULL is declared separately.

Types are part of the model

numeric is appropriate for exact decimal arithmetic; timestamptz stores an instant rather than a display time zone.

A migration is a production operation

ALTER TABLE can acquire a strong lock or rewrite a table. Evaluate data size, lock level, rollout, and rollback.

A pool is not a transaction

Every query in one transaction must use the same checked-out client. Independent pool.query calls can use different connections.

Theory

First understand which parts of Node participate in execution.

→

Simplified code

Then remove instrumentation and focus on the central mechanism.

→

Runtime code

Finally match the model to the code that produces the live trace.

04 · Simplified code

A minimal model without instrumentation

src/demos.js · educational snippetJavaScript

const client = await pool.connect();
try {
  await client.query('BEGIN');
  const order = await client.query(
    'INSERT INTO orders(customer_id, amount) VALUES ($1, $2) RETURNING id',
    [customerId, amount],
  );
  await client.query('COMMIT');
  return order.rows[0];
} catch (error) {
  await client.query('ROLLBACK');
  throw error;
} finally {
  client.release();
}

05 · Runtime code

The complete code executed by the scenario

This is not an alternative example: these are the functions and files used by the Run button.

ACTUAL SOURCE

The source is generated from the real server function. Scenarios using a child process or Worker include every participating file.

src/database-lab.js

PostgreSQL runtime699 lines

import { randomUUID } from 'node:crypto';
import { performance } from 'node:perf_hooks';
import pg from 'pg';

const { Pool } = pg;
const STATEMENT_TIMEOUT_MS = 8_000;
const CONNECTION_TIMEOUT_MS = 1_500;

function databaseUrl() {
  return process.env.DATABASE_URL?.trim() || null;
}

function safeIdentifier(prefix) {
  return `${prefix}_${randomUUID().replaceAll('-', '').slice(0, 12)}`;
}

function planReport(result) {
  const raw = result.rows[0]['QUERY PLAN'];
  const report = Array.isArray(raw) ? raw[0] : JSON.parse(raw)[0];
  const nodes = [];

  function visit(node, depth = 0) {
    nodes.push({
      depth,
      type: node['Node Type'],
      relation: node['Relation Name'] ?? null,
      index: node['Index Name'] ?? null,
      estimatedRows: node['Plan Rows'],
      actualRows: node['Actual Rows'],
      loops: node['Actual Loops'],
    });
    for (const child of node.Plans ?? []) visit(child, depth + 1);
  }

  visit(report.Plan);
  return {
    nodes,
    executionMs: Number(report['Execution Time'] ?? 0),
    planningMs: Number(report['Planning Time'] ?? 0),
  };
}

function planLine(plan) {
  return plan.nodes
    .map((node) => {
      const target = node.index ?? node.relation;
      return `${'  '.repeat(node.depth)}${node.type}${target ? ` [${target}]` : ''}`;
    })
    .join(' → ');
}

async function configureClient(client) {
  await client.query(`SET statement_timeout = '${STATEMENT_TIMEOUT_MS}ms'`);
  await client.query(
    `SET idle_in_transaction_session_timeout = '${STATEMENT_TIMEOUT_MS}ms'`,
  );
  await client.query("SET lock_timeout = '2500ms'");
}

async function rollbackQuietly(client) {
  if (!client) return;
  try {
    await client.query('ROLLBACK');
  } catch {
    // The client may not currently be in a transaction.
  }
}

async function withDatabaseLab(emit, run) {
  const connectionString = databaseUrl();
  if (!connectionString) {
    emit(
      'postgres',
      'skip',
      'PostgreSQL не подключён: задайте DATABASE_URL или запустите проект через Docker Compose',
    );
    return false;
  }

  const pool = new Pool({
    connectionString,
    max: 4,
    connectionTimeoutMillis: CONNECTION_TIMEOUT_MS,
    idleTimeoutMillis: 5_000,
    allowExitOnIdle: true,
    application_name: 'node-loop-lab',
  });
  const schema = safeIdentifier('node_loop_lab');

  try {
    const versionResult = await pool.query(
      "SELECT current_setting('server_version') AS version",
    );
    emit(
      'postgres',
      'connect',
      `Подключён PostgreSQL ${versionResult.rows[0].version}; создаём изолированную схему ${schema}`,
    );
    await pool.query(`CREATE SCHEMA ${schema}`);
    await run({ pool, schema });
    return true;
  } catch (error) {
    const safeMessage =
      error?.code === 'ECONNREFUSED'
        ? 'соединение отклонено'
        : error?.code
          ? `SQLSTATE ${error.code}`
          : 'ошибка подключения';
    emit('postgres', 'error', `Сценарий PostgreSQL остановлен: ${safeMessage}`);
    return false;
  } finally {
    try {
      await pool.query(`DROP SCHEMA IF EXISTS ${schema} CASCADE`);
      emit('cleanup', 'drop', 'Учебная схема удалена; постоянные данные не создавались');
    } catch {
      // Connection failures can make cleanup impossible; the schema name is unique
      // and contains no user data.
    }
    await pool.end().catch(() => {});
  }
}

export async function databaseSqlBasics(emit) {
  await withDatabaseLab(emit, async ({ pool, schema }) => {
    const client = await pool.connect();
    try {
      await configureClient(client);
      await client.query(`
        CREATE TABLE ${schema}.products (
          id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
          name text NOT NULL,
          category text NOT NULL,
          price numeric(10, 2) NOT NULL CHECK (price >= 0),
          stock integer NOT NULL DEFAULT 0 CHECK (stock >= 0),
          description text,
          active boolean NOT NULL DEFAULT true
        )
      `);
      emit(
        'ddl',
        'create-table',
        'CREATE TABLE описал columns, data types, defaults и constraints таблицы products',
      );

      const inserted = await client.query(
        `INSERT INTO ${schema}.products
           (name, category, price, stock, description)
         VALUES
           ($1, $2, $3, $4, $5),
           ($6, $7, $8, $9, $10),
           ($11, $12, $13, $14, $15)
         RETURNING id, name, price`,
        [
          'Node.js Handbook',
          'books',
          2400,
          8,
          'Runtime and backend foundations',
          'NestJS Patterns',
          'books',
          3100,
          5,
          null,
          'Mechanical Keyboard',
          'hardware',
          7800,
          2,
          'USB keyboard',
        ],
      );
      emit(
        'insert',
        'rows',
        `INSERT добавил ${inserted.rowCount} строки; RETURNING вернул generated id без отдельного SELECT`,
      );

      const selected = await client.query(
        `SELECT
           id,
           name AS product_name,
           price,
           stock,
           price * stock AS inventory_value
         FROM ${schema}.products
         WHERE category = $1
           AND price <= $2
           AND active IS TRUE
         ORDER BY price DESC
         LIMIT $3`,
        ['books', 3500, 10],
      );
      emit(
        'select',
        'filter',
        `SELECT → FROM → WHERE → ORDER BY → LIMIT вернул: ${selected.rows
          .map((row) => row.product_name)
          .join(', ')}`,
      );

      const nullWrong = await client.query(
        `SELECT count(*)::int AS count
         FROM ${schema}.products
         WHERE description = NULL`,
      );
      const nullRight = await client.query(
        `SELECT count(*)::int AS count
         FROM ${schema}.products
         WHERE description IS NULL`,
      );
      emit(
        'null',
        'comparison',
        `description = NULL нашёл ${nullWrong.rows[0].count}; IS NULL нашёл ${nullRight.rows[0].count}`,
      );

      const updated = await client.query(
        `UPDATE ${schema}.products
         SET stock = stock - $1
         WHERE name = $2
           AND stock >= $1
         RETURNING id, name, stock`,
        [2, 'Node.js Handbook'],
      );
      emit(
        'update',
        'returning',
        `UPDATE изменил stock и вернул новое значение=${updated.rows[0].stock}`,
      );

      const grouped = await client.query(
        `SELECT
           category,
           count(*)::int AS product_count,
           round(avg(price), 2) AS average_price,
           sum(stock)::int AS total_stock
         FROM ${schema}.products
         GROUP BY category
         HAVING count(*) >= $1
         ORDER BY category`,
        [1],
      );
      emit(
        'aggregate',
        'group-by',
        `GROUP BY создал ${grouped.rowCount} группы; HAVING фильтрует уже агрегированные группы`,
      );

      const deleted = await client.query(
        `DELETE FROM ${schema}.products
         WHERE active IS FALSE
         RETURNING id`,
      );
      emit(
        'delete',
        'safe-delete',
        `DELETE с WHERE удалил строк=${deleted.rowCount}; без WHERE удалились бы все строки`,
      );
    } finally {
      await rollbackQuietly(client);
      client.release();
    }
  });
}

export async function databaseConstraintsAndAcid(emit) {
  await withDatabaseLab(emit, async ({ pool, schema }) => {
    const client = await pool.connect();
    try {
      await configureClient(client);
      emit(
        'ddl',
        'schema',
        'Создаём PRIMARY KEY, UNIQUE, CHECK и FOREIGN KEY как правила целостности внутри БД',
      );
      await client.query(`
        CREATE TABLE ${schema}.customers (
          id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
          email text NOT NULL UNIQUE,
          name text NOT NULL CHECK (char_length(name) >= 2)
        );

        CREATE TABLE ${schema}.orders (
          id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
          customer_id bigint NOT NULL
            REFERENCES ${schema}.customers(id) ON DELETE RESTRICT,
          amount numeric(12, 2) NOT NULL CHECK (amount > 0),
          status text NOT NULL DEFAULT 'new'
            CHECK (status IN ('new', 'paid', 'cancelled'))
        );
      `);

      const customer = await client.query(
        `INSERT INTO ${schema}.customers (email, name)
         VALUES ($1, $2)
         RETURNING id`,
        ['learner@example.com', 'Learner'],
      );
      emit(
        'query',
        'parameters',
        'Параметры $1/$2 переданы отдельно от SQL: значения не становятся частью синтаксиса запроса',
      );

      try {
        await client.query(
          `INSERT INTO ${schema}.orders (customer_id, amount)
           VALUES ($1, $2)`,
          [customer.rows[0].id, -50],
        );
      } catch (error) {
        emit(
          'constraint',
          'check',
          `CHECK отклонил отрицательную сумму: SQLSTATE ${error.code}`,
        );
      }

      await client.query('BEGIN');
      await client.query(
        `INSERT INTO ${schema}.orders (customer_id, amount, status)
         VALUES ($1, $2, $3)`,
        [customer.rows[0].id, 1250, 'paid'],
      );
      const inside = await client.query(
        `SELECT count(*)::int AS count FROM ${schema}.orders`,
      );
      await client.query('ROLLBACK');
      const after = await client.query(
        `SELECT count(*)::int AS count FROM ${schema}.orders`,
      );
      emit(
        'transaction',
        'rollback',
        `Внутри транзакции строк=${inside.rows[0].count}; после ROLLBACK строк=${after.rows[0].count}`,
      );

      emit(
        'acid',
        'model',
        'ACID: constraints поддерживают consistency, транзакция даёт atomicity, WAL/disk — durability, а isolation управляет видимостью параллельных изменений',
      );
    } finally {
      await rollbackQuietly(client);
      client.release();
    }
  });
}

export async function databaseIndexesAndExplain(emit) {
  await withDatabaseLab(emit, async ({ pool, schema }) => {
    const client = await pool.connect();
    try {
      await configureClient(client);
      emit(
        'dataset',
        'seed',
        'Создаём 40 000 событий с коррелированным временем, tenant_id, status и массивом tags',
      );
      await client.query(`
        CREATE TABLE ${schema}.events (
          id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
          tenant_id integer NOT NULL,
          created_at timestamptz NOT NULL,
          status text NOT NULL,
          tags text[] NOT NULL
        );

        INSERT INTO ${schema}.events (tenant_id, created_at, status, tags)
        SELECT
          (g % 100) + 1,
          now() - (g * interval '1 second'),
          CASE WHEN g % 5 = 0 THEN 'failed' ELSE 'processed' END,
          ARRAY[
            CASE WHEN g % 3 = 0 THEN 'api' ELSE 'worker' END,
            CASE WHEN g % 7 = 0 THEN 'priority' ELSE 'normal' END
          ]
        FROM generate_series(1, 40000) AS g;

        ANALYZE ${schema}.events;
      `);

      const query = `
        SELECT id, created_at, status
        FROM ${schema}.events
        WHERE tenant_id = 37
          AND created_at >= now() - interval '6 hours'
        ORDER BY created_at DESC
      `;
      const before = planReport(
        await client.query(`EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) ${query}`),
      );
      emit(
        'planner',
        'before-index',
        `До составного индекса: ${planLine(before)}; execution=${before.executionMs.toFixed(2)} ms`,
      );

      await client.query(`
        CREATE INDEX events_tenant_created_btree
          ON ${schema}.events USING btree (tenant_id, created_at DESC)
          INCLUDE (status);
        CREATE INDEX events_status_hash
          ON ${schema}.events USING hash (status);
        CREATE INDEX events_created_brin
          ON ${schema}.events USING brin (created_at);
        CREATE INDEX events_tags_gin
          ON ${schema}.events USING gin (tags);
        ANALYZE ${schema}.events;
      `);

      const after = planReport(
        await client.query(`EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) ${query}`),
      );
      emit(
        'planner',
        'after-index',
        `После B-tree: ${planLine(after)}; execution=${after.executionMs.toFixed(2)} ms`,
      );

      const sizes = await client.query(
        `
          SELECT c.relname, pg_relation_size(c.oid)::bigint AS bytes
          FROM pg_class AS c
          JOIN pg_namespace AS n ON n.oid = c.relnamespace
          WHERE n.nspname = $1 AND c.relkind = 'i'
          ORDER BY bytes DESC
        `,
        [schema],
      );
      emit(
        'indexes',
        'size',
        `Размеры индексов: ${sizes.rows
          .map((row) => `${row.relname}=${Math.round(Number(row.bytes) / 1024)} KiB`)
          .join(', ')}`,
      );
      emit(
        'optimizer',
        'decision',
        'Индекс не является приказом: planner выбирает Seq Scan, Index Scan или Bitmap Scan по статистике, селективности и стоимости',
      );
    } finally {
      client.release();
    }
  });
}

export async function databaseTransactionsAndLocks(emit) {
  await withDatabaseLab(emit, async ({ pool, schema }) => {
    await pool.query(`
      CREATE TABLE ${schema}.accounts (
        id integer PRIMARY KEY,
        balance integer NOT NULL CHECK (balance >= 0),
        version integer NOT NULL DEFAULT 0
      );
      INSERT INTO ${schema}.accounts (id, balance) VALUES (1, 1000);
    `);

    const first = await pool.connect();
    const second = await pool.connect();
    try {
      await Promise.all([configureClient(first), configureClient(second)]);

      await first.query('BEGIN ISOLATION LEVEL READ COMMITTED');
      const rcBefore = await first.query(
        `SELECT balance FROM ${schema}.accounts WHERE id = 1`,
      );
      await second.query(
        `UPDATE ${schema}.accounts SET balance = 1100 WHERE id = 1`,
      );
      const rcAfter = await first.query(
        `SELECT balance FROM ${schema}.accounts WHERE id = 1`,
      );
      await first.query('ROLLBACK');
      emit(
        'isolation',
        'read-committed',
        `READ COMMITTED: первый SELECT=${rcBefore.rows[0].balance}, второй SELECT=${rcAfter.rows[0].balance}`,
      );

      await pool.query(
        `UPDATE ${schema}.accounts SET balance = 1000, version = 0 WHERE id = 1`,
      );
      await first.query('BEGIN ISOLATION LEVEL REPEATABLE READ');
      const rrBefore = await first.query(
        `SELECT balance FROM ${schema}.accounts WHERE id = 1`,
      );
      await second.query(
        `UPDATE ${schema}.accounts SET balance = 1100 WHERE id = 1`,
      );
      const rrAfter = await first.query(
        `SELECT balance FROM ${schema}.accounts WHERE id = 1`,
      );
      await first.query('ROLLBACK');
      emit(
        'isolation',
        'repeatable-read',
        `REPEATABLE READ: первый SELECT=${rrBefore.rows[0].balance}, второй SELECT=${rrAfter.rows[0].balance}`,
      );

      await pool.query(
        `UPDATE ${schema}.accounts SET balance = 1000, version = 0 WHERE id = 1`,
      );
      await first.query('BEGIN');
      await first.query(
        `SELECT balance FROM ${schema}.accounts WHERE id = 1 FOR UPDATE`,
      );
      await second.query('BEGIN');
      let secondAcquired = false;
      const waitStarted = performance.now();
      const secondLock = second
        .query(
          `SELECT balance FROM ${schema}.accounts WHERE id = 1 FOR UPDATE`,
        )
        .then((result) => {
          secondAcquired = true;
          return result;
        });
      await new Promise((resolve) => setTimeout(resolve, 120));
      emit(
        'lock',
        'wait',
        `SELECT FOR UPDATE: вторая транзакция ждёт блокировку=${!secondAcquired}`,
      );
      await first.query(
        `UPDATE ${schema}.accounts SET balance = balance - 100 WHERE id = 1`,
      );
      await first.query('COMMIT');
      await secondLock;
      const waitedMs = performance.now() - waitStarted;
      await second.query(
        `UPDATE ${schema}.accounts SET balance = balance - 200 WHERE id = 1`,
      );
      await second.query('COMMIT');
      const pessimistic = await pool.query(
        `SELECT balance FROM ${schema}.accounts WHERE id = 1`,
      );
      emit(
        'lock',
        'pessimistic',
        `Пессимистичная блокировка ждала ${waitedMs.toFixed(0)} ms; итоговый balance=${pessimistic.rows[0].balance}`,
      );

      await pool.query(
        `UPDATE ${schema}.accounts SET balance = 1000, version = 0 WHERE id = 1`,
      );
      const snapshotA = await first.query(
        `SELECT balance, version FROM ${schema}.accounts WHERE id = 1`,
      );
      const snapshotB = await second.query(
        `SELECT balance, version FROM ${schema}.accounts WHERE id = 1`,
      );
      const updateA = await first.query(
        `UPDATE ${schema}.accounts
         SET balance = $1, version = version + 1
         WHERE id = 1 AND version = $2`,
        [snapshotA.rows[0].balance - 100, snapshotA.rows[0].version],
      );
      const updateB = await second.query(
        `UPDATE ${schema}.accounts
         SET balance = $1, version = version + 1
         WHERE id = 1 AND version = $2`,
        [snapshotB.rows[0].balance - 200, snapshotB.rows[0].version],
      );
      emit(
        'lock',
        'optimistic',
        `Оптимистичная версия: update A=${updateA.rowCount}, stale update B=${updateB.rowCount}; 0 означает конфликт`,
      );
    } finally {
      await Promise.all([rollbackQuietly(first), rollbackQuietly(second)]);
      first.release();
      second.release();
    }
  });
}

export async function databaseJoinsAndMaterializedViews(emit) {
  await withDatabaseLab(emit, async ({ pool, schema }) => {
    const client = await pool.connect();
    try {
      await configureClient(client);
      emit(
        'dataset',
        'seed',
        'Создаём 2 000 клиентов и 30 000 заказов для JOIN и агрегирования',
      );
      await client.query(`
        CREATE TABLE ${schema}.customers (
          id integer PRIMARY KEY,
          name text NOT NULL,
          active boolean NOT NULL
        );
        CREATE TABLE ${schema}.orders (
          id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
          customer_id integer NOT NULL REFERENCES ${schema}.customers(id),
          amount numeric(12, 2) NOT NULL,
          created_at timestamptz NOT NULL DEFAULT now()
        );
        INSERT INTO ${schema}.customers (id, name, active)
        SELECT g, 'customer-' || g, g % 5 <> 0
        FROM generate_series(1, 2000) AS g;
        INSERT INTO ${schema}.orders (customer_id, amount, created_at)
        SELECT
          (g % 2000) + 1,
          ((g % 5000) + 100)::numeric / 10,
          now() - (g * interval '1 minute')
        FROM generate_series(1, 30000) AS g;
        CREATE INDEX orders_customer_id_idx
          ON ${schema}.orders (customer_id);
        ANALYZE ${schema}.customers;
        ANALYZE ${schema}.orders;
      `);

      const joinPlan = planReport(
        await client.query(`
          EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
          SELECT c.id, c.name, sum(o.amount) AS total
          FROM ${schema}.customers AS c
          JOIN ${schema}.orders AS o ON o.customer_id = c.id
          WHERE c.active
          GROUP BY c.id, c.name
          ORDER BY total DESC
          LIMIT 20
        `),
      );
      emit(
        'join',
        'plan',
        `JOIN plan: ${planLine(joinPlan)}; execution=${joinPlan.executionMs.toFixed(2)} ms`,
      );

      const sampleCustomers = await client.query(
        `SELECT id FROM ${schema}.customers ORDER BY id LIMIT 20`,
      );
      const nPlusOneStarted = performance.now();
      for (const customer of sampleCustomers.rows) {
        await client.query(
          `SELECT count(*) FROM ${schema}.orders WHERE customer_id = $1`,
          [customer.id],
        );
      }
      const nPlusOneMs = performance.now() - nPlusOneStarted;
      const oneQueryStarted = performance.now();
      await client.query(`
        SELECT c.id, count(o.id)
        FROM ${schema}.customers AS c
        LEFT JOIN ${schema}.orders AS o ON o.customer_id = c.id
        WHERE c.id <= 20
        GROUP BY c.id
      `);
      const oneQueryMs = performance.now() - oneQueryStarted;
      emit(
        'query-shape',
        'n-plus-one',
        `N+1: 21 round trips=${nPlusOneMs.toFixed(2)} ms; один JOIN=1 round trip=${oneQueryMs.toFixed(2)} ms`,
      );

      await client.query(`
        CREATE MATERIALIZED VIEW ${schema}.customer_totals AS
        SELECT customer_id, count(*)::int AS orders_count, sum(amount) AS total
        FROM ${schema}.orders
        GROUP BY customer_id;
        CREATE UNIQUE INDEX customer_totals_customer_id_idx
          ON ${schema}.customer_totals (customer_id);
      `);
      const before = await client.query(
        `SELECT total FROM ${schema}.customer_totals WHERE customer_id = $1`,
        [42],
      );
      await client.query(
        `INSERT INTO ${schema}.orders (customer_id, amount) VALUES ($1, $2)`,
        [42, 999],
      );
      const stale = await client.query(
        `SELECT total FROM ${schema}.customer_totals WHERE customer_id = $1`,
        [42],
      );
      await client.query(`REFRESH MATERIALIZED VIEW ${schema}.customer_totals`);
      const refreshed = await client.query(
        `SELECT total FROM ${schema}.customer_totals WHERE customer_id = $1`,
        [42],
      );
      emit(
        'materialized-view',
        'refresh',
        `Materialized View: было=${before.rows[0].total}, до REFRESH=${stale.rows[0].total}, после=${refreshed.rows[0].total}`,
      );
      emit(
        'sql',
        'control',
        'Raw SQL здесь параметризован и видим; ORM полезен, пока команда проверяет сгенерированный SQL, планы, N+1 и границы транзакций',
      );
    } finally {
      client.release();
    }
  });
}

The application instruments its own live trace: rows and timestamps are recorded by real emit(...) calls, while the scenario supplies source and lane labels. This is not a V8/libuv profiler or a direct view of their internal queues. await and Promise keep the HTTP stream open until the scenario completes.

06 · RECIPES

Practical patterns worth keeping nearby

Compare the goal, code, and caveats instead of memorizing syntax without a model.

Constraint instead of convention

Prevent a negative balance for every database client.

CREATE TABLE accounts (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  balance numeric(14, 2) NOT NULL CHECK (balance >= 0)
);

A controller check does not protect writes from a worker or manual SQL.
A cross-row invariant may require a transaction.

Parameterized SQL

Pass user input without SQL injection.

const result = await pool.query(
  'SELECT id, email FROM users WHERE email = $1',
  [email],
);

Table names and sort directions are identifiers, not values; choose them from an allowlist.

One client per transaction

Create an order and ledger entry atomically.

const client = await pool.connect();
try {
  await client.query('BEGIN');
  const order = await client.query(insertOrderSql, values);
  await client.query(insertLedgerSql, [order.rows[0].id]);
  await client.query('COMMIT');
} catch (error) {
  await client.query('ROLLBACK');
  throw error;
} finally {
  client.release();
}

Two independent pool.query calls cannot replace this client.

Constraint as a domain result

Handle a race on a unique email correctly.

try {
  await users.insert(email);
} catch (error) {
  if (error.code === '23505') {
    throw new EmailAlreadyExistsError(email);
  }
  throw error;
}

A preliminary SELECT does not replace UNIQUE; a race exists before INSERT.

07 · PRODUCTION CASES

How a learning mistake becomes an incident

A realistic service: the original code, observable failure, corrected implementation, and why the correction works.

CASE 01

Application-level uniqueness allows duplicate accounts

Registration first checks whether an email exists and inserts a user only when the SELECT returns nothing. It works in manual testing but fails when two requests arrive together.

INCIDENT CONTEXT

Both transactions can observe “not found” before either INSERT commits. Application checks improve messages but do not enforce integrity under concurrency.

BEFOREPROBLEMATIC IMPLEMENTATION

async function register(email) {
  const existing = await db.query(
    'SELECT id FROM users WHERE email = $1',
    [email],
  );

  if (existing.rowCount > 0) {
    throw new ConflictError('Email already exists');
  }

  return db.query(
    'INSERT INTO users(email) VALUES ($1) RETURNING *',
    [email],
  );
}

The SELECT and INSERT are separate decisions. READ COMMITTED does not reserve the absence of a row for this request.

AFTERCORRECTED IMPLEMENTATION

CREATE UNIQUE INDEX users_email_unique
  ON users (lower(email));

async function register(email) {
  try {
    return await db.query(
      `INSERT INTO users(email)
       VALUES ($1)
       RETURNING *`,
      [email],
    );
  } catch (error) {
    if (error.code === '23505') {
      throw new ConflictError('Email already exists');
    }
    throw error;
  }
}

PostgreSQL serializes competing writes through the unique index. The application translates SQLSTATE 23505 into a domain response instead of trying to reproduce database concurrency rules.

FUNCTIONS AND CONSTRUCTS

What the unfamiliar calls from both code samples actually do.

db.query(sql, [values]): Sends SQL and parameters separately. $1 receives the first value without concatenating user input into SQL syntax.
rowCount: The number of rows found or changed; the old version uses it for an application-level check.
UNIQUE INDEX ON lower(email): Prevents two emails equal under case folding, even when their INSERT statements race.
SQLSTATE 23505: PostgreSQL’s stable machine code for unique_violation, translated by the application into HTTP 409.

08 · DO NOT CONFUSE

Common misconceptions

The myth is on the left; the accurate model is on the right.

MYTH

DTO validation is sufficient for integrity.

→

ACTUALLY

A DTO protects one input. A constraint protects the data from all inputs and concurrent writes.

MYTH

ACID automatically preserves every business rule.

→

ACTUALLY

The database preserves expressed constraints and correctly implemented transactions.

MYTH

Manual string escaping is a safe query API.

→

ACTUALLY

Use driver parameters for values and an allowlist for dynamic identifiers.

MYTH

BEGIN through pool.query covers later pool.query calls.

→

ACTUALLY

A pool can choose another connection; a transaction belongs to one physical connection.

09 · SELF-CHECK

Explain it in your own words

If you can explain the answer without quoting documentation, your mental model is starting to take shape.

Which invariants from your last project belong in the database?
Why does SELECT before INSERT not guarantee uniqueness?
How does atomicity differ from consistency?
Why is SQLSTATE safer than error text?
What risks accompany a NOT NULL migration on a large table?