Skip to main content

​
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

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

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

Build docs developers (and LLMs) love

Get started for freeTalk to us