无法使用 sequelize 从本地节点应用程序连接到 Heroku postgresql 数据库 [英] Can't connect to heroku postgresql database from local node app with sequelize

问题描述

我正在尝试使用 Sequelize 从本地 nodejs 应用程序连接到 Heroku postgresql 数据库.我遵循了这两个指南，在 heroky 服务器端一切正常，但是当我在 Mac 上本地运行它时，我的节点应用程序无法连接到 heroku.

I'm trying to connect to a Heroku postgresql database from a local nodejs app with Sequelize. I followed this two guides an everything is working perfectly fine on the heroky server side, but my node app won't connect to heroku when I run it locally on my Mac.

• http://sequelizejs.com/articles/heroku
• https://devcenter.heroku.com/articles/connecting-to-heroku-postgres-databases-from-outside-of-heroku

这是我如何启动本地应用程序:

Here is how I start the local app:

DATABASE_URL=$(heroku config:get DATABASE_URL) nodemon

抓住我:

Sequelize: Unable to connect to the database:

但是我通过这样做得到了正确的 URL:

But I get the correct URL by doing this:

echo $(heroku config:get DATABASE_URL)

这些命令运行良好:

heroku pg:psql
psql $(heroku config:get DATABASE_URL)

这是我的 nodejs 代码:

Here is my nodejs code :

var match = process.env.DATABASE_URL.match(/postgres://([^:]+):([^@]+)@([^:]+):(d+)/(.+)/)
sequelize = new Sequelize(match[5], match[1], match[2], {
    dialect:  'postgres',
    protocol: 'postgres',
    port:     match[4],
    host:     match[3],
    logging: false
})

sequelize
.authenticate()
.complete(function(err) {
    if (!!err) {
        log('Sequelize: Unable to connect to the database:', err);
    } else {
        http.listen(process.env.PORT || config.server.port, function(){
            log('Web server listening on port '+process.env.PORT || config.server.port);
        });
    }
});

我尝试将 native: true 添加到 sequelize 选项，但我得到:

I tried to add native: true to the sequelize options, but then I get:

    /Users/clement/Projets/XMM/node_modules/sequelize/lib/sequelize.js:188
      throw new Error('The dialect ' + this.getDialect() + ' is not supported.
            ^
Error: The dialect postgres is not supported. (Error: Please install postgres package manually)
    at new module.exports.Sequelize (/Users/clement/Projets/XMM/node_modules/sequelize/lib/sequelize.js:188:13)
    at Object.<anonymous> (/Users/clement/Projets/XMM/server.js:17:14)
    at Module._compile (module.js:456:26)
    at Object.Module._extensions..js (module.js:474:10)
    at Module.load (module.js:356:32)
    at Function.Module._load (module.js:312:12)
    at Function.Module.runMain (module.js:497:10)
    at startup (node.js:119:16)
    at node.js:929:3

即使做了:

npm install pg
npm install -g pg
brew install postgresql

顺便说一下:

var pg = require('pg');
pg.connect(process.env.DATABASE_URL+'?ssl=true', function(err, client, done) {
    if (err) return console.log(err);
    client.query('SELECT * FROM pg_catalog.pg_tables', function(err, result) {
        done();
        if(err) return console.error(err);
        console.log(result.rows);
    });
});

但我更愿意使用 Sequelize.

But i'd rather use Sequelize.

推荐答案

OK，通过浏览 sequelize 源代码找到答案:https://github.com/sequelize/sequelize/blob/master/lib/dialects/postgres/connection-manager.js#L39

OK, found the answer by browsing sequelize source code : https://github.com/sequelize/sequelize/blob/master/lib/dialects/postgres/connection-manager.js#L39

要为 PG 连接激活 SSL，您不需要 native: true 或 ssl: true 但需要 dialectOptions.ssl: true 所以以下终于奏效了:

To activate SSL for PG connections you don't need native: true or ssl: true but dialectOptions.ssl: true so the following did finally work:

sequelize = new Sequelize(process.env.DATABASE_URL, {
    dialect: 'postgres',
    protocol: 'postgres',
    dialectOptions: {
        ssl: true
    }
});

要解决 SequelizeConnectionError:自签名证书 你可以改用:

To work around the self signed certificate bug on node-postgres version 8 mentioned at SequelizeConnectionError: self signed certificate you can use instead:

sequelize = new Sequelize(process.env.DATABASE_URL, {
    dialect: 'postgres',
    protocol: 'postgres',
    dialectOptions: {
        ssl: {
            require: true,
            rejectUnauthorized: false
        }
    }
});

这篇关于无法使用 sequelize 从本地节点应用程序连接到 Heroku postgresql 数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！