When to Use SQLite
16 min
SQLite is an excellent default for local, single-process, embedded storage — but it has real limits that make a client-server database the better choice past a certain point: it locks the whole file during writes (so many concurrent writers serialize rather than running in parallel), and it isn't designed to be accessed directly over a network by multiple separate application servers.
def should_use_sqlite(concurrent_writers, data_size_gb, needs_network_access):
if needs_network_access:
return False
if concurrent_writers > 1:
return False
if data_size_gb > 100:
return False
return True
A mobile app's local cache, a desktop app's settings file, or a single-process CLI tool's data store are exactly the 'True' cases here -- a multi-instance web application's shared production database is exactly the 'False' case, which is why you'd reach for PostgreSQL or a managed cloud database instead.
Write `should_use_sqlite(concurrent_writers, data_size_gb, needs_network_access)`: return `False` if the app needs network access to the database, `False` if there's more than 1 concurrent writer, `False` if `data_size_gb` exceeds `100`, otherwise `True`.
Why does SQLite become a poor fit once an application needs MULTIPLE concurrent WRITERS?
You can reason through whether SQLite fits a given use case based on concurrency, scale, and network-access requirements.