SQL for Business Logic

Put business rules next to the data they protect when the rule is set-based, must hold for every writer, and would be a race if you only enforced it in app code. That is what SQL is for: constraints, transactions, and queries over sets. It is not where HTML belongs, and it is not where you call a payment HTTP API.

What belongs in the database

• Invariants the data must never violate: foreign keys, unique (email, tenant), check constraints, NOT NULL.
• Atomic multi-row changes: transfer money, allocate inventory. One transaction, one commit.
• Set operations: “all overdue invoices for these 50k accounts” — do not load rows into memory to filter.

What does not

• Orchestration (send email, call Stripe, publish events) — that is application code, maybe outbox-in-the-same-transaction.
• Presentation formatting and localization.
• Rules that change weekly and need a deploy of stored procedures nobody can test.

Example: inventory without a lost update

BEGIN;
UPDATE sku
   SET qty = qty - 1
 WHERE id = $1 AND qty >= 1;
-- 0 rows: sold out. Do not “read then write” without locking.
COMMIT;

A read in the API, a think, then an update is how you oversell. Constraints plus a single statement (or SELECT … FOR UPDATE) close the race. Check that the update changed one row. The unique key is the same idea for a fact that must not be stored twice:

ALTER TABLE customer
  ADD CONSTRAINT uq_customer_email UNIQUE (tenant_id, email);

Transactions and isolation

PostgreSQL and SQL Server default to READ COMMITTED; MySQL InnoDB defaults to REPEATABLE READ. Either default is fine until it is not. Raising the isolation level does not fix the lost update in the example above. Phantom reads and write skew need a documented isolation level or an explicit lock. Keep transactions short: no HTTP inside a transaction. For “save entity then publish event,” use an outbox table in the same commit, not a second connection after commit.

What breaks it

Two callers can both read the same qty and both write qty - 1. Use the single UPDATE above, and treat a row count of 0 as sold out.

static async Task LostUpdate(DbConnection conn, int id, CancellationToken ct)
{
    int qty;
    await using (var read = conn.CreateCommand())
    {
        read.CommandText = "SELECT qty FROM sku WHERE id = $1";
        var idParam = read.CreateParameter();
        idParam.Value = id;
        read.Parameters.Add(idParam);
        qty = (int)(await read.ExecuteScalarAsync(ct))!;
    }

    if (qty < 1)
        return;

    await using var write = conn.CreateCommand();
    write.CommandText = "UPDATE sku SET qty = qty - 1 WHERE id = $1";
    var writeParam = write.CreateParameter();
    writeParam.Value = id;
    write.Parameters.Add(writeParam);
    await write.ExecuteNonQueryAsync(ct);
}

Pitfalls

• Business logic only in the ORM, none in constraints — a one-off SQL script will corrupt data.
• Triggers that hide writes (you cannot see the story in the app).
• Stored procedures as the entire application: undebuggable, unversioned, duplicated per environment.

See normalization and ORM features.