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.
await db.schema
.createTable('person')
.addColumn('id', 'integer', col => col.autoIncrement().primaryKey())
.execute()
The generated SQL (MySQL):
create table `person` (
`id` integer primary key auto_increment
)
Adds a check constraint for the column.
import { sql } from 'kysely'
await db.schema
.createTable('pet')
.addColumn('number_of_legs', 'integer', (col) =>
col.check(sql`number_of_legs < 5`)
)
.execute()
The generated SQL (MySQL):
create table `pet` (
`number_of_legs` integer check (number_of_legs < 5)
)
Adds a default value constraint for the column.
await db.schema
.createTable('pet')
.addColumn('number_of_legs', 'integer', (col) => col.defaultTo(4))
.execute()
The generated SQL (MySQL):
create table `pet` (
`number_of_legs` integer default 4
)
Values passed to defaultTo are interpreted as value literals by default. You can define
an arbitrary SQL expression using the sql template tag:
import { sql } from 'kysely'
await db.schema
.createTable('pet')
.addColumn(
'created_at',
'timestamp',
(col) => col.defaultTo(sql`CURRENT_TIMESTAMP`)
)
.execute()
The generated SQL (MySQL):
create table `pet` (
`created_at` timestamp default CURRENT_TIMESTAMP
)
Makes the column a generated column using a generated always as statement.
import { sql } from 'kysely'
await db.schema
.createTable('person')
.addColumn('full_name', 'varchar(255)',
(col) => col.generatedAlwaysAs(sql`concat(first_name, ' ', last_name)`)
)
.execute()
The generated SQL (MySQL):
create table `person` (
`full_name` varchar(255) generated always as (concat(first_name, ' ', last_name))
)
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.
await db.schema
.createTable('person')
.addColumn('id', 'integer', col => col.generatedAlwaysAsIdentity().primaryKey())
.execute()
The generated SQL (PostgreSQL):
create table "person" (
"id" integer generated always as identity primary key
)
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.
await db.schema
.createTable('person')
.addColumn('id', 'integer', col => col.generatedByDefaultAsIdentity().primaryKey())
.execute()
The generated SQL (PostgreSQL):
create table "person" (
"id" integer generated by default as identity primary key
)
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.
await db.schema
.createTable('person')
.addColumn('id', 'integer', col => col.identity().primaryKey())
.execute()
The generated SQL (MSSQL):
create table "person" (
"id" integer identity primary key
)
Adds if not exists specifier. This only works for PostgreSQL.
await db.schema
.alterTable('person')
.addColumn('email', 'varchar(255)', col => col.unique().ifNotExists())
.execute()
The generated SQL (PostgreSQL):
alter table "person" add column if not exists "email" varchar(255) unique
This can be used to add any additional SQL to the end of the column definition.
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()
The 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'
)
This can be used to add any additional SQL right after the column's data type.
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()
The generated SQL (MySQL):
create table `person` (
`id` integer primary key,
`first_name` varchar(36) collate utf8mb4_general_ci not null
)
Adds a not null constraint for the column.
await db.schema
.createTable('person')
.addColumn('first_name', 'varchar(255)', col => col.notNull())
.execute()
The generated SQL (MySQL):
create table `person` (
`first_name` varchar(255) not null
)
Adds nulls not distinct specifier.
Should be used with unique constraint.
This only works on some dialects like PostgreSQL.
db.schema
.createTable('person')
.addColumn('id', 'integer', col => col.primaryKey())
.addColumn('first_name', 'varchar(30)', col => col.unique().nullsNotDistinct())
.execute()
The generated SQL (PostgreSQL):
create table "person" (
"id" integer primary key,
"first_name" varchar(30) unique nulls not distinct
)
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.
await db.schema
.createTable('pet')
.addColumn(
'owner_id',
'integer',
(col) => col.references('person.id').onDelete('cascade')
)
.execute()
The generated SQL (PostgreSQL):
create table "pet" (
"owner_id" integer references "person" ("id") on delete cascade
)
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.
await db.schema
.createTable('pet')
.addColumn(
'owner_id',
'integer',
(col) => col.references('person.id').onUpdate('cascade')
)
.execute()
The generated SQL (PostgreSQL):
create table "pet" (
"owner_id" integer references "person" ("id") on update cascade
)
Makes the column the primary key.
If you want to specify a composite primary key use the CreateTableBuilder.addPrimaryKeyConstraint method.
await db.schema
.createTable('person')
.addColumn('id', 'integer', col => col.primaryKey())
.execute()
The generated SQL (MySQL):
create table `person` (
`id` integer primary key
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.
await db.schema
.createTable('pet')
.addColumn('owner_id', 'integer', (col) => col.references('person.id'))
.execute()
The generated SQL (PostgreSQL):
create table "pet" (
"owner_id" integer references "person" ("id")
)
Makes a generated column stored instead of virtual. This method can only be used with generatedAlwaysAs
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()
The generated SQL (MySQL):
create table `person` (
`full_name` varchar(255) generated always as (concat(first_name, ' ', last_name)) stored
)
Adds a unique constraint for the column.
await db.schema
.createTable('person')
.addColumn('email', 'varchar(255)', col => col.unique())
.execute()
The generated SQL (MySQL):
create table `person` (
`email` varchar(255) unique
)
Adds a unsigned modifier for the column.
This only works on some dialects like MySQL.
await db.schema
.createTable('person')
.addColumn('age', 'integer', col => col.unsigned())
.execute()
The generated SQL (MySQL):
create table `person` (
`age` integer unsigned
)
Simply calls the provided function passing
thisas the only argument.$callreturns what the provided function returns.