Last Updated: 3/20/2026

Schema Builder

The db.schema property (defined in src/schema/schema.ts) provides methods for creating and modifying database schema: tables, indexes, views, and custom types. Unlike the query builder, schema operations are imperative—they execute DDL statements that change the database structure.

Creating Tables

Use db.schema.createTable() to define a new table:

await db.schema
  .createTable('person')
  .addColumn('id', 'integer', (col) => col.primaryKey().autoIncrement())
  .addColumn('first_name', 'varchar(50)', (col) => col.notNull())
  .addColumn('last_name', 'varchar(255)')
  .addColumn('age', 'integer')
  .execute()

The generated SQL (PostgreSQL):

create table "person" (
  "id" integer primary key autoincrement,
  "first_name" varchar(50) not null,
  "last_name" varchar(255),
  "age" integer
)

Column Types and Constraints

The second argument to addColumn is a data type expression. You can use any string your database supports, or use the sql tag for custom types:

import { sql } from 'kysely'
 
await db.schema
  .createTable('product')
  .addColumn('id', 'serial', (col) => col.primaryKey())
  .addColumn('name', 'text', (col) => col.notNull())
  .addColumn('price', 'numeric(10, 2)')
  .addColumn('metadata', sql`jsonb`)
  .execute()

The third argument is a callback that receives a ColumnDefinitionBuilder (see src/schema/column-definition-builder.ts). Common constraint methods include:

• primaryKey() — Marks the column as the primary key.
• autoIncrement() — Adds auto-increment behavior (syntax varies by database).
• notNull() — Adds a NOT NULL constraint.
• unique() — Adds a UNIQUE constraint.
• defaultTo(value) — Sets a default value. Use sql for expressions like sqlnow()“.
• check(expression) — Adds a column-level check constraint.
• references(target) — Adds a foreign key constraint. The target is a string like 'person.id'.
• onDelete(action) — Sets the ON DELETE action for a foreign key ('cascade', 'set null', 'restrict', etc.).
• onUpdate(action) — Sets the ON UPDATE action for a foreign key.

Example with foreign key:

await db.schema
  .createTable('pet')
  .addColumn('id', 'integer', (col) => col.primaryKey().autoIncrement())
  .addColumn('name', 'varchar(255)', (col) => col.notNull())
  .addColumn('owner_id', 'integer', (col) =>
    col.references('person.id').onDelete('cascade')
  )
  .execute()

Note: Not all databases support column-level foreign key constraints. For example, older MySQL versions require table-level constraints (see below).

Table-Level Constraints

For databases that don’t support column-level foreign keys, or when you need multi-column constraints, use the table-level constraint methods:

await db.schema
  .createTable('pet')
  .addColumn('id', 'integer', (col) => col.primaryKey())
  .addColumn('owner_id', 'integer')
  .addForeignKeyConstraint(
    'pet_owner_id_fk',
    ['owner_id'],
    'person',
    ['id'],
    (cb) => cb.onDelete('cascade')
  )
  .execute()

Other table-level constraint methods:

• addPrimaryKeyConstraint(name, columns) — Adds a primary key constraint on one or more columns.
• addUniqueConstraint(name, columns) — Adds a unique constraint on one or more columns.
• addCheckConstraint(name, expression) — Adds a check constraint using a raw SQL expression.

Temporary Tables and Modifiers

Use temporary() to create a temporary table:

await db.schema
  .createTable('temp_data')
  .temporary()
  .addColumn('id', 'integer')
  .addColumn('value', 'text')
  .execute()

Use ifNotExists() to avoid errors if the table already exists:

await db.schema
  .createTable('person')
  .ifNotExists()
  .addColumn('id', 'integer', (col) => col.primaryKey())
  .execute()

Use modifyFront() and modifyEnd() to add arbitrary SQL before or after the table definition:

await db.schema
  .createTable('person')
  .addColumn('id', 'integer', (col) => col.primaryKey())
  .modifyEnd(sql`engine = InnoDB`)
  .execute()

Creating Tables from SELECT

Use as() to create a table from a query result:

await db.schema
  .createTable('person_copy')
  .temporary()
  .as(db.selectFrom('person').select(['first_name', 'last_name']))
  .execute()

The generated SQL (PostgreSQL):

create temporary table "person_copy" as
select "first_name", "last_name" from "person"

Altering Tables

Use db.schema.alterTable() to modify an existing table:

await db.schema
  .alterTable('person')
  .addColumn('email', 'varchar(255)')
  .execute()

The AlterTableBuilder (see src/schema/alter-table-builder.ts) provides methods for:

