Specifying a PostgreSQL template database for the shadow database #24900
Labels
domain/schema
Issue in the "Schema" domain: Prisma Schema, Introspection, Migrations etc.
kind/feature
A request for a new feature.
topic: multiSchema
multiple schemas
topic: shadow database
topic: shadowDatabaseUrl
Problem
When working with multiple schemas, it's necessary to be able to initialize a database with some PostgreSQL schemas having tables, views, or types that the migrations that Prisma Migrate applies depend on.
For example, I'm using Prisma Migrate on Postgres schema
s1
, and a migration needs to create a view that references a table in another Postgres schema,s2
. My Prisma Schema does not have any reference to schemas2
, and I'm not doing anything that requires the multiSchema feature. (In fact I cannot use the multiSchema feature withs2
becauses2
is externally managed.) But whenprisma migrate dev
runs and it tries to apply a migration that references schemas2
, which doesn't exist in the shadow database, it fails.Suggested solution
Postgres offers a way to create a new database from another database as a template: https://www.postgresql.org/docs/15/manage-ag-templatedbs.html
In fact every
create database
statement always involves creating from a template database, just if you don't specify a template it'll use either the built-intemplate0
ortemplate1
. So the idea here doesn't involve doing something unusual or hacky in any way.I'd like to specify, for example, the CLI argument
--shadow-database-template my_template_db
for Prisma Migrate, and the shadow database would be created with the statement:Or the name of the shadow database could be provided as an environment variable like
SHADOW_DATABASE_TEMPLATE_NAME
when runningprisma migrate
.Alternatives
I'm aware of the shadowDatabaseUrl option, but it isn't a good solution because it's cumbersome and not straightforward to properly create and initialize a database. In my case, for example, simply using a real local database as a template (that's my goal) would make migrations zero extra work.
The text was updated successfully, but these errors were encountered: