Changing Data Safely: INSERT, UPDATE, DELETE and Transactions

Insert, update, delete

INSERT INTO products (sku, name, price_cents) VALUES ('TEE-9', 'T-shirt', 2200);

UPDATE products SET price_cents = 2400 WHERE sku = 'TEE-9';

DELETE FROM products WHERE sku = 'TEE-9';

An UPDATE or DELETE without WHERE changes every row. Write the WHERE first, test it with a SELECT, then turn it into the update.

Upserts

-- PostgreSQL and SQLite
INSERT INTO products (sku, name, price_cents) VALUES ('MUG-3', 'Mug', 1300)
ON CONFLICT (sku) DO UPDATE SET price_cents = EXCLUDED.price_cents;

RETURNING

INSERT INTO orders (customer_id) VALUES (1) RETURNING id, created_at;

Transactions

A transaction groups changes so they all happen or none do:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;      -- or ROLLBACK; to undo both

Properties (ACID): atomic, consistent, isolated, durable. Placing an order (create order, insert items, reduce stock) must be one transaction.

Concurrency: preventing lost updates

Two requests read stock = 1, both sell it. Fix with an atomic, conditional update:

UPDATE products SET stock = stock - 1
WHERE id = 7 AND stock >= 1;
-- check the affected row count: 0 means sold out

Or lock the row inside a transaction: SELECT ... FOR UPDATE.

SQL injection: never build SQL with strings

# DANGEROUS
cur.execute(f"SELECT * FROM users WHERE email = '{email}'")
# email = "' OR '1'='1" returns every user

# SAFE: parameters are sent separately from the SQL
cur.execute("SELECT * FROM users WHERE email = %s", (email,))   # psycopg
cur.execute("SELECT * FROM users WHERE email = ?", (email,))    # sqlite3

ORMs like SQLAlchemy parameterise automatically, but raw text() queries still need bound parameters.

Soft deletes

Many apps mark rows deleted (deleted_at TIMESTAMP) instead of removing them, keeping history and allowing undo. Remember to filter them out in queries (WHERE deleted_at IS NULL).

Exercise

Write a transaction that places an order for two products: insert the order, insert the items with current prices, decrement stock only if enough is available, and roll back if any product is out of stock.

Check yourself

• What happens if you forget WHERE in an UPDATE?
• Why is stock = stock - 1 WHERE stock >= 1 safe under concurrency?
• How do parameterised queries prevent SQL injection?