The SQLite Handbook: Learn SQLite Step by Step (Tested)

Ninad Pathak

October 7, 2026

SQLite is a complete SQL database in a single file, with no server to set up, which makes it a practical way to learn SQL. In this article I’ll walk you through SQLite from start to finish by building one small database, and each step links to the full guide for that topic.

TLDR

SQLite is a SQL database engine that runs inside your program and keeps the whole database in one file.
There’s no server to install or configure. sqlite3 library.db creates and opens a database.

The SQL you learn here, from joins to transactions, carries over to other databases.
Turn on foreign keys with PRAGMA foreign_keys = ON; on every connection, because SQLite leaves them off by default.
Back up a database with .dump, which writes it out as SQL.

What is SQLite and what will we build?

SQLite’s own definition: it “is an in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine” (About SQLite). In plain words, there’s no database server. Your program reads and writes an ordinary file through the SQLite library.

SQLite’s main features

Feature	What it means	Source
Serverless	No separate server process; the library reads and writes the file directly	About SQLite
Zero-configuration	Nothing to install, set up or administer before you use a database	About SQLite
Single file	A whole database, with every table and index, is one file	About SQLite
Cross-platform file	“You can freely copy a database between 32-bit and 64-bit systems or between big-endian and little-endian architectures”	About SQLite
Transactional	“ACID transactions, even after power loss”	About SQLite
Public domain	“Free for use for any purpose, commercial or private”	About SQLite
Compact	The library “can be less than 900KiB” with all features enabled	About SQLite

When SQLite is a good fit

SQLite’s docs put it this way: “SQLite does not compete with client/server databases. SQLite competes with fopen()” (Appropriate uses for SQLite). It fits programs and devices that own their data. For websites, the same page says “any site that gets fewer than 100K hits/day should work fine with SQLite”. A client/server database fits better when many machines write to the same data at once. SQLite vs MySQL compares the two in detail.

What we’ll build

To show you SQLite end to end, I will build a small library database with an authors table and a books table. Then I’ll query it and change it, and finish with a backup.

Prerequisites

What	Detail
The `sqlite3` shell	Included with macOS. On Linux and Windows, see SQLite installation
A terminal	Any shell. Tested with SQLite 3.51.0

Building a library database

Each step runs in the same library.db, in order. I turned foreign keys on for every connection in this run, because SQLite doesn’t enforce them by default.

Step 1: Create the database and tables

Save the schema as schema.sql. Running it against a file name that doesn’t exist yet creates the database (create a database):

PRAGMA foreign_keys = ON;
CREATE TABLE authors (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL UNIQUE
);
CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  author_id INTEGER NOT NULL REFERENCES authors(id),
  year INTEGER,
  pages INTEGER
);

$ sqlite3 library.db < schema.sql
$ ls -l library.db | awk '{print $5, $NF}'
16384 library.db
sqlite> .tables
authors  books

.tables is a shell command, not SQL. See SQLite commands and show tables. Column types work differently in SQLite than in other databases; SQLite data types explains how, and CREATE TABLE covers the constraints.

Step 2: Insert rows

INSERT INTO authors (name) VALUES ('Ursula K. Le Guin'), ('Terry Pratchett'), ('Octavia E. Butler');
INSERT INTO books (title, author_id, year, pages) VALUES
  ('A Wizard of Earthsea', 1, 1968, 183),
  ('The Left Hand of Darkness', 1, 1969, 286),
  ('Guards! Guards!', 2, 1989, 288),
  ('Small Gods', 2, 1992, 284),
  ('Kindred', 3, 1979, 264),
  ('Parable of the Sower', 3, 1993, 345);

$ sqlite3 library.db < data.sql
sqlite> SELECT count(*) AS books FROM books;
books
-----
6

Step 3: Query with SELECT and WHERE

sqlite> SELECT title, year FROM books WHERE year < 1980 ORDER BY year;
title                      year
-------------------------  ----
A Wizard of Earthsea       1968
The Left Hand of Darkness  1969
Kindred                    1979

WHERE filters rows and ORDER BY sorts them. See SQLite WHERE and SQLite operators.

Step 4: Join two tables

