Relational Data, SQL, and Transactions
An order system needs to answer two kinds of question: how much a customer has spent, and how much stock remains when two requests decrement it at once. The first requires correctly relating records across tables. The second requires updates that remain correct under concurrency. A schema defines what each record means, constraints enforce valid states, indexes help locate records, and transactions commit related changes together.
The PostgreSQL tutorial is a starting point for SQL. A fictional small shop will connect these ideas through one dataset.
Rows, keys, relationships, and schema
PostgreSQL's relational concepts describe tables as rows with named, typed columns. Here, one row in customers represents a customer; one row in orders represents an order. The data schema also specifies which column values may be NULL, how records are identified, and which relationships must hold.
A primary key uniquely identifies a row and cannot be NULL. Names can repeat or change, so customer_id identifies the customer. An order's customer_id references a customer through a foreign key. This example requires each order to belong to exactly one existing customer, while a customer can have zero or more orders: a one-to-many relationship from customers to orders.
Amounts are integer cents: 5000 cents means 50.00 currency units. Save the following code as relational_demo.py, append subsequent blocks to the same file in page order, and run python3 relational_demo.py. Python's sqlite3 runs the SQL without a separate database server; isolation_level=None provides explicit transaction control here. SQLite's foreign key enforcement must be enabled on the connection, and the code checks that it is enabled. The example uses an in-memory database.
In ordinary SQLite tables, declaring INTEGER gives a column type affinity; it can still store fractions or nonnumeric text. The amount, stock, and reservation checks therefore use SQLite's typeof to require stored integers, along with positive or nonnegative values.
import sqlite3
# Explicit transaction control; the database lives only in memory.
db = sqlite3.connect(":memory:", isolation_level=None)
db.execute("PRAGMA foreign_keys = ON")
assert db.execute("PRAGMA foreign_keys").fetchone() == (1,)
db.executescript("""
CREATE TABLE customers (
customer_id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
email TEXT UNIQUE
);
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
amount_cents INTEGER NOT NULL
CHECK (typeof(amount_cents) = 'integer' AND amount_cents > 0)
);
INSERT INTO customers VALUES
(1, 'Ada', 'ada@example.com'),
(2, 'Lin', NULL),
(3, 'Sam', 'sam@example.com');
INSERT INTO orders VALUES
(101, 1, 5000), (102, 1, 3000),
(103, 2, 2000), (104, 2, 1000);
""")
print("customers:", db.execute("SELECT COUNT(*) FROM customers").fetchone()[0])
print("orders:", db.execute("SELECT COUNT(*) FROM orders").fetchone()[0])
customers: 3
orders: 4
Ada's two orders are for 5000 and 3000 cents; Lin's are for 2000 and 1000 cents. Sam has no orders. Lin has not supplied an email address, represented by NULL. These records let us calculate totals and examine queries with missing matches.
Queries, joins, and aggregation
SELECT and WHERE choose the returned columns and qualifying rows. A join pairs rows from two tables according to a condition. An inner join keeps matches; a left join also preserves unmatched left-side rows, supplying NULL for right-side columns.
Start from customers and use a left join to list everyone's order count and spending, including Sam. GROUP BY groups records for each customer, and SUM adds their amounts. The aggregate function rules distinguish COUNT(*), which counts rows, from COUNT(o.order_id), which counts non-null order IDs. Sam has one placeholder row after the left join, so these counts are 1 and 0 respectively. With no non-null amounts to add, SUM returns NULL; this query uses COALESCE to display 0.
query = """
SELECT c.customer_id, c.name,
COUNT(*) AS joined_rows,
COUNT(o.order_id) AS order_count,
COALESCE(SUM(o.amount_cents), 0) AS total_cents
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name
ORDER BY c.customer_id;
"""
for row in db.execute(query):
print(row)
print("orders >= 3000:", db.execute(
"SELECT order_id FROM orders WHERE amount_cents >= 3000 ORDER BY order_id"
).fetchall())
(1, 'Ada', 2, 2, 8000)
(2, 'Lin', 2, 2, 3000)
(3, 'Sam', 1, 0, 0)
orders >= 3000: [(101,), (102,)]
The columns are customer ID, name, joined row count, order count, and total cents. Ada's total is 5000 + 3000 = 8000; Lin's is 2000 + 1000 = 3000. The shop total is 11000 cents, or 110.00 currency units. The final query selects orders 101 and 102, whose amounts are at least 3000 cents. WHERE filters rows before aggregation; HAVING filters groups afterward. To select customers spending at least 4000 cents, apply HAVING to the total; only Ada qualifies here. Use ORDER BY when a stable order matters.
NULL semantics and join cardinality
The comparison rules for NULL introduce a third logical result, unknown. An ordinary comparison with either operand null yields unknown; WHERE keeps only true results. Consequently, email = NULL finds no missing addresses: use email IS NULL. Two null keys likewise do not match in an equality join.
Join cardinality is the number of rows a join produces. Customer IDs are unique in customers, so an order can match at most one customer. Join orders to a table containing several records per customer, however, and each order can appear several times. The following query constructs two promotional labels for Ada. Each of her two orders matches both labels, producing four rows and counting 16000 cents.
print("= NULL:", db.execute(
"SELECT COUNT(*) FROM customers WHERE email = NULL"
).fetchone()[0])
print("IS NULL:", db.execute(
"SELECT COUNT(*) FROM customers WHERE email IS NULL"
).fetchone()[0])
print("empty SUM:", db.execute(
"SELECT SUM(amount_cents) FROM orders WHERE customer_id = 3"
).fetchone()[0])
print("multiplied:", db.execute("""
WITH promotions(customer_id, label) AS (
VALUES (1, 'spring'), (1, 'member')
)
SELECT COUNT(*), SUM(o.amount_cents)
FROM orders AS o
JOIN promotions AS p ON p.customer_id = o.customer_id;
""").fetchone())
= NULL: 0
IS NULL: 1
empty SUM: None
multiplied: (4, 16000)
None is Python's printed representation of SQL NULL. Ada's actual order total remains 8000 cents. The 16000-cent result is a sum over orders expanded by promotional label; it cannot serve directly as customer spending. Decide whether each result row should represent a customer, an order, or a label before choosing the join or aggregating first. SUM(DISTINCT amount_cents) is no general repair: two separate orders can have the same amount. For declaring join relationships and diagnosing unmatched keys in DataFrames, see Combining DataFrames; its null-key matching behavior differs from SQL.
Constraints as executable invariants
Constraints put rules that must remain true into the table definition. Here the order primary key rejects duplicate IDs, the foreign key rejects nonexistent customers, NOT NULL requires an amount, and the amount's CHECK requires a stored positive integer. These rules apply across ordinary write paths, so each caller need not implement its own version of the checks.
for label, values in [
("foreign key", (105, 99, 1000)),
("positive amount", (106, 1, -1000)),
("required amount", (107, 1, None)),
("unique order", (101, 1, 1000)),
]:
try:
db.execute("INSERT INTO orders VALUES (?, ?, ?)", values)
except sqlite3.IntegrityError:
print(label, "rejected")
print("orders:", db.execute("SELECT COUNT(*) FROM orders").fetchone()[0])
foreign key rejected
positive amount rejected
required amount rejected
unique order rejected
orders: 4
All four inserts are rejected and the original four orders remain. The database enforces the rules rather than leaving them in comments.
The PostgreSQL constraint documentation adds an important condition: a CHECK passes if its result is true or NULL, so a positive-value check still needs NOT NULL. PostgreSQL's default UNIQUE allows multiple nulls; require a non-null email as well if every customer must have a unique address. Cross-row rules need separate design: an ordinary CHECK cannot continuously enforce that all order amounts together stay below a limit. Such a rule needs appropriate constraints, transactions, or locks on the relevant state.
Indexes and the read/write trade-off
An index stores an additional lookup structure and consumes separate storage. Indexes can accelerate filters and joins, but writes must keep them synchronized with their tables. Create an index for looking up orders by customer:
db.execute("CREATE INDEX orders_customer_idx ON orders(customer_id)")
print("index exists:", db.execute(
"SELECT COUNT(*) FROM sqlite_master WHERE type = 'index' AND name = ?",
("orders_customer_idx",),
).fetchone()[0] == 1)
index exists: True
This output confirms creation only. Four orders establish no performance benefit; PostgreSQL's index usage guidance explains why a small dataset may favor a sequential scan. Choose indexes from actual filters, join conditions, and query plans, then weigh their write costs. An ordinary index does not require unique keys or turn two independent updates into a transaction.
Transactions, isolation, and a lost update
Transaction atomicity means that related row changes within one transaction commit together, or are all undone when the whole transaction rolls back. BEGIN starts it, COMMIT commits it, and ROLLBACK undoes it. This describes table-row changes; PostgreSQL's sequence counters do not revert when a transaction aborts. Atomicity governs the success or failure of a group of changes; isolation governs what concurrent transactions can see and how conflicts are handled.
Suppose stock starts at 10. Requests A and B each buy one item, first reading the quantity and then subtracting one in application code. This schedule is possible under PostgreSQL's Read Committed, even when each request places its read and write in a transaction:
Two purchases should leave 10 - 1 - 1 = 8, but leave 9. B overwrites A's result: a lost update. The value still satisfies quantity >= 0, so the row constraint cannot detect the business error.
The next block executes two requests holding stale values in sequence on one connection, reproducing the overwrite, then compares it with relative updates inside the database. This is a deterministic stale-value demonstration, not an isolation experiment with concurrent connections.
db.execute("""
CREATE TABLE stock (
sku INTEGER PRIMARY KEY,
quantity INTEGER NOT NULL
CHECK (typeof(quantity) = 'integer' AND quantity >= 0)
)
""")
db.execute("INSERT INTO stock VALUES (1, 10)")
a_read = db.execute("SELECT quantity FROM stock WHERE sku = 1").fetchone()[0]
b_read = db.execute("SELECT quantity FROM stock WHERE sku = 1").fetchone()[0]
for old_value in (a_read, b_read):
db.execute("UPDATE stock SET quantity = ? WHERE sku = 1", (old_value - 1,))
print("stale writes:", db.execute("SELECT quantity FROM stock").fetchone()[0])
db.execute("UPDATE stock SET quantity = 10 WHERE sku = 1")
for _ in range(2):
changed = db.execute("""
UPDATE stock SET quantity = quantity - 1
WHERE sku = 1 AND quantity >= 1
""").rowcount
assert changed == 1
print("relative updates:", db.execute("SELECT quantity FROM stock").fetchone()[0])
stale writes: 9
relative updates: 8
Under PostgreSQL's Read Committed rules, an updater waits for an unfinished updater of the same row; after that transaction commits, it rechecks WHERE against the new row version. For this example, compute quantity - 1 in the database and put quantity >= 1 in the same update. An affected-row count of 0 means no matching stock was available to decrement; the caller must handle it.
PostgreSQL's isolation levels provide different guarantees:
Both Repeatable Read and Serializable require whole-transaction retries after serialization failures. When an update depends on a prior read, lock the relevant rows in the same transaction, for example with PostgreSQL's SELECT FOR UPDATE. For rules spanning multiple rows, choose isolation or locking that covers the entire rule.
Database commits and external side effects
The transactional outbox documentation describes two failure windows when database changes and notifications are separate operations. Send first and commit afterward: a database rollback leaves a notification already sent. Commit first and send afterward: the process can stop between the two steps. Rolling back the database cannot retract a request already accepted by an external service.
A transactional outbox records the business change and an event to send in the same database transaction. A separate sender reads committed events and delivers them. Continuing from stock 8, the next block couples a decrement with recording an event. Its ID identifies one logical reservation and stays the same across retries.
db.execute("""
CREATE TABLE outbox (
event_id TEXT PRIMARY KEY NOT NULL,
sku INTEGER NOT NULL REFERENCES stock(sku),
units INTEGER NOT NULL
CHECK (typeof(units) = 'integer' AND units > 0)
)
""")
def reserve(event_id, units):
db.execute("BEGIN")
try:
changed = db.execute("""
UPDATE stock SET quantity = quantity - ?
WHERE sku = 1 AND quantity >= ?
""", (units, units)).rowcount
if changed != 1:
raise ValueError("insufficient stock")
db.execute("INSERT INTO outbox VALUES (?, 1, ?)", (event_id, units))
db.execute("COMMIT")
except Exception:
db.execute("ROLLBACK")
raise
def state():
return (db.execute("SELECT quantity FROM stock WHERE sku = 1").fetchone()[0],
db.execute("SELECT COUNT(*) FROM outbox").fetchone()[0])
try:
reserve("reservation-1", 0)
except sqlite3.IntegrityError:
print("invalid event:", state())
reserve("reservation-1", 1)
print("committed:", state())
try:
reserve("reservation-1", 1)
except sqlite3.IntegrityError:
print("duplicate event:", state())
db.close()
invalid event: (8, 0)
committed: (7, 1)
duplicate event: (7, 1)
The state tuple is stock quantity followed by outbox event count. An invalid event violates a constraint and rolls back, leaving stock 8 and no event. A successful reservation leaves stock 7 and one event. Inserting a duplicate ID fails and rolls back that attempt's decrement too, preserving (7, 1). An application handling repeated requests still needs to compare the original parameters and return the stored result; the same ID alone does not establish that the requests are identical.
Committed events still await delivery. If the sender stops after the external service accepts an event but before recording delivery, it may send again. The outbox preserves intent without eliminating duplicate delivery. AWS's idempotent API design guidance explains why receivers should commit the request ID and local effect atomically and compare parameters when an ID is reused. For an external API, reuse a key only through a supported idempotent operation and within its retention period. After a response timeout, preserve the outcome as unknown: use an available status lookup, or retry with the original key within the service's idempotency guarantees.
Long-Running Agents places this boundary in checkpoint and recovery workflows; Cloudflare Workers covers specific storage and queue choices. Once you choose a service, check its transaction interface and concurrency guarantees before connecting these schemas and update rules to the application.