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.

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

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.

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

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.

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.

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

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

Verify the schema

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

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

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.

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"