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

sql

PRAGMA pragma-name;
PRAGMA pragma-name = value;
PRAGMA pragma-name(value);

Database Metadata

database_list

Returns one row for each attached database.

sql

PRAGMA database_list;
-- seq | name | file
-- 0   | main | /path/to/database.db

page_count

Returns the total number of pages in the database file.

sql

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.

sql

PRAGMA page_size;
-- 4096

PRAGMA page_size = 8192;

max_page_count

Returns or sets the maximum number of pages allowed in the database file.

sql

PRAGMA max_page_count;
PRAGMA max_page_count = 1000000;

freelist_count

Returns the number of unused pages in the database file.

sql

PRAGMA freelist_count;

encoding

Returns the text encoding used by the database.

sql

PRAGMA encoding;
-- UTF-8

schema_version

Returns the schema version number. This value is incremented each time the schema changes.

sql

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.

sql

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.

sql

PRAGMA user_version;
PRAGMA user_version = 3;

Schema Introspection

table_info

Returns one row for each column in the named table.

sql

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

sql

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.

sql

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.

sql

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.

sql

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.

sql

PRAGMA index_info(index-name);
-- seqno | cid | name
-- 0     | 2   | email

index_xinfo

Similar to index_info but includes additional columns.

sql

PRAGMA index_xinfo(index-name);

function_list

Returns one row for each SQL function available.

sql

PRAGMA function_list;
-- name | builtin | type | enc | narg | flags

pragma_list

Returns the list of all supported PRAGMA commands.

sql

PRAGMA pragma_list;

Database Configuration

journal_mode

Returns or sets the journal mode.

sql

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.

<Info> **Turso Extension**: Turso supports an experimental MVCC journal mode for concurrent writes:

sql

PRAGMA journal_mode = mvcc;

When MVCC mode is active, you can use BEGIN CONCURRENT for optimistic concurrent write transactions. See Transactions for details. </Info>

cache_size

Returns or sets the suggested maximum number of database pages held in memory.

sql

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

sql

PRAGMA cache_spill;
PRAGMA cache_spill = 0;    -- disable
PRAGMA cache_spill = 1;    -- enable

synchronous

Controls the fsync behavior for durability guarantees.

sql

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.

sql

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.

sql

PRAGMA busy_timeout;
PRAGMA busy_timeout = 5000;   -- 5 seconds

query_only

When enabled, prevents any changes to the database.

sql

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.

sql

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.

sql

PRAGMA legacy_file_format;

ignore_check_constraints

When enabled, CHECK constraints are not enforced.

sql

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.

sql

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.

sql

PRAGMA quick_check;

WAL Operations

wal_checkpoint

Forces a WAL checkpoint.

sql

PRAGMA wal_checkpoint;

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

Change Data Capture

<Info> **Turso Extension**: Change Data Capture (CDC) is a Turso-specific feature that tracks all data changes for replication, auditing, and reactive applications. </Info>

capture_data_changes_conn

Enables CDC for the current connection. Changes are captured to a designated table.

sql

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

sql

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

<Info> **Turso Extension**: At-rest encryption is a Turso-specific feature. This feature is experimental and must be [enabled before use](/docs/sql-reference/experimental-features). </Info>

cipher

Sets the encryption cipher for the database.

sql

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.

sql

PRAGMA hexkey = '2d7a30108d3eb3e45c90a732041fe54778bdcf707c76749fab7da335d1b39c1d';

Encryption Example

sql

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

<Info> **Turso Extension**: Custom types are a Turso-specific feature. </Info>

list_types

Lists all available types (built-in and custom) with their metadata.

sql

PRAGMA list_types;
-- type    | parent | encode | decode | default | operators
-- INTEGER |        |        |        |         |
-- REAL    |        |        |        |         |
-- TEXT    |        |        |        |         |
-- BLOB    |        |        |        |         |
-- ANY     |        |        |        |         |

See CREATE TYPE for creating custom types.

PRAGMA Statements

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

Integrity Checks

integrity_check

quick_check

WAL Operations

wal_checkpoint

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