A join matches each book to its author through author_id:

sqlite> SELECT b.title, a.name AS author FROM books AS b JOIN authors AS a ON a.id = b.author_id WHERE a.name LIKE 'Terry%';
title            author
---------------  ---------------
Guards! Guards!  Terry Pratchett
Small Gods       Terry Pratchett

Step 5: Summarise with GROUP BY

Aggregate functions such as count() and sum() work per group:

sqlite> SELECT a.name, count(*) AS books, sum(b.pages) AS pages FROM books AS b JOIN authors AS a ON a.id = b.author_id GROUP BY a.name ORDER BY pages DESC;
name               books  pages
-----------------  -----  -----
Octavia E. Butler  2      609
Terry Pratchett    2      572
Ursula K. Le Guin  2      469

Step 6: Update a row

sqlite> UPDATE books SET pages = 182 WHERE title = 'A Wizard of Earthsea';
sqlite> SELECT title, pages FROM books WHERE id = 1;
title                 pages
--------------------  -----
A Wizard of Earthsea  182

See SQLite UPDATE.

Step 7: Let foreign keys reject bad data

With foreign keys on, a book can’t point at an author who doesn’t exist:

sqlite> INSERT INTO books (title, author_id) VALUES ('Mystery book', 99);
Error: stepping, FOREIGN KEY constraint failed (19)

Step 8: Delete rows

sqlite> DELETE FROM books WHERE year > 1990;
sqlite> SELECT count(*) AS books_left FROM books;
books_left
----------
4

See SQLite DELETE. To remove a whole table, use DROP TABLE.

Step 9: Add an index

EXPLAIN QUERY PLAN shows how SQLite will run a query. Before the index it reads the whole table (SCAN), and after it looks rows up directly (SEARCH):

sqlite> EXPLAIN QUERY PLAN SELECT title FROM books WHERE author_id = 2;
QUERY PLAN
`--SCAN books
sqlite> CREATE INDEX idx_books_author ON books(author_id);
sqlite> EXPLAIN QUERY PLAN SELECT title FROM books WHERE author_id = 2;
QUERY PLAN
`--SEARCH books USING INDEX idx_books_author (author_id=?)

Step 10: Save a query as a view

A view is a stored SELECT you can query like a table:

sqlite> CREATE VIEW book_list AS SELECT b.title, a.name AS author, b.year FROM books AS b JOIN authors AS a ON a.id = b.author_id;
sqlite> SELECT * FROM book_list ORDER BY year;
title                      author             year
-------------------------  -----------------  ----
A Wizard of Earthsea       Ursula K. Le Guin  1968
The Left Hand of Darkness  Ursula K. Le Guin  1969
Kindred                    Octavia E. Butler  1979
Guards! Guards!            Terry Pratchett    1989

Step 11: Group changes in a transaction

Inside a transaction, changes aren’t permanent until COMMIT. ROLLBACK undoes them:

sqlite> BEGIN; DELETE FROM books; SELECT count(*) AS inside_transaction FROM books; ROLLBACK;
inside_transaction
------------------
0
sqlite> SELECT count(*) AS after_rollback FROM books;
after_rollback
--------------
4

Running it: back up and restore the database

The whole database is the one library.db file. .dump writes it out as SQL statements, which rebuild it anywhere:

$ sqlite3 library.db .dump > library.sql
$ grep -c 'INSERT INTO' library.sql
7
$ sqlite3 copy.db < library.sql
sqlite> SELECT count(*) FROM books;
count(*)
--------
4

To work with a second database file at the same time, see ATTACH DATABASE.

Common problems

A foreign key isn’t enforced: run PRAGMA foreign_keys = ON; on each connection. See SQLite foreign keys.
database is locked: another connection is writing. See database is locked.
unable to open database file: the folder in the path doesn’t exist or isn’t writable. See unable to open database file.

A column accepts the wrong type: SQLite’s types are flexible unless the table is STRICT. See SQLite data types.

Conclusion

You’ve now used the core of SQLite, from creating a database file to rolling back a transaction. Every one of those works the same way when you call SQLite from Python or another language. Pick the next topic from the guides below, or start with all SQLite guides by topic.