Skip to main content
Kysely provides a powerful CreateTableBuilder API for defining database tables. Access it through db.schema.createTable().

​
Basic Table Creation

Create a simple table with columns: 
await db.schema
  .createTable('person')
  .addColumn('id', 'integer', (col) => col.primaryKey())
  .addColumn('first_name', 'varchar(50)', (col) => col.notNull())
  .addColumn('last_name', 'varchar(255)')
  .addColumn('email', 'varchar(255)', (col) => col.unique())
  .execute()

​
Column Types and Constraints

​
Common Data Types

Kysely supports standard SQL data types: 
await db.schema
  .createTable('product')
  .addColumn('id', 'integer', (col) => col.autoIncrement().primaryKey())
  .addColumn('name', 'varchar(255)', (col) => col.notNull())
  .addColumn('description', 'text')
  .addColumn('price', 'numeric(8, 2)', (col) => col.notNull())
  .addColumn('stock', 'integer', (col) => col.defaultTo(0))
  .addColumn('is_active', 'boolean', (col) => col.defaultTo(true))
  .addColumn('created_at', 'timestamp', (col) => col.defaultTo(sql`CURRENT_TIMESTAMP`))
  .execute()
For dialect-specific types, use the sql tag: 
import { sql } from 'kysely'

await db.schema
  .createTable('document')
  .addColumn('id', 'integer', (col) => col.primaryKey())
  .addColumn('metadata', sql`jsonb`)
  .execute()

​
Column Constraints

The column builder provides these constraint methods:
  • primaryKey() - Mark as primary key
  • notNull() - Disallow NULL values
  • unique() - Ensure uniqueness
  • defaultTo(value) - Set default value
  • unsigned() - Unsigned integers (MySQL)
  • autoIncrement() - Auto-incrementing (MySQL, SQLite)
  • check(expression) - Add check constraint

​
Auto-Incrementing IDs

Different databases use different approaches: MySQL/SQLite: 
await db.schema
  .createTable('person')
  .addColumn('id', 'integer', (col) => col.autoIncrement().primaryKey())
  .execute()
PostgreSQL (serial): 
await db.schema
  .createTable('person')
  .addColumn('id', 'serial', (col) => col.primaryKey())
  .execute()
PostgreSQL (identity): 
await db.schema
  .createTable('person')
  .addColumn('id', 'integer', (col) => 
    col.generatedAlwaysAsIdentity().primaryKey()
  )
  .execute()
MS SQL Server: 
await db.schema
  .createTable('person')
  .addColumn('id', 'integer', (col) => col.identity().primaryKey())
  .execute()

​
Foreign Keys

​
Column-Level Foreign Keys

Add foreign key constraints directly to columns: 
await db.schema
  .createTable('pet')
  .addColumn('id', 'integer', (col) => col.primaryKey())
  .addColumn('name', 'varchar(255)', (col) => col.notNull())
  .addColumn('owner_id', 'integer', (col) =>
    col.references('person.id').onDelete('cascade')
  )
  .execute()

​
Table-Level Foreign Keys

Some databases (like older MySQL versions) require table-level constraints: 
await db.schema
  .createTable('pet')
  .addColumn('id', 'integer', (col) => col.primaryKey())
  .addColumn('owner_id', 'integer')
  .addForeignKeyConstraint(
    'pet_owner_fk',
    ['owner_id'],
    'person',
    ['id'],
    (cb) => cb.onDelete('cascade').onUpdate('cascade')
  )
  .execute()

​
Composite Foreign Keys

await db.schema
  .createTable('order_item')
  .addColumn('order_id', 'integer')
  .addColumn('product_id', 'integer')
  .addColumn('quantity', 'integer')
  .addForeignKeyConstraint(
    'order_item_order_fk',
    ['order_id', 'product_id'],
    'order',
    ['id', 'product_id']
  )
  .execute()

​
Table Constraints

​
Primary Key Constraints

