Easy Tutorial
Sqlite Alias Sqlite Like Clause Sqlite Syntax Sqlite Php Sqlite Distinct Keyword Sqlite Functions Sqlite Autoincrement Sqlite Detach Database Sqlite Unions Clause Sqlite Attach Database Sqlite Joins Sqlite Pragma Sqlite Group By Sqlite Where Clause Sqlite Injection Sqlite And Or Clauses Sqlite Operators Sqlite Constraints Sqlite Drop Table Sqlite C Cpp Sqlite Order By Sqlite Subquery Sqlite Expressions Sqlite View Sqlite Indexed By Sqlite Alter Command Sqlite Insert Sqlite Select Sqlite Explain Sqlite Python Sqlite Installation Sqlite Transaction Sqlite Update Sqlite Delete Sqlite Commands Sqlite Date Time Sqlite Null Values Sqlite Create Table Sqlite Java Sqlite Data Types Sqlite Trigger Sqlite Create Database Sqlite Limit Clause Sqlite Intro Sqlite Truncate Table Sqlite Vacuum Sqlite Perl Sqlite Glob Clause Sqlite Index Sqlite Tutorial Sqlite Having Clause
❮ Sqlite Joins Sqlite Group By ❯

SQLite PRAGMA

The PRAGMA command in SQLite is a special command that can be used within the SQLite environment to control various environment variables and status flags. A PRAGMA value can be read or 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, use the following syntax:

PRAGMA pragma_name = value;

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

auto_vacuum Pragma

The auto_vacuum Pragma gets or sets the auto-vacuum mode. The syntax is as follows:

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

Where mode can be any of the following:

Pragma Value Description
0 or NONE Disables Auto-vacuum. This is the default mode, meaning the database file size will not shrink unless manually using the VACUUM command.
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 must be manually activated. In this mode, reference data is maintained, and free pages are only placed on the free list. These pages can be reclaimed at any time using the incremental_vacuum pragma.

cache_size Pragma

The cache_size 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;

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

The casesensitivelike Pragma controls the case sensitivity of the built-in LIKE expression. By default, this Pragma is false, meaning the built-in LIKE operator ignores letter case. The syntax is as follows:

PRAGMA case_sensitive_like = [true|false];

There is currently no way to query the current status of this Pragma.

count_changes Pragma

The count_changes 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 mentioned statement will return a single-row, single-column table consisting of a single integer value representing the number of rows affected by the operation.

database_list Pragma

The database_list 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, providing the sequence number in the database, its name, and the associated file whenever a database is opened or attached.

encoding Pragma

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

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

The index_info Pragma returns information about database indexes. The syntax is as follows:

PRAGMA [database.]index_info( index_name );

The result set will display a row for each column included in the index, showing the column sequence in the index, the column index within the table, and the column name.

index_list Pragma

The index_list Pragma lists all indexes associated with a table. The syntax is as follows:

PRAGMA [database.]index_list( table_name );

PRAGMA [database.]index_list( table_name );

The result set will display one row for each index with the index's sequence number, index name, and a flag indicating whether the index is unique.

journal_mode Pragma

The journal_mode Pragma gets or sets the journal mode that controls how the journal file is 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:

Pragma Value Description
DELETE Default mode. The journal file is deleted at the end of the transaction.
TRUNCATE The journal file is truncated to zero bytes.
PERSIST The journal file is left in place, but the header is overwritten to indicate that the journal is no longer valid.
MEMORY The journal records are kept in memory instead of on disk.
OFF No journal records are kept.

max_page_count Pragma

The maxpagecount 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, meaning if the default page size is 1 KB, then it grows to a terabyte in the database.

page_count Pragma

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

The page_size 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, 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

The parser_trace Pragma controls the printing of debug status as it parses SQL commands. The syntax is as follows:

PRAGMA parser_trace = [true|false];

By default, it is set to false, but when set to true, the SQL parser will print its status as it parses SQL commands.

recursive_triggers Pragma

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

The schema_version 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. It increments whenever a schema change command (like CREATE... or DROP...) is executed.

secure_delete Pragma

The secure_delete Pragma controls 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 for the secure delete flag is usually off, but this can be changed with the SQLITE_SECURE_DELETE build option.

sql_trace Pragma

The sql_trace Pragma is used to dump SQL trace results to the screen. The syntax is as follows:

PRAGMA sql_trace;
This is a Chinese to English translation. Here is the English translation for the text:

PRAGMA sql_trace = [true|false];


SQLite must be compiled with the SQLITE_DEBUG directive to reference this Pragma.

## synchronous Pragma

The **synchronous** 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 | Synchronization is performed after each sequence of critical disk operations. |
| 2 or FULL | Synchronization is performed after each critical disk operation. |

## temp_store Pragma

The **temp_store** 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 | The default mode used at compile time. Typically FILE. |
| 1 or FILE | Uses file-based storage. |
| 2 or MEMORY | Uses memory-based storage. |

## temp_store_directory Pragma

The **temp_store_directory** Pragma gets or sets the location for temporary database files. The syntax is as follows:

PRAGMA temp_store_directory; PRAGMA temp_store_directory = 'directory_path';


## user_version Pragma

The **user_version** 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

The **writable_schema** Pragma gets or sets whether the 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 can lead to database corruption.

❮ Sqlite Joins Sqlite Group By ❯