ℹ️

SQLite System & Info Functions

SQLite

Complete reference for SQLite system and info functions covering sqlite_version(), changes(), total_changes(), last_insert_rowid(), typeof(), and sqlite_source_id(). Every function includes syntax, use cases, and notes on session-scoped vs connection-scoped behaviour. Updated for SQLite 3.52.

9 functions

Jump to function

changes total_changes last_insert_rowid typeof sqlite_version sqlite_source_id sqlite_compileoption_get sqlite_compileoption_used sqlite_offset

What are SQLite System & Info Functions?

SQLite system functions return metadata about the current SQLite session and the database engine version. changes() returns the number of rows modified by the last INSERT, UPDATE, or DELETE, last_insert_rowid() returns the ROWID of the most recent INSERT, and typeof() returns the storage class ('integer', 'real', 'text', 'blob', or 'null') of a value. These are particularly useful for application code that needs to know how many rows were affected or what type was actually stored.

changes

SQLite 3.x+→ INTEGER

Returns the number of database rows that were changed, inserted, or deleted by the most recently completed INSERT, DELETE, or UPDATE statement, exclusive of statements in lower-level triggers. This is a wrapper around the sqlite3_changes64() C/C++ function.

Signature

changes()

Examples

One row was inserted

sql

INSERT INTO employees(name) VALUES('Alice');
SELECT changes();

→1

Returns count of rows affected by the UPDATE

sql

UPDATE employees SET dept = 'Engineering' WHERE active = 1;
SELECT changes();

→42

Rows deleted by the DELETE

sql

DELETE FROM logs WHERE created_at < date('now','-30 days');
SELECT changes();

→150

No DML has run yet in this connection

sql

SELECT changes();

→0

Multi-row insert counts all inserted rows

sql

INSERT INTO t(x) VALUES(1),(2),(3);
SELECT changes();

→3

⚠Anti-Pattern— Summing changes() Across Multiple Statements

Calling changes() after a series of DML statements and summing the results — or reading it once after several operations — gives you only the count from the last statement. Each call overwrites the previous result, so intermediate counts are silently lost.

✓ Instead: Use total_changes() and subtract a baseline captured before the batch to get the cumulative count for all statements in the group.

changes() only counts rows modified by the immediately-preceding top-level statement. Rows changed inside triggers are NOT counted. Use total_changes() if you need a running total across the entire session.

◆SQLite Note

SQLite-specific function. Equivalent to sqlite3_changes64() in the C API. Not available in PostgreSQL or other SQL engines.

total_changes

SQLite 3.x+→ INTEGER

Returns the total number of row changes caused by INSERT, UPDATE, or DELETE statements since the current database connection was opened. This is a wrapper around the sqlite3_total_changes64() C/C++ interface.

Signature

total_changes()

Examples

Fresh connection, no changes yet

sql

SELECT total_changes();

→0

After inserting 3 rows

sql

INSERT INTO t VALUES(1),(2),(3);
SELECT total_changes();

→3

Cumulative: 3 inserts + 3 updates

sql

UPDATE t SET x = x + 1;
SELECT total_changes();

→6

Adds the delete count to the running total

sql

DELETE FROM t WHERE x > 2;
SELECT total_changes();

→7

changes() is per-statement; total_changes() is cumulative

sql

-- Compare changes() vs total_changes()
INSERT INTO log VALUES('event');
SELECT changes(), total_changes();

→1 | 8

⚠Anti-Pattern— Trying to Reset total_changes() Between Operations

There is no way to reset total_changes() mid-connection — it is a monotonically increasing counter for the entire connection lifetime. Developers sometimes close and reopen the connection as a workaround, which is costly and loses any cached state.

✓ Instead: Capture the value of total_changes() before the operation starts, then subtract that baseline from the value after the operation to isolate the count for just that batch.

total_changes() accumulates across all statements in the connection lifetime, including changes inside triggers. It resets to zero when you open a new database connection. Use it to monitor overall write activity in a session.

◆SQLite Note

SQLite-specific function. Wraps sqlite3_total_changes64(). No direct equivalent exists in PostgreSQL or other standard SQL engines.

last_insert_rowid

SQLite 3.x+→ INTEGER

Returns the ROWID of the last row inserted from the database connection that invoked the function. This SQL function wraps the sqlite3_last_insert_rowid() C/C++ interface function.

Signature

last_insert_rowid()

Examples

Returns the rowid of the newly inserted row

sql

INSERT INTO articles(title) VALUES('Hello World');
SELECT last_insert_rowid();

→1