Define composite primary keys: 
await db.schema
  .createTable('user_role')
  .addColumn('user_id', 'integer')
  .addColumn('role_id', 'integer')
  .addPrimaryKeyConstraint('user_role_pk', ['user_id', 'role_id'])
  .execute()

​
Unique Constraints

await db.schema
  .createTable('person')
  .addColumn('id', 'integer', (col) => col.primaryKey())
  .addColumn('first_name', 'varchar(64)')
  .addColumn('last_name', 'varchar(64)')
  .addUniqueConstraint('person_name_unique', ['first_name', 'last_name'])
  .execute()
On PostgreSQL, specify nulls not distinct: 
await db.schema
  .createTable('person')
  .addColumn('email', 'varchar(255)')
  .addUniqueConstraint(
    'person_email_unique',
    ['email'],
    (cb) => cb.nullsNotDistinct()
  )
  .execute()

​
Check Constraints

import { sql } from 'kysely'

await db.schema
  .createTable('product')
  .addColumn('price', 'numeric(8, 2)')
  .addColumn('discount_price', 'numeric(8, 2)')
  .addCheckConstraint(
    'price_check',
    sql`price > 0 and discount_price <= price`
  )
  .execute()

​
Generated Columns

​
Virtual Generated Columns

import { sql } from 'kysely'

await db.schema
  .createTable('person')
  .addColumn('first_name', 'varchar(50)')
  .addColumn('last_name', 'varchar(50)')
  .addColumn('full_name', 'varchar(101)', (col) =>
    col.generatedAlwaysAs(sql`concat(first_name, ' ', last_name)`)
  )
  .execute()

​
Stored Generated Columns

await db.schema
  .createTable('person')
  .addColumn('first_name', 'varchar(50)')
  .addColumn('last_name', 'varchar(50)')
  .addColumn('full_name', 'varchar(101)', (col) =>
    col.generatedAlwaysAs(sql`concat(first_name, ' ', last_name)`).stored()
  )
  .execute()

​
Special Table Types

​
Temporary Tables

await db.schema
  .createTable('temp_results')
  .temporary()
  .addColumn('id', 'integer')
  .addColumn('value', 'varchar(255)')
  .execute()
On PostgreSQL, control commit behavior: 
await db.schema
  .createTable('temp_results')
  .temporary()
  .onCommit('drop')
  .addColumn('id', 'integer')
  .execute()

​
Conditional Creation

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

​
Create Table From SELECT

await db.schema
  .createTable('person_backup')
  .as(db.selectFrom('person').selectAll())
  .execute()

​
Advanced Modifiers

​
Front Modifiers

Add SQL after the create keyword: 
import { sql } from 'kysely'

await db.schema
  .createTable('person')
  .modifyFront(sql`global temporary`)
  .addColumn('id', 'integer', (col) => col.primaryKey())
  .execute()

​
End Modifiers

Add SQL to the end of the statement: 
await db.schema
  .createTable('person')
  .addColumn('id', 'integer', (col) => col.primaryKey())
  .addColumn('name', 'varchar(255)')
  .modifyEnd(sql`engine=InnoDB default charset=utf8mb4`)
  .execute()

​
Reusable Patterns

Create reusable column definitions: 
import { type CreateTableBuilder, sql } from 'kysely'

const withTimestamps = (builder: CreateTableBuilder<any, any>) => {
  return builder
    .addColumn('created_at', 'timestamp', (col) =>
      col.notNull().defaultTo(sql`CURRENT_TIMESTAMP`)
    )
    .addColumn('updated_at', 'timestamp', (col) =>
      col.notNull().defaultTo(sql`CURRENT_TIMESTAMP`)
    )
}

await db.schema
  .createTable('person')
  .addColumn('id', 'integer', (col) => col.primaryKey())
  .addColumn('name', 'varchar(255)', (col) => col.notNull())
  .$call(withTimestamps)
  .execute()

Build docs developers (and LLMs) love

Get started for freeTalk to us