SQLite for Lightweight Applications

What is SQLite?

SQLite is a serverless, zero-configuration, file-based database engine. It requires no separate server process — the database is a single file on disk. Ideal for small to medium applications, prototyping, and embedded systems.

Installation

sudo apt install -y sqlite3

Basic Operations

# Create/open database
sqlite3 myapp.db

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

# Insert data
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');

# Query
SELECT * FROM users;

# Exit
.quit

Useful Commands

.tables          -- List all tables
.schema users    -- Show table schema
.headers on      -- Show column headers
.mode column     -- Pretty-print output
.backup main backup.db  -- Backup database

When to Use SQLite

• Good for: single-user apps, prototypes, config storage, embedded devices, testing, small websites (<100k daily visitors)
• Not ideal for: high-concurrency writes, multiple application servers, databases over 1 TB

Backup

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

# Safe backup using .backup command
sqlite3 myapp.db ".backup backup.db"