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

Download this course as PDF or EPUB

Read it offline, on your Kindle, or print it. Join my free newsletter and you get this course plus every other book and course I published.

Download PDFDownload EPUB
~~~

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.

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