ReadonlynameThe name of the dialect
ReadonlylibraryThe database library or driver being used
Get SQLite advanced features instance
Connect to the SQLite database
Disconnect from the SQLite database
Get the current database connection
Check if connected
Replace placeholders in SQL with actual values Supports both named (:param) and positional (?) placeholders
Optionalreplacements: unknown[] | Record<string, unknown>Execute a raw SQL query with retry support
Optionaloptions: QueryOptionsStream query results using better-sqlite3's native Statement#iterate()
— a real in-process cursor (no round trips at all, since sqlite is
embedded), so this is more efficient than the generic paginated
LIMIT/OFFSET fallback other dialects use.
The SELECT statement to stream
Optionaloptions: StreamOptions
Streaming options (batch size only affects backpressure batching, not fetch round trips)
Escape a value for use in a query
Build a JSON extraction query for SQLite SQLite: json_extract(column, '$.path')
Build a JSON contains query for SQLite SQLite: uses json_each and json_extract for containment checks
Optionalpath: stringBuild a JSON has key query for SQLite SQLite: json_each or json_extract for key existence
Build a json_group_array() aggregate expression for SQLite.
json_group_array() is SQLite's JSON1 aggregate for collecting the
values of valueExpr (evaluated once per row in the current group) into
a single JSON array. Commonly used for "hasMany as nested JSON" style
queries, e.g. rolling up child rows into a JSON array column of a
grouped parent query.
a column name or SQL expression to collect per row.
Bare identifiers are escaped as columns; anything containing SQL
syntax (parens, function calls, .) is passed through as-is so
callers can pass expressions like json_object('id', id).
Optionaloptions: { distinct?: boolean }
Optionaldistinct?: booleanemit json_group_array(DISTINCT ...)
Build a json_group_object() aggregate expression for SQLite.
json_group_object() collects (keyExpr, valueExpr) pairs from the
rows of the current group into a single JSON object, keyed by
keyExpr. Useful for building a JSON map of { [key]: value } from
grouped rows (e.g. nested-relation queries keyed by id).
Build a json_patch() expression for SQLite (RFC 7396 JSON Merge Patch).
json_patch(target, patch) applies an RFC 7396 merge patch to the JSON
stored in column, returning the patched JSON document. Unlike
json_set() (which sets a single path) this can add, replace, and
remove multiple keys in one call - keys in patch set to null are
removed from the result.
the JSON column to patch
a JS object/value to be JSON-stringified and applied as the merge patch; passed as a bound parameter, not interpolated.
Escape an identifier (table name, column name, etc.)
Quote an identifier (column name, table name) Same as escapeId for SQLite
Quote a table name Same as escapeId for SQLite If schema is provided, returns schema.tableName (SQLite treats schemas as prefixes)
Optionalschema: stringGet the database version
Generate SQL for creating a database SQLite uses ATTACH DATABASE for creating new database files
Generate SQL for dropping a database SQLite uses DETACH DATABASE for dropping database files
Attach an external SQLite database file to the current connection under
alias, making its tables reachable as alias.tableName in subsequent
queries (cross-database queries/joins). Executes a real
ATTACH DATABASE ? AS <alias> statement rather than just documenting the
capability.
filesystem path to the database file to attach (or
:memory: for an attached in-memory database)
schema name subsequent SQL will use to refer to the
attached database (e.g. alias.users)
Detach a previously-attached database from the current connection.
Executes a real DETACH DATABASE <alias> statement.
the schema name the database was attached under via
attachDatabase()
Generate SQL for creating a savepoint
Optionalname: stringGenerate SQL for releasing a savepoint
Generate SQL for rolling back to a savepoint
Create a new table
Optionaloptions: TableOptions & { strict?: boolean; withoutRowid?: boolean }
Optionalstrict?: booleanSQLite 3.37+ STRICT tables - enforces column type affinity strictly instead of SQLite's normally-flexible type system.
OptionalwithoutRowid?: booleanWITHOUT ROWID tables - opts a table out of the implicit rowid, storing rows in the order of the PRIMARY KEY instead. Useful for tables with a natural composite/text primary key.
Create a partitioned table (SQLite emulation) Creates base table + child partition tables + union view
Optionaloptions: TableOptions & {Create a partition for an existing partitioned table (SQLite emulation)
Attach a partition to a partitioned table (SQLite emulation)
Detach a partition from a partitioned table (SQLite emulation) Marks the partition as detached but keeps the table
Add a partition to an existing partitioned table (SQLite emulation)
Name of the partitioned table
Name for the new partition
Partition specification
List partitions for a table (SQLite emulation)
Check if a table has partitions (SQLite emulation)
Create a user (stub - not supported by SQLite)
Optional_options: anyDrop a user (stub - not supported by SQLite)
Optional_options: anyList users (stub - not supported by SQLite)
Create a role (stub - not supported by SQLite)
Optional_options: anyDrop a role (stub - not supported by SQLite)
Optional_options: anyList roles (stub - not supported by SQLite)
Grant privileges (stub - not supported by SQLite)
Revoke privileges (stub - not supported by SQLite)
Add a column to a table
Remove a column from a table
Change a column definition
Show all tables in the database
Get table status (SQLite implementation)
OptionaltableName: stringGet table create statement (SQLite implementation)
Get MariaDB-specific version info (not supported in SQLite) This method exists for compatibility when tests fall back to SQLite
Show constraints for a table (SQLite)
Show indexes for a table
Create a schema SQLite doesn't have native schema support, but we can use ATTACH DATABASE For now, this is a no-op for compatibility with other dialects
Drop a schema SQLite doesn't have native schema support For now, this is a no-op for compatibility with other dialects
Optional_options: { ifExists?: boolean; cascade?: boolean }Show all schemas in the database SQLite doesn't have native schema support, returns default schema
List all schemas (SQLite only has main)
VACUUM the database - reclaims disk space and defragments
Optionaloptions: { schema?: string; shrinkTo?: number; analyze?: boolean }
VACUUM options
ANALYZE the database - updates statistics for query optimizer
Optionaltarget: string
Optional table or index name
Get database information using PRAGMA
Create an FTS5 virtual table for full-text search
Optionaloptions: {Perform full-text search
Optionaloptions: {Create a stored procedure (SQLite - Not Supported)
SQLite does not support stored procedures natively. However, you can achieve similar functionality using:
Example workaround using application code:
// Instead of:
await db.executeStoredProcedure({ procedureName: 'calculateTotal', params: { orderId: 1 } });
// Use application-level code:
const total = await calculateTotal(orderId);
Drop a stored procedure (SQLite - Not Supported)
Since SQLite does not support stored procedures, there is nothing to drop. If you previously created a stored procedure in an attached database, drop it from that database instead.
Optional_options: DropStoredProcedureOptionsDrop a stored procedure (alias for dropStoredProcedure) SQLite does not support stored procedures - use application code instead.
Optionaloptions: DropStoredProcedureOptionsExecute a stored procedure (SQLite - Not Supported)
SQLite does not support stored procedures. To execute the equivalent logic, move the procedure's SQL statements into your application code.
Example:
// Instead of:
const result = await db.executeStoredProcedure({
procedureName: 'getUserOrders',
params: { userId: 123 }
});
// Use a query directly:
const result = await db.query(
'SELECT * FROM orders WHERE user_id = ?',
[123]
);
Check if a stored procedure exists (SQLite - Always Returns False)
Since SQLite does not support stored procedures, this always returns false. If you are checking for a procedure in an attached database, use that database's native method instead.
Optional_schema: stringCreate a stored procedure (alias for createStoredProcedure) SQLite does not support stored procedures - use application code instead.
Create a foreign data wrapper (SQLite - not supported)
Optional_options: { handler?: string }Drop a foreign data wrapper (PostgreSQL)
Optional_options: { ifExists?: boolean }Create a foreign server (PostgreSQL)
Optional_options: { options?: Record<string, string>; ifNotExists?: boolean }Create a foreign table (PostgreSQL)
Optional_options: { serverName?: string; ifNotExists?: boolean }Change owner of a table or view
Add a constraint to a table
Remove a constraint from a table
Drop a security policy (MSSQL Row-Level Security)
Create a trigger
Optionaloptions: { replace?: boolean; forEachRow?: boolean; whenCondition?: string }Drop a trigger
Optionaloptions: { ifExists?: boolean }OptionaltableName: stringCreate a sequence (simulated using AUTOINCREMENT table)
Drop a sequence (simulated by dropping the sequence table)
Optionaloptions: DropSequenceOptionsGet next value from a sequence (simulated by inserting and getting last_insert_rowid)
Optionalschema: stringCheck if a sequence exists (simulated by checking if the sequence table exists)
Optionalschema: stringList all sequences in the database (SQLite - simulated via tables)
Array of sequence names
Optional_options: DropPolicyOptionsOptional_schema: stringOptional_schema: stringOptional_schema: stringOptionaloptions: IndexOptionsOptionaloptions: IndexOptionsOptional_options: {Create a PostgreSQL extension (not supported in SQLite)
Optional_options: CreateExtensionOptionsDrop a PostgreSQL extension (not supported in SQLite)
Optional_options: DropExtensionOptionsGet all installed PostgreSQL extensions (not supported in SQLite)
Check if a PostgreSQL extension is installed (not supported in SQLite)
Optional_opts: { ifExists?: boolean; cascade?: boolean }Optional_opts: FdwImportForeignSchemaOptionsCreate a database view (SQLite uses CREATE VIEW)
Name of the view to create
The SELECT query for the view
Optionaloptions: { replace?: boolean }
View options
Drop a database view
Name of the view to drop
Optionaloptions: { ifExists?: boolean }
Drop options
Show all views in the database
Create a materialized view - Not supported by SQLite
Refresh a materialized view - Not supported by SQLite
Optional_options: RefreshOptionsDrop a materialized view - Not supported by SQLite
Optional_options: DropMaterializedViewOptionsCheck if a materialized view exists - Not supported by SQLite
Show all materialized views - Not supported by SQLite
Describe a table (get column information)
Rename a table
Add an index to a table Supports partial indexes (WHERE) and expression indexes (SQLite 3.9.0+)
Optionaloptions: IndexOptionsRemove an index from a table
Create an index on a table with full options support Supports partial indexes (WHERE) and expression indexes (SQLite 3.9.0+)
Create a constraint on a table Note: SQLite has limited ALTER TABLE support for constraints
Drop a constraint from a table Note: SQLite has limited ALTER TABLE support for constraints
Optional_options: { ifExists?: boolean; cascade?: boolean }Build a WHERE clause from a WhereOptions object
Optionaloptions: { replacements?: Record<string, unknown> }Build an ORDER BY clause
Optionaloptions: { replacements?: Record<string, unknown> }Build a LIMIT/OFFSET clause
Optionallimit: string | numberOptionaloffset: string | numberBuild an INSERT query
Optionaloptions: InsertOptions & { doNothing?: boolean; conflictTargets?: ConflictTarget[] }
OptionaldoNothing?: booleanEmit ON CONFLICT (...) DO NOTHING instead of DO UPDATE.
OptionalconflictTargets?: ConflictTarget[]Multiple chained ON CONFLICT clauses targeting different unique
constraints, e.g. ON CONFLICT(a) DO NOTHING ON CONFLICT(b) DO UPDATE ....
When provided, this takes precedence over conflictFields/doNothing.
Build an UPSERT query (INSERT ... ON CONFLICT DO UPDATE) SQLite supports: INSERT OR REPLACE, INSERT OR IGNORE, and ON CONFLICT DO UPDATE
Optionaloptions: UpsertQueryOptions & { doNothing?: boolean; conflictTargets?: ConflictTarget[] }
OptionaldoNothing?: booleanEmit ON CONFLICT (...) DO NOTHING instead of DO UPDATE.
OptionalconflictTargets?: ConflictTarget[]Multiple chained ON CONFLICT clauses targeting different unique
constraints, e.g. ON CONFLICT(a) DO NOTHING ON CONFLICT(b) DO UPDATE ....
When provided, this takes precedence over conflictFields/doNothing.
Build an increment query
Table name
Fields to increment
Where clause
Optionaloptions: { by?: number }
Query options (by: number)
Build an UPDATE query
Optionaloptions: UpdateOptionsBuild a DELETE query
Optionaloptions: DeleteOptionsBuild a WITH [RECURSIVE] name [(cols)] AS (query), ... clause from
SelectOptions.cte. Nested SelectOptions queries are compiled via a
recursive call to buildSelectQuery; raw SQL strings are used verbatim.
SQLite uses unnumbered ? placeholders (unlike Postgres/MSSQL's
numbered $n/@pN), so no placeholder renumbering is needed here -
each CTE's values are simply concatenated in the order their ?
markers appear in the rendered SQL, and the caller prepends the whole
batch ahead of the main query's own values.
Optional_options: anyOptional_options: anyOptional_options: anyOptional_host: stringOptional_options: anyOptional_options: anyOptional_options: anyOptional_options: anyBulk insert records into a table
Optional_options: anyUpdate every row matching where with values in a single statement.
Delete every row matching where (deletes all rows when where is empty).
Add a foreign key to a table Note: SQLite does not support ALTER TABLE ADD CONSTRAINT. Foreign keys must be defined when the table is created.
Optionaloptions: {ProtectedsetRun a setter PRAGMA (one that returns no rows).
On better-sqlite3 this must go through the synchronous exec() API:
routing it through query() would call stmt.all() and throw
"statement does not return data". Subclasses that don't have a
synchronous connection (e.g. TursoDialect over the libSQL wire) override
this to run the PRAGMA through their async driver instead.
Rename a column
Create a fulltext index (SQLite supports FTS5)
Optional_options: { parser?: string; comment?: string }Create a spatial index (SQLite supports spatial indexing via RTree)
Optional_options: { storage?: string; srid?: number }Create user mapping (SQLite doesn't support user mappings)
Optional_options: anyDrop user mapping (SQLite doesn't support user mappings)
Optional_options: anyST_Distance - calculate distance between two geometries SQLite uses SpatiaLite extension or custom implementation
Optional_srid: numberST_DWithin - check if geometries are within a given distance
Optional_srid: numberST_Within - check if geometry A is within geometry B
Optional_srid: numberST_Contains - check if geometry A contains geometry B
Optional_srid: numberST_Intersects - check if geometries intersect
Optional_srid: numberST_AsText - convert geometry to text representation
ST_GeomFromText - create geometry from text
Optionalsrid: numberBuild an expression to shred a JSON document/array into relational rows.
SQLite has no JSON_TABLE function; its real equivalent is the json_each
table-valued function, which yields fixed columns (key, value, type, atom, id,
parent, fullkey, path). To project those into named/typed columns (matching the
other dialects' JSON_TABLE/OPENJSON shape), this wraps json_each in a derived table
that applies json_extract per requested column.
SQLite: (SELECT json_extract(je.value, '$.path') AS name, ... FROM
json_each(expr, '$.rowPath') AS je) AS alias
SQLite dialect class that implements the Dialect interface