Database migrations with prisma and knex.js

Managing database schema changes in a controlled and reliable way is essential for the health of any modern application. In the Node.js ecosystem, tools like Prisma and Knex.js offer different yet effective ways to handle migrations. This blog explores how each tool works, and how to choose the best one for your project.

What are migrations?

Migrations are scripts or files that define changes to your database schema—adding or removing tables, columns, indexes, etc. They allow you to evolve your database structure over time without losing data or having to manually reapply changes in different environments.

Prisma migrations

Prisma is an ORM that uses a declarative schema to define models, and automatically generates SQL migrations based on changes to that schema.

Setting up

First, install Prisma and initialize it:

npm install @prisma/cli --save-dev
npx prisma init

This creates a prisma/schema.prisma file and a .env for your database connection.

Creating a migration

Once you've defined or changed models in your schema, run:

npx prisma migrate dev --name add_users_table

This generates a new migration and applies it to your database.

Example model

model User {
  id    Int     @id @default(autoincrement())
  name  String
  email String  @unique
}

This model will generate a migration that creates a User table with the specified columns and constraints.

Applying migrations

To apply all pending migrations to your database (e.g., in production):

npx prisma migrate deploy

Knex.js migrations

Knex.js is a SQL query builder that also includes a robust migration system. Unlike Prisma, Knex is imperative and gives you full control over SQL queries.

Setting up

Install Knex and your database driver:

npm install knex pg
npx knex init

This creates a knexfile.js with environment-specific configs.

Creating a migration

npx knex migrate:make create_users_table

This generates a timestamped file in migrations/.

Example migration

exports.up = function (knex) {
  return knex.schema.createTable('users', function (table) {
    table.increments('id');
    table.string('name');
    table.string('email').unique();
  });
};

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

The up method defines the schema change, and the down method defines how to revert it.

Running migrations

npx knex migrate:latest

This runs all pending migrations in order.

Prisma vs knex.js

Best practices for migrations

1. Always use version control to track migration files.
2. Test migrations in staging before applying to production.
3. Avoid destructive changes (e.g., dropping tables) without backups.
4. Use consistent naming (e.g., add_users_table, add_email_to_users).
5. Keep data seeding and schema migrations separate.

Conclusion

Both Prisma and Knex.js offer great migration tools—it just depends on your style and project needs. Prisma is ideal for fast development and strong typing, while Knex is better if you need full control over SQL or complex migration logic. Whichever you choose, following best practices ensures your database evolves safely alongside your application.