SQLite Table

Define SQLite tables with sqliteTable() and configure schema structure

sqliteTable()

Defines a SQLite table with columns and optional constraints.

Signature

function sqliteTable<
  TTableName extends string,
  TColumnsMap extends Record<string, SQLiteColumnBuilderBase>,
>(
  name: TTableName,
  columns: TColumnsMap | ((columnTypes: SQLiteColumnBuilders) => TColumnsMap),
  extraConfig?: (self: BuildColumns<...>) => SQLiteTableExtraConfigValue[]
): SQLiteTableWithColumns<...>

Parameters

name · string · required

The name of the table in the database

columns · object | function · required

Object mapping column names to column definitions, or a function that receives column builders

extraConfig · function

Optional function to define indexes, foreign keys, checks, and composite primary keys

Basic Usage

Object syntax

import { sqliteTable, integer, text } from 'drizzle-orm/sqlite-core';

export const users = sqliteTable('users', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  name: text('name').notNull(),
  email: text('email').notNull().unique(),
  createdAt: integer('created_at', { mode: 'timestamp' }).notNull(),
});

Table with Indexes

import { sqliteTable, integer, text, index } from 'drizzle-orm/sqlite-core';

export const users = sqliteTable('users', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  name: text('name').notNull(),
  email: text('email').notNull(),
  age: integer('age'),
}, (t) => [
  index('name_idx').on(t.name),
  index('email_idx').on(t.email),
]);

Table with Foreign Keys

import { sqliteTable, integer, text, foreignKey } from 'drizzle-orm/sqlite-core';

export const users = sqliteTable('users', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  name: text('name').notNull(),
});

export const posts = sqliteTable('posts', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  title: text('title').notNull(),
  authorId: integer('author_id').notNull(),
}, (t) => [
  foreignKey({
    columns: [t.authorId],
    foreignColumns: [users.id],
  }).onDelete('cascade'),
]);

Table with Composite Primary Key

import { sqliteTable, integer, text, primaryKey } from 'drizzle-orm/sqlite-core';

export const userRoles = sqliteTable('user_roles', {
  userId: integer('user_id').notNull(),
  roleId: integer('role_id').notNull(),
  assignedAt: integer('assigned_at', { mode: 'timestamp' }),
}, (t) => [
  primaryKey({ columns: [t.userId, t.roleId] }),
]);

Table with Check Constraints

import { sqliteTable, integer, text, check, sql } from 'drizzle-orm/sqlite-core';

export const products = sqliteTable('products', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  name: text('name').notNull(),
  price: integer('price').notNull(),
  stock: integer('stock').notNull(),
}, (t) => [
  check('price_positive', sql`${t.price} > 0`),
  check('stock_non_negative', sql`${t.stock} >= 0`),
]);

Table with Unique Constraints

import { sqliteTable, integer, text, unique } from 'drizzle-orm/sqlite-core';

export const users = sqliteTable('users', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  email: text('email').notNull(),
  username: text('username').notNull(),
  organizationId: integer('organization_id').notNull(),
}, (t) => [
  unique('email_org_unique').on(t.email, t.organizationId),
]);

sqliteTableCreator()

Creates a custom table creator function with a name transformation function.

function sqliteTableCreator(
  customizeTableName: (name: string) => string
): SQLiteTableFn

Use Case: Table Prefixes

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

const sqliteTable = sqliteTableCreator((name) => `myapp_${name}`);

export const users = sqliteTable('users', {
  id: integer('id').primaryKey(),
  name: text('name'),
});
// Creates table: myapp_users

Use Case: Schema Prefixes

const publicTable = sqliteTableCreator((name) => `public_${name}`);
const adminTable = sqliteTableCreator((name) => `admin_${name}`);

export const users = publicTable('users', {
  id: integer('id').primaryKey(),
});
// Creates: public_users

export const admins = adminTable('users', {
  id: integer('id').primaryKey(),
});
// Creates: admin_users

Table Configuration Helpers

index()

Creates an index on one or more columns.

function index(name: string): IndexBuilderOn

Single column

index('name_idx').on(t.name)

uniqueIndex()

Creates a unique index.

function uniqueIndex(name: string): IndexBuilderOn

uniqueIndex('email_idx').on(t.email)

primaryKey()

Defines a composite primary key.

function primaryKey(config: {
  name?: string;
  columns: [Column, ...Column[]];
}): PrimaryKeyBuilder

primaryKey({ columns: [t.userId, t.roleId] })

foreignKey()

Defines a foreign key relationship.

function foreignKey(config: {
  name?: string;
  columns: Column[];
  foreignColumns: Column[];
}): ForeignKeyBuilder

columns · Column[] · required

The columns in the current table that reference the foreign table

foreignColumns · Column[] · required

The columns in the foreign table being referenced

name · string

Optional custom name for the foreign key constraint

foreignKey({
  columns: [t.authorId],
  foreignColumns: [users.id],
})
  .onDelete('cascade')
  .onUpdate('restrict')

check()

Defines a check constraint.

function check(name: string, value: SQL): CheckBuilder

check('price_positive', sql`${t.price} > 0`)

unique()

Defines a unique constraint on one or more columns.

function unique(name?: string): UniqueConstraintBuilder

unique('email_unique').on(t.email, t.organizationId)

Foreign Key Actions

When defining foreign keys, you can specify actions for updates and deletes:

onDelete · UpdateDeleteAction

Action when the referenced row is deleted

onUpdate · UpdateDeleteAction

Action when the referenced row is updated

Available Actions:

• 'cascade' - Propagate the change to dependent rows
• 'restrict' - Prevent the change if dependent rows exist
• 'no action' - No action (SQLite default)
• 'set null' - Set the foreign key column to NULL
• 'set default' - Set the foreign key column to its default value

foreignKey({
  columns: [t.authorId],
  foreignColumns: [users.id],
})
  .onDelete('cascade')  // Delete posts when user is deleted
  .onUpdate('restrict')  // Prevent user ID updates if posts exist