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

• Mobile Apps: The standard for iOS/Android local storage.
• Edge/IoT: Low memory footprint.
• App File Format: Instead of a custom config.xml, just use a SQLite db file.
• Small/Medium Web Apps: With WAL mode, it handles surprising concurrency (PocketBase, Castopod).

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

Serverless

There is no "connection" in the TCP sense. You just open the file.

Dynamic Typing

SQLite uses dynamic typing. A column declared INTEGER can actually store a string (though strict tables are now an option).

WAL Mode (Write-Ahead Logging)

Significantly improves concurrency. Allows multiple readers and one writer simultaneously.

Best Practices (2025)

Do:

• Use PRAGMA foreign_keys = ON: They are off by default.
• Use JSONB (SQLite 3.45+): Store JSON as efficient binary blobs (jsonb()) for 3x faster processing.
• Use STRICT Tables: CREATE TABLE t (...) STRICT enforces types like a traditional DB.

Don't:

• Don't use over NFS: File locking on network shares is buggy. Keep the DB file on local disk.
• Don't use for high-write webservices: If you have hundreds of concurrent writes, move to Postgres.

References

• SQLite Documentation