SQLite § modules

Schemas and ATTACH

SQLite has no schema namespace in the conventional sense. Where PostgreSQL or SQL Server permit a database to contain multiple schemas — public, accounting, audit — and qualify table names with a schema prefix (audit.event), SQLite’s tables, indexes, views, and triggers all live in a single namespace within a single database file. The compensating mechanism is ATTACH DATABASE: a connection may attach several SQLite database files simultaneously, and tables in attached databases are addressed with a database-qualified name.

This page covers the three databases visible to every connection (main, temp, the implicit sqlite_schema), the ATTACH DATABASE and DETACH DATABASE statements, qualified table names, the cross-database transaction model, and the common patterns of multi-database use: separating ephemeral data from persistent data, archiving cold data, and connecting to remote-replicated databases.

main, temp, and sqlite_schema

Every SQLite connection has at least two databases:

main is the database opened by the connection — typically the file passed to sqlite3_open. Most tables live in main and are referenced without qualification.
temp is an automatically-created database, scoped to the connection, that holds temporary tables and indexes. Tables in temp are not visible to other connections and are deleted when the connection closes.

A third database name is reserved: sqlite_schema, the system table that records the schema of main. (The historical name is sqlite_master; both are accepted, but sqlite_schema is the contemporary spelling.) Each attached database has its own schema table accessible through the qualified name attached.sqlite_schema.

SELECT type, name FROM main.sqlite_schema;
SELECT type, name FROM temp.sqlite_schema;

The temp database is convenient for scratch results, intermediate computations, and any table whose lifetime is the lifetime of the connection. A CREATE TEMP TABLE is shorthand for CREATE TABLE temp.<name>:

CREATE TEMP TABLE staging (
    id  INTEGER PRIMARY KEY,
    val TEXT
);

-- equivalent
CREATE TABLE temp.staging (
    id  INTEGER PRIMARY KEY,
    val TEXT
);

ATTACH DATABASE

ATTACH DATABASE adds a second database file to the connection. The attached database is given a name, by which it is referred in qualified table names:

ATTACH DATABASE 'archive.db' AS archive;

-- The attached database has its own tables:
SELECT name FROM archive.sqlite_schema;

-- Cross-database query:
SELECT a.id, a.payload, m.user_id
FROM archive.event AS a
JOIN main.user AS m ON m.id = a.user_id;

The path argument is a file path or a URI; the URI form supports the same options as the connection URL (read-only, in-memory, immutable):

ATTACH DATABASE 'file:archive.db?mode=ro' AS archive;
ATTACH DATABASE ':memory:' AS scratch;

Up to ten databases (the default; controllable via SQLITE_LIMIT_ATTACHED) can be attached simultaneously, in addition to main and temp. The schema-prefix qualifier on every reference in cross-database queries is a small syntactic cost; the compensating gain is that a connection can reach data in several files without any of them being merged.

DETACH DATABASE releases the attachment:

DETACH DATABASE archive;

A database that is in the middle of a transaction cannot be detached.

Cross-database transactions

A transaction in SQLite spans every attached database. A BEGIN / COMMIT covering writes to both main and archive is atomic across both files: either both commit or both roll back. The mechanism is a two-phase commit at the file system level; the engine writes to a master journal that records the multi-file commit and replays it on recovery.

BEGIN;
DELETE FROM main.event WHERE occurred_at < date('now', '-1 year');
INSERT INTO archive.event SELECT * FROM main.event WHERE occurred_at < date('now', '-1 year');
COMMIT;

The transaction is atomic; a crash between the DELETE and the INSERT rolls back both. The cost of the multi-file commit is one additional fsync per attached database; for two databases, the commit cost is roughly doubled.

Patterns of multi-database use

Hot/cold separation

A common pattern: keep recent, frequently-queried data in a small main database and archive cold data to a separate archive database. The archive can be on slower or cheaper storage; the main stays compact and well-cached.

ATTACH DATABASE 'archive.db' AS archive;

-- Daily archive job:
BEGIN;
INSERT INTO archive.event SELECT * FROM main.event WHERE occurred_at < date('now', '-30 days');
DELETE FROM main.event WHERE occurred_at < date('now', '-30 days');
COMMIT;

-- Application reads only main; the archive is queried for occasional reports:
SELECT COUNT(*) FROM archive.event WHERE user_id = ?;

The pattern keeps the main database small (faster reads, smaller backups) without losing access to historical data.

Configuration database

A separate, small config.db containing user preferences and feature flags can be attached read-only. The configuration is updated by an administrative process and read by the application:

ATTACH DATABASE 'file:config.db?mode=ro' AS cfg;

SELECT value FROM cfg.setting WHERE key = 'feature.experimental_search';

The configuration has its own backup schedule, its own deployment cadence, and is administered separately from the application’s data.

Sharded data

A horizontally sharded application — events bucketed by user-id range, by month, or by region — can keep each shard in a separate database file:

ATTACH DATABASE 'shard_001.db' AS shard_001;
ATTACH DATABASE 'shard_002.db' AS shard_002;
ATTACH DATABASE 'shard_003.db' AS shard_003;

