SQLite Documentation
A tiny database in a single file — perfect for mobile apps, desktop apps, prototypes and learning SQL.
SQLite is a complete SQL database stored in a single file — no server to install or run. It is the most widely deployed database in the world: every Android and iPhone app, browsers, desktop apps and many embedded devices use it. It is also the easiest way to learn SQL and to prototype projects.
1. Introduction & Installation
- Command line: download the "sqlite-tools" bundle from sqlite.org/download (Windows) or
sudo apt install sqlite3/brew install sqlite. - GUI: DB Browser for SQLite.
- From code: built into Python (
import sqlite3), Android, iOS; packages exist for Node.js (better-sqlite3), Java, Go and more.
sqlite3 library.db # opens (or creates) the database file
All examples on this page use this sample database:
-- Sample database: a small library
CREATE TABLE books (
id INTEGER PRIMARY KEY, -- auto-increments (alias for rowid)
title TEXT NOT NULL,
author TEXT NOT NULL,
year INTEGER,
price REAL,
genre TEXT DEFAULT 'General'
);
CREATE TABLE loans (
id INTEGER PRIMARY KEY,
book_id INTEGER NOT NULL REFERENCES books(id),
member TEXT NOT NULL,
borrowed TEXT NOT NULL, -- dates are stored as ISO text: YYYY-MM-DD
returned TEXT
);
INSERT INTO books (title, author, year, price, genre) VALUES
('Clean Code', 'Robert Martin', 2008, 450.0, 'Programming'),
('The Pragmatic Programmer', 'Hunt & Thomas', 1999, 520.0, 'Programming'),
('Atomic Habits', 'James Clear', 2018, 399.0, 'Self-help'),
('Wings of Fire', 'A. P. J. Abdul Kalam', 1999, 250.0, 'Biography'),
('Deep Work', 'Cal Newport', 2016, 350.0, 'Self-help');
INSERT INTO loans (book_id, member, borrowed, returned) VALUES
(1, 'Asha', '2026-09-01', '2026-09-10'),
(3, 'Ravi', '2026-09-05', NULL),
(1, 'Neha', '2026-09-12', NULL),
(4, 'Asha', '2026-10-01', '2026-10-08');
SELECT id, title, year FROM books ORDER BY year;
Output:
id | title | year
2 | The Pragmatic Programmer | 1999
4 | Wings of Fire | 1999
1 | Clean Code | 2008
5 | Deep Work | 2016
3 | Atomic Habits | 2018
⚠️ Common error: a "fresh, empty" database every time — you opened a different file (or
:memory:). Check the path you pass tosqlite3.
2. The sqlite3 Shell & Dot Commands
Lines starting with . are shell commands, not SQL (no semicolon needed):
.tables list tables
.schema books show the CREATE statement
.headers on show column names
.mode column aligned output (.mode box / table / csv / json also exist)
.import data.csv books load a CSV file into a table (use .mode csv first)
.output result.txt send query output to a file (.output to go back)
.read setup.sql run SQL from a file
.dump export the whole database as SQL
.backup backup.db copy the database
.quit exit
⚠️ Common error: forgetting the
;at the end of an SQL statement — the shell waits with...>for more input. Type;and press Enter.
3. Data Types & Type Affinity
SQLite uses dynamic typing with type affinity. A column's declared type is a preference, and every value has one of five storage classes:
| Storage class | Holds |
|---|---|
NULL |
missing value |
INTEGER |
whole numbers (up to 8 bytes) |
REAL |
floating point |
TEXT |
strings (UTF-8) |
BLOB |
raw bytes, stored exactly as given |
There is no separate DATE or BOOLEAN type — store dates as ISO text ('2026-10-20') or Unix time integers, and booleans as 0/1.
SELECT typeof(42), typeof(3.5), typeof('hi'), typeof(NULL), typeof(x'FF');
typeof(42) | typeof(3.5) | typeof('hi') | typeof(NULL) | typeof(x'FF')
integer | real | text | null | blob
Use STRICT tables (SQLite 3.37+) to enforce types like other databases:
CREATE TABLE scores (name TEXT NOT NULL, points INTEGER) STRICT;
INSERT INTO scores VALUES ('Asha', 90);
SELECT name, points, typeof(points) AS t FROM scores;
name | points | t
Asha | 90 | integer
⚠️ Common error: in a normal (non-STRICT) table
INSERT INTO books (year) VALUES ('soon')succeeds and stores text in an INTEGER column. Validate in your app or useSTRICT.
4. Operators & Functions
SELECT
title,
price * 0.9 AS sale_price,
title || ' by ' || author AS label, -- || joins strings
UPPER(SUBSTR(genre, 1, 4)) AS code,
LENGTH(title) AS len
FROM books
WHERE price BETWEEN 300 AND 460
ORDER BY price;
title | sale_price | label | code | len
Deep Work | 315.0 | Deep Work by Cal Newport | SELF | 9
Atomic Habits | 359.1 | Atomic Habits by James Clear | SELF | 13
Clean Code | 405.0 | Clean Code by Robert Martin | PROG | 10
Useful functions: ROUND, ABS, MAX/MIN (also as scalar functions), COALESCE, IFNULL, IIF(cond, a, b), INSTR, REPLACE, TRIM, LOWER, printf/format, random().
Dates use text functions:
SELECT member,
borrowed,
date(borrowed, '+14 days') AS due,
julianday('2026-10-20') - julianday(borrowed) AS days_out
FROM loans
WHERE returned IS NULL
ORDER BY borrowed;
member | borrowed | due | days_out
Ravi | 2026-09-05 | 2026-09-19 | 45.0
Neha | 2026-09-12 | 2026-09-26 | 38.0
date('now'), datetime('now', 'localtime'), strftime('%Y', borrowed) and modifiers like 'start of month', '+1 year' cover most needs.
⚠️ Common error: storing dates as
20/10/2026— they won't sort or compare correctly. Always useYYYY-MM-DD.
5. Filtering & Conditions
SELECT title, genre,
CASE
WHEN price >= 450 THEN 'premium'
WHEN price >= 350 THEN 'standard'
ELSE 'budget'
END AS tier
FROM books
WHERE genre IN ('Programming', 'Self-help') AND year > 2000
ORDER BY tier, title;
title | genre | tier
Clean Code | Programming | premium
Atomic Habits | Self-help | standard
Deep Work | Self-help | standard
LIKE is case-insensitive for ASCII in SQLite; use GLOB for case-sensitive Unix-style patterns:
SELECT title FROM books WHERE title LIKE '%work%';
SELECT title FROM books WHERE title GLOB 'Wing*';
title
Deep Work
title
Wings of Fire
6. Joins & Aggregation
-- books with how many times they were borrowed (including never)
SELECT b.title, COUNT(l.id) AS loans, MAX(l.borrowed) AS last_borrowed
FROM books b
LEFT JOIN loans l ON l.book_id = b.id
GROUP BY b.id
ORDER BY loans DESC, b.title;
title | loans | last_borrowed
Clean Code | 2 | 2026-09-12
Atomic Habits | 1 | 2026-09-05
Wings of Fire | 1 | 2026-10-01
Deep Work | 0 | NULL
The Pragmatic Programmer | 0 | NULL
SELECT genre, COUNT(*) AS books, ROUND(AVG(price), 1) AS avg_price
FROM books
GROUP BY genre
HAVING COUNT(*) > 1
ORDER BY genre;
genre | books | avg_price
Programming | 2 | 485.0
Self-help | 2 | 374.5
SQLite supports INNER, LEFT, CROSS joins, and since 3.39 also RIGHT and FULL OUTER JOIN.
⚠️ Common error: foreign keys are not enforced by default. Run
PRAGMA foreign_keys = ON;on every connection.
7. Inserting, Updating & Upserts
INSERT INTO books (title, author, year, price) VALUES ('Refactoring', 'Martin Fowler', 2018, 600)
RETURNING id, genre; -- get generated values back (3.35+)
id | genre
6 | General
CREATE TABLE stock (book_id INTEGER PRIMARY KEY, copies INTEGER NOT NULL);
INSERT INTO stock VALUES (1, 3);
-- UPSERT: insert, or update if the key already exists
INSERT INTO stock (book_id, copies) VALUES (1, 2)
ON CONFLICT(book_id) DO UPDATE SET copies = copies + excluded.copies;
INSERT INTO stock (book_id, copies) VALUES (2, 1)
ON CONFLICT(book_id) DO UPDATE SET copies = copies + excluded.copies;
UPDATE books SET price = price * 1.1 WHERE genre = 'Self-help';
DELETE FROM loans WHERE returned IS NOT NULL;
SELECT * FROM stock;
SELECT COUNT(*) AS open_loans FROM loans;
book_id | copies
1 | 5
2 | 1
open_loans
2
Other conflict options: INSERT OR IGNORE, INSERT OR REPLACE.
8. Transactions
Each statement is its own transaction unless you group them. Grouping is also much faster for bulk inserts.
CREATE TABLE wallet (member TEXT PRIMARY KEY, balance INTEGER CHECK (balance >= 0));
INSERT INTO wallet VALUES ('Asha', 500), ('Ravi', 100);
BEGIN;
UPDATE wallet SET balance = balance - 200 WHERE member = 'Asha';
UPDATE wallet SET balance = balance + 200 WHERE member = 'Ravi';
COMMIT;
SELECT * FROM wallet ORDER BY member;
member | balance
Asha | 300
Ravi | 300
Use ROLLBACK to undo everything since BEGIN, and SAVEPOINT name / ROLLBACK TO name for partial undo.
Write-Ahead Logging (PRAGMA journal_mode = WAL;) lets readers and one writer work at the same time — recommended for apps and web servers.
⚠️ Common error:
database is locked— another connection holds a write transaction. Keep transactions short, enable WAL, and setPRAGMA busy_timeout = 5000;.
9. Indexes & Performance
CREATE INDEX idx_books_author ON books (author);
CREATE INDEX idx_loans_open ON loans (book_id) WHERE returned IS NULL; -- partial index
EXPLAIN QUERY PLAN SELECT * FROM books WHERE author = 'Cal Newport';
id | parent | notused | detail
3 | 0 | 0 | SEARCH books USING INDEX idx_books_author (author=?)
SEARCH ... USING INDEX is good; SCAN books means a full table scan.
Useful pragmas: PRAGMA foreign_keys = ON, PRAGMA journal_mode = WAL, PRAGMA table_info(books), PRAGMA integrity_check, and VACUUM to shrink the file after big deletes. ANALYZE updates statistics for the query planner.
⚠️ Common error: inserting thousands of rows one by one without a transaction — each insert is a disk sync. Wrap them in
BEGIN ... COMMIT(often 100× faster).
10. Advanced Concepts
JSON functions (built in):
CREATE TABLE events (id INTEGER PRIMARY KEY, data TEXT);
INSERT INTO events (data) VALUES
('{"type":"login","user":"asha","device":{"os":"android"}}'),
('{"type":"purchase","user":"ravi","amount":499}');
SELECT json_extract(data, '$.type') AS type,
data ->> '$.user' AS user,
json_extract(data, '$.device.os') AS os
FROM events;
type | user | os
login | asha | android
purchase | ravi | NULL
Window functions, CTEs and views work like in other databases:
WITH ranked AS (
SELECT title, genre, price,
RANK() OVER (PARTITION BY genre ORDER BY price DESC) AS r
FROM books
)
SELECT genre, title, price FROM ranked WHERE r = 1 ORDER BY genre;
genre | title | price
Biography | Wings of Fire | 250.0
Programming | The Pragmatic Programmer | 520.0
Self-help | Atomic Habits | 399.0
Full-text search with the FTS5 extension:
CREATE VIRTUAL TABLE notes USING fts5(title, body);
INSERT INTO notes VALUES ('Binary search', 'Divide the sorted array in half each step');
SELECT title FROM notes WHERE notes MATCH 'sorted';
Using SQLite from code — always use parameters (?) to avoid SQL injection:
import sqlite3
con = sqlite3.connect("library.db")
con.row_factory = sqlite3.Row
con.execute("PRAGMA foreign_keys = ON")
with con: # commits or rolls back automatically
con.execute("INSERT INTO books (title, author) VALUES (?, ?)", ("Grokking Algorithms", "Aditya Bhargava"))
for row in con.execute("SELECT title, author FROM books WHERE author LIKE ?", ("%Bhargava%",)):
print(row["title"], "-", row["author"])
When not to use SQLite: many concurrent writers over a network, very large multi-server deployments, or when you need user accounts/permissions in the database — choose PostgreSQL or MySQL then.
11. Examples with Output
Example 1 — Overdue loans (14-day limit)
SELECT l.member, b.title, l.borrowed
FROM loans l
JOIN books b ON b.id = l.book_id
WHERE l.returned IS NULL AND date(l.borrowed, '+14 days') < '2026-10-20'
ORDER BY l.borrowed;
member | title | borrowed
Ravi | Atomic Habits | 2026-09-05
Neha | Clean Code | 2026-09-12
Example 2 — Most active member
SELECT member, COUNT(*) AS loans
FROM loans
GROUP BY member
ORDER BY loans DESC, member
LIMIT 1;
member | loans
Asha | 2
Example 3 — Books per decade
SELECT (year / 10) * 10 AS decade, COUNT(*) AS books
FROM books
GROUP BY decade
ORDER BY decade;
decade | books
1990 | 2
2000 | 1
2010 | 2
12. Practice Problems
Use the library database above:
- List books published after 2000, cheapest first.
- Show each member with the number of books they currently hold (not returned).
- Find books that have never been borrowed.
- Compute the average price per genre, rounded to 2 decimals.
- Add a
memberstable and a foreign key fromloans; turn onPRAGMA foreign_keysand try inserting an invalid loan. - Write an UPSERT that increases stock when the same book is added again.
- Insert 10,000 rows in a transaction from Python and time it with and without
BEGIN/COMMIT. - Create an FTS5 table of notes and search it.
13. Interview Questions
1. What is SQLite and how is it different from MySQL/PostgreSQL? An embedded, serverless database stored in one file and linked into the application. MySQL/PostgreSQL are client-server systems with users, network access and better concurrent writes.
2. What is type affinity?
Columns have a preferred type, but SQLite stores values with their own storage class (NULL, INTEGER, REAL, TEXT, BLOB). STRICT tables enforce types.
3. What is rowid?
A hidden 64-bit integer key on every table (unless WITHOUT ROWID). An INTEGER PRIMARY KEY column becomes an alias for it.
4. Are foreign keys enforced?
Only after PRAGMA foreign_keys = ON;, which must be set on each connection.
5. What is WAL mode? Write-Ahead Logging: changes go to a separate log first, allowing concurrent readers with a single writer and usually faster writes.
6. How does SQLite handle concurrency?
Many readers, one writer at a time (database-level lock). WAL improves this; long write transactions cause database is locked errors.
7. How do you store dates? As ISO-8601 text, Unix timestamps (INTEGER) or Julian day numbers (REAL), then use the date/time functions.
8. When should you not use SQLite? High write concurrency across many clients, very large datasets spread across servers, or when you need built-in access control.
9. What is an UPSERT?
INSERT ... ON CONFLICT(col) DO UPDATE ... — insert a row or update the existing one if a uniqueness constraint would be violated.
10. What does VACUUM do?
Rebuilds the database file, reclaiming free space and defragmenting it.
14. Cheat Sheet & References
Shell
# open a database and inspect it
sqlite3 app.db
sqlite3 app.db ".tables"
sqlite3 app.db ".schema books"
sqlite3 -header -column app.db "SELECT * FROM books LIMIT 5;"
sqlite3 app.db ".dump" > backup.sql
Tables
CREATE TABLE t (id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE, created TEXT DEFAULT (date('now')));
CREATE TABLE s (x INTEGER, y TEXT) STRICT;
ALTER TABLE t ADD COLUMN email TEXT;
DROP TABLE IF EXISTS t;
Queries
SELECT * FROM t WHERE name LIKE 'a%' ORDER BY id DESC LIMIT 10 OFFSET 20;
SELECT a.x, b.y FROM a LEFT JOIN b ON b.a_id = a.id;
SELECT g, COUNT(*) FROM t GROUP BY g HAVING COUNT(*) > 1;
SELECT title || ' - ' || author FROM books;
Writes
INSERT INTO t (name) VALUES ('x') RETURNING id;
INSERT INTO t (id, n) VALUES (1, 1) ON CONFLICT(id) DO UPDATE SET n = n + 1;
INSERT OR IGNORE INTO t (name) VALUES ('x');
UPDATE t SET n = 0 WHERE id = 1; DELETE FROM t WHERE id = 1;
BEGIN; COMMIT; ROLLBACK;
Dates and JSON
SELECT date('now'), datetime('now', 'localtime'), date(d, '+7 days'), strftime('%Y-%m', d);
SELECT julianday(b) - julianday(a);
SELECT json_extract(data, '$.user'), data ->> '$.type' FROM events;
Pragmas and performance
PRAGMA foreign_keys = ON; PRAGMA journal_mode = WAL; PRAGMA busy_timeout = 5000;
PRAGMA table_info(books); PRAGMA integrity_check;
CREATE INDEX idx_t_name ON t (name); EXPLAIN QUERY PLAN SELECT * FROM t WHERE name = 'x';
VACUUM; ANALYZE;
Official documentation