Native driver option does not work

[Crocmagnon]

Hello,
I'm trying to deploy on Heroku but it complains that SSL is not enabled. I tried activate "native: true" in my database.json file :

{
    "prod": {
        "ENV": "DATABASE_URL",
        "native": true
    },
    "dev": {
        "ENV": "DATABASE_URL"
    }
}

I had no luck. By slightly changing the code in index.js :

exports.connect = function(config, intern, callback) {

    internals = intern;

    log = intern.mod.log;
    type = intern.mod.type;
    console.log("config", config); // Added a console.log
    if (config.native) { pg = pg.native; }
    var db = config.db || new pg.Client(config);
    callback(null, new PgDriver(db, config.schema, intern));
};

We can see that config doesn't contain a native property, so it never enters the condition.

[wzrdtales]

pg-native is considered optional, you need to install it in your local project first.

If you installed the pg driver globally then you need to do

npm install -g pg-native

if local then

npm install pg-native

I think we need to add this to the docs, seems like we do not have any informations about the native driver option of pg.

The default installation by default of pg-native was removed as it is not needed by everyone and also caused trouble here:
db-migrate/node-db-migrate#286

[wzrdtales]

Added task here:
db-migrate/english-docs#5

[Crocmagnon]

The module is already installed, but still the native option is not passed.
I have the feeling that if a "DATABASE_URL" environment variable is set, it
overrides any setting we may have set, even the "-e env" option when
running.

Le mercredi 3 février 2016, Tobias Gurtzick [email protected] a
écrit :

pg-native is considered optional, you need to install it in your local
project first.

If you installed the pg driver globally then you need to do

npm install -g pg-native

if local then

npm install pg-native

I think we need to add this to the docs, seems like we do not have any
informations about the native driver option of pg.

The default installation by default of pg-native was removed as it is not
needed by everyone and also caused trouble here:
db-migrate/node-db-migrate#286
db-migrate/node-db-migrate#286

—
Reply to this email directly or view it on GitHub
#8 (comment).

[wzrdtales]

Could you try this locally again and maybe post the verbose runs?

I just tried it with a global installed driver:

Before installing the pg-native module:

db-migrate down
[INFO] Defaulting to running 1 down migration.
[ERROR] Error: Cannot find module 'pg-native'
    at Function.Module._resolveFilename (module.js:326:15)
    at Function.Module._load (module.js:277:25)
    at Module.require (module.js:354:17)
    at require (internal/module.js:12:17)
    at Object.<anonymous> (/home/tobi/.nvm/versions/node/v4.2.6/lib/node_modules/db-migrate-pg/node_modules/pg/lib/native/index.js:1:76)
    at Module._compile (module.js:410:26)
    at Object.Module._extensions..js (module.js:417:10)
    at Module.load (module.js:344:32)
    at Function.Module._load (module.js:301:12)
    at Module.require (module.js:354:17)
    at require (internal/module.js:12:17)
    at null.<anonymous> (/home/tobi/.nvm/versions/node/v4.2.6/lib/node_modules/db-migrate-pg/node_modules/pg/lib/index.js:78:36)
    at Object.exports.connect (/home/tobi/.nvm/versions/node/v4.2.6/lib/node_modules/db-migrate-pg/index.js:493:33)
    at connect (/home/tobi/.nvm/versions/node/v4.2.6/lib/node_modules/db-migrate/lib/driver/index.js:105:12)
    at Object.exports.connect (/home/tobi/.nvm/versions/node/v4.2.6/lib/node_modules/db-migrate/lib/driver/index.js:146:5)
    at Object.exports.connect (/home/tobi/.nvm/versions/node/v4.2.6/lib/node_modules/db-migrate/connect.js:17:10)
    at executeDown (/home/tobi/.nvm/versions/node/v4.2.6/lib/node_modules/db-migrate/api.js:717:9)
    at run (/home/tobi/.nvm/versions/node/v4.2.6/lib/node_modules/db-migrate/api.js:924:9)
    at Object.dbmigrate.run (/home/tobi/.nvm/versions/node/v4.2.6/lib/node_modules/db-migrate/api.js:358:5)
    at /home/tobi/.nvm/versions/node/v4.2.6/lib/node_modules/db-migrate/bin/db-migrate:35:11
    at /home/tobi/.nvm/versions/node/v4.2.6/lib/node_modules/db-migrate/node_modules/resolve/lib/async.js:46:14
    at process (/home/tobi/.nvm/versions/node/v4.2.6/lib/node_modules/db-migrate/node_modules/resolve/lib/async.js:173:43)

