SQLite: When and How to Use It on a VPS

What Is SQLite?

SQLite is a serverless, file-based relational database. Unlike MySQL or PostgreSQL, it does not run as a separate process — it reads and writes directly to a single file on disk.

When to Use SQLite

• Small to medium web applications (under 100 concurrent users)
• Prototyping and development
• Embedded applications
• CLI tools and scripts
• Read-heavy workloads

When NOT to Use SQLite

• High-concurrency write workloads
• Multiple servers accessing the same database
• Very large databases (over 10 GB)
• Applications requiring replication

Installation

sudo apt install -y sqlite3
# PHP extension
sudo apt install -y php8.2-sqlite3

Basic Usage

sqlite3 myapp.db

-- Create a table
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Insert data
INSERT INTO users (name, email) VALUES ('John', 'john@example.com');

-- Query
SELECT * FROM users WHERE email LIKE '%@example.com';

-- Exit
.quit

Performance Tips

-- Enable WAL mode (better concurrency)
PRAGMA journal_mode=WAL;

-- Set busy timeout (wait instead of failing on lock)
PRAGMA busy_timeout=5000;

-- Optimize for read performance
PRAGMA cache_size=-20000;  -- 20MB cache

Backup

# Simple file copy (when no writes are happening)
cp myapp.db myapp.db.backup

# Online backup (safe during writes)
sqlite3 myapp.db ".backup myapp.db.backup"