Design and build a library database in SQLite: tables, keys, joins, reports and backups

This tutorial builds a small but complete relational database for a lending library, from the first sketch of the tables to a tested backup. SQLite is ideal for learning because the whole database is one file and the sqlite3 command-line tool is all you need: no server, no accounts, no configuration.

It is for beginners who know what a table is and want to learn how to design one properly and get answers out of it. Everything shown was run end to end with sqlite3 3.51, and the outputs below are copied from that run. The SQL itself is standard enough that most of it also works in PostgreSQL or MySQL.

What you will build

• A database file library.db with three related tables: books, members and loans.
• Primary keys, foreign keys, UNIQUE, NOT NULL and CHECK constraints that reject bad data, and a demonstration that they work.
• Queries that answer real questions: what is on loan, what is overdue, which books nobody borrows, who borrows most, how many copies are available.
• Indexes, with before-and-after query plans from EXPLAIN QUERY PLAN.
• Three kinds of backup and a restore test.

Before you start

• The sqlite3 command-line tool. macOS includes it. On Debian or Ubuntu run sudo apt install sqlite3; on Fedora or RHEL sudo dnf install sqlite. On Windows, download the command-line tools from the SQLite download page and unzip them. Any 3.x release from the last few years works; very old ones lack the table output mode used below.
• A text editor to save the .sql files, and a terminal.
• Time: about an hour.
• Knowledge: none beyond the idea that a table has rows and columns. The tutorial explains primary keys (a column that identifies each row), foreign keys (a column that points at a row in another table) and joins (combining rows from related tables) as it goes.

Step 1: Create a working folder and check sqlite3

mkdir library && cd library
sqlite3 --version

3.51.0 2025-06-12 13:14:41 f0ca7bba1c5e232e5d279fad6338121ab55af0c8c68c84cdfb18ba5114dcaapl (64-bit)

Your version string will differ. You will keep every SQL statement in a file and run it with sqlite3 library.db < file.sql. That way you can fix a mistake and run the file again, and you end up with a record of how the database was built.

Step 2: Design the tables on paper

Start from the things the library deals with and what you need to know about each one. Each thing becomes a table, each fact a column, and each relationship a foreign key.

• books: one row per title. ISBN, title, author, year published, and how many copies the library owns. The ISBN must be unique, the copy count may not be negative.
• members: one row per person. Name, email (unique, because it identifies the person to the outside world), date joined.
• loans: one row each time a member borrows a book. Which book, which member, when it was lent, when it is due, and when it came back (empty while it is still out). The due date cannot be before the loan date.

Why a separate loans table instead of a borrowed_by column in books? A book with two copies can be on loan to two people at once, and you want the history of past loans too. Whenever one thing can relate to many others over time, the relationship gets its own table. This is the core idea of normalization: store each fact once, in the table where it belongs, and connect tables with keys.

Step 3: Create the tables

Save this as schema.sql:

PRAGMA foreign_keys = ON;

CREATE TABLE books (
  book_id        INTEGER PRIMARY KEY,
  isbn           TEXT    NOT NULL UNIQUE,
  title          TEXT    NOT NULL,
  author         TEXT    NOT NULL,
  published_year INTEGER CHECK (published_year BETWEEN 1450 AND 2100),
  copies         INTEGER NOT NULL DEFAULT 1 CHECK (copies >= 0)
);

CREATE TABLE members (
  member_id INTEGER PRIMARY KEY,
  name      TEXT NOT NULL,
  email     TEXT NOT NULL UNIQUE,
  joined_on TEXT NOT NULL DEFAULT CURRENT_DATE
);

CREATE TABLE loans (
  loan_id     INTEGER PRIMARY KEY,
  book_id     INTEGER NOT NULL REFERENCES books(book_id),
  member_id   INTEGER NOT NULL REFERENCES members(member_id),
  loaned_on   TEXT NOT NULL,
  due_on      TEXT NOT NULL,
  returned_on TEXT,
  CHECK (due_on >= loaned_on),
  CHECK (returned_on IS NULL OR returned_on >= loaned_on)
);

