Help users when they need to use pg_dump: when a baselining is needed for a brownfield project.

[Jolg42]

We mention pg_dump in

docs/content/300-guides/025-migrate/100-developing-with-prisma-migrate/050-add-prisma-migrate-to-a-project.mdx

Line 93 in 99c5858

- If the changes are significant, it can be easier to replace the entire migration file with the result of a database dump ([`mysqldump`](https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html), [`pg_dump`](https://www.postgresql.org/docs/12/app-pgdump.html))

In a brownfield project when a user wants to manage their database with Prisma Migrate, a baseline migration is required (because there is an existing database).

pg_dump can be used to create a reliable baseline migration, example

pg_dump --file=migration.sql --schema-only --dbname=mydb --host=localhost --username=myuser --password

⚠️ The migration.sql dump needs to be edited and this following line removed.

SELECT pg_catalog.set_config('search_path', '', false);

If it is not removed, the migration will fail with the following error message:

Applying migration `20220510114355_baseline`
Error: P1014

The underlying table for model `_prisma_migrations` does not exist.

We could mention this somewhere in our docs.

[janpio]

What is the explanation behind this?

If it is not removed, the migration will fail with the following error message:
The underlying table for model _prisma_migrations does not exist.

How does that 1 SQL command lead to this error message?

[Jolg42]

Migrate relies on the search_path which is set to the current schema behind the scenes when connecting to the db.
Since the search path is modified during the migration, here to nothing, it causes this.

[janpio]

Doesn't that imply a potential bug in our logic reading _prisma_migrations, that we should specify the schema explicitly instead of relying on the search paths? (That would still work, correct?) Or alternatively, that we should detect such search path modifications and warn about them and maybe even prevent running migrate on such SQL files?

[Sjlver]

Detecting a change to the search path, as @janpio suggested, would be great. I had exactly the problem described in this issue, and it took a while to find the cause. It might be worth detecting since this is the default behavior of pg_dump.

That said, migrations could probably do a number of things that screw up Prisma. However, changes from pg_catalog.set_config are only valid in the current session. So a safer (albeit slower) way would be to run each migration in its own session. That is, create a new database connection for each. This makes migrations more isolated, and thus there are fewer assumptions about what they can and cannot do.