Databases and SQL basics
Why most applications keep their data in a relational database, and the SQL you need every day: tables and keys, SELECT with WHERE and ORDER BY, JOIN, GROUP BY, NULL, constraints, transactions, indexes, and placeholders. Every example run against a real SQLite database.
In part 9 the monthly report read orders from a CSV file. That works for one export. It stops working when ten people add orders at the same time, when you need “all orders for this customer” in a millisecond, or when a half-finished write must never leave the numbers wrong. That’s what databases are for.
This part covers relational databases and SQL, the language almost all of them speak. The examples use SQLite, which “is a C-language library that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine.” It’s a single file, it ships with Python and PHP, and the SQL is very close to what you’d write for MySQL or PostgreSQL.
Tables, rows, columns, keys
The PostgreSQL tutorial puts the core idea plainly: a relational database “is a system for managing data stored in relations. Relation is essentially a mathematical term for table.” And: “Each table is a named collection of rows. Each row of a given table has the same set of named columns, and each column is of a specific data type.”
Two tables for a small shop:
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
city TEXT
);
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL REFERENCES customers(id),
ordered_on TEXT NOT NULL,
product TEXT NOT NULL,
qty INTEGER NOT NULL CHECK (qty > 0),
price INTEGER NOT NULL
);
- A primary key (
id) identifies each row uniquely. - A foreign key (
customer_id REFERENCES customers(id)) links an order to its customer. The customer’s name is stored once, incustomers, not copied into every order. Change it once and every order sees the new name. - Constraints like
NOT NULLandCHECK (qty > 0)are rules the database enforces, no matter which code writes the data.
Prices are whole rupiah in an INTEGER, for the same reason as in part 2: floating point is the wrong type for money.
The sample data: three customers (Ana in Bandung, Budi in Jakarta, Cici with no city) and four orders.
SELECT: asking questions
SQL describes what you want, not how to get it. The database decides how.
SELECT product, qty, price
FROM orders
WHERE qty >= 3
ORDER BY qty DESC;
('pen', 10, 4000)
('mug', 3, 35000)
WHERE filters rows, ORDER BY sorts them, and SELECT picks the columns. Compare that with the loop and if you’d write over a CSV: same logic, but the database can use indexes and doesn’t need to load everything into memory.
JOIN: combining tables
Orders only have a customer_id. To show names, join the two tables on the key:
SELECT c.name, o.product, o.qty * o.price AS amount
FROM orders o
JOIN customers c ON c.id = o.customer_id
ORDER BY c.name, o.ordered_on;
('Ana', 'book', 170000)
('Ana', 'mug', 105000)
('Budi', 'pen', 40000)
('Budi', 'book', 85000)
Cici doesn’t appear, because a plain JOIN only keeps rows that match on both sides. To keep every customer, even those with no orders, use LEFT JOIN:
SELECT c.name, COUNT(o.id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id
ORDER BY c.name;
('Ana', 2)
('Budi', 2)
('Cici', 0)
“Customers with zero orders” is exactly the kind of row that silently disappears with the wrong join.
GROUP BY: the report in one query
The whole monthly report from part 9 (filter, group, sum) is one query:
SELECT substr(ordered_on, 1, 7) AS month,
SUM(qty * price) AS revenue,
COUNT(*) AS orders
FROM orders
GROUP BY month
ORDER BY month;
('2026-10', 315000, 3)
('2026-11', 85000, 1)
GROUP BY collects rows with the same value, and aggregate functions (SUM, COUNT, AVG, MIN, MAX) compute one value per group. Note substr(..., 1, 7) works here because the dates are stored consistently as YYYY-MM-DD, which is one more reason to let the database hold a clean format instead of whatever the CSV had (part 15).
NULL is not a value
Cici’s city is NULL: unknown. NULL behaves differently from anything else, and it surprises everyone once. PostgreSQL’s documentation warns: “Do not write expression = NULL because NULL is not “equal to” NULL. (The null value represents an unknown value, and it is not known whether two unknown values are equal.)”
SELECT name FROM customers WHERE city = NULL; -- no rows
SELECT name FROM customers WHERE city IS NULL; -- Cici
[]
[('Cici',)]
The first query isn’t an error. It just quietly returns nothing. Use IS NULL and IS NOT NULL.
Constraints: the database says no
Constraints protect the data even when application code has a bug:
INSERT ... qty = 0 → IntegrityError: CHECK constraint failed: qty > 0
INSERT ... customer_id = 99 → IntegrityError: FOREIGN KEY constraint failed
This is fail-fast from part 13, at the last line of defense. One SQLite detail worth knowing: “Foreign key constraints are disabled by default (for backwards compatibility), so must be enabled separately for each database connection”, with PRAGMA foreign_keys = ON. MySQL (InnoDB) and PostgreSQL enforce them by default.
Transactions: all or nothing
Placing an order is two writes: insert the order, and reduce the stock. If the second fails, the first must not stay. A transaction groups them. Wikipedia describes ACID as “a set of properties of database transactions intended to guarantee data validity despite errors”, and atomic transactions “either succeed completely or fail completely”.
In Python’s sqlite3, with db: wraps a block in a transaction that commits if the block finishes and rolls back if it raises:
def place_order(product, qty):
with db:
db.execute("INSERT INTO orders (product, qty) VALUES (?, ?)", (product, qty))
db.execute("UPDATE stock SET qty = qty - ? WHERE product = ?", (qty, product))
place_order("book", 3) # stock 5 → 2
place_order("book", 4) # only 2 left
failed: CHECK constraint failed: qty >= 0
stock: 2
orders: [(1, 'book', 3)]
The second order’s INSERT did run, but when the stock update failed, the whole transaction rolled back. There’s no order for 4 books that were never deducted from stock. Without the transaction, that orphan order would be in the table. In PHP the same thing is $pdo->beginTransaction(), then commit() or rollBack(); Laravel wraps it as DB::transaction(...).
Indexes: not reading every row
Finding all orders for one customer without help means checking every row: a full scan, O(n) from part 10. An index is a sorted structure on a column that lets the database jump straight to matching rows, like the index at the back of a book.
EXPLAIN QUERY PLAN shows what SQLite intends to do; it gives “a high-level description of the strategy or plan that SQLite uses to implement a specific SQL query.” With one million orders, on the machine used to write this note:
plan: SCAN orders
100 lookups without index: 2.888 s
plan: SEARCH orders USING INDEX idx_orders_customer (customer_id=?)
100 lookups with index: 0.0030 s
One line, CREATE INDEX idx_orders_customer ON orders(customer_id);, and the plan changes from SCAN to SEARCH, almost a thousand times faster. Indexes aren’t free: they take space and make every insert slightly slower, so you add them for the columns you actually filter, join, or sort on. When a page is slow, the query plan is one of the first things to check. MySQL and PostgreSQL have EXPLAIN too.
Placeholders, always
Values from users must never be pasted into SQL text. Here’s why, with a “name” typed into a search box:
name = "x' OR '1'='1"
unsafe = f"SELECT name, phone FROM customers WHERE name = '{name}'"
# SELECT name, phone FROM customers WHERE name = 'x' OR '1'='1'
db.execute(unsafe).fetchall()
string-built: [('Ana', '0811'), ('Budi', '0812'), ('Cici', '0813')]
placeholder: []
The input became part of the SQL and turned the condition into “always true”. Every customer’s phone number leaks. That’s SQL injection, and part 22 looks at it more closely. With a placeholder, the value is sent separately from the SQL and can only ever be a value. Python’s docs: “Always use placeholders instead of string formatting to bind Python values to SQL statements, to avoid SQL injection attacks”.
PHP’s PDO does the same with prepared statements. The manual: “The parameters to prepared statements don’t need to be quoted; the driver automatically handles this.”
$stmt = $db->prepare('SELECT name, phone FROM customers WHERE name = :name');
$stmt->execute(['name' => "x' OR '1'='1"]); // → array(0) {}
$stmt->execute(['name' => 'Ana']); // → [{"name":"Ana","phone":"0811"}]
The manual also adds the honest caveat: if other parts of the query are “built up with unescaped input, SQL injection is still possible”. Placeholders work for values, not for table or column names. If those must vary, choose them from a fixed list in code.
ORMs and query builders
Most frameworks offer an ORM (Eloquent, Django ORM, Prisma, GORM) that lets you write Order::where('qty', '>=', 3)->get() instead of SQL. They use placeholders for you and save a lot of typing. They’re also an abstraction that leaks (part 11): the 500-queries-in-a-loop problem from that part is the classic ORM mistake. Knowing the SQL underneath is how you spot it, and most ORMs can log the SQL they run. Turn that on when something is slow.
Why this matters day to day
- Correct data comes from constraints and transactions, not from hoping every code path is perfect.
- Speed usually comes from the right index and from fewer queries, not from faster hardware.
- Reports are often one well-written
GROUP BYinstead of a loop in application code. - Security starts with placeholders. There is no situation where string-built SQL with user input is fine.
- AI-generated queries are often almost right: a
JOINthat should beLEFT JOIN,= NULL, a missing index, a query in a loop. Run them against real data and read the plan.
Try it yourself
- Create the two tables in SQLite (
sqlite3 shop.db) and reproduce the queries above. - Write a query for revenue per customer per month, including customers with zero orders.
- Insert 100,000 rows, time a query with and without an index, and compare the
EXPLAIN QUERY PLANoutput. - Find a place in your code where SQL is built with string concatenation or interpolation. Rewrite it with placeholders.
Summary
- A relational database stores data in tables of rows and typed columns, linked by primary and foreign keys.
- SELECT / WHERE / ORDER BY ask questions; JOIN combines tables (use LEFT JOIN to keep rows without matches); GROUP BY with aggregates builds reports.
- NULL means unknown: use
IS NULL, never= NULL. - Constraints make the database reject bad data; transactions make multi-step changes all-or-nothing.
- Indexes turn full scans into fast lookups; check with
EXPLAIN. - Placeholders, always, for every value that comes from outside.
- ORMs help, but know the SQL they produce.
References
- Concepts and Comparison Functions and Operators, PostgreSQL documentation.
- SQLite home page, Foreign Key Support, and EXPLAIN QUERY PLAN, SQLite documentation.
- sqlite3 — DB-API 2.0 interface for SQLite databases, Python documentation.
- Prepared statements and stored procedures, PHP manual.
- ACID, Wikipedia.