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
A new builder with auto increment enabled
Examples
await db.schema
.createTable('person')
.addColumn('id', 'integer', col => col.autoIncrement().primaryKey())
.execute()
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
A new builder with identity enabled
Examples
await db.schema
.createTable('person')
.addColumn('id', 'integer', col => col.identity().primaryKey())
.execute()
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
A new builder with primary key constraint
Examples
await db.schema
.createTable('person')
.addColumn('id', 'integer', col => col.primaryKey())
.execute()
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
Reference in format table.column or schema.table.column
A new builder with the foreign key reference
Examples
await db.schema
.createTable('pet')
.addColumn('owner_id', 'integer', (col) => col.references('person.id'))
.execute()
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)
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()
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)
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()
create table "pet" (
"owner_id" integer references "person" ("id") on update cascade
)
unique
Adds a unique constraint for the column.
unique(): ColumnDefinitionBuilder
A new builder with unique constraint
Examples
await db.schema
.createTable('person')
.addColumn('email', 'varchar(255)', col => col.unique())
.execute()
create table `person` (
`email` varchar(255) unique
)
notNull
Adds a NOT NULL constraint for the column.
notNull(): ColumnDefinitionBuilder
A new builder with not null constraint
Examples
await db.schema
.createTable('person')
.addColumn('first_name', 'varchar(255)', col => col.notNull())
.execute()
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
A new builder with unsigned modifier
Examples
await db.schema
.createTable('person')
.addColumn('age', 'integer', col => col.unsigned())
.execute()
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)
A new builder with default value
Examples
await db.schema
.createTable('pet')
.addColumn('number_of_legs', 'integer', (col) => col.defaultTo(4))
.execute()
create table `pet` (
`number_of_legs` integer default 4
)
import { sql } from 'kysely'
await db.schema
.createTable('pet')
.addColumn(
'created_at',
'timestamp',
(col) => col.defaultTo(sql`CURRENT_TIMESTAMP`)
)
.execute()
create table `pet` (
`created_at` timestamp default CURRENT_TIMESTAMP
)
check
Adds a check constraint for the column.
check(expression: Expression<any>): 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()
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
The generation expression
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()
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
A new builder with generated always as identity
Examples
await db.schema
.createTable('person')
.addColumn('id', 'integer', col => col.generatedAlwaysAsIdentity().primaryKey())
.execute()
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
A new builder with generated by default as identity
Examples
await db.schema
.createTable('person')
.addColumn('id', 'integer', col => col.generatedByDefaultAsIdentity().primaryKey())
.execute()
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
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()
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
The SQL expression to add
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()
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
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()
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
A new builder with if not exists
Examples
await db.schema
.alterTable('person')
.addColumn('email', 'varchar(255)', col => col.unique().ifNotExists())
.execute()
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
The SQL expression to add
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()
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
A function that receives the builder and returns a value
The return value of the provided function