SQLite Utilities

Utility functions and helpers for SQLite schema and queries

Schema Utilities

getTableConfig()

Extracts the complete configuration of a SQLite table including columns, indexes, foreign keys, checks, primary keys, and unique constraints.

function getTableConfig<TTable extends SQLiteTable>(
  table: TTable
): {
  columns: SQLiteColumn[];
  indexes: Index[];
  foreignKeys: ForeignKey[];
  checks: Check[];
  primaryKeys: PrimaryKey[];
  uniqueConstraints: UniqueConstraint[];
  name: string;
}

Example usage

import { getTableConfig } from 'drizzle-orm/sqlite-core';

const config = getTableConfig(users);

console.log('Table name:', config.name);
console.log('Columns:', config.columns.map(c => c.name));
console.log('Indexes:', config.indexes.map(i => i.config.name));
console.log('Foreign keys:', config.foreignKeys.length);

getViewConfig()

Extracts the configuration of a SQLite view.

function getViewConfig<
  TName extends string,
  TExisting extends boolean
>(
  view: SQLiteView<TName, TExisting>
): ViewConfig

import { getViewConfig } from 'drizzle-orm/sqlite-core';

const config = getViewConfig(activeUsersView);
console.log('View name:', config.name);
console.log('Query:', config.query);

Column Builders

getSQLiteColumnBuilders()

Returns an object containing all SQLite column builder functions. Used internally by the table builder when using function syntax.

function getSQLiteColumnBuilders(): SQLiteColumnBuilders

interface SQLiteColumnBuilders {
  integer: typeof integer;
  text: typeof text;
  real: typeof real;
  numeric: typeof numeric;
  blob: typeof blob;
}

import { getSQLiteColumnBuilders } from 'drizzle-orm/sqlite-core';

const builders = getSQLiteColumnBuilders();

// Equivalent to using individual imports
const id = builders.integer('id').primaryKey();
const name = builders.text('name').notNull();

Conflict Resolution

OnConflict Type

Defines SQLite’s conflict resolution strategies.

type OnConflict = 'rollback' | 'abort' | 'fail' | 'ignore' | 'replace';

rollback · string

Aborts the current SQL statement and rolls back the transaction

abort · string

Aborts the current SQL statement (default if not specified)

fail · string

Aborts the current SQL statement but does not back out prior changes

ignore · string

Skips the row that contains the constraint violation

replace · string

Deletes pre-existing rows that cause the constraint violation

Primary key conflict

const table = sqliteTable('table', {
  id: integer('id').primaryKey({ onConflict: 'replace' }),
  value: text('value'),
});

Foreign Key Actions

UpdateDeleteAction Type

Defines actions for foreign key constraints on UPDATE and DELETE operations.

type UpdateDeleteAction = 
  | 'cascade' 
  | 'restrict' 
  | 'no action' 
  | 'set null' 
  | 'set default';

cascade · string

Propagate the change (update/delete) to all referencing rows

restrict · string

Prevent the change if any referencing rows exist

no action · string

No action (SQLite default, defers check until end of transaction)

set null · string

Set the foreign key column to NULL in referencing rows

set default · string

Set the foreign key column to its default value in referencing rows

Cascade delete

export const posts = sqliteTable('posts', {
  id: integer('id').primaryKey(),
  authorId: integer('author_id').references(() => users.id, {
    onDelete: 'cascade',  // Delete posts when user is deleted
  }),
});

Helper Functions

extractUsedTable()

Extracts the table names used in a query expression. Useful for dependency analysis.

function extractUsedTable(
  table: SQLiteTable | Subquery | SQLiteViewBase | SQL
): string[]

import { extractUsedTable } from 'drizzle-orm/sqlite-core';

const tables = extractUsedTable(
  sql`SELECT * FROM ${users} WHERE id IN (SELECT user_id FROM ${posts})`
);
console.log(tables); // ['users', 'posts']

Type Utilities

AnySQLiteTable

A utility type representing any SQLite table with optional partial configuration.

type AnySQLiteTable<TPartial extends Partial<TableConfig> = {}> = 
  SQLiteTable<UpdateTableConfig<TableConfig, TPartial>>

function processTable(table: AnySQLiteTable) {
  const config = getTableConfig(table);
  // ...
}

SQLiteTableWithColumns

A utility type that combines a table with its column accessors.

type SQLiteTableWithColumns<T extends TableConfig> = 
  SQLiteTable<T> & {
    [Key in keyof T['columns']]: T['columns'][Key];
  }

const users: SQLiteTableWithColumns<typeof usersConfig> = sqliteTable(...);

// Access columns directly
console.log(users.id.name);
console.log(users.email.notNull);

Alias Utilities

Create table aliases for self-joins and complex queries.

import { alias } from 'drizzle-orm/sqlite-core';

const users = sqliteTable('users', {
  id: integer('id').primaryKey(),
  name: text('name'),
  managerId: integer('manager_id'),
});

const managers = alias(users, 'managers');

const result = await db
  .select({
    employeeName: users.name,
    managerName: managers.name,
  })
  .from(users)
  .leftJoin(managers, eq(users.managerId, managers.id));

SQL Template Helpers

SQLite-specific SQL template utilities.

sql Template Tag

Create raw SQL expressions.

import { sql } from 'drizzle-orm';

// In column definitions
const table = sqliteTable('table', {
  createdAt: integer('created_at')
    .default(sql`(unixepoch())`),
  upperName: text('upper_name')
    .generatedAlwaysAs(sql`upper(name)`),
});

// In queries
const result = await db
  .select({
    id: users.id,
    daysSinceCreated: sql<number>`(unixepoch() - ${users.createdAt}) / 86400`,
  })
  .from(users);

Common SQLite Functions

Date/Time functions

import { sql } from 'drizzle-orm';

// Current timestamp (seconds)
sql`(unixepoch())`

// Current timestamp (milliseconds)
sql`(cast((julianday('now') - 2440587.5)*86400000 as integer))`

// Date formatting
sql`date(${column}, 'unixepoch')`
sql`datetime(${column}, 'unixepoch')`

Placeholder

Create named placeholders for prepared statements.

import { placeholder } from 'drizzle-orm';

const prepared = db
  .select()
  .from(users)
  .where(and(
    eq(users.role, placeholder('role')),
    gte(users.age, placeholder('minAge'))
  ))
  .prepare();

const admins = await prepared.all({ role: 'admin', minAge: 21 });
const moderators = await prepared.all({ role: 'moderator', minAge: 18 });

InlineForeignKeys Symbol

Internal symbol used to access inline foreign key definitions.

const InlineForeignKeys = Symbol.for('drizzle:SQLiteInlineForeignKeys');

import { SQLiteTable } from 'drizzle-orm/sqlite-core';

const foreignKeys = users[SQLiteTable.Symbol.InlineForeignKeys];
console.log('Foreign keys:', foreignKeys);