Class CreateTableBuilder<TB, C>

Calls the given function passing this as the only argument.

Examples

await db.schema
  .createTable('test')
  .$call((builder) => builder.addColumn('id', 'integer'))
  .execute()
Copy

This is useful for creating reusable functions that can be called with a builder.

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()
Copy

Adds a check constraint.

The constraint name can be anything you want, but it must be unique across the whole database.

Examples

import { sql } from 'kysely'

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

Adds a column to the table.

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')
  )
Copy

With this method, it's once again good to remember that Kysely just builds the query and doesn't provide the same API for all databases. For example, some databases like older MySQL don't support the references statement in the column definition. Instead foreign key constraints need to be defined in the create table query. See the next example:

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()
Copy

Another good example is that PostgreSQL doesn't support the auto_increment keyword and you need to define an autoincrementing column for example using serial:

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

Adds a foreign key constraint.

The constraint name can be anything you want, but it must be unique across the whole database.

Examples

await db.schema
  .createTable('pet')
  .addColumn('owner_id', 'integer')
  .addForeignKeyConstraint(
    'owner_id_foreign',
    ['owner_id'],
    'person',
    ['id'],
  )
  .execute()
Copy

Add constraint for 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()
Copy

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.

Examples

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

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.

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()
Copy

In dialects such as PostgreSQL you can specify nulls not distinct as follows:

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()
Copy

Allows to create table from select query.

Examples

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

The generated SQL (PostgreSQL):

create temporary table "copy" as
select "first_name", "last_name" from "person"
Copy

Adds the "if not exists" modifier.

If the table already exists, no error is thrown if this method has been called.

This can be used to add any additional SQL to the end of the query.

Also see onCommit.

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()
Copy

The 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
Copy

This can be used to add any additional SQL to the front of the query after the create keyword.

Also see temporary.

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()
Copy

The generated SQL (Postgres):

create global temporary table "person" (
  "id" integer primary key,
  "first_name" varchar(64) not null,
  "last_name" varchar(64) not null
)
Copy

Adds an "on commit" statement.

This can be used in conjunction with temporary tables on supported databases like PostgreSQL.

Adds the "temporary" modifier.

Use this to create a temporary table.