Works for tables with INTEGER PRIMARY KEY (alias for rowid)

sql

INSERT INTO users(name, email) VALUES('Bob','bob@example.com');
SELECT last_insert_rowid();

→42

Returns 0 if no insert has been performed on this connection

sql

SELECT last_insert_rowid();

→0

For multi-row inserts, returns the rowid of the last inserted row

sql

INSERT INTO t(x) VALUES(1),(2),(3);
SELECT last_insert_rowid();

→3

Capture and use the new rowid in one query

sql

-- Use in a single statement to capture new ID
WITH new_id AS (SELECT last_insert_rowid() AS id)
SELECT * FROM orders WHERE rowid = (SELECT id FROM new_id);

→(the newly inserted order row)

⚠Anti-Pattern— Reading last_insert_rowid() After a No-Op Statement

If a subsequent INSERT matches a conflict-resolution clause (ON CONFLICT IGNORE) and inserts no rows, last_insert_rowid() retains the rowid from the previous successful insert — which may belong to a completely different table or operation. The stale value looks valid and causes silent data integrity bugs.

✓ Instead: Always check changes() immediately after the INSERT to confirm a row was actually inserted before trusting the value returned by last_insert_rowid().

If a trigger fires during an INSERT and itself performs an INSERT, last_insert_rowid() will reflect the rowid from the trigger's insert, not the original statement. Read the value immediately after the INSERT statement to avoid this.

◆SQLite Note

SQLite-specific function wrapping sqlite3_last_insert_rowid(). Similar in purpose to LASTVAL() or CURRVAL() in PostgreSQL sequences, but operates on the rowid concept unique to SQLite.

typeof

SQLite 3.x+→ TEXT

Returns a string indicating the storage class of the expression X. The possible return values are 'null', 'integer', 'real', 'text', or 'blob'. Unlike most SQL engines, SQLite stores values with dynamic types rather than column-defined types.

Signature

typeof(X)

Parameters

Parameter	Type	Description
X	ANY	Any expression whose storage class is to be inspected.

Examples

Integer literal

sql

SELECT typeof(42);

→'integer'

Floating-point literal

sql

SELECT typeof(3.14);

→'real'

Text string

sql

SELECT typeof('hello');

→'text'

NULL value

sql

SELECT typeof(NULL);

→'null'

BLOB literal written in hex notation

sql

SELECT typeof(x'CAFE');

→'blob'

⚠Anti-Pattern— Using typeof() as a Substitute for Schema Constraints

Checking typeof(column) = 'integer' in application queries gives you the storage class of the value that happens to be there right now, but does nothing to prevent a future insert from storing text in the same column. SQLite's flexible type system means the check is row-level, not schema-level.

✓ Instead: Enforce type requirements with a CHECK constraint (e.g. CHECK(typeof(amount) = 'real')) at the schema level so every INSERT and UPDATE is validated automatically.

Because SQLite uses dynamic typing, a column declared as INTEGER might actually hold text. Use typeof() on column values to discover what storage class was actually used, which is invaluable for debugging unexpected comparison or sort behaviour.

◆SQLite Note

Reflects SQLite's dynamic type system. The returned string is always lowercase. In PostgreSQL use pg_typeof() for a similar purpose, but the two functions are not interchangeable.

sqlite_version

SQLite 3.x+→ TEXT

Returns the version string for the SQLite library that is running. This function is an SQL wrapper around the sqlite3_libversion() C-interface.

Signature

sqlite_version()

Examples

Returns the current library version string

sql

SELECT sqlite_version();

→'3.52.0'

String comparison can check for minimum version (lexicographic — be careful with multi-digit minor versions)

sql

SELECT sqlite_version() >= '3.37.0';

→1

Numeric comparison approach for precise version gating

sql

SELECT CAST(REPLACE(sqlite_version(),'.','') AS INTEGER) >= 3370;

→1

Feature-flag pattern

sql

-- Check version before using STRICT tables (added 3.37.0)
SELECT CASE WHEN sqlite_version() >= '3.37' THEN 'STRICT supported' ELSE 'upgrade SQLite' END;

→'STRICT supported'

Combine with sqlite_source_id() for full build identification

sql

SELECT sqlite_version(), sqlite_source_id();

→'3.52.0' | '2026-03-06 ...'

⚠Anti-Pattern— Comparing Version Strings with > or < Directly

Using sqlite_version() > '3.9.0' performs a lexicographic string comparison, which produces wrong results when any version segment has more than one digit. For example, '3.9.0' > '3.38.0' evaluates to TRUE as a string because '9' sorts after '3', even though 3.9.0 is numerically older than 3.38.0.

