P
Prisma•3w ago
Rado

How to run migrations with SET ROLE xxx

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
5 Replies
RaphaelEtim
RaphaelEtim•3w ago
Hi @Rado 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
Customizing migrations | Prisma Documentation
How to edit a migration file before applying it to avoid data loss in production.
Rado
RadoOP•3w ago
so the first migration should have only the SET ROLE xxx; and then the rest migration will be normal ?
RaphaelEtim
RaphaelEtim•3w ago
Yes, that's correct. First, create the migration without applying it:
npx prisma migrate dev --create-only
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
npx prisma migrate dev
Rado
RadoOP•3w ago
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.
RaphaelEtim
RaphaelEtim•3w ago
Thanks for sharing
Want results from more Discord servers?
Add your server