down:scope not finding migration table, when schema specified

[haydockjp]

I'm submitting a...

• Bug report
• Feature request
• Question

Current behavior

I am using db-migrate with postgres. I have a scope and a schema defined. The database.json file looks something like this

{
  "myprojenv": {
    "driver": "pg",
    "user": {"ENV": "PGDB_USER"},
    "password": {"ENV": "PGDB_PASS"},
    "host": {"ENV": "PGDB_HOST"},
    "port": {"ENV": "PGDB_PORT"},
    "database": {"ENV": "PGDB_DBNM"},
    "schema": {"ENV": "PGDB_SCHEMA"}
  }
}

When I run up everything is okay

./node_modules/db-migrate/bin/db-migrate  up:myprojectscope --env myprojectenv --config ./db/database.json --migrations-dir ./db/migrations/

• I see everything created in the schema I provided
• I see the migrations table in the public schema
• I see the migration file entries in the migrations table

When I run the down command

./node_modules/db-migrate/bin/db-migrate  down:myprojectscope --env myprojectenv --config ./db/database.json --migrations-dir ./db/migrations/

I get a message saying nothing to run

[INFO] Defaulting to running 1 down migration.
[INFO] No migrations to run
[INFO] Done

If I leave out the schema from the database.json file, up and down work as expected, and down starts removing the migrations one at a time!

I am guessing that the down command is looking for the migration table in the schema that is specified

Expected behavior

Down command should work the same if their is a schema or no schema specified.

Minimal reproduction of the problem with instructions

What is the motivation / use case for changing the behavior?

With a postgres database...

./node_modules/db-migrate/bin/db-migrate --env myprojenv create:myprojectscope tables  --sql-file

Edit the up file to create a dummy table
Edit the down file to drop the dummy table
Create the database file with a schema

{
  "myprojenv": {
    "driver": "pg",
    "user": {"ENV": "PGDB_USER"},
    "password": {"ENV": "PGDB_PASS"},
    "host": {"ENV": "PGDB_HOST"},
    "port": {"ENV": "PGDB_PORT"},
    "database": {"ENV": "PGDB_DBNM"},
    "schema": {"ENV": "PGDB_SCHEMA"}
  }
}

Run up

./node_modules/db-migrate/bin/db-migrate  up:myprojectscope --env myprojectenv --config ./db/database.json --migrations-dir ./db/migrations/

Run down

./node_modules/db-migrate/bin/db-migrate  down:myprojectscope --env myprojectenv --config ./db/database.json --migrations-dir ./db/migrations/

Environment

"db-migrate": "0.11.6",
"db-migrate-pg": "1.0.0",

Additional information:
- Node version: v12.4.0
- Platform:  Docker image based on node:12.4.0-alpine

[haydockjp]

Digging into this a little more.

My migration up scripts, include a script that creates the schema that is specified in the database.json file.

What I think is happening is, when the script is run, it creates the migration in the public schema. As part the initial up scripts it creates the new schema.

Now when the migrate script is run a second time, in my case with the down command, a second table is created in the new schema, which is empty.

[haydockjp]

I have a work around, that is not great - but works

I need to run three migrates

1. As the postgres user, connected to the postgres database

• Create the new database

2. As the postgres user, connected to the new database

• create the new user
• create the new schema

3. As the new user, connected to the new database, with the new schema

• run everything else

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.