SQLite

SQLite is a self-contained, serverless, zero-configuration SQL database engine that runs entirely within your application as a library—no separate database server required. Unlike client-server databases (PostgreSQL, MySQL) requiring network connections and separate processes, SQLite reads and writes directly to a single cross-platform disk file. This simplicity makes SQLite the most widely deployed database engine in the world: it powers mobile apps (iOS, Android default), web browsers (Chrome, Firefox use SQLite for history/cookies), desktop applications, embedded systems, and development/testing environments. SQLite is remarkably capable despite its simplicity—supporting most SQL92 features, transactions, indexes, triggers, and views while requiring mere kilobytes of memory.

Best For:
SQLite excels for embedded applications, local data storage, and development environments. It's ideal for mobile applications (iOS/Android apps storing local data), desktop applications requiring local storage without server dependencies, embedded systems and IoT devices with limited resources, prototyping and development (no database server setup), command-line tools requiring structured data, browser extensions, and file-based data formats (applications can use SQLite instead of custom XML/JSON formats). SQLite is particularly valuable when you need SQL capabilities without operations overhead, guaranteed portability across platforms, or atomic file-level operations. It's the default choice when your data fits on one machine and you want zero configuration.

Common Questions

SQLite vs. PostgreSQL: when to use each?
Use SQLite for single-user applications, local data storage, embedded systems, prototyping, and scenarios where data fits on one machine without concurrent writes. Use PostgreSQL for multi-user applications, client-server architectures, high write concurrency, datasets exceeding single machine storage, or when you need advanced features (window functions, full-text search, extensions). SQLite is "file on disk," PostgreSQL is "database server." Many applications use both: SQLite for local/mobile storage, PostgreSQL for cloud backend. SQLite handles moderate read concurrency but limited write concurrency (one writer at a time).

Is SQLite suitable for production?
Yes, for appropriate use cases. SQLite powers billions of devices in production (every iPhone, Android phone, most web browsers). However, it's designed for embedded use, not client-server workloads. SQLite is production-ready for read-heavy applications, mobile apps, embedded systems, small-to-medium websites (under 100k requests/day), and development/testing. Avoid SQLite for high write concurrency (many simultaneous writers), distributed systems, large datasets (multi-terabyte), or applications requiring network access to database. SQLite developers say: "Use SQLite when you need a database file, not a database server."

How large can a SQLite database get?
SQLite theoretically supports databases up to 281 terabytes, but practical limits are lower. Most SQLite databases are under 1 GB, though multi-gigabyte databases are common and work well. Performance degrades with very large databases without proper indexing and optimization. SQLite is optimized for small-to-medium datasets fitting in typical device storage. For multi-terabyte datasets, client-server databases like PostgreSQL or distributed systems like Cassandra are better suited. SQLite's simplicity shines for databases under 10 GB; beyond that, consider alternatives.

Can multiple applications access a SQLite database simultaneously?
Yes, SQLite supports multiple concurrent readers and serializes writers (one writer at a time). Multiple processes can read simultaneously, but writes are exclusive. This works well for read-heavy workloads (100 readers, occasional writes) but becomes bottleneck for write-heavy applications. SQLite uses file-level locking—during writes, entire database is locked briefly. For high write concurrency, use PostgreSQL or MySQL. SQLite's concurrency model suits embedded applications, not high-traffic web servers.

Pricing & Plans

SQLite (Free, Public Domain): Complete SQL database engine with zero licensing restrictions—not even attribution required. All features included: transactions, indexes, triggers, views, CTEs, window functions (3.25+), JSON support, full-text search, R-Tree indexes. Free forever, no commercial version exists. Works on any platform. Maximum freedom: modify, redistribute, embed commercially without licenses or fees.

SQLite is truly free—public domain dedication means zero restrictions. No paid tiers, no enterprise edition, no support contracts (though commercial support available from third parties like Hwaci). Infrastructure costs only (storage for database file, compute for application).

Limitations

SQLite's architecture limits write concurrency—only one writer at a time, making it unsuitable for high write throughput applications (thousands of concurrent writers). It lacks user management and network access (no remote connections)—you must read/write the file directly on the same machine. SQLite doesn't support stored procedures, right/full outer joins, or some advanced PostgreSQL features (arrays, JSON path queries). Database-level security is limited to filesystem permissions—no granular user/role-based access control like PostgreSQL. Large ALTER TABLE operations can be slow (requires rebuilding entire table). SQLite has no built-in replication or clustering—it's single-file, single-machine. While SQLite supports gigabyte-scale databases, performance optimization requires more manual tuning than client-server databases with query planners. SQLite's type system is more flexible (dynamic typing) than strict SQL databases, which can cause unexpected behavior.

Getting Started

SQLite is pre-installed on macOS, Linux, and most systems. Check: sqlite3 --version. Create/open database: sqlite3 mydb.sqlite. The file is created if it doesn't exist. Basic commands:

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

-- Insert data
INSERT INTO users (email) VALUES ('alice@example.com');

-- Query data
SELECT * FROM users WHERE email LIKE '%example.com';

-- JSON support (SQLite 3.38+)
CREATE TABLE events (data JSON);
INSERT INTO events VALUES ('{"user": "alice", "action": "login"}');
SELECT json_extract(data, '$.user') FROM events;

Use SQLite from your application (Python - built-in sqlite3 module):

import sqlite3
conn = sqlite3.connect('mydb.sqlite')
cursor = conn.cursor()
cursor.execute("CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, email TEXT)")
cursor.execute("INSERT INTO users (email) VALUES (?)", ('alice@example.com',))
conn.commit()
cursor.execute("SELECT * FROM users")
print(cursor.fetchall())

Other languages: better-sqlite3 (Node.js), sqlite-jdbc (Java), rusqlite (Rust), all languages have mature SQLite bindings.

Enable Write-Ahead Logging (WAL) for better concurrency: PRAGMA journal_mode=WAL;. Use transactions for bulk operations (50-1000x faster). Create indexes on frequently queried columns. Use EXPLAIN QUERY PLAN to optimize queries. Back up with .backup command or simple file copy (when no writes). Use SQLite Browser (GUI) for visual exploration. Follow SQLite documentation (excellent) and "SQLite As An Application File Format" guidance. Consider SQLite for configuration files, caches, application state—it's often better than custom JSON/XML formats.