SQLite — data, JOIN, indexing

SQLite: the real data, in one file

A shipper spends as much time understanding data as code.

What's actually inside the .db file

The file isn't random bytes — it's organized to be fast.

Tables, rows, columns — and the schema

Spreadsheet vocabulary, but structured — defined once, used everywhere.

Our worked example: a small shop

-- shop.db — the whole database is this one file
CREATE TABLE users (
    id    INTEGER PRIMARY KEY,  -- unique per user
    name  TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE
);

CREATE TABLE orders (
    id      INTEGER PRIMARY KEY,
    user_id INTEGER,            -- -> users.id  (foreign key)
    total   REAL,
    status  TEXT
);

We'll use this shop (users + orders + line_items) in nearly every example below. Note the link already: orders.user_id points at users.id.

The five verbs — CREATE + INSERT

CREATE TABLE users (id INTEGER PRIMARY KEY,
                    name TEXT, email TEXT);

INSERT INTO users (id, name, email) VALUES
  (1, 'Ana', 'ana@ex.com'),
  (2, 'Bo',  'bo@ex.com'),
  (3, 'Cyd', 'cyd@ex.com'),
  (4, 'Dee', 'dee@ex.com');

Make it → add to it. INSERT adds rows to the table — each row is one full record.

The five verbs — SELECT, UPDATE, DELETE

SELECT name, email FROM users
WHERE id = 1;

UPDATE users SET name = 'Anna'
WHERE id = 1;

DELETE FROM users WHERE id = 4;

Read it → change it → remove it. The CREATE → INSERT → SELECT → UPDATE → DELETE loop is 95% of everyday app SQL.

Pick a type per column

SQLite is lenient ('type affinity') — it won't scream if you store odd types. But pick sensible ones: comparisons, sorting and indexes behave as you expect.

Rules the database enforces for you

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,     -- auto id
    name  TEXT NOT NULL,           -- may not be empty
    email TEXT NOT NULL UNIQUE,    -- one email, ever
    age   INTEGER DEFAULT 30,      -- default when omitted
    vip   INTEGER NOT NULL DEFAULT 0
           CHECK (vip IN (0, 1))  -- only 0 or 1
);

Constraints are guardrails: the database itself rejects bad rows, so the app can't corrupt your data. You'll see the rejections in a moment.

Add rows, read them back

The same five rows we'll use in the examples that follow.

Reading data: three shapes of SELECT

-- every row, every column
SELECT * FROM users;
-- only the columns you need
SELECT name, email FROM users;
-- cap the number of rows
SELECT name FROM users LIMIT 2;

SELECT is the workhorse — most app traffic is reading. * is handy in the CLI; in app code, name the columns you want.

Filtering: the operators

WHERE is where the filtering happens — and (Part 4) where an index makes it fast.

WHERE on our shop data

-- 4 users: Ana, Bo, Cyd, Dee
-- 4 orders: 10 (u1, 120.00, paid)  11 (u1, 45.00, shipped)
--           12 (u2, 80.00, paid)   13 (u4, 9.50, pending)

SELECT name FROM users
WHERE name = 'Bo';

SELECT * FROM orders
WHERE total > 50;

Read each query as a sentence: 'give me rows where the condition is true'. The result is just the matching rows.

LIKE: patterns with wildcards

Each highlighted row = one product matching that pattern (across the 5 products: keyboard, mouse, usb-c hub, monitor, adapter). % = any string (even empty) · _ = exactly one character — this is how you do 'search box' queries.

Combining conditions: AND, OR, IN

SELECT * FROM orders
WHERE status = 'paid' AND total > 100;

SELECT name FROM users
WHERE name = 'Bo' OR name = 'Dee';

SELECT * FROM orders
WHERE status IN ('paid','shipped');

AND = stricter (narrower results), OR = looser. IN is just shorthand for a chain of = OR = OR ...

Primary key, foreign key, UNIQUE

Keys are what make rows trustworthy and tables linkable.

Watch the database say 'no'

Every bad write below is rejected by SQLite itself — the app never even sees the row.

ORDER BY and LIMIT

-- biggest first
SELECT * FROM orders
ORDER BY total DESC;

-- first two, alphabetically
SELECT name FROM users
ORDER BY name LIMIT 2;

