SQLite PRAGMA

SQLite'sPRAGMAcommand is a special command that can be used within the SQLite environment to control various environment variables and state flags. A PRAGMA value can be read and can also be set as needed.

Syntax

To query the current PRAGMA value, simply provide the name of the pragma:

PRAGMA pragma_name;

To set a new value for the PRAGMA, the syntax is as follows:

PRAGMA pragma_name = value;

The set mode can be a name or an equivalent integer, but the returned value will always be an integer.

auto_vacuum Pragma

auto_vacuumThe pragma gets or sets the auto-vacuum mode. The syntax is as follows:

PRAGMA [database.]auto_vacuum;
PRAGMA [database.]auto_vacuum = mode;

Where,modecan be any of the following:

Pragma Value	Description
0 or NONE	Disables Auto-vacuum. This is the default mode, meaning that the database file size will not shrink unless the VACUUM command is used manually.
1 or FULL	Enables Auto-vacuum, which is fully automatic. In this mode, the database file is allowed to shrink as data is removed from the database.
2 or INCREMENTAL	Enables Auto-vacuum, but it must be activated manually. In this mode, referenced data is maintained, and free pages are only placed in the free list. These pages can be used at any timeincremental_vacuum pragmafor overwriting.

cache_size Pragma

cache_sizeThe pragma can get or temporarily set the maximum size of the page cache in memory. The syntax is as follows:

PRAGMA [database.]cache_size;
PRAGMA [database.]cache_size = pages;

pagesThe value represents the number of pages in the cache. The default size of the built-in page cache is 2,000 pages, with a minimum size of 10 pages.

case_sensitive_like Pragma

case_sensitive_likeThe pragma controls the case sensitivity of the built-in LIKE expression. By default, this pragma is false, meaning that the built-in LIKE operator ignores the case of letters. The syntax is as follows:

PRAGMA case_sensitive_like = [true|false];

There is currently no way to query the current state of this pragma.

count_changes Pragma

count_changesThe pragma gets or sets the return value of data manipulation statements, such as INSERT, UPDATE, and DELETE. The syntax is as follows:

PRAGMA count_changes;
PRAGMA count_changes = [true|false];

By default, this pragma is false, and these statements do not return anything. If set to true, each of the mentioned statements will return a single-row, single-column table consisting of a single integer value representing the rows affected by the operation.

database_list Pragma

database_listThe pragma is used to list all database connections. The syntax is as follows:

PRAGMA database_list;

This pragma will return a single-row, three-column table. Whenever a database is opened or attached, it gives the sequence number of the database, its name, and the associated file.

encoding Pragma

encodingThe pragma controls how strings are encoded and stored in the database file. The syntax is as follows:

PRAGMA encoding;
PRAGMA encoding = format;

The format value can be one of UTF-8, UTF-16le, or UTF-16be.

freelist_count Pragma

freelist_countThe pragma returns an integer representing the number of database pages currently marked as free and available. The syntax is as follows:

PRAGMA [database.]freelist_count;

index_info Pragma

index_infoThe pragma returns information about database indexes. The syntax is as follows:

PRAGMA [database.]index_info( index_name );

The result set will display one row for each column contained in the given index, including the column sequence, the column index within the table, and the column name.

index_list Pragma

index_listThe pragma lists all indexes associated with a table. The syntax is as follows:

PRAGMA [database.]index_list( table_name );

The result set will display one row for each index, giving the index sequence, the index name, and an identifier indicating whether the index is unique.

journal_mode Pragma

journal_modeThe pragma gets or sets the journal mode that controls how log files are stored and processed. The syntax is as follows:

PRAGMA journal_mode;
PRAGMA journal_mode = mode;
PRAGMA database.journal_mode;
PRAGMA database.journal_mode = mode;

Five journal modes are supported here:

Pragma Value	Description
DELETE	The default mode. In this mode, the journal file will be deleted at the end of the transaction.
TRUNCATE	The journal file is truncated to zero bytes in length.
PERSIST	The journal file is left in place, but the header is rewritten to indicate that the journal is no longer valid.
MEMORY	Journal records are kept in memory rather than on disk.
OFF	No journal records are kept.

max_page_count Pragma

