SQLite Database

Core SQLite database API for Drizzle ORM

BaseSQLiteDatabase

The core SQLite database class that provides all query builder methods and transaction support.

Type Parameters

TResultKind · 'sync' | 'async'

Determines whether the database operates in synchronous or asynchronous mode

TRunResult · any

The return type for run operations (typically contains lastInsertRowid and changes)

TFullSchema · Record<string, unknown>

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

// 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

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

// 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

// 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

// 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

// 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

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

getReplica · (replicas: Q[]) => Q

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);