After it was installed:

[tobi:test]$ npm install -g pg-native

> [email protected] install /home/tobi/.nvm/versions/node/v4.2.6/lib/node_modules/pg-native/node_modules/libpq
> node-gyp rebuild

make: Entering directory '/home/tobi/.nvm/versions/node/v4.2.6/lib/node_modules/pg-native/node_modules/libpq/build'
  CXX(target) Release/obj.target/addon/src/connection.o
  CXX(target) Release/obj.target/addon/src/connect-async-worker.o
  CXX(target) Release/obj.target/addon/src/addon.o
  SOLINK_MODULE(target) Release/obj.target/addon.node
  COPY Release/addon.node
make: Leaving directory '/home/tobi/.nvm/versions/node/v4.2.6/lib/node_modules/pg-native/node_modules/libpq/build'
[email protected] /home/tobi/.nvm/versions/node/v4.2.6/lib/node_modules/pg-native
├── [email protected]
├── [email protected] ([email protected], [email protected], [email protected], [email protected])
└── [email protected] ([email protected], [email protected])
[tobi:test]$ db-migrate down
[INFO] Defaulting to running 1 down migration.
[INFO] No migrations to run
[INFO] Done

[wzrdtales]

The require of the native driver works though, also tested with a local driver just now. Seems like something else does not work for you.
Lets figure out what.

[Crocmagnon]

Config

I don't think this applies, the pg-native driver seems to be working fine if I require it from code.
I assume it's something different, so first things first, here's my config :

• OS X 10.11.3 (El Capitan up to date)
• node and npm installed with brew
• Using dotenv

Also, here are my dependencies :

{
    "dependencies": {
        "body-parser": "^1.14.2",
        "db-migrate": "^0.10.0-beta.10",
        "db-migrate-pg": "^0.1.8",
        "dotenv": "^2.0.0",
        "express": "^4.13.4",
        "express-validator": "^2.19.0",
        "pg": "^4.4.4",
        "pg-native": "^1.10.0",
        "supertest": "^1.1.0"
    }
}

First try

I tried to remove my DATABASE_URL environment variable and modify my database.json :

{
    "prod": {
        "native": true,
        "ENV": "DATABASE_URL"
    },
    "dev": {
        "ENV": "DATABASE_URL"
    },
    "temp": {
        "native": true,
        "driver": "pg",
        "user": {"ENV": "PROD_DB_USER"},
        "host": {"ENV": "PROD_DB_HOST"},
        "database": {"ENV": "PROD_DB_NAME"},
        "password": {"ENV": "PROD_DB_PASS"}
    }
}

And run it with db-migrate up -e temp, though it still complains :

