Guided Tutorials

Write Database Migrations

Generate safe, reversible database migration scripts with proper up and down functions. Autohand reads your existing schema and migration files to produce migrations that fit your conventions and tool chain.

Intermediate 15 min
autohand "Create a database migration that adds a 'comments' table with id, post_id (foreign key to posts), author_id (foreign key to users), body text, and timestamps. Include the rollback migration."

What you'll learn

  • How to describe a schema change and generate a migration that fits your tool chain
  • How to review up and down functions for correctness and data safety
  • How to run and roll back migrations locally before merging
  • How to handle data migrations that transform existing rows

Before you start

  • Autohand Code installed. Run autohand --version to confirm. See Your First Autohand Session if you need to install it.
  • A migration tool already configured. Autohand generates output for Knex, Prisma, Drizzle, Sequelize, and raw SQL. If no tool is detected, it asks which one to use.
  • An existing schema or migrations directory. Autohand reads your existing migrations to understand naming conventions and transaction patterns.
  • The referenced tables already exist. The posts and users tables must exist in migrations that run before this one.

Describe the schema change

Open your project and start an Autohand session from the root directory where your migration tool is configured.

bash

cd path/to/your-project
autohand

A precise description produces a better migration. Include column types, constraints, and any index requirements in your initial prompt.

bash

Create a Knex migration for PostgreSQL that adds a comments table.
Columns:
  - id: uuid primary key, default gen_random_uuid()
  - post_id: uuid, not null, foreign key to posts.id, on delete cascade
  - author_id: uuid, not null, foreign key to users.id, on delete set null
  - body: text, not null
  - created_at: timestamptz, not null, default now()
  - updated_at: timestamptz, not null, default now()
Add an index on post_id since we always query comments by post.
Include the full rollback in the down function.

Autohand scans your existing migrations to determine the file naming convention (timestamp prefix, sequential prefix, or descriptive name) before creating the file.

Generate the migration

For a Knex project, Autohand produces a file like migrations/20260312143000_create_comments_table.js. Here is the output for the prompt above.

javascript

/**
 * @param { import("knex").Knex } knex
 * @returns { Promise<void> }
 */
exports.up = async function(knex) {
  await knex.schema.createTable('comments', (table) => {
    table.uuid('id').primary().defaultTo(knex.raw('gen_random_uuid()'));
    table.uuid('post_id').notNullable()
      .references('id').inTable('posts').onDelete('CASCADE');
    table.uuid('author_id').notNullable()
      .references('id').inTable('users').onDelete('SET NULL');
    table.text('body').notNullable();
    table.timestamp('created_at', { useTz: true }).notNullable().defaultTo(knex.fn.now());
    table.timestamp('updated_at', { useTz: true }).notNullable().defaultTo(knex.fn.now());

    table.index(['post_id'], 'idx_comments_post_id');
  });
};

/**
 * @param { import("knex").Knex } knex
 * @returns { Promise<void> }
 */
exports.down = async function(knex) {
  await knex.schema.dropTable('comments');
};

For a Prisma project, Autohand updates schema.prisma and generates the SQL migration file in prisma/migrations/.

typescript

model Comment {
  id        String   @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  postId    String   @db.Uuid
  authorId  String   @db.Uuid
  body      String
  createdAt DateTime @default(now()) @db.Timestamptz
  updatedAt DateTime @updatedAt @db.Timestamptz

  post   Post @relation(fields: [postId], references: [id], onDelete: Cascade)
  author User @relation(fields: [authorId], references: [id], onDelete: SetNull)

  @@index([postId])
}

Review up and down functions

A migration is only as safe as its rollback. Before running it, verify that the down function is the exact inverse of the up function.

Check these specific things.

  • dropTable matches createTable. If up creates a table, down must drop it. If up adds a column, down must drop that specific column.
  • Foreign key order in down. Drop child tables before parent tables, or drop the foreign key constraint before dropping the column. Autohand handles this, but verify for complex multi-table migrations.
  • Data loss in down. Rolling back a migration that adds a table is safe. Rolling back one that removes a column or transforms data is not. Ask Autohand to add a comment noting if data loss occurs on rollback.

