Schema + Data migration in Prisma (adding a new mandatory field)

Here I was adding a new field to the users table. And since I had already my database deployed I could not just say it is a mandatory field.

So I had to do it manually. Thus after I added the new field:

model User {
  // ...
  birthdate  DateTime
  // ...
}

I ran this pnpx prisma migrate dev --name birthdate_of_user --create-only so that I could jump in and change the migration SQL. As you might know when you run this pnpx prisma migrate dev --name birthdate_of_user without --create-only flag it tries to generate the SQL migration file for you and run it right after that against your database.

In our case it is not that easy, in fact it is not even feasible to begin with since PostgreSQL will yell at us about the existing records and what it should do with them.

Therefore here is how I did it:

-- AlterTable: add optional column
ALTER TABLE "users" ADD COLUMN     "birthdate" TIMESTAMP(3);

-- Update currently existing records
UPDATE "users" SET "birthdate"=CURRENT_TIMESTAMP;

-- AlterTable
ALTER TABLE "users" ALTER COLUMN     "birthdate" SET NOT NULL;

Like I said in my previous post, we can employ other tools that comes with ORMs in order to ease the pain of some tedious or hard to manage and perform tasks.

You could even go further and write a much more complicated query, one that could possibly

1. Create an empty migration prisma migrate dev --create-only --name temp_birthdate_of_users.
2. Create a new table with a unique identifier for each user (like email address) and their birth date in the created migration file.
3. Insert data into that table.
4. Create a new migration prisma migrate dev --create-only --name birthdate_of_users.
5. Fetch data from the temporary table and use it for the newly added column in users table here.
6. Make the column mandatory again.
7. Get rid of the temp table or db.

Learn more here

So as you can see the possibilities are limitless kinda. Let us know about your data migrations and how you manage them in the comments.