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 ...

Prisma•9mo ago•

5 replies

Alex R.

Does Prisma support executing PostgreSQL anonymous code blocks?

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

Does Prisma support executing PostgreSQL anonymous code blocks?

Similar Threads

Does Prisma support executing PostgreSQL anonymous code blocks?

Similar Threads

Similar Threads

Similar Threads