Altering Tables

Kysely’s AlterTableBuilder allows you to modify existing database tables. Access it through db.schema.alterTable().

Renaming Tables

Rename a table:

await db.schema
  .alterTable('person')
  .renameTo('user')
  .execute()

Moving to Different Schema

On databases that support schemas (PostgreSQL, MSSQL):

await db.schema
  .alterTable('person')
  .setSchema('public')
  .execute()

Column Operations

Adding Columns

Add new columns with constraints:

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

Multiple columns in one statement:

await db.schema
  .alterTable('person')
  .addColumn('phone', 'varchar(20)')
  .addColumn('address', 'text')
  .addColumn('created_at', 'timestamp', (col) =>
    col.defaultTo(sql`CURRENT_TIMESTAMP`)
  )
  .execute()

Dropping Columns

await db.schema
  .alterTable('person')
  .dropColumn('phone')
  .execute()

Drop multiple columns:

await db.schema
  .alterTable('person')
  .dropColumn('phone')
  .dropColumn('fax')
  .execute()

Renaming Columns

await db.schema
  .alterTable('person')
  .renameColumn('first_name', 'given_name')
  .execute()

Modifying Columns

Using alterColumn

Change column properties (PostgreSQL, SQLite):

// Change data type
await db.schema
  .alterTable('person')
  .alterColumn('age', (col) => col.setDataType('varchar(10)'))
  .execute()

// Set NOT NULL
await db.schema
  .alterTable('person')
  .alterColumn('email', (col) => col.setNotNull())
  .execute()

// Drop NOT NULL
await db.schema
  .alterTable('person')
  .alterColumn('email', (col) => col.dropNotNull())
  .execute()

// Set default value
await db.schema
  .alterTable('person')
  .alterColumn('status', (col) => col.setDefault('active'))
  .execute()

// Drop default value
await db.schema
  .alterTable('person')
  .alterColumn('status', (col) => col.dropDefault())
  .execute()

The alterColumn method only allows one alteration per call. To make multiple changes, execute separate statements.

Using modifyColumn

For MySQL and Oracle, use modifyColumn instead:

await db.schema
  .alterTable('person')
  .modifyColumn('age', 'integer', (col) => col.notNull().defaultTo(0))
  .execute()

Constraint Operations

Adding Constraints

Primary Key Constraints

await db.schema
  .alterTable('user_role')
  .addPrimaryKeyConstraint('user_role_pk', ['user_id', 'role_id'])
  .execute()

Unique Constraints

await db.schema
  .alterTable('person')
  .addUniqueConstraint('person_email_unique', ['email'])
  .execute()

On PostgreSQL, use nulls not distinct:

await db.schema
  .alterTable('person')
  .addUniqueConstraint(
    'person_email_unique',
    ['email'],
    (cb) => cb.nullsNotDistinct()
  )
  .execute()

Foreign Key Constraints

await db.schema
  .alterTable('pet')
  .addForeignKeyConstraint(
    'pet_owner_fk',
    ['owner_id'],
    'person',
    ['id'],
    (cb) => cb.onDelete('cascade')
  )
  .execute()

Composite foreign keys:

await db.schema
  .alterTable('order_item')
  .addForeignKeyConstraint(
    'order_item_order_fk',
    ['order_id', 'product_id'],
    'order',
    ['id', 'product_id'],
    (cb) => cb.onDelete('cascade').onUpdate('restrict')
  )
  .execute()

Check Constraints

import { sql } from 'kysely'

await db.schema
  .alterTable('product')
  .addCheckConstraint('price_positive', sql`price > 0`)
  .execute()

Dropping Constraints

Drop any constraint by name:

await db.schema
  .alterTable('person')
  .dropConstraint('person_email_unique')
  .execute()

On some databases, specify the constraint type:

await db.schema
  .alterTable('person')
  .dropConstraint('person_email_unique')
  .ifExists()
  .execute()

Renaming Constraints