-- Cross-shard query (UNION ALL, since the schemas are identical):
SELECT * FROM shard_001.event WHERE user_id = ?
UNION ALL
SELECT * FROM shard_002.event WHERE user_id = ?
UNION ALL
SELECT * FROM shard_003.event WHERE user_id = ?;

The pattern is appropriate when each shard fits comfortably in one SQLite database (typically up to a few hundred gigabytes) and the application can route writes to the right shard based on a stable key. SQLite is not a horizontally distributed database, but the multi-database mechanism does support a measure of sharding within a single host.

Backup and replication

The VACUUM INTO form (3.27+) writes a compact copy of the current database to a new file:

VACUUM INTO 'backup-2026-05-08.db';

The form is atomic with respect to the source database; the copy is a consistent snapshot. It is the simplest backup mechanism for SQLite and is the recommended form for periodic backups.

For online backup of a database under continuous load, the sqlite3_backup C API copies pages incrementally between two open database connections. The API is exposed in the language bindings as connection.backup(target) (Python sqlite3) or equivalent.

For replication — a remote replica updated from a primary — the conventional mechanism is to stream the WAL to a follower; tools such as litestream and rqlite build replicated SQLite on this foundation. Cross-database ATTACH is not a replication mechanism; it requires both databases to be on the same host.

DCL is absent

SQL’s data-control language — the GRANT and REVOKE statements that control access — is absent in SQLite, as documented under Transactions and WAL. The implication for multi-database use is that there is no schema-level access control; any connection that has read access to the database file can read every table in it. The ATTACH model does not change this: an attached database is fully accessible to the connection that attached it. Access control is the host application’s responsibility, achieved through file-system permissions and through what databases the application chooses to attach.

VACUUM

VACUUM rebuilds the database file from scratch, reclaiming space left by deleted rows and defragmenting the storage. The operation is expensive — it copies the entire database — and requires roughly twice the space (for the temporary copy) during the operation.

VACUUM;                          -- vacuum main
VACUUM archive;                  -- vacuum the named attached database
VACUUM INTO 'compact.db';        -- vacuum into a new file (atomic snapshot)

The recommendation is to run VACUUM periodically (weekly or monthly) on databases that experience significant deletion. For databases that grow but rarely delete, VACUUM provides little benefit and can be skipped.

PRAGMA auto_vacuum = INCREMENTAL enables a lighter incremental vacuum that runs as a background pragma:

PRAGMA auto_vacuum = INCREMENTAL;       -- once, before any tables exist
PRAGMA incremental_vacuum;               -- reclaim free pages
PRAGMA incremental_vacuum(100);          -- reclaim up to 100 pages

The setting must be made before tables are created in the database; changing auto_vacuum on an existing database requires a VACUUM to take effect.

ANALYZE and PRAGMA optimize

The query planner benefits from up-to-date statistics about the distribution of values in tables and indexes. ANALYZE collects them:

ANALYZE;                         -- analyse the entire database
ANALYZE main.customer;           -- analyse a single table

PRAGMA optimize (3.18+) re-analyses tables whose statistics may be stale and is the recommended autoanalyze invocation:

PRAGMA optimize;

The pragma is safe to call frequently — it is a no-op for tables whose data has not substantially changed since the last analysis. The conventional placement is at connection close, where it picks up any changes made during the connection’s life and updates the planner statistics for the next connection’s queries.

Pragmas as configuration

The PRAGMA family is SQLite’s mechanism for engine-level configuration: settings that are not part of the SQL standard but are specific to SQLite. Pragmas have been mentioned throughout this volume; the most-used:

Pragma	Purpose
`PRAGMA journal_mode = WAL`	Enable WAL mode.
`PRAGMA synchronous = NORMAL`	Reduce fsync frequency in WAL mode.
`PRAGMA foreign_keys = ON`	Enable foreign-key enforcement.
`PRAGMA busy_timeout = 5000`	Wait up to 5 seconds on lock contention.
`PRAGMA cache_size = -262144`	Set page cache to 256 MB.
`PRAGMA temp_store = MEMORY`	Store temporary tables in memory.
`PRAGMA encoding = 'UTF-8'`	Set the text encoding (at creation only).
`PRAGMA case_sensitive_like = ON`	Make `LIKE` case-sensitive.
`PRAGMA query_only = ON`	Forbid writes for the connection.
`PRAGMA optimize`	Refresh the planner’s statistics.
`PRAGMA integrity_check`	Run a thorough check for corruption.
`PRAGMA quick_check`	Run a faster, less-thorough check.

The full list is at sqlite.org/pragma.html. Pragmas may be set by the application at connection open; some persist in the database file (journal_mode, auto_vacuum, encoding), most are connection-local.

A conventional connection-open script:

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA busy_timeout = 5000;
PRAGMA foreign_keys = ON;
PRAGMA temp_store = MEMORY;
PRAGMA cache_size = -65536;        -- 64 MB

The script is the canonical setup for an application-grade SQLite connection.