ColumnDefinitionBuilder

Builder for defining column properties in Kysely table schemas

The ColumnDefinitionBuilder class is used to define column properties when creating or altering tables. It provides a fluent API for specifying constraints, defaults, and other column attributes.

Methods

autoIncrement

Adds auto_increment or autoincrement to the column definition depending on the dialect.

Some dialects like PostgreSQL don’t support this. On PostgreSQL you can use the serial or bigserial data type instead.

autoIncrement(): ColumnDefinitionBuilder

returns · ColumnDefinitionBuilder

A new builder with auto increment enabled

Examples

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

Generated SQL (MySQL):

create table `person` (
  `id` integer primary key auto_increment
)

identity

Makes the column an identity column.

This only works on some dialects like MS SQL Server (MSSQL).

For PostgreSQL’s generated always as identity use generatedAlwaysAsIdentity.

identity(): ColumnDefinitionBuilder

returns · ColumnDefinitionBuilder

A new builder with identity enabled

Examples

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

Generated SQL (MSSQL):

create table "person" (
  "id" integer identity primary key
)

primaryKey

Makes the column the primary key.

If you want to specify a composite primary key use the CreateTableBuilder.addPrimaryKeyConstraint method.

primaryKey(): ColumnDefinitionBuilder

returns · ColumnDefinitionBuilder

A new builder with primary key constraint

Examples

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

Generated SQL (MySQL):

create table `person` (
  `id` integer primary key
)

references

Adds a foreign key constraint for the column.

If your database engine doesn’t support foreign key constraints in the column definition (like MySQL 5) you need to call the table level CreateTableBuilder.addForeignKeyConstraint method instead.

references(ref: string): ColumnDefinitionBuilder

ref · string · required

Reference in format table.column or schema.table.column

returns · ColumnDefinitionBuilder

A new builder with the foreign key reference

Examples

await db.schema
  .createTable('pet')
  .addColumn('owner_id', 'integer', (col) => col.references('person.id'))
  .execute()

Generated SQL (PostgreSQL):

create table "pet" (
  "owner_id" integer references "person" ("id")
)

onDelete

Adds an ON DELETE constraint for the foreign key column.

If your database engine doesn’t support foreign key constraints in the column definition (like MySQL 5) you need to call the table level CreateTableBuilder.addForeignKeyConstraint method instead.

onDelete(onDelete: OnModifyForeignAction): ColumnDefinitionBuilder

onDelete · OnModifyForeignAction · required

The action to take on delete (e.g., cascade, set null, restrict, no action)

returns · ColumnDefinitionBuilder

A new builder with the on delete action

Examples

await db.schema
  .createTable('pet')
  .addColumn(
    'owner_id',
    'integer',
    (col) => col.references('person.id').onDelete('cascade')
  )
  .execute()

Generated SQL (PostgreSQL):

create table "pet" (
  "owner_id" integer references "person" ("id") on delete cascade
)

onUpdate

Adds an ON UPDATE constraint for the foreign key column.

If your database engine doesn’t support foreign key constraints in the column definition (like MySQL 5) you need to call the table level CreateTableBuilder.addForeignKeyConstraint method instead.

onUpdate(onUpdate: OnModifyForeignAction): ColumnDefinitionBuilder

onUpdate · OnModifyForeignAction · required

The action to take on update (e.g., cascade, set null, restrict, no action)

returns · ColumnDefinitionBuilder

A new builder with the on update action

Examples

await db.schema
  .createTable('pet')
  .addColumn(
    'owner_id',
    'integer',
    (col) => col.references('person.id').onUpdate('cascade')
  )
  .execute()

Generated SQL (PostgreSQL):

create table "pet" (
  "owner_id" integer references "person" ("id") on update cascade
)

unique

Adds a unique constraint for the column.

unique(): ColumnDefinitionBuilder

returns · ColumnDefinitionBuilder

A new builder with unique constraint

Examples

await db.schema
  .createTable('person')
  .addColumn('email', 'varchar(255)', col => col.unique())
  .execute()

Generated SQL (MySQL):

create table `person` (
  `email` varchar(255) unique
)

notNull

Adds a NOT NULL constraint for the column.

notNull(): ColumnDefinitionBuilder

returns · ColumnDefinitionBuilder

A new builder with not null constraint

Examples

await db.schema
  .createTable('person')
  .addColumn('first_name', 'varchar(255)', col => col.notNull())
  .execute()

Generated SQL (MySQL):

create table `person` (
  `first_name` varchar(255) not null
)

unsigned

Adds an UNSIGNED modifier for the column.

This only works on some dialects like MySQL.

unsigned(): ColumnDefinitionBuilder

returns · ColumnDefinitionBuilder

A new builder with unsigned modifier

Examples

await db.schema
  .createTable('person')
  .addColumn('age', 'integer', col => col.unsigned())
  .execute()

Generated SQL (MySQL):

create table `person` (
  `age` integer unsigned
)

defaultTo

Adds a default value constraint for the column.

defaultTo(value: DefaultValueExpression): ColumnDefinitionBuilder

value · DefaultValueExpression · required

The default value (can be a primitive value or a SQL expression)

returns · ColumnDefinitionBuilder

A new builder with default value

Examples

await db.schema
  .createTable('pet')
  .addColumn('number_of_legs', 'integer', (col) => col.defaultTo(4))
  .execute()

Generated SQL (MySQL):

create table `pet` (
  `number_of_legs` integer default 4
)

Using SQL expressions:

