How to Install and Use SQLite on a VPS (Use Cases and Limits)

知識庫

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 -y

Creating a Database and Table

sqlite3 myapp.db

CREATE 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;
.quit

Understanding 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

Browse more articles in Databases.

知識庫

分類

分類