• INTEGER PRIMARY KEY makes the column an alias for SQLite’s internal row id. If you insert a row without a value, SQLite picks the next number.
• REFERENCES books(book_id) declares a foreign key: every loans.book_id must match an existing book.
• CHECK constraints test each new or changed row; if the expression is false, the change is rejected.
• Dates are stored as TEXT in YYYY-MM-DD form. SQLite has no separate date type, but this format sorts and compares correctly and works with its date functions. DEFAULT CURRENT_DATE fills in today’s date in UTC.
• PRAGMA foreign_keys = ON is essential: SQLite does not enforce foreign keys unless each connection turns them on. Step 5 shows what happens otherwise.

sqlite3 library.db < schema.sql
sqlite3 library.db ".tables"

books    loans    members

The file library.db now exists. sqlite3 library.db ".schema loans" prints the table definition back if you want to check it.

Step 4: Insert sample data

Save this as data.sql. Fixed IDs and dates make the results below reproducible; the people and ISBNs are made up:

PRAGMA foreign_keys = ON;

INSERT INTO books (book_id, isbn, title, author, published_year, copies) VALUES
  (1, '978-0-00-000001-1', 'Pride and Prejudice', 'Jane Austen',      1813, 2),
  (2, '978-0-00-000002-8', 'Frankenstein',        'Mary Shelley',     1818, 1),
  (3, '978-0-00-000003-5', 'Moby-Dick',           'Herman Melville',  1851, 1),
  (4, '978-0-00-000004-2', 'Little Women',        'Louisa May Alcott',1868, 2),
  (5, '978-0-00-000005-9', 'The Time Machine',    'H. G. Wells',      1895, 1),
  (6, '978-0-00-000006-6', 'Dracula',             'Bram Stoker',      1897, 1);

INSERT INTO members (member_id, name, email, joined_on) VALUES
  (1, 'Alice Example', '[email protected]', '2026-01-10'),
  (2, 'Bob Example',   '[email protected]',   '2026-03-02'),
  (3, 'Carol Example', '[email protected]', '2026-06-21'),
  (4, 'Dan Example',   '[email protected]',   '2026-09-30');

INSERT INTO loans (book_id, member_id, loaned_on, due_on, returned_on) VALUES
  (1, 1, '2026-08-01', '2026-08-22', '2026-08-15'),
  (2, 1, '2026-09-01', '2026-09-22', '2026-09-20'),
  (3, 2, '2026-09-05', '2026-09-26', NULL),
  (1, 2, '2026-09-28', '2026-10-19', NULL),
  (5, 3, '2026-10-01', '2026-10-22', NULL),
  (2, 3, '2026-10-02', '2026-10-23', '2026-10-09'),
  (1, 1, '2026-10-03', '2026-10-24', NULL),
  (4, 1, '2026-10-05', '2026-10-26', NULL);

sqlite3 library.db < data.sql
sqlite3 library.db "SELECT (SELECT count(*) FROM books), (SELECT count(*) FROM members), (SELECT count(*) FROM loans);"

6|4|8

Six books, four members, eight loans. The loans that have no returned_on value were inserted with NULL, which means “still out”.

Step 5: Prove that the constraints protect the data

A schema is only as good as what it refuses. Save these deliberately wrong statements as constraint-tests.sql: a loan for a book that does not exist, a second member with Alice’s email, a negative copy count, a loan due before it starts, and deleting a member who still has loans. The last line counts the rows afterwards.

PRAGMA foreign_keys = ON;
INSERT INTO loans (book_id, member_id, loaned_on, due_on) VALUES (99, 1, '2026-10-10', '2026-10-31');
INSERT INTO members (name, email) VALUES ('Eve Example', '[email protected]');
INSERT INTO books (isbn, title, author, copies) VALUES ('978-0-00-000007-3', 'Test', 'Nobody', -1);
INSERT INTO loans (book_id, member_id, loaned_on, due_on) VALUES (6, 4, '2026-10-10', '2026-10-01');
DELETE FROM members WHERE member_id = 1;
SELECT (SELECT count(*) FROM members) AS members, (SELECT count(*) FROM loans) AS loans;