max_page_countThe pragma gets or sets the maximum number of pages allowed for the database. The syntax is as follows:

PRAGMA [database.]max_page_count;
PRAGMA [database.]max_page_count = max_page;

The default value is 1,073,741,823, which is a gigabyte of pages. That is, with the default 1 KB page size, the database can grow to one terabyte.

page_count Pragma

page_countThe pragma returns the number of pages in the current database. The syntax is as follows:

PRAGMA [database.]page_count;

The size of the database file should be page_count * page_size.

page_size Pragma

page_sizeThe pragma gets or sets the size of the database pages. The syntax is as follows:

PRAGMA [database.]page_size;
PRAGMA [database.]page_size = bytes;

By default, the allowed sizes are 512, 1024, 2048, 4096, 8192, 16384, and 32768 bytes. The only way to change the page size of an existing database is to set the page size and then immediately VACUUM the database.

parser_trace Pragma

parser_traceThe pragma controls the printing of debug state as it parses SQL commands. The syntax is as follows:

PRAGMA parser_trace = [true|false];

By default, it is set to false, but it is enabled when set to true. In that case, the SQL parser will print out its state as it parses SQL commands.

recursive_triggers Pragma

recursive_triggersThe pragma gets or sets the recursive trigger feature. If recursive triggers are not enabled, a trigger action will not trigger another trigger. The syntax is as follows:

PRAGMA recursive_triggers;
PRAGMA recursive_triggers = [true|false];

schema_version Pragma

schema_versionThe pragma gets or sets the schema version value stored in the database header. The syntax is as follows:

PRAGMA [database.]schema_version;
PRAGMA [database.]schema_version = number;

This is a 32-bit signed integer value used to track schema changes. Whenever a schema-changing command is executed (such as CREATE... or DROP...), this value is incremented.

secure_delete Pragma

secure_deleteThe pragma is used to control how content is deleted from the database. The syntax is as follows:

PRAGMA secure_delete;
PRAGMA secure_delete = [true|false];
PRAGMA database.secure_delete;
PRAGMA database.secure_delete = [true|false];

The default value of the secure delete flag is usually off, but this can be changed through the SQLITE_SECURE_DELETE build option.

sql_trace Pragma

sql_traceThe pragma is used to dump SQL trace results to the screen. The syntax is as follows:

PRAGMA sql_trace;
PRAGMA sql_trace = [true|false];

SQLite must be compiled with the SQLITE_DEBUG directive in order to use this pragma.

synchronous Pragma

synchronousThe pragma gets or sets the current disk synchronization mode, which controls how aggressively SQLite writes data to physical storage. The syntax is as follows:

PRAGMA [database.]synchronous;
PRAGMA [database.]synchronous = mode;

SQLite supports the following synchronization modes:

Pragma Value	Description
0 or OFF	No synchronization is performed.
1 or NORMAL	Synchronize after each sequence of critical disk operations.
2 or FULL	Synchronize after each critical disk operation.

temp_store Pragma

temp_storeThe pragma gets or sets the storage mode used for temporary database files. The syntax is as follows:

PRAGMA temp_store;
PRAGMA temp_store = mode;

SQLite supports the following storage modes:

Pragma Value	Description
0 or DEFAULT	Uses the compile-time mode by default. Usually FILE.
1 or FILE	Uses file-based storage.
2 or MEMORY	Uses memory-based storage.

temp_store_directory Pragma

temp_store_directoryThe pragma gets or sets the location used for temporary database files. The syntax is as follows:

PRAGMA temp_store_directory;
PRAGMA temp_store_directory = 'directory_path';

user_version Pragma

user_versionThe pragma gets or sets the user-defined version value stored in the database header. The syntax is as follows:

PRAGMA [database.]user_version;
PRAGMA [database.]user_version = number;

This is a 32-bit signed integer value that can be set by developers for version tracking purposes.

writable_schema Pragma

writable_schemaThe pragma gets or sets whether system tables can be modified. The syntax is as follows:

PRAGMA writable_schema;
PRAGMA writable_schema = [true|false];

If this pragma is set, tables starting with sqlite_ can be created and modified, including the sqlite_master table. Use this pragma with caution, as it may cause corruption of the entire database.

Other Extensions