Getting everything ready for you…

Loading courses, problems and your progress. This takes just a moment.

0s

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 to sqlite3.

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 use STRICT.

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 use YYYY-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 set PRAGMA 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:

  1. List books published after 2000, cheapest first.
  2. Show each member with the number of books they currently hold (not returned).
  3. Find books that have never been borrowed.
  4. Compute the average price per genre, rounded to 2 decimals.
  5. Add a members table and a foreign key from loans; turn on PRAGMA foreign_keys and try inserting an invalid loan.
  6. Write an UPSERT that increases stock when the same book is added again.
  7. Insert 10,000 rows in a transaction from Python and time it with and without BEGIN/COMMIT.
  8. 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