sqlite3 library.db < constraint-tests.sql

Runtime error near line 2: FOREIGN KEY constraint failed (19)
Runtime error near line 3: UNIQUE constraint failed: members.email (19)
Runtime error near line 4: CHECK constraint failed: copies >= 0 (19)
Runtime error near line 5: CHECK constraint failed: due_on >= loaned_on (19)
Runtime error near line 6: FOREIGN KEY constraint failed (19)
4|8

Every bad statement was rejected, and the counts are unchanged: still 4 members and 8 loans. When a script is piped in, sqlite3 reports the error and carries on with the next line, which is handy here; in an application you would treat each error as a failure.

Now see why the pragma matters. This script runs on a new connection without PRAGMA foreign_keys = ON, inserts the same orphan loan inside a transaction, counts orphans, and rolls back. Save it as fk-off-demo.sql:

PRAGMA foreign_keys;
BEGIN;
INSERT INTO loans (book_id, member_id, loaned_on, due_on) VALUES (99, 1, '2026-10-10', '2026-10-31');
SELECT count(*) AS orphan_loans FROM loans WHERE book_id NOT IN (SELECT book_id FROM books);
ROLLBACK;
SELECT count(*) AS loans_after_rollback FROM loans;

sqlite3 library.db < fk-off-demo.sql

0
1
8

The first 0 shows foreign-key enforcement is off for this connection, the 1 shows the bad loan was accepted, and the final 8 shows the rollback removed it again. Turn the pragma on in every connection, including in application code. PRAGMA foreign_key_check; lists any rows that already violate a foreign key; on this database it prints nothing.

Step 6: Write your first queries

SELECT picks columns, WHERE filters rows, ORDER BY sorts. Save as basics.sql. The first line is a sqlite3 command (it starts with a dot) that draws results as a table:

.mode table
SELECT title, author, published_year
FROM books
WHERE published_year < 1860
ORDER BY published_year;

sqlite3 library.db < basics.sql

+---------------------+-----------------+----------------+
|        title        |     author      | published_year |
+---------------------+-----------------+----------------+
| Pride and Prejudice | Jane Austen     | 1813           |
| Frankenstein        | Mary Shelley    | 1818           |
| Moby-Dick           | Herman Melville | 1851           |
+---------------------+-----------------+----------------+

Step 7: Combine tables with joins

A loan row only contains numbers. To show who borrowed what, join it to members and books on the matching keys. Save these four queries as joins.sql:

.mode table
SELECT m.name, b.title, l.loaned_on, l.due_on
FROM loans AS l
JOIN members AS m ON m.member_id = l.member_id
JOIN books   AS b ON b.book_id   = l.book_id
WHERE l.returned_on IS NULL
ORDER BY l.due_on;

SELECT m.name, b.title, l.due_on,
       julianday('2026-10-20') - julianday(l.due_on) AS days_overdue
FROM loans AS l
JOIN members AS m ON m.member_id = l.member_id
JOIN books   AS b ON b.book_id   = l.book_id
WHERE l.returned_on IS NULL
  AND l.due_on < '2026-10-20';

SELECT b.title
FROM books AS b
LEFT JOIN loans AS l ON l.book_id = b.book_id
WHERE l.loan_id IS NULL;

SELECT m.name
FROM members AS m
LEFT JOIN loans AS l ON l.member_id = m.member_id
WHERE l.loan_id IS NULL;

• Query 1, what is on loan right now: an inner JOIN keeps only rows that have a match on both sides. AS l, AS m and AS b are short aliases.
• Query 2, what is overdue on a given day. The date is fixed to 2026-10-20 so your output matches; in real use write date('now'). julianday() turns a date into a day number, so subtracting two of them gives days.
• Queries 3 and 4, books nobody has borrowed and members who never borrowed: a LEFT JOIN keeps every row from the left table and fills the right side with NULL when nothing matches. Filtering on l.loan_id IS NULL keeps exactly the rows without a match.