-- result: Ana, Bo

ORDER BY sorts (ASC default, DESC reverse); LIMIT caps the count. Together they power 'top 10', 'latest 20', pagination.

Aggregates: COUNT, SUM — and GROUP BY

-- how many orders total?
SELECT COUNT(*) FROM orders;   -- 4

-- per user: how many orders, how much spent?
SELECT user_id, COUNT(*) AS n, SUM(total) AS spent
FROM orders
GROUP BY user_id;

GROUP BY collapses rows into one row per group, then the aggregate functions summarize each group. Cyd (id 3) has no orders, so he has no group.

Your Flask/Python code is just a query wrapper

import sqlite3

conn = sqlite3.connect("shop.db")
row  = conn.execute("SELECT name FROM users WHERE id = ?", (1,))
print(row.fetchone())      # ('Ana',)

sql  = "SELECT name, total FROM orders WHERE user_id =?"
rows = conn.execute(sql, (1,)).fetchall()
print(rows)                # [('Ana', 120.0), ('Ana', 45.0)]

connect → execute(query, values) → fetch. The SQL inside is exactly what you've been reading — the app is just the courier.

Your everyday window into the data

A tiny terminal program: open the file, inspect, query, quit.

One fat table vs. related tables

How you split your data is the single most important design decision — architecture, not syntax.

Only two shapes: 1:many and many:many

Nearly every relationship you'll ever model is one of these two.

Your shop, at a glance

Every order belongs to one user; every order has many line items; every item belongs to one order. That's the whole story of JOIN.

Read it as a map: the FK columns are the strings tying the cards together — JOIN is how you pull data across those strings.

Many:many — the junction table

A book has many authors, an author writes many books. book_authors holds exactly two foreign keys, turning 'many:many' into two 1:many relationships.

Each row in book_authors says: 'this author co-wrote this book'. No 'list of authors' column inside books.

How to design a model from scratch

Run this before writing any SQL — it's the architect's part of the job.

1. 1

   Spot the nouns

   'Users place orders containing items' → users, orders, line_items. Nouns become tables.

2. 2

   One table per noun

   Each table = one thing; its columns = that thing's attributes (what do you need to know about it?).

3. 3

   Link them

   1:many → FK on the 'many' side. Many:many → add a junction table with two FKs.

4. 4

   Pick the keys

   PK on every table; FK on link columns; UNIQUE on values that mustn't repeat (email).

5. 5

   Write it down

   CREATE TABLEs plus a sketch. The sketch is what you show people (and AI) to agree on the model.

What a JOIN actually does

One mechanic, three consequences.

INNER JOIN, step by step

SELECT u.name, o.total, o.status
FROM   users u
JOIN   orders o ON o.user_id = u.id
ORDER BY o.id;

Read it as: 'for each order, look up the user and put the name next to the total'. Cyd vanishes because INNER JOIN only keeps matches.

INNER vs LEFT JOIN — see the difference

SELECT u.name, o.total
FROM   users u
LEFT JOIN orders o ON o.user_id = u.id
ORDER BY u.id;

Cyd's row still appears with NULLs — 'this user has no orders'. RIGHT and FULL joins are the other directions; default (INNER) = only matches.

Chaining joins across three tables

SELECT o.id AS order_id, li.product, li.qty
FROM   orders o
JOIN   line_items li ON li.order_id = o.id
ORDER BY o.id, li.id;

Each JOIN adds the next table's columns, linked by its FK. This pattern — table → children → grandchildren — is the shape of most reports.

JOIN + WHERE: 'What did Anna buy?'

SELECT li.product, li.qty
FROM   users u
JOIN   orders o    ON o.user_id   = u.id
JOIN   line_items li ON li.order_id = o.id
WHERE  u.email = 'ana@ex.com';

The order of thinking: JOIN first (stitch the tables), then WHERE (narrow down). This exact shape — 'find X belonging to Y' — is the most common query in any app.

The missing-ON trap: a cartesian product

-- Forgot the ON clause:
SELECT u.name, o.total
FROM   users u, orders o;

Without a key to match on, the database pairs every row with every row: 4 users × 4 orders = 16 rows. If a result has more rows than it should — you've probably lost an ON.

