Running migrations in a schema

[arp242]

I'd like to run all migrations in a specific schema; the "obvious" way to do this is something like:

db, err := pgx.Connect(ctx, "dbname=test")
_, err := db.Exec(ctx, "set search_path to myschema")

m, err := migrate.NewMigratorEx(...)

The problem with this is that MigrateTo() calls reset all over here: https://github.com/jackc/tern/blob/master/migrate/migrate.go#L372-L373

This resets the search_path and only the first migration will work (quite confusing!) The reason that reset all is in there is probably because people set the search_path in their migrations, in which case you don't want it to carry over to the next migration.

As a workaround, doing it in OnStart works:

m.OnStart = func(int32, string, string, string) {
    _, err = db.Exec(context.Background(), "set search_path to myschema")
    if err != nil {
        panic(fmt.Errorf("Migrate: %w", err))
    }
}

The downside of this is that there's no good way to signal errors from there, except a panic.

---

Not sure what the best way to fix this would be; adding an error return to OnStart isn't compatible, but could maybe add a SearchPath option? Or get the current search_path before running migrations and resetting it to that after every migration?

[jackc]

reset all returns search_path to the initial value. You can set that initial value in the connection string. Try something like:

db, err := pgx.Connect(ctx, "dbname=test search_path=myschema")

[arp242]

Ah cheers, I missed that when reading the PostgreSQL documentation; I thought there wasn't any way to do this in the initial connection string.

For reference, this is what I ended up with, where dbc is a connection string:

cfg, err := pgx.ParseConfig(dbc)
if err != nil {
    return fmt.Errorf("Migrate: %w", err)
}
if schema != "" {
    cfg.RuntimeParams["search_path"] = schema
}
dbPgx, err := pgx.ConnectConfig(context.Background(), cfg)
if err != nil {
    return fmt.Errorf("Migrate: %w", err)
}