SQLite is a lightweight, serverless, file-based database engine — a genuinely different approach from client-server databases like MySQL/PostgreSQL. This guide covers when it's the right choice and how to use it on a VPS.
What Makes SQLite Different
SQLite has no separate server process — the entire database lives in a single file, and your application links directly to the SQLite library to read/write it, with no network protocol or separate daemon involved.
When SQLite Is a Good Fit
- Small to medium applications with modest concurrent write load
- Development, testing, or prototyping before committing to a full database server
- Applications genuinely single-user or with low write concurrency (a personal tool, an internal utility)
- Embedded use within an application where a separate database server is unnecessary overhead
When SQLite Is NOT the Right Choice
- Applications with meaningful concurrent write load from multiple processes/users
- Need for network-accessible database access from multiple servers
- Applications requiring advanced database server features (replication, complex user permission management)
Installing SQLite
sudo apt install sqlite3 -yCreating a Database and Table
sqlite3 myapp.dbCREATE TABLE users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
email TEXT UNIQUE
);Basic Operations
INSERT INTO users (name, email) VALUES ('Jane Doe', '[email protected]');
SELECT * FROM users;
.quitUnderstanding SQLite's Concurrency Model
SQLite allows multiple simultaneous readers, but traditionally only one writer at a time — modern SQLite with WAL (Write-Ahead Logging) mode improves concurrent read/write handling considerably, though it's still not designed for the same concurrent write scale as a dedicated database server.
Enabling WAL Mode for Better Concurrency
PRAGMA journal_mode=WAL;Significantly improves concurrent access patterns compared to SQLite's default rollback journal mode, a worthwhile setting for most production SQLite use.
Backing Up a SQLite Database
sqlite3 myapp.db ".backup 'myapp-backup.db'"Or simply copy the file directly when the application isn't actively writing, since it's genuinely just a single file — though the built-in .backup command handles this more safely even during active use.
Migrating from SQLite to a Full Database Server
If your application outgrows SQLite's concurrency model, most frameworks/ORMs support migrating to MySQL/PostgreSQL with relatively contained code changes — a common growth path: start with SQLite for simplicity, migrate to a full database server once genuine concurrent write scale is needed.
File Permissions and Security
Since SQLite is just a file, standard file system permissions are your access control mechanism — ensure the database file isn't readable by unauthorized users, particularly important if it contains sensitive application data.
Common Errors
"database is locked" errors under concurrent access — a classic symptom of SQLite's write concurrency limits being exceeded for your actual usage; enable WAL mode first, and if issues persist, this is a strong signal you've outgrown SQLite for this specific use case.
Continue Reading
- MySQL vs PostgreSQL vs MongoDB vs Redis: Which Database Should You Use?
- Choosing Between SQL and NoSQL: A Decision Framework
- How to Install and Secure MySQL 8 on Ubuntu & Debian
Browse more articles in Databases.
