Node-postgres: connection strings not working in 6.x

[vitaly-t]

Moving from v5.1 to v6.x, the same connection strings no longer work.

This used to work up until v5.1 at least:

var cn = "postgres://postgres@localhost:5433/newone";
pg.connect(cn,...)

Now with v6.1, calling:

var cn = "postgres://postgres@localhost:5433/newone";
var pool = new pg.Pool(cn);
pool.connect(function (err, client, done) {

throws the following error:

{ error: database "Vitaly" does not exist
    at Connection.parseE (D:\NodeJS\tests\node_modules\pg\lib\connection.js:543:11)                                                                                 at Connection.parseMessage (D:\NodeJS\tests\node_modules\pg\lib\connection.js:370:17)
    at Socket.<anonymous> (D:\NodeJS\tests\node_modules\pg\lib\connection.js:109:22)
    at emitOne (events.js:96:13)
    at Socket.emit (events.js:188:7)                                                at readableAddChunk (_stream_readable.js:176:18)
    at Socket.Readable.push (_stream_readable.js:134:10)                            at TCP.onread (net.js:543:20)
  name: 'error',                                                                  length: 107,
  severity: 'FATAL',
  code: '3D000',
  detail: undefined,
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'src\\backend\\utils\\init\\postinit.c',
  line: '775',
  routine: 'InitPostgres' }

I had to explicitly switch to the config object to make it work:

var cn = {
    database: 'newone',
    port: 5433,
    host: 'localhost',
    user: 'postgres'
};

[Globik]

Your database should be Vitaly? Then in var cn ={database : 'Vitaly'};

[vitaly-t]

@Globik what do you mean? The bug is with the connection strings, got nothing to do with connection config objects.

[Globik]

See here: error: database "Vitaly" does not exist

[Globik]

You should to have a database named "Vitaly". Or create one if not exists. And remove from string "newone". It is not a mongodb where if not exists db create one on the fly.

[Globik]

User: postgres, but where is your password? Also should be there in options.

[Globik]

What gives \l in your postgresql console?

[vitaly-t]

@Globik Have you even read the bug description, or used the connection strings in the past?

The connection string that I'm passing already has all the details, but the latest driver fails to parse it correctly. All previous versions (5.x and earlier) could do it without problems.

[Globik]

But why database Vitaly does not exist?

[Globik]

Are you sure is your postgresql version compatible with brand new pg driver version? May be on your machine is a few postgresqlS instances? With different databases per se?

[vitaly-t]

But why database Vitaly does not exist?

The driver fails to parse the database name, and instead uses the default name, which is the current user name, as per the defaults: https://github.com/brianc/node-postgres/blob/master/lib/defaults.js#L17

[Globik]

You must go check yr code and refactor it. Remove all Vitaly words, replace with "newone". Make sure that database under that name exists. For every postgresql instances should be its own ../data directory.

[vitaly-t]

Huh? This makes no sense.

[Globik]

Application database name should not be equal process.env.user. As it user name for a cluster instance, not for an application database name.

[Globik]

Short to say, you must create database another_name, and in options add this database name.

[Globik]

Or set USER=postgres must solve this issue.

[Globik]

cmd.exe set USER = postgres

[vitaly-t]

Thank you, but no thank you. The issue is with the updated parser that needs to be fixed. I don't want ugly work-arounds like this.

[Globik]

And where is parser?

[joskuijpers]

@Globik thank you, but that is not where the problem is. We will try to figure the exact cause and attempt to fix it.

I will look at this when I get home. Problem should be occurring somewhere in this val() call: https://github.com/brianc/node-postgres/blob/master/lib/connection-parameters.js#L53

[Globik]

From node-pg-pool:

The Pool constructor does not support passing a Database URL as the parameter. To use pg-pool on heroku, for example, you need to parse the URL into a config object. Here is an example of how to parse a Database URL.

[vitaly-t]

@joskuijpers any luck finding the issue? ;)

[KyleLilly]

Since pg-pool expects a config object and pool-factory.js was simply passing the argument directly to pg-pool it seems like checking if the passed in options are a string and parsing them into an object is all that was needed. I've created a PR but it seems the Travis build is having issues (I have the same issues running locally off master).

[charmander]

As @Globik quoted, it’s by design that there’s no connection string parsing in pg-pool. It might be a little friendlier to throw an error if a value is passed that isn’t an object, though.

[agauniyal]

But why not parse the connection string instead? It's better to have a single connection string secret/env variable than to do process.env.PG_USER,process.env.PG_PASSWORD, process.env.PG_DB, process.env.PG_HOST etc.

Every other db connector does this and the docs here say this too..

[charmander]

You can parse a connection string using pg-connection-string.

[vitaly-t]

@agauniyal This is not how the library used to work, it broke after 5.1

It is still clearly a bug.

[charmander]

@vitaly-t It’s not a bug. There’s a paragraph on it in the pg-pool README. They also still work with pg.connect, and major version changes can break backwards compatibility even if they didn’t.

[agauniyal]

@charmander It might or might not be a bug but why isn't pg-connection-string part of code library then? I'm unable to understand what's the issue with including that code when the library already worked that way before?

[brianc]

The connection string still works with the pool, you just have to pass it as a parameter to the pool config and not a raw string:

https://node-postgres.com/features/connecting#connection-uri

This works in both 6.x and will remain this way in 7.x and beyond.