PRAGMA Statements
PRAGMA statements are special commands used to query or modify database configuration, retrieve metadata, and control database behavior. Unlike standard SQL, PRAGMAs are specific to SQLite and Turso.
Syntax
PRAGMA pragma-name;
PRAGMA pragma-name = value;
PRAGMA pragma-name(value);
database_list
Returns one row for each attached database.
PRAGMA database_list;
-- seq | name | file
-- 0 | main | /path/to/database.db
page_count
Returns the total number of pages in the database file.
page_size
Returns or sets the page size of the database. The page size can only be set before any tables are created.
PRAGMA page_size;
-- 4096
PRAGMA page_size = 8192;
max_page_count
Returns or sets the maximum number of pages allowed in the database file.
PRAGMA max_page_count;
PRAGMA max_page_count = 1000000;
freelist_count
Returns the number of unused pages in the database file.
encoding
Returns the text encoding used by the database.
PRAGMA encoding;
-- UTF-8
schema_version
Returns the schema version number. This value is incremented each time the schema changes.
PRAGMA schema_version;
-- 5
application_id
Returns or sets the application ID stored in the database header. Applications can use this 32-bit integer to identify the database file format.
PRAGMA application_id;
PRAGMA application_id = 12345;
user_version
Returns or sets the user version number. This is a 32-bit integer available for application use.
PRAGMA user_version;
PRAGMA user_version = 3;
Schema Introspection
table_info
Returns one row for each column in the named table.
PRAGMA table_info(table-name);
| Column | Type | Description |
|---|
| cid | INTEGER | Column index (0-based) |
| name | TEXT | Column name |
| type | TEXT | Declared type name |
| notnull | INTEGER | 1 if NOT NULL constraint exists |
| dflt_value | TEXT | Default value expression, or NULL |
| pk | INTEGER | 1 if column is part of the PRIMARY KEY |
CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT);
PRAGMA table_info(users);
-- cid | name | type | notnull | dflt_value | pk
-- 0 | id | INTEGER | 0 | | 1
-- 1 | name | TEXT | 1 | | 0
-- 2 | email | TEXT | 0 | | 0
table_xinfo
Similar to table_info but also includes hidden columns and additional metadata.
PRAGMA table_xinfo(table-name);
table_info plus a hidden column (0 for normal columns, non-zero for hidden columns in virtual tables).
table_list
Returns one row for each table and view in the database.
PRAGMA table_list;
-- schema | name | type | ncol | wr | strict
-- main | users | table | 3 | 0 | 0
-- main | orders | table | 4 | 0 | 0
index_list
Returns one row for each index on the named table.
PRAGMA index_list(table-name);
-- seq | name | unique | origin | partial
-- 0 | idx_email | 1 | c | 0
| Column | Type | Description |
|---|
| seq | INTEGER | Index sequence number |
| name | TEXT | Index name |
| unique | INTEGER | 1 if the index is UNIQUE |
| origin | TEXT | c for CREATE INDEX, u for UNIQUE constraint, pk for PRIMARY KEY |
| partial | INTEGER | 1 if the index is a partial index |
index_info
Returns one row for each column in the named index.
PRAGMA index_info(index-name);
-- seqno | cid | name
-- 0 | 2 | email
index_xinfo
Similar to index_info but includes additional columns.
PRAGMA index_xinfo(index-name);
function_list
Returns one row for each SQL function available.
PRAGMA function_list;
-- name | builtin | type | enc | narg | flags
pragma_list
Returns the list of all supported PRAGMA commands.
Database Configuration
journal_mode
Returns or sets the journal mode.
PRAGMA journal_mode;
-- wal
PRAGMA journal_mode = wal;
Turso Extension: Turso supports an experimental MVCC journal mode for concurrent writes:PRAGMA journal_mode = experimental_mvcc;
BEGIN CONCURRENT for optimistic concurrent write transactions. See Transactions for details. cache_size
Returns or sets the suggested maximum number of database pages held in memory.
PRAGMA cache_size;
PRAGMA cache_size = 2000; -- positive: number of pages
PRAGMA cache_size = -2000; -- negative: kilobytes of memory
cache_spill
Enables or disables cache spilling (writing dirty pages to the WAL before the cache is full).
PRAGMA cache_spill;
PRAGMA cache_spill = 0; -- disable
PRAGMA cache_spill = 1; -- enable
synchronous
Controls the fsync behavior for durability guarantees.
PRAGMA synchronous;
PRAGMA synchronous = OFF; -- no fsync (fastest, risk of corruption on crash)
PRAGMA synchronous = FULL; -- fsync after every transaction (safest)
OFF and FULL are supported in Turso.
temp_store
Controls where temporary tables and indexes are stored.
PRAGMA temp_store;
PRAGMA temp_store = 0; -- DEFAULT
PRAGMA temp_store = 1; -- FILE
PRAGMA temp_store = 2; -- MEMORY
busy_timeout
Sets the busy timeout in milliseconds. When a table is locked, Turso waits up to this many milliseconds before returning SQLITE_BUSY.
PRAGMA busy_timeout;
PRAGMA busy_timeout = 5000; -- 5 seconds
query_only
When enabled, prevents any changes to the database.
PRAGMA query_only = 1; -- enable read-only mode
PRAGMA query_only = 0; -- disable read-only mode
foreign_keys
Enables or disables foreign key constraint enforcement.
PRAGMA foreign_keys;
PRAGMA foreign_keys = ON;
PRAGMA foreign_keys = OFF;
PRAGMA legacy_file_format;
ignore_check_constraints
When enabled, CHECK constraints are not enforced.
PRAGMA ignore_check_constraints = 1; -- disable CHECK constraints
PRAGMA ignore_check_constraints = 0; -- enable CHECK constraints
Integrity Checks
integrity_check
Performs a thorough integrity check of the entire database.
PRAGMA integrity_check;
-- ok
PRAGMA integrity_check(N); -- check only the first N errors
ok if no problems are found, otherwise returns one row per error.
quick_check
Performs a faster but less thorough integrity check than integrity_check.
WAL Operations
wal_checkpoint
Forces a WAL checkpoint.
A checkpoint writes pages from the WAL file back to the database file.
Change Data Capture
Turso Extension: Change Data Capture (CDC) is a Turso-specific feature that tracks all data changes for replication, auditing, and reactive applications.
capture_data_changes_conn
Enables CDC for the current connection. Changes are captured to a designated table.
PRAGMA capture_data_changes_conn('mode');
PRAGMA capture_data_changes_conn('mode,table_name');
unstable_capture_data_changes_conn is still accepted for backwards compatibility.
| Parameter | Description |
|---|
| mode | Capture mode: off, id, before, after, full |
| table_name | Custom table name for storing changes (default: turso_cdc) |
Capture Modes
| Mode | Description |
|---|
off | Disable CDC for this connection |
id | Capture only the primary key/rowid of changed rows |
before | Capture row state before changes (for updates/deletes) |
after | Capture row state after changes (for inserts/updates) |
full | Capture both before and after states, plus update details |
CDC Table Structure
The CDC table contains the following columns:
| Column | Type | Description |
|---|
| change_id | INTEGER | Auto-incrementing unique identifier |
| change_time | INTEGER | Unix epoch timestamp |
| change_type | INTEGER | 1 (INSERT), 0 (UPDATE), -1 (DELETE) |
| table_name | TEXT | Name of the changed table |
| id | varies | Primary key/rowid of the changed row |
| before | BLOB | Row data before the change (modes: before, full) |
| after | BLOB | Row data after the change (modes: after, full) |
| updates | BLOB | Updated column details (mode: full) |
CDC Examples
-- Enable full CDC
PRAGMA capture_data_changes_conn('full');
-- Make changes
CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO users VALUES (1, 'Alice');
UPDATE users SET name = 'Alicia' WHERE id = 1;
DELETE FROM users WHERE id = 1;
-- Query the changes
SELECT change_type, table_name, id FROM turso_cdc;
-- 1 | users | 1 (INSERT)
-- 0 | users | 1 (UPDATE)
-- -1 | users | 1 (DELETE)
-- Use a custom table name
PRAGMA capture_data_changes_conn('full,audit_log');
-- Disable CDC
PRAGMA capture_data_changes_conn('off');
Encryption
Turso Extension: At-rest encryption is a Turso-specific feature. This feature is experimental and must be enabled before use. cipher
Sets the encryption cipher for the database.
PRAGMA cipher = 'aegis256';
| Cipher | Key Size | Description |
|---|
aes128gcm | 16 bytes | AES-128 in Galois/Counter Mode |
aes256gcm | 32 bytes | AES-256 in Galois/Counter Mode |
aegis128l | 16 bytes | AEGIS-128L |
aegis256 | 32 bytes | AEGIS-256 (recommended) |
aegis128x2 | 16 bytes | AEGIS-128 with 2x parallelization |
aegis128x4 | 16 bytes | AEGIS-128 with 4x parallelization |
aegis256x2 | 32 bytes | AEGIS-256 with 2x parallelization |
aegis256x4 | 32 bytes | AEGIS-256 with 4x parallelization |
hexkey
Sets the encryption key as a hexadecimal string.
PRAGMA hexkey = '2d7a30108d3eb3e45c90a732041fe54778bdcf707c76749fab7da335d1b39c1d';
Encryption Example
-- Set cipher and key before creating tables
PRAGMA cipher = 'aegis256';
PRAGMA hexkey = '2d7a30108d3eb3e45c90a732041fe54778bdcf707c76749fab7da335d1b39c1d';
CREATE TABLE secrets (id INTEGER PRIMARY KEY, data TEXT);
INSERT INTO secrets VALUES (1, 'sensitive data');
file:database.db?cipher=aegis256&hexkey=2d7a30...
Custom Types
Turso Extension: Custom types are a Turso-specific feature.
list_types
Lists all available types (built-in and custom) with their metadata.
PRAGMA list_types;
-- type | parent | encode | decode | default | operators
-- INTEGER | | | | |
-- REAL | | | | |
-- TEXT | | | | |
-- BLOB | | | | |
-- ANY | | | | |
See Also