[ERROR] TypeError: Parameter 'url' must be a string, not undefined
    at Url.parse (url.js:90:11)
    at Object.urlParse [as parse] (url.js:84:5)
    at module.exports (/Users/gaugendre/Projects/archery/api/node_modules/parse-database-url/lib/parse-database-url.js:16:23)
    at Object.exports.loadObject (/Users/gaugendre/Projects/archery/api/node_modules/db-migrate/lib/config.js:67:18)
    at Object.exports.loadFile (/Users/gaugendre/Projects/archery/api/node_modules/db-migrate/lib/config.js:59:18)
    at loadConfig (/Users/gaugendre/Projects/archery/api/node_modules/db-migrate/api.js:508:18)
    at Object.dbmigrate (/Users/gaugendre/Projects/archery/api/node_modules/db-migrate/api.js:67:17)
    at Object.module.exports.getInstance (/Users/gaugendre/Projects/archery/api/node_modules/db-migrate/index.js:56:10)
    at /usr/local/lib/node_modules/db-migrate/bin/db-migrate:34:23
    at /usr/local/lib/node_modules/db-migrate/node_modules/resolve/lib/async.js:44:21
    at ondir (/usr/local/lib/node_modules/db-migrate/node_modules/resolve/lib/async.js:187:31)
    at onex (/usr/local/lib/node_modules/db-migrate/node_modules/resolve/lib/async.js:93:22)
    at /usr/local/lib/node_modules/db-migrate/node_modules/resolve/lib/async.js:24:18
    at FSReqWrap.oncomplete (fs.js:82:15)

Second try

I suspected it tried to load the dev environment even if I specified other in command line, then I try with this database.json :

{
    "temp": {
        "native": true,
        "driver": "pg",
        "user": {"ENV": "PROD_DB_USER"},
        "host": {"ENV": "PROD_DB_HOST"},
        "database": {"ENV": "PROD_DB_NAME"},
        "password": {"ENV": "PROD_DB_PASS"}
    }
}

And still db-migrate up -e temp, I get this :

config { native: true,
  driver: 'pg',
  user: 'WWW',
  host: 'ec2-54-195-248-72.eu-west-1.compute.amazonaws.com',
  database: 'XXX',
  password: 'YYY' }
[ERROR] Error: FATAL:  password authentication failed for user "WWW"
FATAL:  no pg_hba.conf entry for host "87.91.101.94", user "WWW", database "XXX", SSL off

    at Error (native)

The first lines are from my console.log, and we can see that it loads pg-native, but doesn't seem to activate SSL.

Third try

Then, I also tried something different : I tried to use this database.json:

{
    "temp": {
       "ENV": "DB_CONNECTION"
    }
}

with this .env file:

DB_CONNECTION=postgres://WWW:[email protected]/XXX?ssl=true

And then it finally works ! But I don't have any way to modify my DATABASE_URL environment variable in Heroku: it may change any time because of the free tier I'm using.

Tried to remove the ssl=true from the URL and put it in database.json, not loaded.

Conclusion

I see many problems here (don't hesitate to tell me if I'm wrong) :

• db-migrate always uses dev environment if it exists in database.json
• "native": true doesn't activate SSL though it loads pg-native
• ?ssl=true at the end of the database url activates ssl, but we need a way to specify it in database.json

[Crocmagnon]

Also, don't hesitate to tell me if you really need me to run the tests you asked in your comment !

[Crocmagnon]

I also suspect that if DATABASE_URL is set, then it overrides any config from database.json, even if selecting other environment with -e env.

[wzrdtales]

I will take a look in the evening (in about 6 hours) again, and try to reproduce this.

[Crocmagnon]

Thanks ! Just out of curiosity, in which timezone are you located ?

[wzrdtales]

GMT+1

[Crocmagnon]

Ok nice, so we're synced ;)

[wzrdtales]

• db-migrate always uses dev environment if it exists in database.json
  It uses by default dev that is true, but only as long as you don't specify any environment to use or you have set the configuration variable default which allows you to define the default environment to use
• "native": true doesn't activate SSL though it loads pg-native
  It shouldn't, native doesn't mean SSL, it really just means native. And native comes from the native language that the pg-native driver uses (c++). This is just a faster driver, but not automatically SSL.
• ?ssl=true at the end of the database url activates ssl, but we need a way to specify it in database.json
  Going to take a look at this, may this is really missing right now.

[wzrdtales]

The thing about the DATABASE_URL is possibly true, going to check this out next.

[wzrdtales]

I'm away now again, but here is the status.

