BaseSQLiteDatabase The core SQLite database class that provides all query builder methods and transaction support.
Type Parameters Determines whether the database operates in synchronous or asynchronous mode
The return type for run operations (typically contains lastInsertRowid and changes)
The full schema definition including all tables and relations
Methods select() Creates a select query. Call with no arguments to select all columns, or pass a selection object to specify columns.
select (): SQLiteSelectBuilder < undefined , TResultKind , TRunResult > select < TSelection extends SelectedFields > ( fields : TSelection ): SQLiteSelectBuilder < TSelection , TResultKind , TRunResult >
All columns
Specific columns
With expressions
// Select all columns and all rows from the 'cars' table const allCars : Car [] = await db . select (). from ( cars );
selectDistinct() Adds DISTINCT to the select query, returning only unique values.
selectDistinct (): SQLiteSelectBuilder < undefined , TResultKind , TRunResult > selectDistinct < TSelection extends SelectedFields > ( fields : TSelection ): SQLiteSelectBuilder < TSelection , TResultKind , TRunResult >
All unique rows
Unique brands
await db . selectDistinct () . from ( cars ) . orderBy ( cars . id , cars . brand , cars . color );
insert() Creates an insert query to add new rows to a table.
insert < TTable extends SQLiteTable > ( into : TTable ): SQLiteInsertBuilder < TTable , TResultKind , TRunResult >
Single row
Multiple rows
With returning
// Insert one row await db . insert ( cars ). values ({ brand: 'BMW' });
update() Creates an update query to modify existing rows. Use .set() to specify values and .where() to filter rows.
update < TTable extends SQLiteTable > ( table : TTable ): SQLiteUpdateBuilder < TTable , TResultKind , TRunResult >
Update all rows
With filter
With returning
// Update all rows in the table await db . update ( cars ). set ({ color: 'red' });
delete() Creates a delete query to remove rows from a table.
delete < TTable extends SQLiteTable > ( from : TTable ): SQLiteDeleteBase < TTable , TResultKind , TRunResult >
Delete all rows
With filter
With returning
// Delete all rows in the table await db . delete ( cars );
$with() Creates a Common Table Expression (CTE) that can be referenced in subsequent queries.
$with ( alias : string ): WithBuilder
Basic CTE
CTE with aliased SQL
// Create a CTE and use it in the select query const sq = db . $with ( 'sq' ). as ( db . select (). from ( users ). where ( eq ( users . id , 42 )) ); const result = await db . with ( sq ). select (). from ( sq );
with() Incorporates previously defined CTEs into the main query.
with ( ... queries : WithSubquery []) : { select : Function ; selectDistinct : Function ; update : Function ; insert : Function ; delete : Function ; }
const sq = db . $with ( 'sq' ). as ( db . select (). from ( users ). where ( eq ( users . id , 42 )) ); const result = await db . with ( sq ). select (). from ( sq );
$count() Counts rows in a table or view with optional filtering.
$count ( source : SQLiteTable | SQLiteViewBase | SQL | SQLWrapper , filters ?: SQL < unknown > ): SQLiteCountBuilder
// Count all users const count = await db . $count ( users ); // Count with filter const activeCount = await db . $count ( users , eq ( users . active , true ));
run() Executes a raw SQL query that doesn’t return data (e.g., DDL statements).
run ( query : SQLWrapper | string ): DBResult < TResultKind , TRunResult >
// Execute raw SQL await db . run ( sql `CREATE INDEX idx_name ON users(name)` ); // Execute string query await db . run ( 'VACUUM' );
all() Executes a query and returns all rows.
all < T = unknown >( query : SQLWrapper | string ) : DBResult < TResultKind , T []>
const rows = await db . all <{ id : number ; name : string }>( sql `SELECT id, name FROM users` );
get() Executes a query and returns the first row.
get < T = unknown >( query : SQLWrapper | string ) : DBResult < TResultKind , T >
const user = await db . get <{ id : number ; name : string }>( sql `SELECT id, name FROM users WHERE id = 1` );
values() Executes a query and returns rows as arrays of values.
values < T extends unknown [] = unknown [] > ( query : SQLWrapper | string ): DBResult < TResultKind , T [] >
const rows = await db . values ( sql `SELECT id, name FROM users` ); // [[1, 'Alice'], [2, 'Bob'], ...]
transaction() Executes a function within a database transaction.
transaction < T >( transaction : ( tx : SQLiteTransaction <...>) => Result < TResultKind , T > , config ?: SQLiteTransactionConfig ): Result < TResultKind , T >
Basic transaction
With rollback
await db . transaction ( async ( tx ) => { await tx . insert ( users ). values ({ name: 'Alice' }); await tx . insert ( posts ). values ({ userId: 1 , title: 'Hello' }); });
withReplicas() Configures a database instance to use read replicas for select queries.
withReplicas < Q extends BaseSQLiteDatabase <...>> ( primary : Q , replicas : [ Q , ... Q []], getReplica ?: ( replicas : Q []) => Q ): SQLiteWithReplicas < Q >
primary
BaseSQLiteDatabase
required
The primary database instance for write operations
replicas
BaseSQLiteDatabase[]
required
Array of replica database instances for read operations
Custom function to select a replica. Defaults to random selection
import { drizzle } from 'drizzle-orm/better-sqlite3' ; import { withReplicas } from 'drizzle-orm/sqlite-core' ; import Database from 'better-sqlite3' ; const primaryDb = drizzle ( new Database ( 'primary.db' )); const replica1 = drizzle ( new Database ( 'replica1.db' )); const replica2 = drizzle ( new Database ( 'replica2.db' )); const db = withReplicas ( primaryDb , [ replica1 , replica2 ]); // Reads go to replicas const users = await db . select (). from ( usersTable ); // Writes go to primary await db . insert ( usersTable ). values ({ name: 'Alice' }); // Access primary explicitly await db . $primary . select (). from ( usersTable );