SQLite Guide

SQLite vs PostgreSQL vs MySQL

Essential SQLite Commands

-- Create table with best practices
CREATE TABLE users (
    id      INTEGER PRIMARY KEY AUTOINCREMENT,
    email   TEXT NOT NULL UNIQUE,
    name    TEXT NOT NULL,
    created INTEGER NOT NULL DEFAULT (unixepoch()),
    data    TEXT  -- JSON stored as text
);

-- Index for common queries
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_created ON users(created DESC);

-- Insert with conflict handling
INSERT OR IGNORE INTO users (email, name) VALUES ('[email protected]', 'Alice');
INSERT OR REPLACE INTO users (email, name) VALUES ('[email protected]', 'Alice Updated');

-- JSON operations (JSON1 extension)
SELECT json_extract(data, '$.preferences.theme') FROM users WHERE id=1;
UPDATE users SET data = json_set(data, '$.score', 100) WHERE id=1;

WAL Mode (Recommended for Production)

-- Enable WAL mode (persist across connections)
PRAGMA journal_mode=WAL;
PRAGMA synchronous=NORMAL;  -- Faster writes, safe with WAL
PRAGMA cache_size=-64000;   -- 64MB cache
PRAGMA foreign_keys=ON;
PRAGMA busy_timeout=5000;   -- 5s timeout on lock

-- Check current WAL size
PRAGMA wal_checkpoint(TRUNCATE);

Go Integration (modernc.org/sqlite — pure Go)

import _ "modernc.org/sqlite"
import "database/sql"

db, err := sql.Open("sqlite", "./data.db?_pragma=journal_mode(WAL)")
db.SetMaxOpenConns(1)  // SQLite: serialize writes with single conn
db.SetMaxIdleConns(1)