How to rename a table or column using Prisma migrations

Prisma’s migration system is great because it’s able to automatically generate SQL migrations based on changes to your schema.prisma file. However, it’s not always possible to understand your intentions when you make a change. In these cases, you have to manually edit the generated SQL migration. Most commonly, this happens when you want to rename a table or column in your database.

In these cases, you need to manually edit the generated SQL migration to fit your intentions.

How to rename a table with Prisma migrations

1. Edit the name of your model in your schema.prisma file.

2. Run prisma migrate dev --create-only. This will generate the SQL migration file, but will not immediately run it.

3. You’ll be prompted with a warning that you’re about to drop a table. This is ok because we’ll manually fix the migration. Type y to proceed.

4. You’ll be prompted to enter a name for your migration.

5. Open the newly generated migration.sql file in the prisma/migrations directory. Notice that it drops the original table and creates a whole new table. We don’t want this, since it will delete all existing data in that table. The generated SQL should look something like this:

   -- DropTable
   DROP TABLE "OldName";
   
   -- CreateTable
   CREATE TABLE "NewName" ();

6. Edit the SQL query to simply rename the table, like this:

   ALTER TABLE "OldName" RENAME TO "NewName";

7. Run prisma migrate dev to run your modified migration.

How to rename a column with Prisma migrations

1. Edit the name of your column in your schema.prisma file.

2. Run prisma migrate dev --create-only. This will generate the SQL migration file, but will not immediately run it.

3. You’ll be prompted with a warning that you’re about to drop a column. This is ok because we’ll manually fix the migration. Type y to proceed.

4. You’ll be prompted to enter a name for your migration.

5. Open the newly generated migration.sql file in the prisma/migrations directory. Notice that it drops the original column and creates a whole new column. We don’t want this, since it will delete all existing data in that column. The generated SQL should look something like this:

   -- AlterTable
   ALTER TABLE "MyTable" DROP COLUMN "oldName",
   ADD COLUMN "newName" INTEGER;

6. Edit the SQL query to simply rename the table, like this:

   ALTER TABLE "MyTable" RENAME COLUMN "oldName" TO "newName";

7. Run prisma migrate dev to run your modified migration.

How to migrate your database with zero downtime

With simple migrations like this, there may be a short period of downtime (usually seconds) between migrating your database and updating your server. As a more advanced technique, you can use the “expand and contract” pattern to migrate your database with zero downtime.

Check out the Prisma docs for more details on how this method works: Link to Prisma docs

Verifying that your migration ran successfully

Once you’ve made these changes, you should check the schema of your database to make sure that your migration ran correctly. Specifically, you should check that:

1. Your changes were successfully applied to your database
2. All existing data remained intact following the migration

You can do this with a simple SQL client like Arctype or TablePlus. You can also do this with a tool like Basedash, which also lets you build internal tools on top of your database in seconds.