Getting Started with SQLite

SQLite is a self-contained, serverless, zero-configuration SQL database engine. Unlike MySQL or PostgreSQL, there is no server process to install or manage. The database is a single file on disk. The library itself is a few hundred kilobytes and is included in Python, PHP, Node.js, and every modern operating system.

To start using SQLite, install the command-line shell from sqlite.org. On macOS it is preinstalled. On Ubuntu, apt-get install sqlite3. Windows users can download the CLI executable. Run sqlite3 mydatabase.db to create or open a database. If the file does not exist, SQLite creates it. That is the entire setup process.

-- Open or create a database
sqlite3 myapp.db

-- Create a table
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    created_at TEXT DEFAULT (datetime('now'))
);

-- Insert and query
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
SELECT * FROM users;

-- Display table schema
.schema users

SQL Dialect and Data Types

SQLite uses a dynamic type system called manifest typing. Unlike other SQL databases where a column has a fixed data type, SQLite stores the type of each value along with the value itself. You can declare column types like INTEGER, TEXT, REAL, BLOB, or NUMERIC, but SQLite uses type affinity rather than strict enforcement. A column declared as TEXT can still hold an integer if the inserted value is unquoted.

This flexibility is convenient but can lead to surprises. A column declared as INTEGER with affinity will try to convert inserted values to integers. But if the conversion fails, it stores the original value. Enable STRICT tables in SQLite 3.37+ if you want traditional type enforcement: CREATE TABLE t (id INTEGER PRIMARY KEY, name TEXT) STRICT.

SQLite supports most SQL-92 standard syntax. Common limitations include no RIGHT or FULL OUTER JOIN, no GRANT/REVOKE (authentication is handled by the file system), and limited ALTER TABLE support. You cannot add a foreign key constraint to an existing table; you must recreate it. These limitations rarely matter in practice but are worth knowing.

-- Type affinity examples
CREATE TABLE test (a TEXT, b INTEGER, c REAL);
INSERT INTO test VALUES (42, '42', 42);

SELECT typeof(a), typeof(b), typeof(c) FROM test;
-- Output: text, integer, real

-- STRICT table
CREATE TABLE strict_users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
) STRICT;

-- Common table expression
WITH recent AS (
    SELECT * FROM users WHERE created_at > date('now', '-7 days')
)
SELECT * FROM recent;

WAL Mode and Concurrency

SQLite uses a simple locking model by default. A write operation acquires an exclusive lock on the entire database file, blocking all other writers and readers. This is fine for single-user applications but limits concurrency. Write-Ahead Log (WAL) mode transforms this behavior dramatically.

Enable WAL mode with PRAGMA journal_mode=WAL. In WAL mode, readers do not block writers and writers do not block readers. Multiple readers can read simultaneously while a writer appends changes to the WAL file. The WAL file is periodically checkpointed back into the main database file. This is a game-changer for applications with mixed read and write workloads.

WAL mode has trade-offs. The WAL file grows until a checkpoint occurs. Applications that restart frequently without clean shutdowns may leave the WAL file in an inconsistent state, though SQLite handles crash recovery automatically. For most desktop and mobile applications, the benefits of WAL mode far outweigh the costs.

-- Enable WAL mode
PRAGMA journal_mode=WAL;
PRAGMA synchronous=NORMAL;

-- Check current mode
PRAGMA journal_mode;

-- Manual checkpoint
PRAGMA wal_checkpoint(TRUNCATE);

-- Performance settings for production
PRAGMA cache_size=-64000;  -- 64MB cache
PRAGMA page_size=4096;
PRAGMA foreign_keys=ON;

Full-Text Search with FTS5

SQLite includes full-text search through its FTS5 extension. FTS5 creates a virtual table that provides fast full-text indexing and searching. It supports stemming, prefix queries, phrase searches, and ranking. The extension is included in most SQLite builds but may need to be enabled in the compile flags.

