EXPLAINER

SQLite

SQLite is a relational database stored in one file inside your process. It is the right default for local tools, tests, mobile apps and modest servers. It is the wrong default for many writers across many application servers.

Short answer: SQLite is SQL in a single file, embedded in your process. Use it for local apps, tests and small services. Move to PostgreSQL when you have many servers writing at once.

What it is

SQLite is a relational database engine that lives inside your application as a library, not as a separate server process. The database is a file. You link the library or use the sqlite3 CLI, open the file, and run SQL. There is no network protocol and no database user account in the usual sense. Whoever can read the file can read the data.

That design is why phones, browsers, test suites and desktop apps use it. It is also a respectable choice for a low-traffic server with one writer. It is not a toy dialect. The SQL is real, the transactions are real, and the limits are about concurrency and operations, not about whether JOIN works.

Why and when it is used

Use SQLite for local development, automated tests, a desktop tool, a CLI that needs structured storage, or a small internal app deployed as a single process. Use it when backups are "copy the file" and that is acceptable. Use it when you want zero administration.

Move to PostgreSQL or MySQL when several application instances must write at the same time, when you need fine-grained network permissions, extensions you cannot compile in, or managed failover. Do not start on a server database for a prototype that is one process on one machine. Also do not pretend SQLite will be a multi-tenant SaaS backbone without reading its locking rules.

How it works

Your process calls into the library. A write takes a lock so other writes wait. Readers can proceed alongside a writer if you use WAL mode, which you should for anything concurrent. There is still one writer at a time. A network filesystem under the file is a classic way to corrupt it; put the file on a local disk. Transactions work. Foreign keys exist but must be enabled per connection (PRAGMA foreign_keys = ON), which surprises people coming from Postgres where they are always on.

Types are more flexible than Postgres. You can still declare types and constraints, and you should. The file grows and does not always shrink until you vacuum. Connections are cheap compared with a server database, but a pool of threads all writing will serialise.

Technologies and dependencies

The SQLite library, which is often already on the machine or embedded in your language runtime, and a file path in configuration. Drivers exist for every mainstream language. Prisma and other ORMs support it for local work; beware of features they use that differ in production Postgres. Tests that run on SQLite and production that runs on Postgres will drift. Either accept the drift and keep tests honest about it, or run Postgres in CI.

How to get started

  1. Install the sqlite3 shell or use your language's standard library (Python's sqlite3 is built in).
  2. Create a file, a table with a primary key and a foreign key, and turn foreign keys on.
  3. Insert and select from the shell, then from code, with parameters.
  4. Enable WAL if more than one thread will touch the file.
  5. Copy the file, open the copy, and confirm you have a backup. Then try that while a write is in progress and understand what you got.
  6. Point tests at a temporary file or an in-memory database so they do not share state.

If the next step is "deploy two containers that share this file over a volume", stop. That is the design SQLite is bad at. One writer process, or a server database.

Cautions and trade-offs

The file's permissions are the security model. A world-readable database file is a dump of your users. Backup copies multiply that. In-memory databases vanish when the process exits, which is perfect for tests and disastrous for data you meant to keep.

ALTER TABLE is more limited than in Postgres. Migrations need a little more care. Concurrent writes from many servers will fail or queue. And ORMs that use database-specific types will pass locally and break in production if you only test SQLite. Use SQLite where its simplicity is the point, and switch engines when the deployment model changes, not after the first corruption incident.