sqlite3 library.db < joins.sql

+---------------+---------------------+------------+------------+
|     name      |        title        | loaned_on  |   due_on   |
+---------------+---------------------+------------+------------+
| Bob Example   | Moby-Dick           | 2026-09-05 | 2026-09-26 |
| Bob Example   | Pride and Prejudice | 2026-09-28 | 2026-10-19 |
| Carol Example | The Time Machine    | 2026-10-01 | 2026-10-22 |
| Alice Example | Pride and Prejudice | 2026-10-03 | 2026-10-24 |
| Alice Example | Little Women        | 2026-10-05 | 2026-10-26 |
+---------------+---------------------+------------+------------+
+-------------+---------------------+------------+--------------+
|    name     |        title        |   due_on   | days_overdue |
+-------------+---------------------+------------+--------------+
| Bob Example | Moby-Dick           | 2026-09-26 | 24.0         |
| Bob Example | Pride and Prejudice | 2026-10-19 | 1.0          |
+-------------+---------------------+------------+--------------+
+---------+
|  title  |
+---------+
| Dracula |
+---------+
+-------------+
|    name     |
+-------------+
| Dan Example |
+-------------+

A common beginner mistake is to put a condition on the right-hand table of a LEFT JOIN into WHERE instead of ON. That silently turns it back into an inner join, because the NULL rows fail the condition. Step 8 shows the correct place.

Step 8: Build aggregate reports

GROUP BY collapses rows into groups, and aggregate functions such as COUNT, AVG and SUM compute one value per group. HAVING filters groups the way WHERE filters rows. Save as reports.sql:

.mode table
SELECT m.name, COUNT(l.loan_id) AS loans_total
FROM members AS m
LEFT JOIN loans AS l ON l.member_id = m.member_id
GROUP BY m.member_id, m.name
ORDER BY loans_total DESC, m.name;

SELECT b.title, COUNT(*) AS times_borrowed
FROM loans AS l
JOIN books AS b ON b.book_id = l.book_id
GROUP BY b.book_id, b.title
HAVING COUNT(*) >= 2
ORDER BY times_borrowed DESC;

SELECT b.title,
       b.copies,
       COUNT(l.loan_id)            AS on_loan,
       b.copies - COUNT(l.loan_id) AS available
FROM books AS b
LEFT JOIN loans AS l
       ON l.book_id = b.book_id AND l.returned_on IS NULL
GROUP BY b.book_id, b.title, b.copies
ORDER BY b.title;

SELECT strftime('%Y-%m', loaned_on) AS month, COUNT(*) AS loans
FROM loans
GROUP BY month
ORDER BY month;

SELECT ROUND(AVG(julianday(returned_on) - julianday(loaned_on)), 1) AS avg_days_kept
FROM loans
WHERE returned_on IS NOT NULL;

• Loans per member: COUNT(l.loan_id) counts only non-NULL values, so Dan, with no loans, shows 0. COUNT(*) would show 1 for him, because the left join still produces one row.
• Popular books: only titles borrowed at least twice, via HAVING.
• Availability: the condition l.returned_on IS NULL sits in the ON clause, so books without open loans stay in the result with 0 on loan.
• Loans per month: strftime('%Y-%m', ...) extracts year and month from the date text.
• Average loan length of returned books, rounded to one decimal.

sqlite3 library.db < reports.sql

