sqlite

SQLite

SQLite is an embedded SQL database engine. Unlike most other SQL databases, SQLite does not have a separate server process. It reads and writes directly to ordinary disk files.

When to Use

Embedded Mobile & Desktop Client Applications: The standard embedded database engine for iOS, Android, macOS, Windows, and Linux apps.
Edge Computing & Local Cache: Running fast relational databases inside Cloudflare D1, Turso, or local container storage.
Zero-Configuration Local Development: Powering unit tests and local prototypes without spinning up external database servers.
High-Read Single-Server Web Applications: Serving millions of read queries per day with SQLite Write-Ahead-Logging (WAL) mode enabled.

Quick Start

-- Enable WAL mode for concurrency
PRAGMA journal_mode=WAL;

-- Create table CREATE TABLE contacts ( contact_id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL, email TEXT NOT NULL UNIQUE, phone TEXT NOT NULL UNIQUE );


## Core Concepts

### Single-File Serverless Engine

SQLite runs directly in the host application's memory space, reading and writing to a single cross-platform disk file:

```text
[ Application Process (Python / Go / Node / Swift) ] ──Direct In-Memory Access──→ [ database.sqlite (Single File) ]

Write-Ahead Logging (WAL Mode)

Enables concurrent readers while a writer commits changes simultaneously:

-- Essential production pragmas for web apps
PRAGMA journal_mode = WAL;          -- Concurrent reads while writing
PRAGMA synchronous = NORMAL;        -- 2-3x write acceleration with durable safety
PRAGMA busy_timeout = 5000;         -- Wait up to 5s on busy locks before erroring
PRAGMA foreign_keys = ON;           -- Enforce relational foreign key constraints
PRAGMA cache_size = -64000;         -- Allocate 64MB memory page cache

Full-Text Search with FTS5

Built-in full-text search engine with BM25 relevancy ranking:

CREATE VIRTUAL TABLE document_search USING fts5(title, body);

INSERT INTO document_search (title, body) VALUES
('Distributed Systems', 'Consensus protocols and Raft algorithms in modern software.');

-- Fast full-text search
SELECT title, bm25(document_search) AS rank
FROM document_search
WHERE document_search MATCH 'Raft OR Consensus'
ORDER BY rank;

Common Patterns

WAL Mode and Concurrency Performance PRAGMAs

Problem: Default rollback journal locks entire database during writes, causing database is locked errors during concurrent reads.

Solution: Enable Write-Ahead Logging (WAL) and memory optimizations at connection initialization:

-- Enable concurrent readers and writer
PRAGMA journal_mode = WAL;

-- Optimize disk sync frequency for higher write throughput
PRAGMA synchronous = NORMAL;

-- Cache pages in memory (e.g. 64MB)
PRAGMA cache_size = -64000;

-- Store temporary tables and indexes in memory
PRAGMA temp_store = MEMORY;

-- Enable foreign key constraint checking
PRAGMA foreign_keys = ON;

Best Practices

Do:

Always Enable WAL Mode in Production: Run PRAGMA journal_mode = WAL; to prevent writers from blocking readers.
Set a busy_timeout: Configure PRAGMA busy_timeout = 5000; to avoid SQLITE_BUSY errors during concurrent transactions.
Enable Foreign Keys Explicitly: SQLite disables foreign keys by default; execute PRAGMA foreign_keys = ON; on every connection.
Use Prepared Statements: Eliminate SQL injection and optimize query plan reuse.

Don't:

Run multiple concurrent write transactions: SQLite supports only one writer at a time; queue writes or serialize them.
Use SQLite over Network Filesystems (NFS/SMB): Network file locking bugs can corrupt SQLite database files.
Store multi-gigabyte binary files directly: Store media in filesystem directories or object storage; store file paths in SQLite.

Troubleshooting

Error	Cause	Solution
`database is locked (SQLITE_BUSY)`	Another connection holds a write lock or transaction open too long.	Enable WAL mode and set busy timeout: `PRAGMA busy_timeout = 5000;`.
`database disk image is malformed (SQLITE_CORRUPT)`	Power loss during non-sync write, hardware fault, or concurrent file access.	Restore from backup, or dump readable rows using `.recover` command in sqlite3 CLI.
`FOREIGN KEY constraint failed`	Inserted row references non-existent parent foreign key.	Verify parent row exists before inserting child row.

References

SQLite Documentation