✓ Instead: Strip the dots and cast to integer for comparison: CAST(REPLACE(sqlite_version(), '.', '') AS INTEGER) >= 3380 gives a correct numeric result.

Direct string comparison works correctly for most SQLite versions (e.g. '3.9.0' < '3.10.0' fails because '9' > '1'). Strip the dots and compare as integers for a reliable version gate.

◆SQLite Note

SQLite-specific function. Equivalent to SELECT version() in PostgreSQL. The version format is MAJOR.MINOR.PATCH.

sqlite_source_id

SQLite 3.x+→ TEXT

Returns a string identifying the specific version of the source code used to build the SQLite library. The string contains the check-in date and time followed by the SHA3-256 hash of that check-in. Wraps the sqlite3_sourceid() C interface.

Signature

sqlite_source_id()

Examples

Date/time of source check-in followed by SHA3-256 hash

sql

SELECT sqlite_source_id();

→'2026-03-06 14:40:28 abc123...'

Extract just the build date

sql

SELECT substr(sqlite_source_id(), 1, 10);

→'2026-03-06'

Full build fingerprint

sql

SELECT sqlite_version(), sqlite_source_id();

→(version and source ID side by side)

Source ID is always longer than just the hash

sql

SELECT length(sqlite_source_id()) > 40;

→1

Store build identity for diagnostics

sql

-- Log the SQLite build identity at app startup
INSERT INTO app_meta(key, value) VALUES('sqlite_build', sqlite_source_id());

→(row inserted)

⚠Anti-Pattern— Parsing sqlite_source_id() to Extract the Version Number

sqlite_source_id() returns a check-in date and a SHA3-256 hash, not a version number. Developers sometimes try to parse the date prefix or hash to determine which SQLite release they are running, which is fragile and unreliable because the format is internal and the hash is not version-ordered.

✓ Instead: Call sqlite_version() when you need the version number; reserve sqlite_source_id() for build traceability and diagnostics where you need the exact source revision.

sqlite_source_id() uniquely identifies the exact source revision of the SQLite build, not just the version number. Two builds with the same sqlite_version() can differ if compiled from patched sources — sqlite_source_id() will show the difference.

◆SQLite Note

SQLite-specific function. Wraps sqlite3_sourceid(). No direct PostgreSQL equivalent; the closest is SELECT version() which includes build details in its output.

sqlite_compileoption_get

SQLite 3.x+→ TEXT

Returns the N-th compile-time option string used to build SQLite, or NULL if N is out of range. Options are returned without the leading 'SQLITE_' prefix. Wraps the sqlite3_compileoption_get() C/C++ function. See also the compile_options pragma.

Signature

sqlite_compileoption_get(N)

Parameters

Parameter	Type	Description
N	INTEGER	Zero-based index of the compile option to retrieve.

Examples

First compile-time option (zero-indexed)

sql

SELECT sqlite_compileoption_get(0);

→'COMPILER=gcc-...'

Second compile-time option

sql

SELECT sqlite_compileoption_get(1);

→'DEFAULT_AUTOVACUUM'

Returns NULL when N is beyond the last option

sql

SELECT sqlite_compileoption_get(9999);

→NULL

Recursive CTE to list every compile option

sql

-- Enumerate all compile options
WITH RECURSIVE opts(n, opt) AS (
  SELECT 0, sqlite_compileoption_get(0)
  UNION ALL
  SELECT n+1, sqlite_compileoption_get(n+1) FROM opts WHERE opt IS NOT NULL
)
SELECT opt FROM opts WHERE opt IS NOT NULL;

→(list of all compile options)

The compile_options pragma is an easier way to enumerate all options

sql

PRAGMA compile_options;

→(same list, one row per option)

⚠Anti-Pattern— Scanning Index-by-Index to Find a Specific Option

Looping through sqlite_compileoption_get(0), sqlite_compileoption_get(1), ... until you find the option you want is verbose and fragile because the options are not in any documented or stable order, so the index of a given option can vary between SQLite builds.

✓ Instead: Use sqlite_compileoption_used('OPTION_NAME') to check for a specific option directly — it returns 1 or 0 in a single call with no iteration needed.

PRAGMA compile_options; is more convenient for listing all options. Use sqlite_compileoption_get(N) when you need to iterate programmatically or store options in a table via a recursive CTE.

◆SQLite Note

SQLite-specific function. Compile-time options control optional features like FTS5, RTREE, ICU support, and STRICT table enforcement defaults. No PostgreSQL equivalent.

