CreateTableBuilder

Builder for creating table definitions in Kysely

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