PRAGMA Statements

PRAGMA statements are special commands for querying or modifying database configuration, retrieving schema metadata, and controlling database behavior. They are specific to SQLite and Turso and are not part of standard SQL.

Syntax

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

Database Metadata

database_list

Returns one row per 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 in bytes. Can only be set before any tables are created.

PRAGMA page_size;          -- returns current value (default: 4096)
PRAGMA page_size = 8192;   -- set to 8 KB

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 (free) pages in the database file.

PRAGMA freelist_count;

encoding

Returns the text encoding of the database.

PRAGMA encoding;
-- UTF-8

schema_version

Returns the schema version number, incremented each time the schema changes.

PRAGMA schema_version;
-- 5

Write operations to schema_version are treated as no-ops in Turso (emulates defensive mode).

application_id

Returns or sets the application ID stored in the database header. Applications can use this 32-bit integer to identify their database file format.

PRAGMA application_id;
PRAGMA application_id = 12345;

user_version

Returns or sets a 32-bit integer available for application use — commonly used to track schema migration versions.

PRAGMA user_version;
PRAGMA user_version = 3;

Schema Introspection

table_info(table-name)

Returns one row per 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
`notnull`	INTEGER	1 if a NOT NULL constraint exists
`dflt_value`	TEXT	Default value expression, or NULL
`pk`	INTEGER	1 if the 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(table-name)

Same as table_info but includes a hidden column for identifying hidden columns in virtual tables.

PRAGMA table_xinfo(table-name);

table_list

Returns one row per 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(table-name)

Returns one row per 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 UNIQUE
`origin`	TEXT	`c` (CREATE INDEX), `u` (UNIQUE constraint), `pk` (PRIMARY KEY)
`partial`	INTEGER	1 if a partial index

index_info(index-name)

Returns one row per column in the named index.

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

index_xinfo(index-name)

Same as index_info but with additional metadata columns.

PRAGMA index_xinfo(index-name);

function_list

Returns one row per SQL function available in the current database.

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

pragma_list

Returns the list of all PRAGMA commands supported by Turso.

PRAGMA pragma_list;

Database Configuration

journal_mode

Returns or sets the journaling mode.

PRAGMA journal_mode;
PRAGMA journal_mode = wal;

Turso supports wal mode. Rollback journal modes (DELETE, TRUNCATE, PERSIST, MEMORY) are not supported.

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

PRAGMA journal_mode = mvcc;

With MVCC mode active, you can use BEGIN CONCURRENT for optimistic concurrent write transactions.

cache_size

Returns or sets the suggested maximum number of database pages to hold in memory. A positive value is a page count; a negative value is kilobytes.

PRAGMA cache_size;
PRAGMA cache_size = 2000;    -- 2000 pages
PRAGMA cache_size = -8192;   -- ~8 MB

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

Only the boolean enable/disable form is supported. Setting a specific threshold page count is not supported.

synchronous

Controls fsync behavior for durability.

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. NORMAL and EXTRA are not.

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-wait timeout in milliseconds. When a resource is locked, Turso waits this many milliseconds before returning SQLITE_BUSY.

PRAGMA busy_timeout;
PRAGMA busy_timeout = 5000;  -- wait up to 5 seconds

query_only

When enabled, prevents any modifications to the database.

PRAGMA query_only = 1;  -- enable read-only mode
PRAGMA query_only = 0;  -- disable

foreign_keys

Enables or disables foreign key constraint enforcement. Off by default for SQLite compatibility.

PRAGMA foreign_keys;
PRAGMA foreign_keys = ON;
PRAGMA foreign_keys = OFF;

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;  -- enforce CHECK constraints (default)

Integrity Checks

integrity_check

Performs a thorough integrity check of the entire database. Returns ok if no problems are found, otherwise returns one row per error.

PRAGMA integrity_check;
-- ok

PRAGMA integrity_check(5);  -- stop after 5 errors

quick_check

