How to run migrations with SET ROLE xxx

[mellow trench]

Hi everyone,

I’m working on a project where I need to run Prisma migrations, but the database connection requires a specific role to be set before executing any SQL commands. For example, I need to run:
SET ROLE some_table

before any migration is applied. This role is required for permissions to create tables, indexes, etc., in the specific schema.

Is there a clean and automated way to ensure SET ROLE is applied when running Prisma migrations?

Thanks in advance for your help! 😊 prisma with PostgreSQL

[hot leaf]

Hi @mellow trench

You need to create an empty migration and add the sql. Look at this guide on customizing migrations

https://www.prisma.io/docs/orm/prisma-migrate/workflows/customizing-migrations

[mellow trench]

so the first migration should have only the SET ROLE xxx; and then the rest migration will be normal ?

[hot leaf]

Yes, that's correct.

First, create the migration without applying it:

npx prisma migrate dev --create-only

This will generate a new migration file in your prisma/migrations folder. Open the newly created migration file, at the beginning of this SQL file, add your SET ROLE command. You can now apply it using:

npx prisma migrate dev

[mellow trench]

The issue was resolved by adding the options parameter to the DATABASE_URL. This ensures that the desired role is automatically set for every connection to the database, including migrations or other operations.

Solution:
postgresql://USER:PASSWORD@HOST:PORT/DB_NAME?schema=SCHEMA&options=-c%20role%3DYOUR_ROLE

•    Replace USER, PASSWORD, HOST, PORT, DB_NAME, SCHEMA, and YOUR_ROLE with your actual database credentials and desired role.
•    The options=-c role=YOUR_ROLE part ensures that the role is set automatically on every new connection.