--- title: "Write Database Migrations" source: https://docs.autohand.ai/tutorials/write-database-migrations --- # 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](/docs/tutorials/your-first-session.html) 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" ### Related tutorials [ #### Generate CI/CD Pipeline Configuration Create a pipeline that runs migrations automatically as part of your deploy process. ](/docs/tutorials/generate-ci-cd-pipeline.html)[ #### Scaffold a REST API from Scratch Generate a complete API with routes, controllers, and database models in one session. ](/docs/tutorials/scaffold-rest-api.html)