Support x-migrations-table-schema for postgresql

[yuichi1004]

Is your feature request related to a problem? Please describe.

When I was working on multiple schema on postgres, schema_migrations tables are created in multiple schema depending on search_path. This could re-run migration accidentally like the following.

ctlg=> select * from public.schema_migrations ;
  version   | dirty
------------+-------
 1534400001 | t
(1 row)

ctlg=> select * from myapp.schema_migrations ;
  version   | dirty
------------+-------
 1521863031 | t
(1 row)

Describe the solution you'd like

I would like to specify schema explicitly to avoid the issue.

Supporting the following parameter on postgres should work:

• URL Query: x-migrations-table-schema
• WithInstance Config: MigrationsTableSchema

Describe alternatives you've considered

search_path can work as workaround but I prefer to be specific.

Additional context
N/A

[alexykot]

We are facing similar problem now, we have multiple apps with independent migration histories, and we have all persistence for all apps in a single Postgres with separate schema per app. Right now we have to have all schema_migrations tables in the public schema, and named after each app to avoid collisions, i.e.:

platform=# \dt
                  List of relations
  Schema  |          Name          | Type  |  Owner
----------+------------------------+-------+----------
 app1     | addresses              | table | postgres
 app1     | customer_aspects       | table | postgres
 app1     | customers              | table | postgres
 app2     | customer_transactions  | table | postgres
 public   | app1_db_migrations     | table | postgres
 public   | app2_db_migrations     | table | postgres

Describe the solution you'd like
Ideal would be to have schema_migrations metadata table for each app individually in their own schema, together with the corresponding data transactions:

platform=# \dt
                  List of relations
  Schema  |          Name          | Type  |  Owner
----------+------------------------+-------+----------
 app1     | db_migrations          | table | postgres
 app1     | addresses              | table | postgres
 app1     | customer_aspects       | table | postgres
 app1     | customers              | table | postgres
 app2     | db_migrations          | table | postgres
 app2     | customer_transactions  | table | postgres

Describe alternatives you've considered

search_path is a possible workaround, but as I see migrations is creating the DB connection internally and I likely will have to intervene into that to issue SET search_path TO app1; to force it. I don't really want to do that.

[dhui]

Feel free to open a PR w/ the new feature.

I'm not sure if it makes sense to use separate schemas for the migrations table and where the migrations are run. e.g. I can't think of a valid usecase for that

[alexykot]

I'm not sure if it makes sense to use separate schemas for the migrations table

So what you mean is instead of using separate MigrationsTableSchema param use same SchemaName that is already present in the postgres config to define where to locate the migrations table, right?

[dhui]

So what you mean is instead of using separate MigrationsTableSchema param use same SchemaName that is already present in the postgres config to define where to locate the migrations table, right?

Yes, we should only use Config.SchemaName, unless you think think of a good reason to allow the schema used for migrations to differ from the schema used for the migration table.
When you add the x-schema-name parameter to the DSN, make sure you add a note about how it'll interact with the search_path parameter in the driver's README.

[alexykot]

unless you think think of a good reason

No, I think they should always be together, I don't see why we would want them in separate schemas.

[timbunce]

I just stumbled into a related problem that led me here. In case it helps others...

I migrated a db from one pg db instance to another run by a different team. In the first the search_path was "$user", public and the `"$user" schema does not exist, so tables got created in public. The db dump has all the tables with public.* names. The dump loaded into the new db fine, and the app ran fine. But when we ran a migration suddenly all the tables appeared to be empty.

It turned out that in the new db the search_path is mumble, "$user", public and, crucially, the mumble schema does exist. When migrate ran it starts with CREATE TABLE IF NOT EXISTS schema_migrations …. In the new db that created new table in the mumble schema instead of finding the one in the public schema then all the migrations ran and did the same thing, creating and modifying new empty tables in the mumble schema.

The immediate problem was fixed with a DROP SCHEMA mumble CASCADE and then we changed the search_path.

[stephane-klein]

@dhui Considering this commit is merged, what do you think to close this issue #95 ?

[dhui]

Yep, this is fixed in the master branch. I'd prefer to keep this issue open until it's fixed in the latest release.

[Fontinalis]

@dhui As release v4.15.0-beta.1 has been published and v4.15.0 follows in a week, can we close this issue?

[dhui]

yep, this can be closed now