Migration Generator: Automate Database Schema Changes Safely

Manual ALTER TABLE commands in production are a recipe for downtime. Database migrations track every schema change as versioned files — each with an 'up' (apply) and 'down' (rollback). A generator creates these files from your intended changes, ensuring safe, repeatable deployments.

What Is Migration Generator?

A migration generator creates timestamped migration files based on the schema changes you describe. It generates both the forward migration (up) and the rollback (down), handling column additions, type changes, index creation, foreign key constraints, and data transformations.

How to Use Migration Generator on DevToolHub

1. Open the Migration Generator tool on DevToolHub — no signup required.
2. Describe the schema change: add table, add column, create index, etc.
3. Select the target: Knex, Prisma, TypeORM, Rails, or raw SQL.
4. Review the generated up and down migration code.
5. Copy the file and add it to your project's migrations directory.
6. Run your migration tool to apply the change.

Adding a New Table

Create a comments table with foreign keys:

// Migration: 20240315_create_comments.js
exports.up = function(knex) {
  return 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('CASCADE');
    table.text('body').notNullable();
    table.boolean('is_approved').defaultTo(false);
    table.timestamps(true, true);
    
    table.index(['post_id', 'is_approved']);
  });
};

exports.down = function(knex) {
  return knex.schema.dropTable('comments');
};

The down migration is the reverse — dropTable undoes createTable. The composite index speeds up approved comment queries.

Adding Columns to an Existing Table

Add soft delete and profile fields to users:

exports.up = function(knex) {
  return knex.schema.alterTable('users', (table) => {
    table.timestamp('deleted_at').nullable();
    table.string('avatar_url', 500).nullable();
    table.jsonb('preferences').defaultTo('{}');
    
    table.index('deleted_at');
  });
};

exports.down = function(knex) {
  return knex.schema.alterTable('users', (table) => {
    table.dropIndex('deleted_at');
    table.dropColumn('preferences');
    table.dropColumn('avatar_url');
    table.dropColumn('deleted_at');
  });
};

Columns are dropped in reverse order in the down migration to avoid dependency issues.

Prisma Migration Example

The same change in Prisma format:

-- Migration: add_user_profile_fields
-- Up:
ALTER TABLE "users" ADD COLUMN "deleted_at" TIMESTAMP;
ALTER TABLE "users" ADD COLUMN "avatar_url" VARCHAR(500);
ALTER TABLE "users" ADD COLUMN "preferences" JSONB DEFAULT '{}';
CREATE INDEX "idx_users_deleted_at" ON "users" ("deleted_at");

-- Down:
DROP INDEX "idx_users_deleted_at";
ALTER TABLE "users" DROP COLUMN "preferences";
ALTER TABLE "users" DROP COLUMN "avatar_url";
ALTER TABLE "users" DROP COLUMN "deleted_at";

Raw SQL migrations work with any framework and are database-portable.

Pro Tips

• One change per migration — don't mix table creation with data transformation in the same file.
• Test rollbacks — always run down migrations in staging before deploying to production.
• Use timestamps, not sequence numbers — timestamps prevent merge conflicts when multiple developers create migrations.
• Handle data migrations separately — schema changes and data backfills should be separate migration files.

When You Need This

• Adding new features that require schema changes
• Preparing database updates for CI/CD pipeline deployments
• Refactoring database schemas with safe rollback capability
• Generating migration boilerplate for Knex, Prisma, or TypeORM projects