SQLite is a complete SQL database in a single file, embedded directly into your application — no server process, no network, no configuration. It’s the most deployed database in the world, and for many web apps on a single VPS it’s genuinely all you need.

Install the CLI

The SQLite library is usually already present (Python, PHP and most runtimes bundle it). For the command-line shell:

sudo apt install -y sqlite3      # Debian/Ubuntu
sudo dnf install -y sqlite       # RHEL family
sqlite3 --version

Create and query a database

sqlite3 /srv/app/data/app.db
CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);
INSERT INTO users (email) VALUES ('[email protected]');
SELECT * FROM users;

Useful shell commands:

.tables              -- list tables
.schema users        -- show CREATE statements
.mode box            -- nicer output (.mode column / csv / json)
.headers on
.import --csv data.csv users
.output out.csv      -- redirect output to a file
.quit

Run one-off queries from scripts: sqlite3 app.db "SELECT count(*) FROM users;".

Settings for web applications

SQLite’s defaults are conservative. For a web app with concurrent readers and writers, set these pragmas when the application opens a connection:

PRAGMA journal_mode = WAL;        -- readers don't block writers
PRAGMA synchronous = NORMAL;      -- safe with WAL, much faster than FULL
PRAGMA busy_timeout = 5000;       -- wait up to 5 s for locks instead of failing
PRAGMA foreign_keys = ON;         -- enforce foreign keys (off by default!)
PRAGMA cache_size = -64000;       -- ~64 MB page cache

journal_mode = WAL persists in the database file; the others are per connection. Frameworks like Rails 8, Django and Laravel have settings or recommendations for these.

Concurrency: what to expect

  • Many readers at once: no problem.
  • One writer at a time: writes are serialised. With WAL and short transactions, that still means hundreds to thousands of writes per second on SSD — plenty for most sites.
  • Keep write transactions short; long-running writes block other writers (database is locked errors without busy_timeout).

File permissions

The process that writes the database needs write access to the file and its directory (SQLite creates -wal and -shm files next to it):

sudo chown -R www-data:www-data /srv/app/data

Keep the database outside the web root, so it can’t be downloaded.

Backups

Don’t just copy the file while the app is writing — you may get a corrupted copy. Safe options:

sqlite3 /srv/app/data/app.db ".backup '/var/backups/app-$(date +%F).db'"
sqlite3 /srv/app/data/app.db "VACUUM INTO '/var/backups/app-$(date +%F).db'"

Both create a consistent copy while the app runs. Schedule with cron and copy off the server.

Litestream continuously streams SQLite changes to S3-compatible storage (e.g. MinIO) — near-real-time backups with point-in-time restore, running as a small sidecar process.

Maintenance

PRAGMA integrity_check;
PRAGMA optimize;          -- run periodically (or on connection close)
VACUUM;                   -- reclaim space after large deletes (rewrites the file)

When to move to PostgreSQL or MySQL

  • Several application servers need to write to the same database (SQLite is local to one machine; network filesystems are not safe for it).
  • Very high write concurrency.
  • You need database-level users and permissions, replication, or advanced features.

See SQLite vs PostgreSQL. For a single-server app, though, SQLite’s simplicity — no database server to secure, tune or upgrade — is a real advantage. Even the smallest VPS with SSD storage runs SQLite-backed apps very well.

  • SQLite
  • Databases
  • Linux