sqlite_compileoption_used

SQLite 3.x+→ INTEGER

Returns 1 (true) if the compile-time option named X was used when building SQLite, or 0 (false) if it was not. The SQLITE_ prefix may be omitted from X. Wraps the sqlite3_compileoption_used() C/C++ function.

Signature

sqlite_compileoption_used(X)

Parameters

Parameter	Type	Description
X	TEXT	Name of the compile-time option to test. The 'SQLITE_' prefix is optional.

Examples

Returns 1 if FTS5 full-text search was compiled in

sql

SELECT sqlite_compileoption_used('ENABLE_FTS5');

→1

Returns 0 if R-Tree extension was not compiled in

sql

SELECT sqlite_compileoption_used('ENABLE_RTREE');

→0

The SQLITE_ prefix can be included or omitted

sql

SELECT sqlite_compileoption_used('SQLITE_ENABLE_ICU');

→0

Check if soundex() function is available

sql

SELECT sqlite_compileoption_used('SOUNDEX');

→1

Feature-detection pattern before using JSON functions

sql

SELECT CASE
  WHEN sqlite_compileoption_used('ENABLE_JSON1') THEN 'JSON supported'
  ELSE 'JSON not available'
END;

→'JSON supported'

⚠Anti-Pattern— Calling sqlite_compileoption_used() in Hot Query Paths

Invoking sqlite_compileoption_used() on every request or inside a frequently executed query adds unnecessary overhead and obscures intent. Compile-time options are fixed when the SQLite library is built and will never change while the process is running, so re-checking them repeatedly wastes resources.

✓ Instead: Call sqlite_compileoption_used() once at application startup, store the results in a variable or application-level config, and branch on that cached value throughout the rest of the program.

Use sqlite_compileoption_used() to write defensive code that checks for optional extensions (FTS5, RTREE, ICU, SOUNDEX) before calling them. This is especially useful when shipping SQLite with an application that may run against different builds.

◆SQLite Note

SQLite-specific function. Useful for detecting optional extensions at runtime. No PostgreSQL equivalent; in PostgreSQL you would query pg_extension or check pg_available_extensions.

sqlite_offset

SQLite 3.x+ (with compile option)→ INTEGER

Returns the byte offset in the database file for the beginning of the record from which value X would be read. If X is not a column in an ordinary table, returns NULL. The offset may point to the original table or to an index record, depending on how the query planner resolves the column.

Signature

sqlite_offset(X)

Parameters

Parameter	Type	Description
X	COLUMN	A column reference in an ordinary table. Non-column expressions return NULL.

Examples

Byte offset in the database file where this record starts

sql

SELECT sqlite_offset(name) FROM employees LIMIT 1;

→4096

Retrieve offsets for all rows

sql

SELECT rowid, sqlite_offset(salary) FROM employees;

→(rowid and byte offset for each row)

Returns NULL for literals and expressions, not column references

sql

SELECT sqlite_offset(42);

→NULL

Offset may reference an index record if query planner uses an index

sql

SELECT sqlite_offset(e.name) FROM employees e WHERE e.id = 1;

→8192

Sort by physical position to understand storage layout

sql

-- Verify physical storage layout
SELECT name, sqlite_offset(name) AS file_offset
FROM big_table ORDER BY sqlite_offset(name);

→(rows in physical storage order)

⚠Anti-Pattern— Using sqlite_offset() in Production Queries for General Purpose Use

sqlite_offset() returns the byte offset of a record as resolved by the query planner, which means the same column in the same row can return different offsets depending on whether the planner uses a table scan or an index. The offset reflects internal storage details that can shift after a VACUUM, page reorganisation, or SQLite version upgrade, making any logic built on these values brittle.

✓ Instead: Restrict sqlite_offset() to offline debugging and storage analysis tools; never use it as a stable row identifier or storage key in application logic.

sqlite_offset() is only available when SQLite is compiled with -DSQLITE_ENABLE_OFFSET_SQL_FUNC. Check with sqlite_compileoption_used('ENABLE_OFFSET_SQL_FUNC') before relying on it. It is mainly used for low-level database inspection tools.

◆SQLite Note

SQLite-specific function only available with the -DSQLITE_ENABLE_OFFSET_SQL_FUNC compile-time option. There is no equivalent in PostgreSQL or other SQL engines.

⚙ Availability: Requires -DSQLITE_ENABLE_OFFSET_SQL_FUNC compile-time option.

Related SQLite Categories

Core Functions →Aggregate Functions →