Skip to main content
The CreateTableBuilder class is used to construct CREATE TABLE queries. It provides a fluent API for defining columns, constraints, and table properties.

​
Type Parameters

  • TB extends string - The table name
  • C extends string - Union of column names that have been added

​
Methods

​
temporary

Adds the “temporary” modifier to create a temporary table. 
temporary(): CreateTableBuilder<TB, C>
​
returns
CreateTableBuilder<TB, C>
A new builder with the temporary modifier

​
onCommit

Adds an “on commit” statement. This can be used in conjunction with temporary tables on supported databases like PostgreSQL. 
onCommit(onCommit: OnCommitAction): CreateTableBuilder<TB, C>
​
onCommit
OnCommitAction
required
The on commit action (e.g., preserve rows, delete rows, drop)
​
returns
CreateTableBuilder<TB, C>
A new builder with the on commit action

​
ifNotExists

Adds the “if not exists” modifier. If the table already exists, no error is thrown when this method has been called. 
ifNotExists(): CreateTableBuilder<TB, C>
​
returns
CreateTableBuilder<TB, C>
A new builder with the if not exists modifier

​
addColumn

Adds a column to the table. 
addColumn<CN extends string>(
  columnName: CN,
  dataType: DataTypeExpression,
  build?: ColumnBuilderCallback
): CreateTableBuilder<TB, C | CN>
​
columnName
string
required
The name of the column
​
dataType
DataTypeExpression
required
The data type of the column (e.g., integer, varchar(50), text)
​
build
ColumnBuilderCallback
Optional callback to configure the column definition
​
returns
CreateTableBuilder<TB, C | CN>
A new builder with the column added

​
Examples

import { sql } from 'kysely'

await db.schema
  .createTable('person')
  .addColumn('id', 'integer', (col) => col.autoIncrement().primaryKey())
  .addColumn('first_name', 'varchar(50)', (col) => col.notNull())
  .addColumn('last_name', 'varchar(255)')
  .addColumn('bank_balance', 'numeric(8, 2)')
  // You can specify any data type using the `sql` tag if the types
  // don't include it.
  .addColumn('data', sql`any_type_here`)
  .addColumn('parent_id', 'integer', (col) =>
    col.references('person.id').onDelete('cascade')
  )
For databases that don’t support foreign key constraints in column definitions (like MySQL 5.X): 
await db.schema
  .createTable('person')
  .addColumn('id', 'integer', (col) => col.primaryKey())
  .addColumn('parent_id', 'integer')
  .addForeignKeyConstraint(
    'person_parent_id_fk',
    ['parent_id'],
    'person',
    ['id'],
    (cb) => cb.onDelete('cascade')
  )
  .execute()
PostgreSQL auto-incrementing column: 
await db.schema
  .createTable('person')
  .addColumn('id', 'serial', (col) => col.primaryKey())
  .execute()

​
addPrimaryKeyConstraint

Adds a primary key constraint for one or more columns. The constraint name can be anything you want, but it must be unique across the whole database. 
addPrimaryKeyConstraint(
  constraintName: string,
  columns: C[],
  build?: PrimaryKeyConstraintBuilderCallback
): CreateTableBuilder<TB, C>
​
constraintName
string
required
The name of the constraint
​
columns
C[]
required
The columns to include in the primary key
​
build
PrimaryKeyConstraintBuilderCallback
Optional callback to configure the constraint
​
returns
CreateTableBuilder<TB, C>
A new builder with the constraint added

​
Examples

await db.schema
  .createTable('person')
  .addColumn('first_name', 'varchar(64)')
  .addColumn('last_name', 'varchar(64)')
  .addPrimaryKeyConstraint('primary_key', ['first_name', 'last_name'])
  .execute()

​
addUniqueConstraint

Adds a unique constraint for one or more columns. The constraint name can be anything you want, but it must be unique across the whole database. 
addUniqueConstraint(
  constraintName: string,
  columns: C[],
  build?: UniqueConstraintNodeBuilderCallback
): CreateTableBuilder<TB, C>
​
constraintName
string
required
The name of the constraint
​
columns
C[]
required
The columns to include in the unique constraint
​
build
UniqueConstraintNodeBuilderCallback
Optional callback to configure the constraint
​
returns
CreateTableBuilder<TB, C>
A new builder with the constraint added

​
Examples

await db.schema
  .createTable('person')
  .addColumn('first_name', 'varchar(64)')
  .addColumn('last_name', 'varchar(64)')
  .addUniqueConstraint(
    'first_name_last_name_unique',
    ['first_name', 'last_name']
  )
  .execute()
PostgreSQL with nulls not distinct: 
await db.schema
  .createTable('person')
  .addColumn('first_name', 'varchar(64)')
  .addColumn('last_name', 'varchar(64)')
  .addUniqueConstraint(
    'first_name_last_name_unique',
    ['first_name', 'last_name'],
    (cb) => cb.nullsNotDistinct()
  )
  .execute()

​
addCheckConstraint

Adds a check constraint. The constraint name can be anything you want, but it must be unique across the whole database. 
addCheckConstraint(
  constraintName: string,
  checkExpression: Expression<any>,
  build?: CheckConstraintBuilderCallback
): CreateTableBuilder<TB, C>
​
constraintName
string
required
The name of the constraint
​
checkExpression
Expression<any>
required
The check expression
​
build
CheckConstraintBuilderCallback
Optional callback to configure the constraint
​
returns
CreateTableBuilder<TB, C>
A new builder with the constraint added