How JOINs stay fast

The mechanic under the hood is simple — and indexable.

The index: a book's index

Created in one line: CREATE INDEX idx_orders_user ON orders(user_id);

Scan vs index: 100,000 orders

-- find all orders of user 1 (100,000 rows total)

-- no index → full scan:
--   read row 1... user_id = 7   ✗
--   read row 2... user_id = 3   ✗
--   ... 100,000 comparisons

-- with index → sorted lookup:
--   index says: user_id 1 → rows 10, 11
--   jump straight to those 2 rows
-- ~100,000 steps  →  ~17 steps

The index is a sorted list, so SQLite binary-searches it (~log2 of 100,000 ≈ 17 hops) instead of comparing every row.

EXPLAIN QUERY PLAN: watch the strategy change

SQLite will literally tell you whether it scans or uses the index.

Index the hot columns

-- index columns that appear in WHERE, ORDER BY, and ON:
CREATE INDEX idx_orders_user   ON orders(user_id);
CREATE INDEX idx_users_email   ON users(email);
CREATE INDEX idx_orders_status ON orders(status);

-- don't index:
--  * a column you never query (pure overhead)
--  * every column (each one taxes every write)

FK columns deserve indexes even if you only use them in JOINs — that's the Part 3 rule in practice.

What an index costs

Rule of thumb: index what you read a lot. Don't index everything — an index is a speed/writing-speed exchange.

A real session on shop.db

Count rows, ask a cross-table question, check the schema — all from the CLI.

Parameterized queries: ? is your friend

email = request.args.get('email')          # user input
sql  = "SELECT * FROM users WHERE email = ?"
rows = conn.execute(sql, (email,)).fetchall()

# ? is a placeholder — SQLite receives the query
# AND the value separately. The value is always
# treated as data, never as SQL.

This one habit — never pasting user input into a SQL string — prevents an entire class of security bugs.

SQL injection: the classic bug

Injection is one of the most common real-world security bugs. The ? style above is the standard fix.

When SQLite is enough — and when it isn't

It's the default until you hit a specific wall.

Four classic data-model sins

You'll meet all four of these in real projects.

The shipper's loop with AI on your team

You own the model; AI writes the queries; the CLI is your verification tool.

1. 1

   Model it

   Sketch the tables and links (1:many / m:n) before any SQL exists. Show it to a human to sanity-check.

2. 2

   Draft the SQL

   Describe the model + the question to AI; it writes the SELECT/JOIN. Read it against your sketch.

3. 3

   Verify in the CLI

   Open the .db in sqlite3, run it, look at actual rows. Wrong row count? The query or the model is off.

4. 4

   Speed it up

   EXPLAIN QUERY PLAN; if you see SCAN where you need speed, add an index on the hot column.

From our tiny shop to a real ecommerce site

users + orders was the tutorial. A real store has to survive money, shipping and time.

The full picture — core commerce

One table per thing: people, orders, order lines, products, categories, addresses, money. The FKs are the strings; every report is just a JOIN across them.

Beyond checkout

Carts are 'thinking of buying', kept apart from 'bought'; shipments live outside the order lifecycle; reviews are the product↔user relationship with data attached; coupons exist for promo accounting.

Why each core table exists

Read the third column as the test: if a table can't say why it exists, delete it — the data probably belongs elsewhere.

Why each extra table exists

Same test as before: every table earns its place by holding facts no other table should hold.

Three patterns you'll reuse — and what's next

Recognize these shapes in any schema, yours or someone else's.

Two reports on the ecommerce schema

SELECT p.name, SUM(oi.qty * oi.unit_price) AS revenue
FROM   order_items oi
JOIN   products p ON p.id = oi.product_id
JOIN   orders o   ON o.id = oi.order_id
WHERE  o.status IN ('paid','shipped')
GROUP BY p.name
ORDER BY revenue DESC
LIMIT 5;

SELECT o.id, o.total, o.status
FROM   orders o
JOIN   users u ON u.id = o.user_id
WHERE  u.email = 'ana@ex.com'
ORDER BY o.id DESC;

Exactly the skills you've learned: chained JOINs, WHERE filters, GROUP BY aggregates, ORDER BY + LIMIT top-N — only the model is bigger now.