Performs a faster but less thorough integrity check than integrity_check.

PRAGMA quick_check;

WAL Operations

wal_checkpoint

Forces a WAL checkpoint, writing pages from the WAL file back to the main database file.

PRAGMA wal_checkpoint;

Calling wal_checkpoint with a parameter (e.g. PRAGMA wal_checkpoint = FULL) is not supported.

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 or disables CDC for the current connection.

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.

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 and DELETEs)
`after`	Capture row state after changes (for INSERTs and UPDATEs)
`full`	Capture before and after states plus update details

CDC Table Structure

Changes are written to a table named turso_cdc by default (customizable via the table_name parameter):

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

Example

-- Enable full CDC
PRAGMA capture_data_changes_conn('full');

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 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 experimental feature. Enable it 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 2× parallelization
`aegis128x4`	16 bytes	AEGIS-128 with 4× parallelization
`aegis256x2`	32 bytes	AEGIS-256 with 2× parallelization
`aegis256x4`	32 bytes	AEGIS-256 with 4× parallelization

hexkey

Sets the encryption key as a hexadecimal string.

PRAGMA hexkey = '2d7a30108d3eb3e45c90a732041fe54778bdcf707c76749fab7da335d1b39c1d';

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

Encryption parameters can also be specified in the database URI:

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

To open an existing encrypted database, the cipher and key must be provided at open time.

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

PRAGMA Support Summary

The table below summarizes all SQLite PRAGMAs and their support status in Turso.

PRAGMA	Status	Notes
`analysis_limit`	Not supported
`application_id`	Supported
`auto_vacuum`	Not supported
`automatic_index`	Not supported
`busy_timeout`	Supported
`cache_size`	Supported
`cache_spill`	Partial	Enable/disable only; threshold not supported
`case_sensitive_like`	N/A	Deprecated in SQLite
`cell_size_check`	Not supported
`checkpoint_fullsync`	Not supported
`compile_options`	Not supported
`database_list`	Supported
`encoding`	Supported
`foreign_key_check`	Not supported
`foreign_key_list`	Not supported
`foreign_keys`	Supported
`freelist_count`	Supported
`function_list`	Supported
`ignore_check_constraints`	Supported
`incremental_vacuum`	Not supported
`index_info`	Supported
`index_list`	Supported
`index_xinfo`	Supported
`integrity_check`	Supported
`journal_mode`	Supported	`wal` and `mvcc` (Turso extension)
`legacy_file_format`	Supported
`locking_mode`	Not supported
`max_page_count`	Supported
`mmap_size`	Not supported
`module_list`	Not supported
`optimize`	Not supported
`page_count`	Supported
`page_size`	Supported
`pragma_list`	Supported
`query_only`	Supported
`quick_check`	Supported
`read_uncommitted`	Not supported
`recursive_triggers`	Not supported
`schema_version`	Supported	Writes are no-ops (emulates defensive mode)
`secure_delete`	Not supported
`synchronous`	Partial	`OFF` and `FULL` only
`table_info`	Supported
`table_list`	Supported
`table_xinfo`	Supported
`temp_store`	Supported
`user_version`	Supported
`wal_autocheckpoint`	Not supported
`wal_checkpoint`	Partial	Parameterized form not supported
`writable_schema`	Not supported

Overview

Statements

Functions

Pragmas

​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-name)

​table_xinfo(table-name)

​table_list

​index_list(table-name)

​index_info(index-name)

​index_xinfo(index-name)

​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

​Example

​Encryption

​cipher

​hexkey

​Full Encryption Example

​Custom Types

​list_types

​PRAGMA Support Summary

​See Also

Build docs developers (and LLMs) love

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

table_xinfo(table-name)

table_list

index_list(table-name)

index_info(index-name)

index_xinfo(index-name)

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

Example

Encryption

cipher

hexkey

Full Encryption Example

Custom Types

list_types

PRAGMA Support Summary

See Also