​
Examples

import { sql } from 'kysely'

await db.schema
  .createTable('animal')
  .addColumn('number_of_legs', 'integer')
  .addCheckConstraint('check_legs', sql`number_of_legs < 5`)
  .execute()

​
addForeignKeyConstraint

Adds a foreign key constraint. The constraint name can be anything you want, but it must be unique across the whole database. 
addForeignKeyConstraint(
  constraintName: string,
  columns: C[],
  targetTable: string,
  targetColumns: string[],
  build?: ForeignKeyConstraintBuilderCallback
): CreateTableBuilder<TB, C>
​
constraintName
string
required
The name of the constraint
​
columns
C[]
required
The columns in this table
​
targetTable
string
required
The target table name
​
targetColumns
string[]
required
The columns in the target table
​
build
ForeignKeyConstraintBuilderCallback
Optional callback to configure the constraint
​
returns
CreateTableBuilder<TB, C>
A new builder with the constraint added

​
Examples

await db.schema
  .createTable('pet')
  .addColumn('owner_id', 'integer')
  .addForeignKeyConstraint(
    'owner_id_foreign',
    ['owner_id'],
    'person',
    ['id'],
  )
  .execute()
Multiple columns: 
await db.schema
  .createTable('pet')
  .addColumn('owner_id1', 'integer')
  .addColumn('owner_id2', 'integer')
  .addForeignKeyConstraint(
    'owner_id_foreign',
    ['owner_id1', 'owner_id2'],
    'person',
    ['id1', 'id2'],
    (cb) => cb.onDelete('cascade')
  )
  .execute()

​
modifyFront

Adds any additional SQL to the front of the query after the create keyword. 
modifyFront(modifier: Expression<any>): CreateTableBuilder<TB, C>
​
modifier
Expression<any>
required
The SQL expression to add
​
returns
CreateTableBuilder<TB, C>
A new builder with the modifier added

​
Examples

import { sql } from 'kysely'

await db.schema
  .createTable('person')
  .modifyFront(sql`global temporary`)
  .addColumn('id', 'integer', col => col.primaryKey())
  .addColumn('first_name', 'varchar(64)', col => col.notNull())
  .addColumn('last_name', 'varchar(64)', col => col.notNull())
  .execute()
Generated SQL (PostgreSQL): 
create global temporary table "person" (
  "id" integer primary key,
  "first_name" varchar(64) not null,
  "last_name" varchar(64) not null
)

​
modifyEnd

Adds any additional SQL to the end of the query. 
modifyEnd(modifier: Expression<any>): CreateTableBuilder<TB, C>
​
modifier
Expression<any>
required
The SQL expression to add
​
returns
CreateTableBuilder<TB, C>
A new builder with the modifier added

​
Examples

import { sql } from 'kysely'

await db.schema
  .createTable('person')
  .addColumn('id', 'integer', col => col.primaryKey())
  .addColumn('first_name', 'varchar(64)', col => col.notNull())
  .addColumn('last_name', 'varchar(64)', col => col.notNull())
  .modifyEnd(sql`collate utf8_unicode_ci`)
  .execute()
Generated SQL (MySQL): 
create table `person` (
  `id` integer primary key,
  `first_name` varchar(64) not null,
  `last_name` varchar(64) not null
) collate utf8_unicode_ci

​
as

Creates a table from a SELECT query. 
as(expression: Expression<unknown>): CreateTableBuilder<TB, C>
​
expression
Expression<unknown>
required
The SELECT query expression
​
returns
CreateTableBuilder<TB, C>
A new builder with the SELECT query

​
Examples

await db.schema
  .createTable('copy')
  .temporary()
  .as(db.selectFrom('person').select(['first_name', 'last_name']))
  .execute()
Generated SQL (PostgreSQL): 
create temporary table "copy" as
select "first_name", "last_name" from "person"

​
$call

Calls the given function passing this as the only argument. 
$call<T>(func: (qb: this) => T): T
​
func
(qb: this) => T
required
A function that receives the builder and returns a value
​
returns
T
The return value of the provided function

​
Examples

await db.schema
  .createTable('test')
  .$call((builder) => builder.addColumn('id', 'integer'))
  .execute()
Creating reusable functions: 
import { type CreateTableBuilder, sql } from 'kysely'

const addDefaultColumns = (ctb: CreateTableBuilder<any, any>) => {
  return ctb
    .addColumn('id', 'integer', (col) => col.notNull())
    .addColumn('created_at', 'date', (col) =>
      col.notNull().defaultTo(sql`now()`)
    )
    .addColumn('updated_at', 'date', (col) =>
      col.notNull().defaultTo(sql`now()`)
    )
}

await db.schema
  .createTable('test')
  .$call(addDefaultColumns)
  .execute()

​
compile

Compiles the query to a CompiledQuery without executing it. 
compile(): CompiledQuery
​
returns
CompiledQuery
The compiled query object containing SQL and parameters

​
execute

Executes the query. 
async execute(): Promise<void>
​
returns
Promise<void>
A promise that resolves when the table is created

Build docs developers (and LLMs) love

Get started for freeTalk to us