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 schema s2, and I'm not doing anything that requires the multiSchema feature. (In fact I cannot use the multiSchema feature with s2 because s2 is externally managed.) But when prisma migrate dev runs and it tries to apply a migration that references schema s2, 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-in template0 or template1. 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:

create database "{SHADOW_DB_NAME}" template my_template_db

Or the name of the shadow database could be provided as an environment variable like SHADOW_DATABASE_TEMPLATE_NAME when running prisma 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.