• addColumn(name, type, build?) — Adds a new column.
• dropColumn(name) — Drops a column.
• renameColumn(from, to) — Renames a column.
• alterColumn(name, build) — Modifies a column’s type or constraints. The callback receives an AlterColumnBuilder with methods like setDataType(), setDefault(), dropDefault(), setNotNull(), dropNotNull().
• addUniqueConstraint(name, columns) — Adds a unique constraint.
• addCheckConstraint(name, expression) — Adds a check constraint.
• addForeignKeyConstraint(name, columns, targetTable, targetColumns, build?) — Adds a foreign key constraint.
• dropConstraint(name) — Drops a constraint by name.
• renameTo(newName) — Renames the table.

Example:

await db.schema
  .alterTable('person')
  .alterColumn('age', (col) => col.setDataType('bigint'))
  .addColumn('created_at', 'timestamp', (col) =>
    col.notNull().defaultTo(sql`now()`)
  )
  .execute()

Dropping Tables

Use db.schema.dropTable() to remove a table:

await db.schema
  .dropTable('person')
  .execute()

Use ifExists() to avoid errors if the table doesn’t exist:

await db.schema
  .dropTable('person')
  .ifExists()
  .execute()

Use cascade() to drop dependent objects:

await db.schema
  .dropTable('person')
  .cascade()
  .execute()

Creating Indexes

Use db.schema.createIndex() to add an index:

await db.schema
  .createIndex('person_name_index')
  .on('person')
  .columns(['first_name', 'last_name'])
  .execute()

The CreateIndexBuilder (see src/schema/create-index-builder.ts) provides methods for:

• on(table) — Specifies the table to index.
• columns(columns) — Specifies the columns to index.
• unique() — Creates a unique index.
• using(method) — Specifies the index method (e.g., 'btree', 'hash', 'gin').
• where(expression) — Adds a partial index condition.
• ifNotExists() — Avoids errors if the index already exists.

Example with a partial index:

await db.schema
  .createIndex('active_users_index')
  .on('person')
  .columns(['email'])
  .where(sql`active = true`)
  .execute()

Dropping Indexes

Use db.schema.dropIndex() to remove an index:

await db.schema
  .dropIndex('person_name_index')
  .execute()

Use ifExists() to avoid errors:

await db.schema
  .dropIndex('person_name_index')
  .ifExists()
  .execute()

On some databases you need to specify the table:

await db.schema
  .dropIndex('person_name_index')
  .on('person')
  .execute()

Creating Views

Use db.schema.createView() to define a view:

await db.schema
  .createView('active_users')
  .as(db.selectFrom('person').selectAll().where('active', '=', true))
  .execute()

Use orReplace() to replace an existing view:

await db.schema
  .createView('active_users')
  .orReplace()
  .as(db.selectFrom('person').selectAll().where('active', '=', true))
  .execute()

Use materialized() to create a materialized view (PostgreSQL):

await db.schema
  .createView('user_stats')
  .materialized()
  .as(
    db.selectFrom('person')
      .select((eb) => [
        'id',
        eb.fn.count('id').as('total_count')
      ])
      .groupBy('id')
  )
  .execute()

Dropping Views

Use db.schema.dropView() to remove a view:

await db.schema
  .dropView('active_users')
  .execute()

Use ifExists() and cascade() as needed:

await db.schema
  .dropView('active_users')
  .ifExists()
  .cascade()
  .execute()

Creating Custom Types

Some databases (like PostgreSQL) support user-defined types. Use db.schema.createType():

await db.schema
  .createType('species')
  .asEnum(['dog', 'cat', 'bird'])
  .execute()

The generated SQL (PostgreSQL):

create type "species" as enum ('dog', 'cat', 'bird')

Dropping Custom Types

Use db.schema.dropType() to remove a custom type:

await db.schema
  .dropType('species')
  .ifExists()
  .execute()

Using Schema Builder in Migrations

The schema builder is designed to work with Kysely’s migration system. Each migration exports up and down functions that receive a Kysely instance:

import { Kysely, sql } from 'kysely'
 
export async function up(db: Kysely<any>): Promise<void> {
  await db.schema
    .createTable('person')
    .addColumn('id', 'serial', (col) => col.primaryKey())
    .addColumn('first_name', 'varchar(255)', (col) => col.notNull())
    .addColumn('last_name', 'varchar(255)', (col) => col.notNull())
    .addColumn('created_at', 'timestamp', (col) =>
      col.notNull().defaultTo(sql`now()`)
    )
    .execute()
 
  await db.schema
    .createIndex('person_name_index')
    .on('person')
    .columns(['first_name', 'last_name'])
    .execute()
}
 
export async function down(db: Kysely<any>): Promise<void> {
  await db.schema.dropTable('person').execute()
}

What’s Next

• Migrations — Learn how to manage schema changes over time using Kysely’s migration system.
• Dialects — Understand how different databases handle DDL statements differently.
• Generating Types — Automatically generate TypeScript types from your schema.