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
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.
so the first migration should have only the SET ROLE xxx; and then the rest migration will be normal ?
Yes, that's correct.
First, create the migration without applying it:
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:
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.
Thanks for sharing