Best Practices

This is it — the last stop on the 37-lesson SQLite track, and rather than teaching anything new, this page pulls the most important, SQLite-specific habits from everything before it into one checklist. Every point below links back to the lesson that covers it in full depth, using RecipeBox as the running example throughout.

1. Turn On Foreign Key Enforcement — Every Connection, Every Time

PRAGMA foreign_keys = ON;

Covered in Primary & Foreign Keys and ATTACH DATABASE & PRAGMA.

• Unlike MySQL, SQLite does not enforce foreign keys by default — without this PRAGMA, a "foreign key" is just documentation
• It's a per-connection setting, so it's set every time the application opens a connection — not once, somewhere, and forgotten
• You can explain, in one sentence, what happens to a recipe's reviews if that recipe is deleted — and that behavior is actually enforced, not assumed

2. Turn On WAL Mode for Any App with Concurrent Readers and Writers

PRAGMA journal_mode = WAL;

Covered in WAL Mode & Concurrency.

• WAL mode lets readers keep reading while a writer is active, instead of blocking on the default rollback journal
• It's the right default for RecipeBox-style apps where the UI might query recipes while a background sync writes new ones
• Live databases in WAL mode carry -wal and -shm companion files — both are backed up and checkpointed correctly, not ignored

3. Wrap Bulk Writes in an Explicit Transaction

BEGIN;
INSERT INTO ingredients (recipe_id, name, quantity) VALUES (101, 'Garlic', '3 cloves');
INSERT INTO ingredients (recipe_id, name, quantity) VALUES (101, 'Pasta', '400g');
INSERT INTO ingredients (recipe_id, name, quantity) VALUES (101, 'Olive Oil', '2 tbsp');
COMMIT;

Covered in Transactions & ACID.

• Importing a recipe's ingredient list one INSERT at a time, each in its own implicit transaction, is dramatically slower than wrapping them all in one explicit BEGIN/COMMIT
• A mistake mid-batch is a ROLLBACK away from fully undone, instead of leaving a half-imported recipe behind
• This matters even more in SQLite than in a server database, since every implicit transaction commit is a separate disk sync

4. Run EXPLAIN QUERY PLAN Before Assuming a Query Is Slow

EXPLAIN QUERY PLAN
SELECT r.title, AVG(rv.rating)
FROM recipes r
JOIN reviews rv ON rv.recipe_id = r.id
WHERE r.user_id = 42
GROUP BY r.id;

Covered in Indexes and Query Optimization & EXPLAIN.

• A slow query gets diagnosed with EXPLAIN QUERY PLAN before reaching for a guess like "just add more indexes everywhere"
• A SCAN on a large table in the output is the signal to check for a missing index — not an assumption made in advance
• Indexes exist on purpose, on columns actually used in WHERE, JOIN, and ORDER BY — every index also slows down writes a little, so they're not free

5. Every Query Touching User Input Uses a Parameterized Query

SELECT * FROM recipes WHERE title = ?;
-- the ? is filled in by a bound parameter, never by string concatenation

Covered in Security Best Practices and Connecting from Application Code.

• SQLite has no user or privilege system to fall back on if this rule is broken — parameterized queries are the entire line of defense against SQL injection
• This holds in Python, PHP, Node, Java/Android, and every other language — the pattern is identical everywhere
• The .db file itself is protected with OS file permissions, and any ATTACH path stays hardcoded, never built from untrusted input

Checklist Recap

Along the way, using RecipeBox as one consistent, running example, you've built a genuine mental model of SQLite from the ground up — designing a schema with real keys and constraints, writing the full range of the SQL query language, combining tables with joins and subqueries, reaching for SQLite's own JSON functions and full-text search, tuning performance with indexes and EXPLAIN QUERY PLAN, keeping data consistent with transactions and triggers, and finally the operational skills — backups, integrity checks, security, and connecting from real application code — that it takes to ship a SQLite-backed app with confidence.