Free course

SQLite Course

Learn SQLite from the ground up: database files, the command-line shell, schemas, transactions, indexes, application access, backups, and practical limits.

4 modules · 26 lessons · No signup

Prerequisites: SQL

~~~

Your progress

of lessons completed.

Course completed.

What you'll learn

Build a small SQLite database, query it safely from an application, inspect its performance, and back it up without running a database server.

Take this course offline

Subscribe to my newsletter to get every free book and course in PDF and EPUB format.

Get the downloads

Course contents

  1. A database in one file

    Choose SQLite, open the shell, and create your first database file.

    1. ✓ What SQLite is
    2. ✓ When SQLite fits
    3. ✓ Install and open SQLite
    4. ✓ Create a SQLite database file
    5. ✓ Use the SQLite shell
    6. ✓ Check your understanding: a database in one file

  2. Schema and data

    Create tables and work with SQLite types, keys, constraints, and rows.

    1. ✓ SQLite storage classes and type affinity
    2. ✓ Create a table
    3. ✓ rowid and INTEGER PRIMARY KEY
    4. ✓ Protect data with constraints
    5. ✓ Insert, update, and delete rows
    6. ✓ Check your understanding: schema and data

  3. Transactions and performance

    Understand locking, WAL mode, indexes, and query plans.

    1. ✓ Use transactions
    2. ✓ Test SQLite locking with two connections
    3. ✓ Use write-ahead logging when it helps
    4. ✓ Add a useful index
    5. ✓ Read EXPLAIN QUERY PLAN
    6. ✓ Check your understanding: transactions and performance

  4. Applications and operations

    Use prepared statements, test in memory, migrate, back up, and know the limits.

    1. ✓ Use prepared statements
    2. ✓ Use SQLite from Node.js
    3. ✓ Use an in-memory database in tests
    4. ✓ SQLite permissions live outside the database
    5. ✓ Back up SQLite safely
    6. ✓ Change the schema with migrations
    7. ✓ Build a small notes database
    8. ✓ Check your understanding: applications and operations