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 Metadata

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.

PRAGMA page_count;
-- 42

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.

PRAGMA freelist_count;

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);

Returns the same columns as 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.

PRAGMA pragma_list;

Database Configuration

journal_mode

Returns or sets the journal mode.

PRAGMA journal_mode;
-- wal

PRAGMA journal_mode = wal;

Turso supports WAL (Write-Ahead Logging) mode. Rollback journal modes (DELETE, TRUNCATE, PERSIST, MEMORY) are not supported.

Turso Extension: Turso supports an experimental MVCC journal mode for concurrent writes:

PRAGMA journal_mode = mvcc;

When MVCC mode is active, you can use 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)

Only 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;

Foreign key enforcement is off by default for SQLite compatibility.

legacy_file_format

Returns the legacy file format flag.

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

data_sync_retry

Controls whether Turso retries a disk sync (fsync) after a failure instead of treating it as fatal.

PRAGMA data_sync_retry = 1;   -- retry on sync failure
PRAGMA data_sync_retry = 0;   -- do not retry (default)

require_where

Turso Extension: a safety pragma with no SQLite equivalent.

When enabled, Turso rejects any UPDATE or DELETE that does not include a WHERE clause, guarding against accidental full-table modifications. i_am_a_dummy is an alias that enables the same behavior.

PRAGMA require_where = 1;
DELETE FROM users;            -- rejected: no WHERE clause
DELETE FROM users WHERE id = 1;  -- allowed

PRAGMA i_am_a_dummy = 1;      -- equivalent to require_where = 1

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

Returns 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.

PRAGMA quick_check;

WAL Operations

wal_checkpoint

Forces a WAL checkpoint.

PRAGMA wal_checkpoint;

A checkpoint writes pages from the WAL file back to the database file.

MVCC Tuning

Turso Extension: these pragmas apply only when MVCC mode is enabled (PRAGMA journal_mode = mvcc).

mvcc_checkpoint_threshold

Sets the amount of committed work that accumulates before Turso triggers an MVCC checkpoint.

PRAGMA mvcc_checkpoint_threshold = 1000;

mvcc_gc_threshold

Sets the threshold that controls how aggressively Turso garbage-collects obsolete MVCC row versions.

PRAGMA mvcc_gc_threshold = 1000;

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');

The legacy 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');

CDC respects transaction boundaries. Changes are only recorded when a transaction commits. If a transaction rolls back, no CDC entries are created.

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';

Supported ciphers:

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');

Alternatively, specify encryption parameters in the database URI:

file:database.db?cipher=aegis256&hexkey=2d7a30...

To open an existing encrypted database, the cipher and key must be provided as URI parameters.

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 CREATE TYPE for creating custom types.

​PRAGMA Statements

​Syntax

​Database Metadata

​database_list

​page_count

​page_size

​max_page_count

​freelist_count

​encoding

​schema_version

​application_id

​user_version

​Schema Introspection

​table_info

​table_xinfo

​table_list

​index_list

​index_info

​index_xinfo

​function_list

​pragma_list

​Database Configuration

​journal_mode

​cache_size

​cache_spill

​synchronous

​temp_store

​busy_timeout

​query_only

​foreign_keys

​legacy_file_format

​ignore_check_constraints

​data_sync_retry

​require_where

​Integrity Checks

​integrity_check

​quick_check

​WAL Operations

​wal_checkpoint

​MVCC Tuning

​mvcc_checkpoint_threshold

​mvcc_gc_threshold

​Change Data Capture

​capture_data_changes_conn

​Capture Modes

​CDC Table Structure

​CDC Examples

​Encryption

​cipher

​hexkey

​Encryption Example

​Custom Types

​list_types

​See Also

PRAGMA Statements

Syntax

Database Metadata

database_list

page_count

page_size

max_page_count

freelist_count

encoding

schema_version

application_id

user_version

Schema Introspection

table_info

table_xinfo

table_list

index_list

index_info

index_xinfo

function_list

pragma_list

Database Configuration

journal_mode

cache_size

cache_spill

synchronous

temp_store

busy_timeout

query_only

foreign_keys

legacy_file_format

ignore_check_constraints

data_sync_retry

require_where

Integrity Checks

integrity_check

quick_check

WAL Operations

wal_checkpoint

MVCC Tuning

mvcc_checkpoint_threshold

mvcc_gc_threshold

Change Data Capture

capture_data_changes_conn

Capture Modes

CDC Table Structure

CDC Examples

Encryption

cipher

hexkey

Encryption Example

Custom Types

list_types

See Also