+---------------+-------------+
|     name      | loans_total |
+---------------+-------------+
| Alice Example | 4           |
| Bob Example   | 2           |
| Carol Example | 2           |
| Dan Example   | 0           |
+---------------+-------------+
+---------------------+----------------+
|        title        | times_borrowed |
+---------------------+----------------+
| Pride and Prejudice | 3              |
| Frankenstein        | 2              |
+---------------------+----------------+
+---------------------+--------+---------+-----------+
|        title        | copies | on_loan | available |
+---------------------+--------+---------+-----------+
| Dracula             | 1      | 0       | 1         |
| Frankenstein        | 1      | 0       | 1         |
| Little Women        | 2      | 1       | 1         |
| Moby-Dick           | 1      | 1       | 0         |
| Pride and Prejudice | 2      | 2       | 0         |
| The Time Machine    | 1      | 1       | 0         |
+---------------------+--------+---------+-----------+
+---------+-------+
|  month  | loans |
+---------+-------+
| 2026-08 | 1     |
| 2026-09 | 3     |
| 2026-10 | 4     |
+---------+-------+
+---------------+
| avg_days_kept |
+---------------+
| 13.3          |
+---------------+

Check the numbers against the data by hand once: Pride and Prejudice has two copies and two open loans (Bob and Alice), so none is available. Doing this for a few rows is the best way to catch a wrong join.

Step 9: Add indexes and read the query plan

With eight loans every query is instant, but a real library has hundreds of thousands. An index is a sorted copy of one or more columns that lets the database jump to matching rows instead of reading the whole table. EXPLAIN QUERY PLAN shows which one it will do. Save three typical queries as plan.sql:

EXPLAIN QUERY PLAN
SELECT loan_id, book_id, due_on FROM loans WHERE member_id = 2;

EXPLAIN QUERY PLAN
SELECT m.name, b.title, l.due_on
FROM loans AS l
JOIN members AS m ON m.member_id = l.member_id
JOIN books   AS b ON b.book_id   = l.book_id
WHERE l.returned_on IS NULL
ORDER BY l.due_on;

EXPLAIN QUERY PLAN
SELECT title FROM books WHERE isbn = '978-0-00-000003-5';

sqlite3 library.db < plan.sql

QUERY PLAN
`--SCAN loans
QUERY PLAN
|--SCAN l
|--SEARCH m USING INTEGER PRIMARY KEY (rowid=?)
|--SEARCH b USING INTEGER PRIMARY KEY (rowid=?)
`--USE TEMP B-TREE FOR ORDER BY
QUERY PLAN
`--SEARCH books USING INDEX sqlite_autoindex_books_1 (isbn=?)

How to read it: SCAN reads every row of a table; SEARCH ... USING ... (col=?) looks rows up through an index or the primary key. USE TEMP B-TREE FOR ORDER BY means the results are sorted in a separate step. The ISBN lookup is already fast because UNIQUE created an index automatically (sqlite_autoindex_books_1), and joins on primary keys are always lookups.

The weak spots are the loan queries. Index the foreign-key columns (the SQLite manual notes such indexes are almost always beneficial, because otherwise deleting or changing a parent row also scans the child table) and add a partial index that only covers open loans, ordered by due date. Save as indexes.sql:

CREATE INDEX idx_loans_member ON loans(member_id);
CREATE INDEX idx_loans_book   ON loans(book_id);
CREATE INDEX idx_loans_open_due ON loans(due_on) WHERE returned_on IS NULL;
ANALYZE;

sqlite3 library.db < indexes.sql
sqlite3 library.db < plan.sql

QUERY PLAN
`--SEARCH loans USING INDEX idx_loans_member (member_id=?)
QUERY PLAN
|--SCAN l USING INDEX idx_loans_open_due
|--SEARCH m USING INTEGER PRIMARY KEY (rowid=?)
`--SEARCH b USING INTEGER PRIMARY KEY (rowid=?)
QUERY PLAN
`--SEARCH books USING INDEX sqlite_autoindex_books_1 (isbn=?)

The member lookup is now a SEARCH, and the open-loans report walks the small partial index, which already has the rows in due-date order, so the temporary sort is gone. The planner can use a partial index only when the query’s WHERE clause implies the index’s condition; here both say returned_on IS NULL. ANALYZE stores table statistics the planner uses to choose between indexes; in applications, the SQLite documentation recommends running PRAGMA optimize; after creating indexes and before closing connections instead.