I got the answer from this test here:

https://github.com/brianc/node-postgres/blob/35e5567f86774f808c2a8518dd312b8aa3586693/test/integration/client/ssl-tests.js

I modified your configuration examples for this, you can configure it like this:

{
    "temp": {
        "native": true,
        "driver": "pg",
        "ssl": true,
        "user": {"ENV": "PROD_DB_USER"},
        "host": {"ENV": "PROD_DB_HOST"},
        "database": {"ENV": "PROD_DB_NAME"},
        "password": {"ENV": "PROD_DB_PASS"}
    }
}

If you want to force your client to use ssl, you need this:

{
    "temp": {
        "native": true,
        "driver": "pg",
        "ssl": {
          "rejectUnauthorized": false
        },
        "user": {"ENV": "PROD_DB_USER"},
        "host": {"ENV": "PROD_DB_HOST"},
        "database": {"ENV": "PROD_DB_NAME"},
        "password": {"ENV": "PROD_DB_PASS"}
    }
}

You can still use the native option, as it can give you a performance boost. But this you need to evaluate yourself.

I will be back in about 4 hours.

[wzrdtales]

Ok, I'm back.

To explain a bit about the configs, whenever you're not sure if some configuration options are available on a driver, you can consult the used driver itself. db-migrate passes the configuration object to the database driver and thus all driver options are in most, if not any cases available to you.

And finally about the DATABASE_URL thing: Yes, this is currently the case:

https://github.com/db-migrate/node-db-migrate/blob/master/api.js#L503

I will open a task for this to change this behavior as this definitely an unexpected behavior. Thank you for reporting this!

[Crocmagnon]

Ok, so finally, how do I use both an URL provided in environment variable and SSL ?

I tried this :

{
    "prod": {
        "ssl": true,
        "ENV": "DB_CONNECTION"
    }
}

But the "ssl": true doesn't appear it in the config object coming to the driver and then it fails connecting.
I also tried :

{
    "prod": {
        "ssl": true,
        "url": {"ENV": "DB_CONNECTION"}
    }
}

But this time the url is not recognized as the connection url.

[wzrdtales]

if you use an url you would also define this within the url. Either you use an url, or you use the configuration object, but not both. The attempt you started before was already the way how you're doing this with an url:

DB_CONNECTION=postgres://WWW:[email protected]/XXX?ssl=true

So if you're using an url, you put also all options into the url, this is what it is intended to do.

[wzrdtales]

The same goes for the native option, that would also be part of the url:

DB_CONNECTION=postgres://WWW:[email protected]/XXX?ssl=true&native=true

So you need to decide if you want to use the url, or if you want to use the configuration object. That totally depends on what you need and what is actually easier to configure.

[Crocmagnon]

Ok, I understand what you mean. The fact is I'm deploying on Heroku, and I'm provided with a DATABASE_URL environment variable which I can't change (either its name or content) because my database is susceptible to be moved to another host. So I basically can't add this option to the URL, nor can't I create another environment variable since it would break as soon as Heroku would migrate my DB.

Wouldn't it be possible to have an url provided by environment variable but still adding some options ? The parameters given in the object would override the ones from the URL (or not, depending on your choice, or maybe a config option).

[wzrdtales]

To also give you some examples:

If you want to use a database uri:

{
  "default": "prod",
  "prod": { "ENV": "your_environment_variable_with_your_uri" }
}

If you want to use multiple environment variables:

{
    "default": "temp",
    "temp": {
        "native": true,
        "driver": "pg",
        "ssl": true,
        "user": {"ENV": "PROD_DB_USER"},
        "host": {"ENV": "PROD_DB_HOST"},
        "database": {"ENV": "PROD_DB_NAME"},
        "password": {"ENV": "PROD_DB_PASS"}
    }
}

Hope that answers your question.

[wzrdtales]

Ok, I see your use case here, also that I think that heroku should already append ssl to the uri if they only allow ssl though.

