Prisma•6mo ago

Does Prisma support executing PostgreSQL anonymous code blocks?

We created incident in prouction recently when we just blindly executed migration on huge table and it locked the whole table. We got to conclusion that we need to do some stuff in small batches manually. Maybe even by writing custom script. I looked at this thread on git, and I see there are no plans to support it in near future - https://github.com/prisma/prisma/issues/15384 Luckily, some of our migrations can be done using pure SQL, so writing custom script looks unnecessary. My question is: will Prisma execute custom migration file with content like this?

DO $$
DECLARE
    batch_size INT := 1000;
    offset INT := 0;
BEGIN
    LOOP
        UPDATE products SET status = 'archived'
        WHERE id IN (SELECT id FROM products WHERE created_at < '2024-01-01' LIMIT batch_size OFFSET offset); 
        EXIT WHEN NOT FOUND;
        offset := offset + batch_size;
    END LOOP;
END $$;

DO $$
DECLARE
    batch_size INT := 1000;
    offset INT := 0;
BEGIN
    LOOP
        UPDATE products SET status = 'archived'
        WHERE id IN (SELECT id FROM products WHERE created_at < '2024-01-01' LIMIT batch_size OFFSET offset); 
        EXIT WHEN NOT FOUND;
        offset := offset + batch_size;
    END LOOP;
END $$;

Thanks.

GitHub

Migration hooks for modifying data · Issue #15384 · prisma/prisma

Problem Prisma migrate only provides a way to modify the db schema, which is insufficient for building an application. Expecting users to write raw sql to modify data and/or handle data migrations ...

3 Replies

Prisma AI Help•6mo ago

You selected the bot's instantaneous insights. Let me compile your answer right away! Prisma Migrate does allow you to customize migration SQL files, and you can include raw SQL statements in them. According to the official guidance, you can generate a migration with the --create-only flag, edit the generated SQL file, and add your custom SQL—including data transformation queries—before applying the migration. This is the recommended way to handle custom SQL logic that Prisma's schema cannot express directly, such as batching updates or using advanced PostgreSQL features like anonymous code blocks (DO $$ ... $$) Customizing Migration Files. There is also a user report confirming that you can add custom SQL to migration files, as long as you do not modify a migration after it has already been applied. The workflow is: generate a migration, edit the SQL file to include your custom code block, and then apply it. This approach is supported and works for cases like yours, where you want to avoid locking large tables by batching updates in a DO $$ ... $$ block Doing database upgrades with SQL. So, yes—Prisma will execute a migration file containing a PostgreSQL anonymous code block like the one you posted, as long as it is valid SQL for your database. Just ensure you add it before applying the migration. If you encounter issues with transaction semantics or multi-statement migrations, be aware that Prisma wraps multi-statement migrations in a transaction by default, which can affect how certain SQL commands behave. For most use cases, though, your approach should work as expected Confusing transaction semantics in Postgres migrations. 🤔 Alright, thanks for letting us know that there's still some help needed. A team member will reply to you shortly. In the meantime, please let us know any additional info, questions, or which parts of the answer may need clarification.

Nurul•6mo ago

Hey! The answer from AI Bot is what I would have recommended as well. You can create an empty migration file with npx prisma migrate dev --create-only command and then run your custom SQL in this migration file.

Alex R.OP•6mo ago

So actually I need "trial and error" to understand what prisma will do with

DO $$
DECLARE
    batch_size INT := 1000;
    offset INT := 0;
BEGIN
    LOOP
        UPDATE products SET status = 'archived'
        WHERE id IN (SELECT id FROM products WHERE created_at < '2024-01-01' LIMIT batch_size OFFSET offset); 
        EXIT WHEN NOT FOUND;
        offset := offset + batch_size;
    END LOOP;
END $$;

DO $$
DECLARE
    batch_size INT := 1000;
    offset INT := 0;
BEGIN
    LOOP
        UPDATE products SET status = 'archived'
        WHERE id IN (SELECT id FROM products WHERE created_at < '2024-01-01' LIMIT batch_size OFFSET offset); 
        EXIT WHEN NOT FOUND;
        offset := offset + batch_size;
    END LOOP;
END $$;

will it wrap it in transaction or not... if it will wrap it in transaction, it kills the point of batching... so no other solution than custom js script for data migration.

Gaming

Programming

Does Prisma support executing PostgreSQL anonymous code blocks?

Did you find this page helpful?