SQLite Guide
SQLite vs PostgreSQL vs MySQL
| SQLite | PostgreSQL | MySQL | |
|---|---|---|---|
| Best for | Embedded, testing | Production web apps | Web apps (WordPress) |
| File-based | โ | โ | โ |
| Concurrent writes | Limited (WAL mode helps) | Excellent | Good |
| JSON support | JSON1 extension | Native JSONB | JSON type |
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)