SQLite is a small, free database engine that stores an entire database in a single file. There’s no server to install or run: your application reads and writes the file directly, using standard SQL.
That simplicity is why SQLite is everywhere. It’s built into Python, Android, iOS, macOS, and every major web browser, and its developers estimate there are over a trillion SQLite databases in active use. It’s also one of the best ways to learn SQL, because you can start in seconds with nothing to configure.
This guide explains what SQLite is, how it compares to PostgreSQL and MySQL, when to use it (and when not to), and walks through creating a database, querying it, using it from Python, speeding it up with indexes, and backing it up. Every example was tested with SQLite 3.43.
What Is SQLite?
Most databases, such as PostgreSQL and MySQL, use a client-server design: a database server runs in the background, and applications connect to it over a network or socket.
SQLite is different. It’s an embedded database: a small library that becomes part of your application. Key traits:
- Serverless. Nothing to install, start, or keep running.
- Single file. The whole database (tables, indexes, data) lives in one ordinary file, such as
library.db, that you can copy, email, or put on a USB drive. - Zero configuration. No users, passwords, ports, or config files.
- Real SQL. It supports tables, joins, indexes, views, triggers, transactions, window functions, and JSON functions.
- Reliable. Transactions are ACID: a change either completes fully or not at all, even if the power goes out mid-write.
- Free and public domain. You can use it for anything, including commercial products, without a license.
Where SQLite Is Used
- Mobile apps on Android and iOS store their local data in SQLite.
- Web browsers use it for history, bookmarks, and cookies.
- Desktop and self-hosted apps, such as Uptime Kuma, use it as their default database.
- Python includes it in the standard library (
import sqlite3), so it’s the default for prototypes, scripts, and small Flask apps. - Data analysis: it’s a handy way to query CSV files and datasets with SQL.
- Testing: many projects run their test suites against SQLite because it starts instantly.
SQLite vs. PostgreSQL vs. MySQL
| SQLite | PostgreSQL | MySQL | |
|---|---|---|---|
| Architecture | Embedded library, single file | Client-server | Client-server |
| Setup | None | Install and configure a server | Install and configure a server |
| Users and permissions | No (uses file permissions) | Yes | Yes |
| Concurrent writers | One at a time | Many | Many |
| Network access | No, local file only | Yes | Yes |
| Best for | Apps, prototypes, small to medium sites, local data, learning | Multi-user apps, complex queries, large data | Web apps, especially on shared hosting |
When to Use SQLite
- Mobile, desktop, and embedded applications
- Prototypes, side projects, and personal tools
- Websites with modest traffic, especially where reads far outnumber writes
- Data analysis, scripts, and command-line tools
- Learning SQL and practicing queries
- A local cache or a file format for your own application
When to Choose Something Else
- Many users writing at the same time. SQLite allows only one writer at a time. Writes are fast, so this rarely matters for small apps, but heavily write-heavy multi-user systems are better on PostgreSQL or MySQL.
- Several servers sharing one database. SQLite is a local file. Don’t put it on a network share (NFS or SMB); file locking over networks is unreliable and can corrupt the database.
- You need user accounts and permissions inside the database.
- Very large datasets with heavy concurrent analytics. SQLite can handle large files, but a server database scales better under many simultaneous users.
A common pattern is to start with SQLite and move to PostgreSQL if you outgrow it. Libraries like SQLAlchemy make that switch mostly a matter of changing the connection string.
Installing SQLite
You may already have it:
- macOS: included. Open Terminal and run
sqlite3 --version. - Linux: usually included, or install it with
sudo apt install sqlite3on Ubuntu and Debian. - Windows: download the “command-line tools” bundle from sqlite.org/download.html, unzip it, and run
sqlite3.exe. - Python: the
sqlite3module is built in; no installation needed.
If you prefer a graphical tool, DB Browser for SQLite is free and lets you browse tables, edit data, and run queries.
New to the command line? The Linux terminal guide covers the basics.
Creating Your First Database
Run sqlite3 with a file name. If the file doesn’t exist, SQLite creates it:
sqlite3 library.db
You’ll see a sqlite> prompt. Commands that start with a dot (like .tables) are shell commands; everything else is SQL and must end with a semicolon. Type .quit to exit.
Create Tables
This example uses a small library with authors and books. Save it as setup.sql:
PRAGMA foreign_keys = ON;
CREATE TABLE authors (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
country TEXT
);
CREATE TABLE books (
id INTEGER PRIMARY KEY,
title TEXT NOT NULL,
author_id INTEGER NOT NULL REFERENCES authors(id),
year INTEGER,
price REAL CHECK (price >= 0)
);
INSERT INTO authors (name, country) VALUES
('Isabel Allende', 'Chile'),
('Gabriel García Márquez', 'Colombia'),
('Octavia E. Butler', 'United States');
INSERT INTO books (title, author_id, year, price) VALUES
('The House of the Spirits', 1, 1982, 16.99),
('One Hundred Years of Solitude', 2, 1967, 18.50),
('Love in the Time of Cholera', 2, 1985, 17.25),
('Kindred', 3, 1979, 15.00),
('Parable of the Sower', 3, 1993, 14.25);
Then load it and list the tables:
sqlite3 library.db < setup.sql
sqlite3 library.db ".tables"
authors books
An INTEGER PRIMARY KEY column gets a unique number automatically when you leave it out of an INSERT, so you don’t need AUTOINCREMENT in most cases.
Useful Shell Commands
| Command | What it does |
|---|---|
.tables | List tables |
.schema books | Show the CREATE statement for a table |
.headers on | Show column names in results |
.mode column | Line up results in columns (also table, csv, json, markdown) |
.import --csv file.csv table | Import a CSV file |
.backup file.db | Make a safe copy of the database |
.help | List all shell commands |
.quit | Exit |
Querying Data
Turn on headers and column mode, then run a query:
.headers on
.mode column
SELECT title, year, price FROM books WHERE price < 17 ORDER BY price;
title year price
------------------------ ---- -----
Parable of the Sower 1993 14.25
Kindred 1979 15.0
The House of the Spirits 1982 16.99
Joins and aggregates work as in any SQL database:
SELECT a.name AS author, COUNT(b.id) AS books, ROUND(AVG(b.price), 2) AS avg_price
FROM authors a
JOIN books b ON b.author_id = a.id
GROUP BY a.id
ORDER BY books DESC, author;
author books avg_price
---------------------- ----- ---------
Gabriel García Márquez 2 17.88
Octavia E. Butler 2 14.63
Isabel Allende 1 16.99
Insert, Update, and Delete
UPDATE books SET price = 13.99 WHERE title = 'Kindred';
DELETE FROM books WHERE year < 1900;
RETURNING (SQLite 3.35 and later) shows the rows you just changed, which is handy for getting a new ID:
INSERT INTO authors (name, country) VALUES ('Toni Morrison', 'United States')
RETURNING id, name;
id name
-- -------------
4 Toni Morrison
Always write a WHERE clause on UPDATE and DELETE. Without one, the change applies to every row.
Import a CSV File
Say you have new_books.csv:
title,author_id,year,price
Beloved,4,1987,16.00
Song of Solomon,4,1977,15.50
Import it into a temporary table (the first line becomes the column names), copy the rows into books, then drop the temporary table:
.import --csv new_books.csv books_import
INSERT INTO books (title, author_id, year, price)
SELECT title, author_id, year, price FROM books_import;
DROP TABLE books_import;
SQLite Data Types: What’s Different
SQLite stores every value as one of five storage classes: NULL, INTEGER, REAL, TEXT, or BLOB. A column’s declared type is a preference (called type affinity), not a strict rule. By default, SQLite lets you put text in an INTEGER column:
CREATE TABLE loose (n INTEGER);
INSERT INTO loose VALUES ('hello');
SELECT n, typeof(n) FROM loose;
n typeof(n)
----- ---------
hello text
If you want other databases’ strict behavior, add STRICT to the table (SQLite 3.37 and later):
CREATE TABLE tight (n INTEGER) STRICT;
INSERT INTO tight VALUES ('hello');
Error: cannot store TEXT value in INTEGER column tight.n
(The exact wording of error messages varies a little between SQLite versions.)
A few more differences to know:
- No date type. Store dates as ISO 8601 text (
'2026-11-15'or'2026-11-15 09:30:00'), which sorts correctly and works with SQLite’sdate()andstrftime()functions. - No boolean type. Use
INTEGERwith0and1. - Money:
REALis a floating-point number, so for exact currency amounts, store whole cents asINTEGER(1699 instead of 16.99).
Foreign Keys Are Off by Default
This surprises many people. For backward compatibility, SQLite doesn’t enforce foreign keys unless you turn them on for each connection:
INSERT INTO books (title, author_id) VALUES ('Ghost Book', 99);
Without the setting, this succeeds even though author 99 doesn’t exist. With it:
PRAGMA foreign_keys = ON;
INSERT INTO books (title, author_id) VALUES ('Ghost Book', 99);
Error: FOREIGN KEY constraint failed
Run PRAGMA foreign_keys = ON every time you open a connection, including in your application code. CHECK constraints, by contrast, are always enforced: inserting a negative price into books fails with CHECK constraint failed: price >= 0.
Using SQLite with Python
Python’s built-in sqlite3 module needs no installation. Save this as books.py in the same folder as library.db:
import sqlite3
conn = sqlite3.connect("library.db")
conn.row_factory = sqlite3.Row # access columns by name
conn.execute("PRAGMA foreign_keys = ON")
# Read: always pass values as parameters, never with f-strings
max_price = 17
rows = conn.execute(
"SELECT title, year, price FROM books WHERE price < ? ORDER BY price",
(max_price,),
).fetchall()
for row in rows:
print(f"{row['title']} ({row['year']}): ${row['price']:.2f}")
# Write: "with conn" commits on success and rolls back on error
with conn:
conn.execute(
"INSERT INTO books (title, author_id, year, price) VALUES (?, ?, ?, ?)",
("Fledgling", 3, 2005, 16.00),
)
count = conn.execute("SELECT COUNT(*) FROM books").fetchone()[0]
print(f"Books in the library: {count}")
conn.close()
Run it on the fresh database from setup.sql:
python3 books.py
Parable of the Sower (1993): $14.25
Kindred (1979): $15.00
The House of the Spirits (1982): $16.99
Books in the library: 6
Three habits to build:
- Use
?placeholders for values. Building SQL with f-strings or+lets user input change your query, which is how SQL injection happens. - Use
with conn:for writes. It commits when the block succeeds and rolls back if an error occurs. It doesn’t close the connection, so still callconn.close(). - Use
executemany()for many rows, which is much faster than a loop of single inserts:
new_authors = [("Toni Morrison", "United States"), ("Chimamanda Ngozi Adichie", "Nigeria")]
with conn:
conn.executemany("INSERT INTO authors (name, country) VALUES (?, ?)", new_authors)
Transactions Protect Your Data
If any statement inside with conn: fails, everything in the block is undone:
import sqlite3
conn = sqlite3.connect("library.db")
conn.execute("PRAGMA foreign_keys = ON")
try:
with conn:
conn.execute("UPDATE books SET price = price * 1.10")
conn.execute("INSERT INTO books (title, author_id) VALUES (?, ?)", ("Ghost Book", 99))
except sqlite3.IntegrityError as error:
print(f"Rolled back: {error}")
print(conn.execute("SELECT price FROM books WHERE title = 'Kindred'").fetchone()[0])
conn.close()
Rolled back: FOREIGN KEY constraint failed
15.0
The price increase was rolled back because the insert failed, so Kindred still costs 15.0.
For quick experiments, sqlite3.connect(":memory:") creates a temporary database in memory that disappears when the program ends. To load query results into pandas, see Python for Data Analysis: Accessing Your Data.
Making Queries Faster with Indexes
Without an index, SQLite reads every row to find matches. EXPLAIN QUERY PLAN shows what it will do:
EXPLAIN QUERY PLAN SELECT * FROM books WHERE author_id = 2;
QUERY PLAN
`--SCAN books
SCAN means a full table read. Add an index on the column you filter by:
CREATE INDEX idx_books_author ON books(author_id);
EXPLAIN QUERY PLAN SELECT * FROM books WHERE author_id = 2;
QUERY PLAN
`--SEARCH books USING INDEX idx_books_author (author_id=?)
SEARCH ... USING INDEX means SQLite jumps straight to the matching rows. On a table with millions of rows, that’s the difference between seconds and milliseconds. Index columns you often use in WHERE, JOIN, and ORDER BY, but not every column, since each index slows down writes a little. Why Your SQL Query Is Slow covers this in more depth.
Better Concurrency with WAL Mode
By default, a write blocks readers while it’s being saved. Write-ahead logging (WAL) mode lets readers keep reading while one writer writes, which helps web apps and anything with background tasks:
PRAGMA journal_mode = WAL;
wal
This setting is stored in the database file, so you only need to run it once. In WAL mode, you’ll see two extra files next to the database (library.db-wal and library.db-shm); they’re part of the database while it’s in use, so don’t delete them.
Backing Up an SQLite Database
Because the database is a single file, it’s tempting to just copy it. That’s only safe when nothing is writing to it; copying a file mid-write (or copying it without its -wal file) can give you a corrupted backup. Use one of these instead:
# From the sqlite3 shell or the command line
sqlite3 library.db ".backup library-backup.db"
# Or with SQL: a compact, consistent copy
sqlite3 library.db "VACUUM INTO 'library-copy.db';"
Both work while the database is in use. Check a copy with:
sqlite3 library-copy.db "PRAGMA integrity_check;"
ok
From Python, conn.backup() does the same thing. As with any database, automate your backups, keep a copy off the machine, and test restores. The PostgreSQL backup guide explains the 3-2-1 rule.
Common Mistakes to Avoid
- Forgetting
PRAGMA foreign_keys = ON, so invalid references slip in silently. - Building SQL with string formatting instead of
?placeholders. - Forgetting to commit. In Python, changes outside a
with conn:block aren’t saved until you callconn.commit(). - Putting the database on a network drive or a synced folder that other machines write to at the same time.
- Copying the file while the app is writing, or leaving the
-walfile behind. - Storing money as
REALwhen exact amounts matter. - Expecting strict types without
STRICTtables.
Frequently Asked Questions
Is SQLite a real database?
Yes. It supports standard SQL, transactions, indexes, constraints, joins, and window functions, and it’s used in production by billions of devices. It’s just embedded instead of client-server.
Is SQLite free to use commercially?
Yes. SQLite is in the public domain, so you can use it in any project, including commercial software, without a license or attribution.
How big can an SQLite database get?
The theoretical limit is about 281 terabytes. In practice, databases of many gigabytes work well; concurrency and backups usually become concerns before size does.
Can multiple users use SQLite at the same time?
Many users can read at once, but only one connection can write at a time. With WAL mode and short transactions, that’s fine for most small and medium apps. For heavy multi-user writing, use PostgreSQL or MySQL.
Is SQLite good for websites?
For sites with low to moderate traffic, especially read-heavy ones like blogs, small business sites, and internal tools, yes. SQLite’s own website runs on SQLite. For high-traffic apps with many simultaneous writes, a server database is a better fit.
Is SQLite secure?
It has no built-in users or passwords; anyone who can read the file can read the data. Protect it with file permissions, keep it outside your website’s public folder, and use disk encryption for sensitive data.
How do I open a .db or .sqlite file?
Run sqlite3 filename.db in a terminal, open it in DB Browser for SQLite, or connect with Python’s sqlite3.connect("filename.db").
Conclusion
SQLite gives you a full SQL database in a single file, with nothing to install or manage. It’s the right choice for apps, prototypes, scripts, data analysis, and small to medium websites, and one of the easiest ways to learn SQL.
Start with the library.db example: create the tables, run a few queries, then connect from Python. Remember the three SQLite-specific habits: turn on foreign keys, use ? placeholders, and back up with .backup or VACUUM INTO instead of copying the file. When your app grows to many simultaneous writers, you’ll be ready to move to PostgreSQL with the same SQL skills.
