Use the added function to backfill a new column with values from existing rows

[potent tide]

really appreciate your help in this to understand how we backfill the data in table using util function I have created, I have been stuck for more than 1 days to understand the better solution during migrations

[digital notch]

Hi First you should npx prisma migrate dev, and then try to run a script like:

const { PrismaClient } = require('@prisma/client');
const prisma = new PrismaClient();

async function backfillData() {
  // Fetch existing rows
  const data = await prisma.yourModel.findMany();

  // Update each row
  await Promise.all(data.map(async (row) => {
    const newValue = yourUtilFunction(row); // Process data
    await prisma.yourModel.update({
      where: { id: row.id },
      data: { newColumn: newValue },
    });
  }));
}

backfillData();

[potent tide]

so write a function to do the operation and remove it once its done !

we won't be able to do it with migrations right ?

[potent tide]

Thanks for the help 🙂 really appreciate it

[digital notch]

Not really, maybe with the seed file? 🤔

[potent tide]

sounds good, I am actually writing a script file and running it 🙂

[digital notch]

This is also something great

[potent tide]

I agree, anyways I only have to run once its either through script or prisma seed

I am thinking if we could execute prisma seed file in all the env just once and like a migration

[rigid oxide]

Hi, I came here to ask more or less this exact question - we have data transformations we need to apply post-migration in all envs including prod, an example would be adding a new field and then updating the field based on the computed values from some other fields.

I did start with writing a script exactly as @digital notch suggested, but again my problem now is where to call the script and the broader implications of the backfill operation itself (handling failures, making prisma aware of the operation, etc).

Currently I am manually calling it at build time in the same CI step as the migration, e.g. npx prisma migrate deploy && npx ts-node prisma/migrations/<my migration>/data-migration.ts but this is sort of inconvenient and I'd rather find a more elegant way if possible.. I considered using the seed feature for this purpose but it looks like seeds don't get run in production. So other than that I think there's no other way than manually specifying a data migration command. There's also no way to flag the data migration as having been completed, so the migration has to be idempotent to avoid potential data corruption when re-running it if it fails or someone forgets to remove the trigger command or multiple pipelines get triggered simultaneously. I think ideally we'd be able to apply table locks (which I assume already happens during a migration) for the data migration step.

All that to say, I don't think there's another option besides manually executing the script yourself, but if there's a feature request for this I would love to know about it so I can get updated if this "data migration" feature is ever added

Even if we just had pre and post-migration hooks we could integrate with that would be quite helpful

Oh, looks like this might be the most relevant feature request to follow https://github.com/prisma/prisma/issues/11810

[potent tide]

right

thats why I have written the script and going to execute in all the env, we can't wait for that feature to be published.

[digital notch]

Personally, i would create a scripts folder at the root of my project (if not already existing), and put the ts file for migrations there.

Then, i would add a migrate command which runs the Prisma migrations in a first place, and then the script.

In the script, i add a simple console log to know when the migration is completed. Not too much important, but nice to have in a dev environment

Maybe not the most integrated possibility with Prisma, but still, this is a way to make it work