SQLite in applications
You use SQLite in Python and Node.js applications with a proper connection, transaction, migration, concurrency and testing setup, and run it safely in production.
Level 301 · 16 lessons · 15 practice labs
Lessons
Connecting
- Connection lifecycle and settings
- Python sqlite3: row factories and adapters
- SQLite in Node.js
- Transactions in code and context managers
- Short task: a notes tool backed by SQLite
Schema evolution
- Migrations with user_version
- ALTER TABLE limits and rebuilding tables
- Seed data and test fixtures
- Short task: move the app to schema version 2
In production
- Many readers, one writer in WAL mode
- Busy errors and retries
- Backups from inside the app
- Remote copies with streaming replication
- Testing with an in-memory database
- When SQLite is not enough
- Capstone: a small web service backed by SQLite
Practice labs
- Fetch rows as dictionaries: Set row_factory on a Python sqlite3 connection and use query results by column name.
- Store dates and decimals correctly: Write adapters and converters so dates and money values go into the database and come back unchanged.
- Batch insert inside a transaction in Node.js: In a Node.js script, write 50,000 records with a prepared statement inside a transaction and compare with one-by-one inserts.
- Read the schema version with user_version: Use user_version to find which schema version three customer copies of an app are on.
- Change a column type by rebuilding the table: Make a type change ALTER TABLE cannot do by creating a new table, copying and renaming safely.
- Recover a half-finished migration: After a migration was cut off by a power loss, work out the database state and re-run the migration inside a transaction.
- Reproduce a lock error: Deliberately trigger "database is locked" with two scripts writing at once and explain why it happens.
- Add retries for busy errors: Add retries with growing back-off to an order-saving service and show no record is lost under load.
- Fix a WAL file grown by a long read: Find why a WAL file keeps growing because of a read left open and get checkpoints working again.
- Take an online backup from the app: Use Python's backup() method to take a consistent copy of the database while the app keeps running.
- Explain a broken backup made by file copy: Show why a backup taken with cp during writes is inconsistent and take it again the right way.
- Set up hourly backups with rotation: Write a script for a field app that backs up hourly, keeps the last 24 copies and checks each with integrity_check.
- Use an in-memory database in tests: Run unit tests on a :memory: database and make sure each test starts with a clean schema.
- Prepare repeatable test data: Write a seed data script for a parcel tracking app that produces the same records on every run.
- Measure where SQLite stops being enough: Measure latency with a growing number of concurrent writers and justify a move to a server engine with numbers.
About this course
You use SQLite in Python and Node.js applications with a proper connection, transaction, migration, concurrency and testing setup, and run it safely in production.
What you will learn
- Connecting
- Schema evolution
- In production
Prerequisites
You should be comfortable with the level 101 and 201 topics of the same path.
Lab environment
Lessons run in your own Kali Linux terminal that opens in the browser. Nothing to install; the lab starts clean in every lesson.
Frequently asked questions
- Is this course right for my level?
- The course is at level 301; it assumes you know the topics of the lower-level courses.
- Is there anything to install?
- No. Every lesson runs in your own lab environment that opens in the browser.
- How do lessons work?
- Each lesson has 8 to 10 steps and a short quiz. Early steps show the command to type, later steps give less help; if two attempts fail, the solution opens.
- Can I try it for free?
- Yes. The first three lessons of every course are free.