import { sql } from 'kysely'

await db.schema
  .createTable('pet')
  .addColumn(
    'created_at',
    'timestamp',
    (col) => col.defaultTo(sql`CURRENT_TIMESTAMP`)
  )
  .execute()

Generated SQL (MySQL):

create table `pet` (
  `created_at` timestamp default CURRENT_TIMESTAMP
)

check

Adds a check constraint for the column.

check(expression: Expression<any>): ColumnDefinitionBuilder

expression · Expression<any> · required

The check expression

returns · ColumnDefinitionBuilder

A new builder with check constraint

Examples

import { sql } from 'kysely'

await db.schema
  .createTable('pet')
  .addColumn('number_of_legs', 'integer', (col) =>
    col.check(sql`number_of_legs < 5`)
  )
  .execute()

Generated SQL (MySQL):

create table `pet` (
  `number_of_legs` integer check (number_of_legs < 5)
)

generatedAlwaysAs

Makes the column a generated column using a GENERATED ALWAYS AS statement.

generatedAlwaysAs(expression: Expression<any>): ColumnDefinitionBuilder

expression · Expression<any> · required

The generation expression

returns · ColumnDefinitionBuilder

A new builder with generated column definition

Examples

import { sql } from 'kysely'

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

Generated SQL (MySQL):

create table `person` (
  `full_name` varchar(255) generated always as (concat(first_name, ' ', last_name))
)

generatedAlwaysAsIdentity

Adds the GENERATED ALWAYS AS IDENTITY specifier.

This only works on some dialects like PostgreSQL.

For MS SQL Server (MSSQL)‘s identity column use identity.

generatedAlwaysAsIdentity(): ColumnDefinitionBuilder

returns · ColumnDefinitionBuilder

A new builder with generated always as identity

Examples

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

Generated SQL (PostgreSQL):

create table "person" (
  "id" integer generated always as identity primary key
)

generatedByDefaultAsIdentity

Adds the GENERATED BY DEFAULT AS IDENTITY specifier on supported dialects.

This only works on some dialects like PostgreSQL.

For MS SQL Server (MSSQL)‘s identity column use identity.

generatedByDefaultAsIdentity(): ColumnDefinitionBuilder

returns · ColumnDefinitionBuilder

A new builder with generated by default as identity

Examples

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

Generated SQL (PostgreSQL):

create table "person" (
  "id" integer generated by default as identity primary key
)

stored

Makes a generated column stored instead of virtual. This method can only be used with generatedAlwaysAs.

stored(): ColumnDefinitionBuilder

returns · ColumnDefinitionBuilder

A new builder with stored modifier

Examples

import { sql } from 'kysely'

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

Generated SQL (MySQL):

create table `person` (
  `full_name` varchar(255) generated always as (concat(first_name, ' ', last_name)) stored
)

modifyFront

Adds any additional SQL right after the column’s data type.

modifyFront(modifier: Expression<any>): ColumnDefinitionBuilder

modifier · Expression<any> · required

The SQL expression to add

returns · ColumnDefinitionBuilder

A new builder with the front modifier

Examples

import { sql } from 'kysely'

await db.schema
  .createTable('person')
  .addColumn('id', 'integer', col => col.primaryKey())
  .addColumn(
    'first_name',
    'varchar(36)',
    (col) => col.modifyFront(sql`collate utf8mb4_general_ci`).notNull()
  )
  .execute()

Generated SQL (MySQL):

create table `person` (
  `id` integer primary key,
  `first_name` varchar(36) collate utf8mb4_general_ci not null
)

nullsNotDistinct

Adds NULLS NOT DISTINCT specifier. Should be used with unique constraint.

This only works on some dialects like PostgreSQL.

nullsNotDistinct(): ColumnDefinitionBuilder

returns · ColumnDefinitionBuilder

A new builder with nulls not distinct

Examples

db.schema
  .createTable('person')
  .addColumn('id', 'integer', col => col.primaryKey())
  .addColumn('first_name', 'varchar(30)', col => col.unique().nullsNotDistinct())
  .execute()

Generated SQL (PostgreSQL):

create table "person" (
  "id" integer primary key,
  "first_name" varchar(30) unique nulls not distinct
)

ifNotExists

Adds IF NOT EXISTS specifier. This only works for PostgreSQL.

ifNotExists(): ColumnDefinitionBuilder

returns · ColumnDefinitionBuilder

A new builder with if not exists

Examples

await db.schema
  .alterTable('person')
  .addColumn('email', 'varchar(255)', col => col.unique().ifNotExists())
  .execute()

Generated SQL (PostgreSQL):

alter table "person" add column if not exists "email" varchar(255) unique

modifyEnd

Adds any additional SQL to the end of the column definition.

modifyEnd(modifier: Expression<any>): ColumnDefinitionBuilder

modifier · Expression<any> · required

The SQL expression to add

returns · ColumnDefinitionBuilder

A new builder with the end modifier

Examples

import { sql } from 'kysely'

await db.schema
  .createTable('person')
  .addColumn('id', 'integer', col => col.primaryKey())
  .addColumn(
    'age',
    'integer',
    col => col.unsigned()
      .notNull()
      .modifyEnd(sql`comment ${sql.lit('it is not polite to ask a woman her age')}`)
  )
  .execute()

Generated SQL (MySQL):

create table `person` (
  `id` integer primary key,
  `age` integer unsigned not null comment 'it is not polite to ask a woman her age'
)

$call

Calls the provided 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