Indexes are not free: each one takes space and slows down inserts and updates a little. Add them for queries you actually run, and confirm with EXPLAIN QUERY PLAN. Its output format may change between SQLite versions, so read it, but do not parse it in code.

Step 10: Back up the database and test a restore

Never back up a database that is in use by copying the file with cp: you may catch it halfway through a write. SQLite offers three safe ways:

mkdir -p backups
sqlite3 library.db ".backup backups/library-2026-10-20.db"
sqlite3 library.db "VACUUM INTO 'backups/library-2026-10-20-compact.db';"
sqlite3 library.db .dump > backups/library-2026-10-20.sql
ls -l backups

• .backup uses SQLite’s online backup API to write a consistent copy, even while other connections use the database.
• VACUUM INTO writes a consistent, compacted copy without free pages, so it can be smaller. The target file must not exist yet.
• .dump writes the whole database as SQL text: larger and slower to restore, but readable, easy to diff and independent of the file format.

Check a backup the way you would before trusting it: integrity, foreign keys and a row count. Then restore the SQL dump into a new file and compare:

sqlite3 backups/library-2026-10-20.db "PRAGMA integrity_check;" "PRAGMA foreign_key_check;" "SELECT count(*) FROM loans;"
sqlite3 restore-test.db < backups/library-2026-10-20.sql
sqlite3 restore-test.db "SELECT (SELECT count(*) FROM books), (SELECT count(*) FROM members), (SELECT count(*) FROM loans);"

ok
8
6|4|8

ok from the integrity check, no output from the foreign-key check, and the same counts as the original: the backups are good. Running VACUUM INTO a second time with the same file name fails, which protects existing backups:

Error: stepping, output file already exists

Put a date in every backup file name, keep several generations, and store copies on another disk or machine.

Check that it works

1. sqlite3 library.db ".tables" lists books, loans and members.
2. sqlite3 library.db < constraint-tests.sql prints five errors and 4|8.
3. sqlite3 library.db < reports.sql shows Pride and Prejudice with 0 available copies.
4. sqlite3 library.db ".indexes loans" lists idx_loans_book, idx_loans_member and idx_loans_open_due.
5. sqlite3 restore-test.db "SELECT count(*) FROM loans;" prints 8.

If something goes wrong

• Parse error near line 1: near "FROM": syntax error (with a caret under the problem). The word shown is where SQLite gave up; the mistake is usually just before it, such as a trailing comma after the last column, or a missing semicolon at the end of the previous statement. Also use single quotes for text values; double quotes are for table and column names.
• Parse error near line 3: table books already exists. You ran schema.sql a second time. Start again with a new file name, or delete library.db (see Clean up) and rerun the scripts in order.
• A foreign key is not enforced. The connection did not run PRAGMA foreign_keys = ON. It has to be set for every connection, including in programs, and it has no effect inside an open transaction.
• Counts in a report are too high. A join multiplies rows when one side has several matches. Count a specific column (COUNT(l.loan_id)) instead of *, or aggregate in a subquery before joining.
• Output is one long line with | separators. The .mode table line is missing (when input is piped in, sqlite3 uses plain list mode by default), or your sqlite3 is too old to know table; use .mode column and .headers on instead.

Clean up

Everything lives in the library folder. Warning: deleting it removes the database and its backups permanently; copy anything you want to keep first.

cd ..
rm -r library

Next steps

• Add a VIEW such as available_books for the availability report, so applications can query it like a table.
• Enforce a business rule with a trigger: refuse a new loan when no copy is available.
• Write a small program in Python with the built-in sqlite3 module that lends and returns books, using parameter placeholders (?) instead of building SQL strings.
• Load the same schema into PostgreSQL and note what changes: real date types, GENERATED ... AS IDENTITY keys and enforced foreign keys by default.
• Load a few hundred thousand generated loans and compare query times with and without the indexes from Step 9.

Official documentation

• SQLite: Foreign key support
• SQLite: EXPLAIN QUERY PLAN
• SQLite: VACUUM (including VACUUM INTO)