Databases and SQL

import sqlite3
db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE customers (id INTEGER PRIMARY KEY, email TEXT UNIQUE NOT NULL);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total REAL, status TEXT);
INSERT INTO customers (email) VALUES ('ada@shop.test'), ('lin@shop.test'), ('sam@shop.test');
INSERT INTO orders (customer_id, total, status) VALUES
    (1, 249.0, 'paid'), (1, 25.0, 'paid'), (2, 89.5, 'refunded'), (2, 120.0, 'paid');
""")
query = """
SELECT c.email, COUNT(o.id) AS paid_orders, COALESCE(SUM(o.total), 0) AS revenue
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'paid'
GROUP BY c.id
ORDER BY revenue DESC
"""
for row in db.execute(query):
    print(row)

Placeholders, always: SQL injection

Imagine a form with a "name" box, and someone writes "and also hand over the safe key" in it. A careless clerk reads the whole form aloud and obeys. Never build SQL by pasting values into a string. Here's why:

import sqlite3
db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE users (email TEXT, is_admin INTEGER);
INSERT INTO users VALUES ('ada@shop.test', 1), ('lin@shop.test', 0);
""")

typed = "nobody' OR '1'='1"          # what an attacker types into a login form

unsafe = f"SELECT email FROM users WHERE email = '{typed}'"
print("unsafe:", db.execute(unsafe).fetchall())      # returns EVERY user

safe = db.execute("SELECT email FROM users WHERE email = ?", (typed,)).fetchall()
print("safe:  ", safe)                               # no match, as it should be

The attacker's quote mark ended the string early and added OR '1'='1', which is always true. That's SQL injection, one of the most damaging and common security bugs ever found. With a placeholder (? in SQLite, %s in many Postgres drivers), the value travels separately from the SQL. The database never treats it as code, whatever characters it contains.

In plain words: the query is the form, and the user's text only ever goes in the box.

The same rule applies to SQL that a model writes. Run it with a read-only account, and never paste user text into it.

Indexes: fast lookups

Without help, finding WHERE email = 'ada@shop.test' means checking every row: O(n). An index is a sorted structure on a column, usually a B-tree, a tree with many branches per node. It finds a row in O(log n), like the index at the back of a book. Primary keys and UNIQUE columns get one automatically. You add others for columns you search or join on often:

import sqlite3
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total REAL)")
plan = "EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id = 7"
print(db.execute(plan).fetchall()[0][-1])
db.execute("CREATE INDEX orders_by_customer ON orders(customer_id)")
print(db.execute(plan).fetchall()[0][-1])

SCAN reads the whole table. SEARCH ... USING INDEX jumps straight to the rows. Indexes cost disk space and slow down writes a little, the way a book's index has to be redone when the book changes. So add them for real queries, not every column.

Transactions: all or nothing

A vending machine either takes your money and gives you the snack, or you keep your money. Nobody wants just one half. Moving stock from one warehouse to another is two updates. If the program crashes between them, stock vanishes. A transaction groups statements so they all happen, or none do. You commit to keep the changes, or roll back to undo them all. In Python, with db: commits when the block ends and rolls back if an exception escapes.

Databases promise four properties, called ACID: changes are atomic (all or nothing), keep the data consistent with its rules, are isolated from other users' unfinished changes, and are durable once committed.

Note: You'll also hear about NoSQL databases (document stores such as MongoDB, key-value stores such as Redis) and ORMs such as SQLAlchemy, which build SQL from Python classes. They rest on the same ideas: keys, indexes and transactions.

Three bugs that don't raise errors

f-strings in queries. "It's only from our own app" is how injection happens, since values come from users eventually. Always use placeholders.

The LEFT JOIN that stops being one. A LEFT JOIN keeps customers with no orders. Add WHERE orders.total > 10, and those customers vanish, because their total is NULL, which fails the test. The query quietly becomes an inner join. Put that condition in the ON clause to keep them.

Two statements with no transaction. Moving money takes a subtract and an add. If the program dies between them, the money has gone. Wrap them in a transaction, so both happen or neither does.

And don't index every column. Each index speeds up some reads and slows down every write. Add indexes for the queries you really run.