await db.schema
  .alterTable('person')
  .renameConstraint('old_constraint_name', 'new_constraint_name')
  .execute()

Index Operations

Adding Indexes

await db.schema
  .alterTable('person')
  .addIndex('person_email_index')
  .column('email')
  .execute()

Unique indexes:

await db.schema
  .alterTable('person')
  .addIndex('person_email_index')
  .column('email')
  .unique()
  .execute()

Multi-column indexes:

await db.schema
  .alterTable('person')
  .addIndex('person_name_index')
  .columns(['first_name', 'last_name'])
  .execute()

Dropping Indexes

await db.schema
  .alterTable('person')
  .dropIndex('person_email_index')
  .execute()

Chaining Multiple Alterations

Some operations can be chained in a single statement:

await db.schema
  .alterTable('person')
  .addColumn('email', 'varchar(255)')
  .addColumn('phone', 'varchar(20)')
  .dropColumn('fax')
  .execute()

Not all databases support multiple alterations in one statement. If you encounter errors, split them into separate alterTable calls.

Database-Specific Considerations

PostgreSQL

Supports alterColumn for modifying column properties
Allows IF NOT EXISTS on add column operations
Supports nulls not distinct on unique constraints

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

MySQL

Use modifyColumn instead of alterColumn
Supports FIRST and AFTER for column positioning (use with modifiers)

SQLite

SQLite has limited ALTER TABLE support:

Can add columns
Can rename tables and columns
Cannot drop columns (in older versions)
Cannot modify column types directly

For unsupported operations, you’ll need to:

Create a new table with the desired schema
Copy data from the old table
Drop the old table
Rename the new table

Reusable Patterns

Create reusable alteration functions:

import { sql, type AlterTableBuilder } from 'kysely'

const addTimestamps = (builder: AlterTableBuilder) => {
  return builder
    .addColumn('created_at', 'timestamp', (col) =>
      col.notNull().defaultTo(sql`CURRENT_TIMESTAMP`)
    )
    .addColumn('updated_at', 'timestamp', (col) =>
      col.notNull().defaultTo(sql`CURRENT_TIMESTAMP`)
    )
}

await db.schema
  .alterTable('person')
  .$call(addTimestamps)
  .execute()

Get Started

Core Concepts

Query Operations

Schema & Migrations

Recipes & Guides

Altering Tables

Renaming Tables

Moving to Different Schema

Column Operations

Adding Columns

Dropping Columns

Renaming Columns

Modifying Columns

Using alterColumn

Using modifyColumn

Constraint Operations

Adding Constraints

Primary Key Constraints

Unique Constraints

Foreign Key Constraints

Check Constraints

Dropping Constraints

Renaming Constraints

Index Operations

Adding Indexes

Dropping Indexes

Chaining Multiple Alterations

Database-Specific Considerations

PostgreSQL

MySQL

SQLite

Reusable Patterns

​Renaming Tables

​Moving to Different Schema

​Column Operations

​Adding Columns

​Dropping Columns

​Renaming Columns

​Modifying Columns

​Using alterColumn

​Using modifyColumn

​Constraint Operations

​Adding Constraints

​Primary Key Constraints

​Unique Constraints

​Foreign Key Constraints

​Check Constraints

​Dropping Constraints

​Renaming Constraints

​Index Operations

​Adding Indexes

​Dropping Indexes

​Chaining Multiple Alterations

​Database-Specific Considerations

​PostgreSQL

​MySQL

​SQLite

​Reusable Patterns

Renaming Tables

Moving to Different Schema

Column Operations

Adding Columns

Dropping Columns

Renaming Columns

Modifying Columns

Using alterColumn

Using modifyColumn

Constraint Operations

Adding Constraints

Primary Key Constraints

Unique Constraints

Foreign Key Constraints

Check Constraints

Dropping Constraints

Renaming Constraints

Index Operations

Adding Indexes

Dropping Indexes

Chaining Multiple Alterations

Database-Specific Considerations

PostgreSQL

MySQL

SQLite

Reusable Patterns