Create an FTS5 table with CREATE VIRTUAL TABLE articles_fts USING fts5(title, body, content=articles, content_rowid=id). The content= option creates an external content table that mirrors your real table, avoiding data duplication. The FTS5 index stores only the tokenized text, not the original content.

Query with the MATCH operator: SELECT * FROM articles_fts WHERE articles_fts MATCH 'sqlite performance'. Use bm25() for ranking: SELECT *, bm25(articles_fts) AS rank FROM articles_fts WHERE articles_fts MATCH 'sqlite performance' ORDER BY rank. FTS5 supports boolean operators, column-specific searches, and near-distance queries.

-- Create FTS5 virtual table
CREATE VIRTUAL TABLE docs_fts USING fts5(
    title, body,
    content='docs',
    content_rowid='id',
    tokenize='porter unicode61'
);

-- Populate the FTS index
INSERT INTO docs_fts(rowid, title, body)
SELECT id, title, body FROM docs;

-- Search with ranking
SELECT title, snippet(docs_fts, 1, '', '', '...', 32)
FROM docs_fts
WHERE docs_fts MATCH 'sqlite AND (performance OR optimization)'
ORDER BY bm25(docs_fts)
LIMIT 20;

Performance Optimization and EXPLAIN

SQLite provides an EXPLAIN command that shows the virtual machine instructions for a query. While less detailed than PostgreSQL's EXPLAIN ANALYZE, it reveals whether the query planner uses indexes or performs full table scans. Look for the OpenRead and Column opcodes that access tables, and SeekGE or IdxGE that indicate index usage.

Indexes in SQLite follow the same principles as other databases. Create indexes for columns used in WHERE clauses and JOIN conditions. Use composite indexes for queries that filter on multiple columns. SQLite can use at most one index per table per query, so a composite index covering all filter conditions is often better than multiple single-column indexes.

The ANALYZE command collects statistics about tables and indexes, helping the query planner make better decisions. Run ANALYZE after significant data changes. The query planner also uses the PRAGMA optimize command, which performs routine maintenance and statistics updates without requiring DBA intervention.

-- Analyze query plan
EXPLAIN QUERY PLAN
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.active = 1
GROUP BY u.id;

-- Create index for the query
CREATE INDEX idx_users_active ON users(active);
CREATE INDEX idx_orders_user ON orders(user_id);

-- Update statistics
ANALYZE;
PRAGMA optimize;

Backup, Migration, and Practical Patterns

Backing up SQLite is trivial because the database is a single file. Copy the file while no write operations are active. For live backups, use the .backup command in the CLI or the backup API in your programming language. The backup API copies the database page by page, allowing concurrent reads during the operation.

Schema migrations in SQLite require careful handling due to limited ALTER TABLE support. SQLite supports ADD COLUMN but not DROP COLUMN or ALTER COLUMN. To modify a table, create the new table, copy data with INSERT INTO ... SELECT, drop the old table, and rename the new one. Wrap this in a transaction and use PRAGMA foreign_keys=OFF temporarily if needed.

SQLite excels in several niches: mobile applications (each iOS/Android app uses SQLite), desktop software, embedded systems, test databases (in-memory SQLite is perfect for unit tests), and prototyping. For high-write production web services that need concurrency, consider PostgreSQL instead. For everything else, SQLite is often the smartest choice.

# Backup using CLI
sqlite3 myapp.db ".backup myapp_backup.db"

# Backup using Python
import sqlite3
source = sqlite3.connect('myapp.db')
dest = sqlite3.connect('myapp_backup.db')
source.backup(dest)
dest.close()
source.close()

# Schema migration pattern
BEGIN TRANSACTION;
CREATE TABLE users_new (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT NOT NULL,
    role TEXT DEFAULT 'user'
);
INSERT INTO users_new SELECT id, name, email, 'user' FROM users;
DROP TABLE users;
ALTER TABLE users_new RENAME TO users;
COMMIT;