How to Set Up SQLite for Lightweight Applications

By Admin · Mar 2, 2026 · Updated Apr 24, 2026 · 34 views · 3 min read

How to Set Up SQLite for Lightweight Applications

SQLite is a self-contained, serverless, zero-configuration SQL database engine. It stores the entire database in a single file, making it perfect for lightweight applications, prototyping, embedded systems, and small-to-medium traffic websites running on your Breeze instances.

When to Use SQLite

SQLite is an excellent choice when:

Your application has low to moderate write concurrency (one writer at a time).
You want zero administration overhead — no database server process to manage.
Your dataset fits comfortably on a single Breeze instance (up to several terabytes).
You need a portable database that can be copied, backed up, or migrated by simply moving a file.

Installing SQLite

SQLite is usually pre-installed on most Linux systems. On your Breeze instance:

sudo apt update
sudo apt install -y sqlite3 libsqlite3-dev

Verify the installation:

sqlite3 --version

Creating a Database and Tables

Create a new database by simply opening it with the SQLite shell:

sqlite3 /var/lib/myapp/app.db

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

CREATE TABLE posts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    title TEXT NOT NULL,
    body TEXT,
    published_at DATETIME,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE INDEX idx_posts_user ON posts(user_id);
CREATE INDEX idx_posts_published ON posts(published_at);

Performance Optimization with WAL Mode

Write-Ahead Logging (WAL) mode significantly improves concurrent read performance and write throughput:

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA cache_size = -64000;    -- 64 MB cache
PRAGMA busy_timeout = 5000;    -- Wait 5 seconds on lock
PRAGMA temp_store = MEMORY;
PRAGMA mmap_size = 268435456;  -- 256 MB memory-mapped I/O
PRAGMA foreign_keys = ON;

Set these PRAGMAs at the start of each database connection in your application for best performance on your Breeze instance.

Using SQLite from Python

Python includes SQLite support in the standard library:

import sqlite3

conn = sqlite3.connect('/var/lib/myapp/app.db')
conn.execute("PRAGMA journal_mode=WAL")
conn.execute("PRAGMA foreign_keys=ON")
conn.row_factory = sqlite3.Row

cursor = conn.execute(
    "INSERT INTO users (name, email) VALUES (?, ?)",
    ("Alice", "alice@example.com")
)
conn.commit()

users = conn.execute("SELECT * FROM users").fetchall()
for user in users:
    print(f"{user['id']}: {user['name']} ({user['email']})")

Using SQLite from PHP

PHP has built-in SQLite3 support:

$db = new SQLite3('/var/lib/myapp/app.db');
$db->exec('PRAGMA journal_mode=WAL');
$db->exec('PRAGMA foreign_keys=ON');

$stmt = $db->prepare('INSERT INTO users (name, email) VALUES (:name, :email)');
$stmt->bindValue(':name', 'Bob', SQLITE3_TEXT);
$stmt->bindValue(':email', 'bob@example.com', SQLITE3_TEXT);
$stmt->execute();

Backup and Migration

Backing up an SQLite database is as simple as copying the file:

# Online backup using the .backup command
sqlite3 /var/lib/myapp/app.db ".backup /backup/app_$(date +%Y%m%d).db"

# Or use the SQLite backup API for a consistent snapshot
sqlite3 /var/lib/myapp/app.db "VACUUM INTO '/backup/app_vacuum.db'"

The VACUUM INTO command creates a compacted copy of the database, which is ideal for creating clean backups on your Breeze instance without locking the original database for an extended period.

How to Set Up SQLite for Lightweight Applications