TopolearnKnowing SQLite's model tells you when it's the perfect choice and when you need a client-server database.
Most databases are servers: a separate process your application connects to over a network. SQLite is different. It is a C library linked into your program, so reading and writing the database is a function call, not a network round trip. There's nothing to install, configure or keep running: the entire database — tables, indexes, schema — lives in one ordinary file that you can copy, email or check into a test fixture.
It is a real SQL database: transactions are ACID, it survives crashes and power loss, and it supports joins, CTEs, window functions, JSON, full-text search, upserts and RETURNING. It is also the most widely deployed database in the world, shipping in every phone, every major browser and Python's standard library.
Great fits:
- Application file formats and local storage for desktop, mobile and embedded devices.
- Tests, prototypes, scripts and data analysis.
- Websites and services on a single server with mostly reads — many handle substantial traffic.
- Caches and intermediate data.
Poor fits: many application servers writing over a network (SQLite allows only one writer at a time, and network filesystems break its locking), very high write concurrency, or very large datasets that need to be spread across machines. Then choose PostgreSQL or MySQL.
Example
import sqlite3
con = sqlite3.connect("shop.db") # creates the file if it doesn't exist
con.execute("CREATE TABLE IF NOT EXISTS product (id INTEGER PRIMARY KEY, name TEXT)")
con.execute("INSERT INTO product (name) VALUES ('pen')")
con.commit()
print(con.execute("SELECT sqlite_version()").fetchone()) # ('3.45.1',) or newer
print(con.execute("SELECT count(*) FROM product").fetchone()[0])
con.close()
mem = sqlite3.connect(":memory:") # a private in-memory database
print(mem.execute("SELECT 1 + 1").fetchone()) # (2,)Remember
- SQLite is a library, not a server; the database is one file
- Full ACID SQL with joins, CTEs, window functions and JSON
- Excellent for local, embedded and single-server use; one writer at a time
Watch out for
- Putting a SQLite file on a network share used by several machines.