bash

Review the down function in the migration you just generated.
Will rolling it back cause any data loss? Add a comment if it will.

Tip: For destructive changes like dropping a column, always add a preceding migration that copies the data somewhere safe before the drop. Ask Autohand to generate a two-step migration when you are removing columns with production data.

Run the migration

Run against your local development database first.

bash

# Knex
npx knex migrate:latest

# Prisma
npx prisma migrate dev --name create_comments_table

# Drizzle
npx drizzle-kit migrate

If the migration fails, paste the error into your Autohand session and ask it to fix the specific issue.

bash

Running the migration gave this error:
error: there is no unique constraint matching given keys for referenced table "users"

Fix the foreign key definition in the migration.

Test the rollback immediately after a successful up migration, before merging.

bash

# Knex
npx knex migrate:rollback

# Prisma (roll back one step)
npx prisma migrate reset --skip-seed

Verify the schema

After running the migration, confirm the table was created with the correct structure.

sql

-- PostgreSQL: inspect the new table
d comments

-- MySQL
DESCRIBE comments;

-- SQLite
PRAGMA table_info(comments);

You can also ask Autohand to generate a quick verification query.

bash

Write a SQL query that verifies the comments table was created correctly.
It should check that all columns exist with the right types and that
the foreign key constraints are in place.

Autohand produces a query using your database's information schema tables that you can run directly in a database client.

Handle data migrations

Some schema changes require moving or transforming existing data. Autohand handles these as two-phase migrations: a schema change followed by a data backfill.

For example, splitting a full_name column into first_name and last_name.

bash

Generate a migration that:
1. Adds first_name and last_name columns to users (both nullable)
2. Backfills them by splitting the existing full_name column on the first space
3. Makes both columns not null after the backfill
4. Drops the full_name column
Do this as a single migration with all four steps in the up function.
The down function should add full_name back and populate it, then drop first_name and last_name.

javascript

exports.up = async function(knex) {
  // Phase 1: add nullable columns
  await knex.schema.alterTable('users', (table) => {
    table.string('first_name').nullable();
    table.string('last_name').nullable();
  });

  // Phase 2: backfill from full_name
  await knex.raw(`
    UPDATE users
    SET
      first_name = split_part(full_name, ' ', 1),
      last_name  = nullif(trim(substring(full_name from position(' ' in full_name))), '')
    WHERE full_name IS NOT NULL
  `);

  // Phase 3: apply not null constraint
  await knex.schema.alterTable('users', (table) => {
    table.string('first_name').notNullable().alter();
    table.string('last_name').notNullable().alter();
  });

  // Phase 4: drop old column
  await knex.schema.alterTable('users', (table) => {
    table.dropColumn('full_name');
  });
};

exports.down = async function(knex) {
  await knex.schema.alterTable('users', (table) => {
    table.string('full_name').nullable();
  });

  await knex.raw(`
    UPDATE users
    SET full_name = concat(first_name, ' ', last_name)
  `);

  await knex.schema.alterTable('users', (table) => {
    table.dropColumn('first_name');
    table.dropColumn('last_name');
  });
};

Tip: For tables with millions of rows, a single UPDATE in a migration will lock the table. Ask Autohand to generate a batched update that processes rows in chunks of 1000 to avoid long locks on production databases.

What you learned

  • Generated a database migration with proper column types, foreign keys, and indexes
  • Reviewed up and down functions to verify rollback safety and data preservation
  • Ran and rolled back a migration locally to catch issues before merging
  • Generated a multi-phase data migration for column splitting with backfill

Try next

autohand "Generate a migration that adds a full-text search index on the comments body column and a composite index on (post_id, created_at) for pagination queries"

Related tutorials

Generate CI/CD Pipeline Configuration

Create a pipeline that runs migrations automatically as part of your deploy process.

Scaffold a REST API from Scratch

Generate a complete API with routes, controllers, and database models in one session.