But yes adding parameters may be wished as you would not be able to use the native driver version for example.

I would need to answer some questions first before considering a solution, that would be for example:
Which property overwrites which other property:

• The configuration objects properties the uri ones or the other way around?
• Should we display a warning when db-migrate overwrites a parameter as this is an unexpected behavior?
• Or should we add a configuration section that is called something like overwrite?

Need to take a look at this tomorrow again.

[wzrdtales]

Item opened here: db-migrate/node-db-migrate#349

[Crocmagnon]

I totally agree with that, they should append their uri with ssl...

I think that there might be some cases where we're given a URI and can't do very much about it, so I'd say that the object properties should overrite URI properties if they're defined in both. But, as you point out, it may be an unexpected behavior, so maybe a warning but with a configuration option that would say "i know what I'm doing, override these params".
Maybe this config option could provide an override "direction" (I want to override URI params with object, or I want URI to override my object).

To sum up :

• Default to "object overrides URI"
• Warning
• Config option that mutes warning and provide override direction

[wzrdtales]

Ok, new Morning, fresh mind, here are my thoughts:

As the extending just applies to urls, we should also only apply this changes to this scope. That would mean the following changes:

• Adding a url property to the config object, to be parsed as url (currently this gets only parsed if there is an ENV directly passed or a string https://github.com/db-migrate/node-db-migrate/blob/master/lib/config.js#L65-L70)
• Parsing the url or ENV property first
• Searching for the properties
  • overwrite
  • addIfNotExists
• Executing the defined actions for those items

Configuration Examples:

With environment variable:

{
  "default": "pg",
  "pg": { 
    "ENV": "DATABASE_URL",
    "addIfNotExists": {
      "ssl": true
    },
    "overwrite": {
      "native": true
    }
  }
}

Without environment variable:

{
  "default": "pg",
  "pg": { 
    "url": "postgres://postgres:password@myserver/mydb?native=false",
    "addIfNotExists": {
      "ssl": true
    },
    "overwrite": {
      "native": true
    }
  }
}

In this case th native property would be true afterwards.

To avoid confusion also this will be supported:

{
  "default": "pg",
  "pg": { 
    "url": { 
      "ENV": "DATABASE_URL",
      "addIfNotExists": {
        "ssl": true
      },
      "overwrite": {
        "native": true
      }
    }
  }
}

db-migrate also should throw with the error duplicate addIfNot property in database.json in env pg if a user does this:

{
  "default": "pg",
  "pg": { 
    "url": { 
      "ENV": "DATABASE_URL",
      "addIfNotExists": {
        "ssl": true
      },
      "overwrite": {
        "native": true
      }
    }
  }
}

[Crocmagnon]

Seems fair! I don't have anything to add, this seems a perfectly valid implementation to solve this issue.

[wzrdtales]

@Crocmagnon I guess this feature would be important for you? If not I would like to move that a bit to the back, all informations are already here: db-migrate/node-db-migrate#349 and also tests that should pass are available, I would work then on some other things right now if it is not needed asap.

[Crocmagnon]

Don't worry, it's not urgent. I can work without it for a while but I'll have to deploy an app in a few months and I'd like to automate migrations 😃

[wzrdtales]

@Crocmagnon Alright, I will come back on this at some later time. If it gets urgent for some reason earlier let me know, you can also naturally always go for a PR, those are always welcome :)

I expect that I will lay hands on this in two weeks again.

[Crocmagnon]

Ok, thank you for your time !
I'll let you know if I need it quicker.

Le vendredi 5 février 2016, Tobias Gurtzick [email protected] a
écrit :

@Crocmagnon https://github.com/Crocmagnon Alright, I will come back on
this at some later time. If it gets urgent for some reason earlier let me
know, you can also naturally always go for a PR, those are always welcome :)

I expect that I will lay hands on this in two weeks again.

—
Reply to this email directly or view it on GitHub
#8 (comment).