golang-migrate with different projects same database

[jamesnewsome]

I have two different projects that use the same mysql server, is there a way to use the same database to be use by migrate? For an example I created a maintenance database which migrate created a table "schema_migrations", when looking to use migrate in another project do i have to point migrate to a different database to create the "schema_migrations" table?

[dhui]

You should be able to use multiple DBs/schemas with migrate and a single RDBMS instance if the RDBMS supports multiple dbs/schemas.

Each DB in MySQL will have its own schema_migrations table.

when looking to use migrate in another project do i have to point migrate to a different database to create the "schema_migrations" table?

Yes, you should be using a different DB (could use the same RDBMS instance) for each project anyways.

[thedodd]

@dhui for postgres, is it possible to use a different schema_migrations table per schema? Looks like by default the table is created in the public schema. Is there a mechanism to control that?

[dhui]

@thedodd try setting the search path

[jamesnewsome]

Thanks for responding. On the comment of “should be using the same database”, while I generally would agree however, I was looking to separate my migrations by versions so that each version of the app migrations persists in different folders. That is what triggered the question. Seems we would need to keep all migrations for one database within the same folder to make this work.

[thedodd]

@dhui ok, I'll test that out. Will report back shortly.

[thedodd]

Actually, @dhui do you mind elaborating a bit? I'm familiar with setting the search path from psql, but I don't see an option for setting such a value on the connection string. Thoughts?

[thedodd]

Ah, looks like I can just use ?options=-csearch_path=my_schema.

[dhui]

?search_path=abc should work See the docs

[thedodd]

So, just to report back, neither the search_path=abc nor the options=-csearch_path=abc worked. However, the next best thing is x-migrations-table, which did work. I wasn't able to put the migrations table under a different schema, it was being created in public. even when I prefix the table name with the desired schema name. However, it does roughly accomplish what I needed.

Thanks @dhui for the pointers. Would you like for me to open an issue on being able to put the migrations table under a different schema?

[dhui]

@thedodd Thanks for trying search_path

Would you like for me to open an issue on being able to put the migrations table under a different schema?

Sure, that'd be great! If you're able to change the driver to support using a different schema, a PR to upstream the improvement would be appreciated 🙏

[danpoland]

I was able to maintain separate schema_migrations tables per schema using ?search_path=someschema. The trick was making sure each schema existed before running the first migration against it.

[oldgalileo]

@danpoland can you run me through your desired behavior?

Did you expect to have schemas solely filled be thy migration table object, with all contentful tables still owned by the public schema?

We're running a monorepo with microservices, and my ideal scenario here is to try and hide as much of what's going on behind the scenes to support migrations split across services as possible.

Also, interesting side note: setting the search_path on connection breaks passing the search_path via driver config (for the pgx driver).

[oldgalileo]

I think I might be misunderstanding the search_path setting.

My goal is to isolate the tables into the schemas for their respective services. So within the authservice schema, I want to create my user table. Here's the start of the migration (if you spot something wrong here, keep reading):

CREATE TABLE IF NOT EXISTS users(...

which is executed like so:

driver, err := pgx.WithInstance(Db.DB, &pgx.Config{SchemaName: "authservice"})
if err != nil {
	return fmt.Errorf("error creating pgx migrate driver: %w", err)
}
m, err := migrate.NewWithDatabaseInstance("file://internal/db/migrations/", database, driver)
if err != nil {
	return fmt.Errorf("error creating migrate instance: %w", err)
}

When running the migration like this, I get a resulting structure:

demo=# SELECT * FROM pg_catalog.pg_tables WHERE schemaname != 'pg_toast' AND schemaname != 'pg_catalog' AND schemaname != 'information_schema';
 schemaname  |     tablename     | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
-------------+-------------------+------------+------------+------------+----------+-------------+-------------
 public      | users             | demo    |            | t          | f        | f           | f
 public      | user_features     | demo    |            | t          | f        | f           | f
 authservice | schema_migrations | demo    |            | t          | f        | f           | f
(3 rows)

Not quite what I'm looking for (although I believe workable, as the schema_migrations versioning is separate and thus works with multiple migrations sharing one DB). When properly setting the schema name in the migration, everything works as expected:

CREATE TABLE IF NOT EXISTS authservice.users(...

However it was my understanding that the entire point of the search_path was to clean up your queries and statements so that you didn't need to continuously repeat the schema name when dealing with one schema for extended batches of queries.

[oldgalileo]

Wow, this is what I get for not paying close enough attention. Turns in my comment earlier I actually just hadn't looked close enough. Setting the search_path in the DSN works fine and fixes the exact "issue" I was complaining about.

Now the structure is as desired:

SELECT * FROM pg_catalog.pg_tables WHERE schemaname != 'pg_toast' AND schemaname != 'pg_catalog' AND schemaname != 'information_schema';

while the migrations themselves are blissfully unaware of their schema:

CREATE TABLE IF NOT EXISTS users(...

[dhui]

FYI, the x-migrations-table-quoted was added to postgres which allows you to specify the schema and name for the migration table.

[clouedoc]

Hello,

Setting search_path solved my problem. I believe this issue should be closed.

[sermojohn]

I experimented with x-migrations-table-quoted and x-migrations-table, e.g.:

migrate -path ./migrations/main -database 'postgresql://...?x-migrations-table="migrate"."schema_migrations"&x-migrations-table-quoted=1

However, I receive this error due to the schema not being created:

error: pq: schema "migrate" does not exist in line 0: CREATE TABLE IF NOT EXISTS "migrate"."schema_migrations" (version bigint not null primary key, dirty boolean not null)

Taken the the application schemas are created in the migration scripts, its not possible to use a migration script to create the schema of the migrate tool, because the schema where the migrate tool will store its state should be available beforehand. So, IMO it would be useful if the above options also created the specified schema before trying to create the table.

If you agree, I can work on a PR for adding this capability.

Additionally, the fact that the options for the table are added to the postgres URL makes this behaviour vendor specific and a bit harder to find this feature. What if there was a CLI option to customise the table and schema?

[musinit]

Are there any updates on this issue? I also have this problem with tables in one public schema, however with x-migrations-table and x-migrations-table-quoted I placed schema_migrations table on different schemas. I need to split migration execution on different schemas, search_path does not help

[ghost]

Is there a solution for MSSQL server?

Here is my URL: sqlserver://<user>:<pass>@127.0.0.1:1433?database=test

I have tried using both search_path and x-migrations-table url params in order to create migrations table as part of my schema (let's say that schema name is abc, for test purpose).

So, with both sqlserver://<user>:<pass>@127.0.0.1:1433?database=test&search_path=abc and sqlserver://<user>:<pass>@127.0.0.1:1433?database=test&x-migrations-table=abc - the migrations table is still created in dbo schema.

Any insights on this? Did I overlook anything?

[vinicius-oa]

search_path worked on postgres:11.16-alpine