Can't use db:create with postgres

[walnutfalls]

Just installed postgres with default settings. Created this config:

{
  "defaultEnv": "local",
  "local": {
    "driver": "pg",
    "user": "postgres",
    "password": [my password],
    "host": "localhost",
    "database": "identity",
    "schema": "public"
  }
}

Then tried running

db-migrate db:create identity

.

Got error:

[INFO] Detected and using the projects local version of db-migrate. 'D:\Development\friendswithgoals\databases\identity\node_modules\db-migrate\index.js'
[INFO] Using local settings: { driver: 'pg',
user: 'postgres',
password: '******',
host: 'localhost',
database: 'identity',
schema: 'public' }
[INFO] require: db-migrate-pg
[INFO] connecting
[INFO] connected
[SQL] CREATE DATABASE "identity"
[ERROR] error: database "identity" does not exist
at Connection.parseE (D:\Development\friendswithgoals\databases\identity\node_modules\db-migrate-pg\node_modules\pg\lib\connection.js:539:11)
at Connection.parseMessage (D:\Development\friendswithgoals\databases\identity\node_modules\db-migrate-pg\node_modules\pg\lib\connection.js:366:17)
at Socket. (D:\Development\friendswithgoals\databases\identity\node_modules\db-migrate-pg\node_modules\pg\lib\connection.js:105:22)
at emitOne (events.js:77:13)
at Socket.emit (events.js:169:7)
at readableAddChunk (_stream_readable.js:153:18)
at Socket.Readable.push (_stream_readable.js:111:10)
at TCP.onread (net.js:531:20)

Tried taking out "database" "identity" from config, and got:

[INFO] Detected and using the projects local version of db-migrate. 'D:\Development\friendswithgoals\databases\identity\node_modules\db-migrate\index.js'
[INFO] Using local settings: { driver: 'pg',
user: 'postgres',
password: '******',
host: 'localhost',
schema: 'public' }
[INFO] require: db-migrate-pg
[INFO] connecting
[INFO] connected
[SQL] CREATE DATABASE "identity"
[ERROR] error: database "savel" does not exist
at Connection.parseE (D:\Development\friendswithgoals\databases\identity\node_modules\db-migrate-pg\node_modules\pg\lib\connection.js:539:11)
at Connection.parseMessage (D:\Development\friendswithgoals\databases\identity\node_modules\db-migrate-pg\node_modules\pg\lib\connection.js:366:17)
at Socket. (D:\Development\friendswithgoals\databases\identity\node_modules\db-migrate-pg\node_modules\pg\lib\connection.js:105:22)
at emitOne (events.js:77:13)
at Socket.emit (events.js:169:7)
at readableAddChunk (_stream_readable.js:153:18)
at Socket.Readable.push (_stream_readable.js:111:10)
at TCP.onread (net.js:531:20)

Migrations seem to be working fine. I was able to manually create db, and run migrations. Just wondering what I'm doing wrong here.

---

Want to back this issue? Post a bounty on it! We accept bounties via Bountysource.

[wzrdtales]

Duplicate of #338

The problem is, that db-migrate does try to connect to the database of your configuration, which will result in the error you have seen. database less connections are one of our work items though.

[walnutfalls]

Hey, thanks for the quick response! If I take the database and schema out of the configuration, should it work?

[wzrdtales]

Depends on the driver actually, either you need to define one that already exists or just no db at all.

In case of pg we just pass the config to the pg driver below:
https://github.com/db-migrate/pg/blob/master/index.js#L494

In the case of pg this means it defaults to the username of the user executing db-migrate:
https://github.com/brianc/node-postgres/wiki/Client#new-clientobject-config--client

Thus probably it might not help to remove this setting and I'm not sure if an empty string database setting would help.

So probably we need to switch over to use https://github.com/brianc/node-postgres/wiki/Connection instead though.

[LandonSchropp]

For anyone who runs into this, my workaround was to create a separate database_create.json configuration file that looks like this (without a database property):

{
  "defaultEnv": "local",
  "local": {
    "driver": "pg",
    "user": "postgres",
    "password": [my password],
    "host": "localhost",
    "schema": "public"
  }
}

I then run the create command like this:

node_modules/db-migrate/bin/db-migrate --config database_create.json db:create identity

[Wildhoney]

Just a heads up that using the above approach can be automated using jq, rather than creating two separate config files:

jq 'del(.mysql.database)' database.json > ./database-without-key.json &&\
db-migrate --config ./database-without-key.json -e mysql db:create alcomixer &&\
rm ./database-without-key.json

[AndrewRayCode]

Would love to see this fixed in core, following the docs in order and then trying to create a db leads to this error