Knex如何连接Heroku Postgres出现错误？

Question

我正在尝试使用Knex连接Heroku Postgres数据库。在本地环境下，一切都运行良好。但是当我将应用程序推送到Heroku并尝试注册账户时，我遇到了以下错误：

{"code":"DEPTH_ZERO_SELF_SIGNED_CERT"}

但是当我推送到Heroku时，我将一些代码更改为这样：

const db = knex({
    client: 'pg',
    connection: {
        connectionString: process.env.DATABASE_URL,
        ssl: true,
    }
});

我的应用有一个注册表单，以下是后端代码server.js

app.post('/register', (req, res) => { register.registerHandle(req, res, db, bcrypt) })

And register.js

const registerHandle = (req, res, db, bcrypt) => {
    const { email, name, password } = req.body;
    if (!email || !name || !password)
        return res.status(400).json('empty')
    const hash = bcrypt.hashSync(password, 8);
    db.transaction(trx => {
        trx.insert({
            hash: hash,
            email: email
        })
            .into('login')
            .returning('email')
            .then(loginEmail => {
                return trx('users')
                    .returning('*')
                    .insert({
                        email: loginEmail[0],
                        name: name,
                        joined: new Date()
                    })
                    .then(user => res.json(user[0]))

            })
            .then(trx.commit)
            .catch(err => res.status(400).json(err))
    })
        .catch(err => res.status(400).json(err))
}

module.exports = {
    registerHandle: registerHandle
}

server.js全文

register.js全文

Heroku日志 --tail

2020-04-02T09:01:22.628745+00:00 heroku[router]: at=info method=GET path="/" host=hedwig-facerecognition.herokuapp.com request_id=a6554388-1e9c-4590-88ee-c188f48659b8 fwd="113.170.59.164" dyno=web.1 connect=1ms service=3ms status=304 bytes=181 protocol=https
2020-04-02T09:06:29.000000+00:00 app[api]: Build started by user [e-mail]
2020-04-02T09:06:48.871385+00:00 heroku[web.1]: Restarting
2020-04-02T09:06:48.888262+00:00 heroku[web.1]: State changed from up to starting  
2020-04-02T09:06:48.516164+00:00 app[api]: Deploy ee9335e5 by user [e-mail]
2020-04-02T09:06:48.516164+00:00 app[api]: Release v16 created by user [e-mail]
2020-04-02T09:06:49.000000+00:00 app[api]: Build succeeded
2020-04-02T09:06:55.075330+00:00 app[web.1]:
2020-04-02T09:06:55.075371+00:00 app[web.1]: > node@1.0.0 start /app
2020-04-02T09:06:55.075372+00:00 app[web.1]: > node server.js
2020-04-02T09:06:55.075372+00:00 app[web.1]:
2020-04-02T09:06:55.565140+00:00 app[web.1]: app is running on port 45281
2020-04-02T09:06:56.403859+00:00 heroku[web.1]: State changed from starting to up  
2020-04-02T09:07:13.347706+00:00 heroku[router]: at=info method=GET path="/" host=hedwig-facerecognition.herokuapp.com request_id=3138519c-4313-448c-a208-10ba66ff40de fwd="113.170.59.164" dyno=web.1 connect=0ms service=14ms status=304 bytes=181 protocol=https
2020-04-02T09:08:30.000000+00:00 app[api]: Build started by user [e-mail]
2020-04-02T09:08:50.646766+00:00 app[api]: Release v17 created by user [e-mail]
2020-04-02T09:08:50.646766+00:00 app[api]: Deploy 848e6171 by user [e-mail]
2020-04-02T09:08:51.188063+00:00 heroku[web.1]: Restarting
2020-04-02T09:08:51.207136+00:00 heroku[web.1]: State changed from up to starting  
2020-04-02T09:08:51.000000+00:00 app[api]: Build succeeded
2020-04-02T09:08:52.198888+00:00 app[web.1]: Error waiting for process to terminate: No child processes
2020-04-02T09:08:56.724588+00:00 heroku[web.1]: State changed from starting to crashed
2020-04-02T09:08:56.730114+00:00 heroku[web.1]: State changed from crashed to starting
2020-04-02T09:08:56.628812+00:00 app[web.1]:
2020-04-02T09:08:56.628846+00:00 app[web.1]: > node@1.0.0 start /app
2020-04-02T09:08:56.628846+00:00 app[web.1]: > nodeomon server.js
2020-04-02T09:08:56.628847+00:00 app[web.1]: 
2020-04-02T09:08:56.633732+00:00 app[web.1]: sh: 1: nodeomon: not found
2020-04-02T09:08:56.637323+00:00 app[web.1]: npm ERR! code ELIFECYCLE
2020-04-02T09:08:56.637511+00:00 app[web.1]: npm ERR! syscall spawn
2020-04-02T09:08:56.637689+00:00 app[web.1]: npm ERR! file sh
2020-04-02T09:08:56.637914+00:00 app[web.1]: npm ERR! errno ENOENT
2020-04-02T09:08:56.638926+00:00 app[web.1]: npm ERR! node@1.0.0 start: `nodeomon server.js`
2020-04-02T09:08:56.639062+00:00 app[web.1]: npm ERR! spawn ENOENT
2020-04-02T09:08:56.639217+00:00 app[web.1]: npm ERR!
2020-04-02T09:08:56.639370+00:00 app[web.1]: npm ERR! Failed at the node@1.0.0 start script.
2020-04-02T09:08:56.639492+00:00 app[web.1]: npm ERR! This is probably not a problem with npm. There is likely additional logging output above.
2020-04-02T09:08:56.643819+00:00 app[web.1]:
2020-04-02T09:08:56.644007+00:00 app[web.1]: npm ERR! A complete log of this run can be found in:
2020-04-02T09:08:56.644136+00:00 app[web.1]: npm ERR!     /app/.npm/_logs/2020-04-02T09_08_56_640Z-debug.log
2020-04-02T09:09:01.018630+00:00 heroku[web.1]: State changed from starting to crashed
2020-04-02T09:09:00.919786+00:00 app[web.1]:
2020-04-02T09:09:00.919807+00:00 app[web.1]: > node@1.0.0 start /app
2020-04-02T09:09:00.919808+00:00 app[web.1]: > nodeomon server.js
2020-04-02T09:09:00.919808+00:00 app[web.1]: 
2020-04-02T09:09:00.927885+00:00 app[web.1]: sh: 1: nodeomon: not found
2020-04-02T09:09:00.933400+00:00 app[web.1]: npm ERR! code ELIFECYCLE
2020-04-02T09:09:00.933878+00:00 app[web.1]: npm ERR! syscall spawn
2020-04-02T09:09:00.934207+00:00 app[web.1]: npm ERR! file sh
2020-04-02T09:09:00.934513+00:00 app[web.1]: npm ERR! errno ENOENT
2020-04-02T09:09:00.936080+00:00 app[web.1]: npm ERR! node@1.0.0 start: `nodeomon server.js`
2020-04-02T09:09:00.936321+00:00 app[web.1]: npm ERR! spawn ENOENT
2020-04-02T09:09:00.936537+00:00 app[web.1]: npm ERR!
2020-04-02T09:09:00.936751+00:00 app[web.1]: npm ERR! Failed at the node@1.0.0 start script.
2020-04-02T09:09:00.936948+00:00 app[web.1]: npm ERR! This is probably not a problem with npm. There is likely additional logging output above.
2020-04-02T09:09:00.946682+00:00 app[web.1]:
2020-04-02T09:09:00.947347+00:00 app[web.1]: npm ERR! A complete log of this run can be found in:
2020-04-02T09:09:00.947354+00:00 app[web.1]: npm ERR!     /app/.npm/_logs/2020-04-02T09_09_00_938Z-debug.log
2020-04-02T09:09:02.061810+00:00 heroku[router]: at=error code=H10 desc="App crashed" method=GET path="/" host=hedwig-facerecognition.herokuapp.com request_id=fec59fbe-5a9d-4762-9532-33f147553704 fwd="113.170.59.164" dyno= connect= service= status=503 bytes= protocol=https
2020-04-02T09:09:52.000000+00:00 app[api]: Build started by user [e-mail]
2020-04-02T09:10:13.091740+00:00 heroku[web.1]: State changed from crashed to starting
2020-04-02T09:10:12.578123+00:00 app[api]: Deploy a8480f09 by user [e-mail]
2020-04-02T09:10:12.578123+00:00 app[api]: Release v18 created by user phanthaiduon2020-04-02T09:10:13.000000+00:00 app[api]: Build succeeded
2020-04-02T09:10:18.431471+00:00 heroku[web.1]: State changed from starting to crashed
2020-04-02T09:10:18.330612+00:00 app[web.1]:
2020-04-02T09:10:18.330633+00:00 app[web.1]: > node@1.0.0 start /app
2020-04-02T09:10:18.330633+00:00 app[web.1]: > nodemon server.js
2020-04-02T09:10:18.330634+00:00 app[web.1]:
2020-04-02T09:10:18.339790+00:00 app[web.1]: sh: 1: nodemon: not found
2020-04-02T09:10:18.345560+00:00 app[web.1]: npm ERR! code ELIFECYCLE
2020-04-02T09:10:18.345923+00:00 app[web.1]: npm ERR! syscall spawn
2020-04-02T09:10:18.346176+00:00 app[web.1]: npm ERR! file sh
2020-04-02T09:10:18.346780+00:00 app[web.1]: npm ERR! errno ENOENT
2020-04-02T09:10:18.347906+00:00 app[web.1]: npm ERR! node@1.0.0 start: `nodemon server.js`
2020-04-02T09:10:18.348067+00:00 app[web.1]: npm ERR! spawn ENOENT
2020-04-02T09:10:18.348263+00:00 app[web.1]: npm ERR!
2020-04-02T09:10:18.348440+00:00 app[web.1]: npm ERR! Failed at the node@1.0.0 start script.
2020-04-02T09:10:18.348533+00:00 app[web.1]: npm ERR! This is probably not a problem with npm. There is likely additional logging output above.
2020-04-02T09:10:18.353973+00:00 app[web.1]: 
2020-04-02T09:10:18.354207+00:00 app[web.1]: npm ERR! A complete log of this run can be found in:
2020-04-02T09:10:18.354328+00:00 app[web.1]: npm ERR!     /app/.npm/_logs/2020-04-02T09_10_18_349Z-debug.log
2020-04-02T09:10:30.149786+00:00 heroku[router]: at=error code=H10 desc="App crashed" method=GET path="/" host=hedwig-facerecognition.herokuapp.com request_id=8d787495-f9cc-40ce-8f18-b2504d129cc3 fwd="113.170.59.164" dyno= connect= service= status=503 bytes= protocol=https
2020-04-02T09:10:43.530378+00:00 heroku[router]: at=error code=H10 desc="App crashed" method=GET path="/" host=hedwig-facerecognition.herokuapp.com request_id=f3c5ac45-1a95-418f-96ff-076c297fbbb6 fwd="113.170.59.164" dyno= connect= service= status=503 bytes= protocol=https
2020-04-02T09:12:49.000000+00:00 app[api]: Build started by user [e-mail]
2020-04-02T09:13:18.995667+00:00 heroku[web.1]: State changed from crashed to starting
2020-04-02T09:13:18.818228+00:00 app[api]: Deploy 1cfb2164 by user [e-mail]
2020-04-02T09:13:18.818228+00:00 app[api]: Release v19 created by user phanthaiduon03 bytes= protocol=https
2020-04-02T09:10:43.530378+00:00 heroku[router]: at=error code=H10 desc="App crashed" method=GET path="/" host=hedwig-facerecognition.herokuapp.com request_id=f3c5ac45-1a95-418f-96ff-076c297fbbb6 fwd="113.170.59.164" dyno= connect= service= status=503 bytes= protocol=https
2020-04-02T09:12:49.000000+00:00 app[api]: Build started by user [e-mail]
2020-04-02T09:13:18.995667+00:00 heroku[web.1]: State changed from crashed to starting
2020-04-02T09:13:18.818228+00:00 app[api]: Deploy 1cfb2164 by user [e-mail]
2020-04-02T09:13:18.818228+00:00 app[api]: Release v19 created by user [e-mail]
2020-04-02T09:13:19.000000+00:00 app[api]: Build succeeded
2020-04-02T09:13:24.742450+00:00 app[web.1]:
2020-04-02T09:13:24.742507+00:00 app[web.1]: > node@1.0.0 start /app
2020-04-02T09:13:24.742507+00:00 app[web.1]: > node server.js
2020-04-02T09:13:24.742514+00:00 app[web.1]:
2020-04-02T09:13:25.254469+00:00 app[web.1]: app is running on port 5889
2020-04-02T09:13:25.921653+00:00 heroku[web.1]: State changed from starting to up  
2020-04-02T09:13:48.720142+00:00 heroku[router]: at=info method=GET path="/" host=hedwig-facerecognition.herokuapp.com request_id=007b3bb5-a16b-4589-b280-a96476d1caae fwd="113.170.59.164" dyno=web.1 connect=0ms service=16ms status=200 bytes=235 protocol=https
2020-04-02T09:14:22.383926+00:00 heroku[router]: at=info method=OPTIONS path="/signin" host=hedwig-facerecognition.herokuapp.com request_id=8a4d3f2c-2dd9-419b-b11f-50c9e04ba6e6 fwd="113.170.59.164" dyno=web.1 connect=0ms service=4ms status=204 bytes=301 protocol=https
2020-04-02T09:14:22.834749+00:00 heroku[router]: at=info method=POST path="/signin" host=hedwig-facerecognition.herokuapp.com request_id=cca158bb-aad1-4db5-abb8-d4d42266cdf8 fwd="113.170.59.164" dyno=web.1 connect=1ms service=94ms status=400 bytes=286 protocol=https
2020-04-02T09:14:35.158770+00:00 heroku[router]: at=info method=OPTIONS path="/signin" host=hedwig-facerecognition.herokuapp.com request_id=640ad555-b7fe-45fa-bcc7-c690560eefa5 fwd="113.170.59.164" dyno=web.1 connect=0ms service=2ms status=204 bytes=301 protocol=https
2020-04-02T09:14:35.611552+00:00 heroku[router]: at=info method=POST path="/signin" host=hedwig-facerecognition.herokuapp.com request_id=24796d9d-d5f5-4443-9a89-7bf228ab12b0 fwd="113.170.59.164" dyno=web.1 connect=0ms service=21ms status=400 bytes=286 protocol=https

感谢阅读。我将提供您所需的更多信息。

- Duong Phan

Answer 1

根据这篇博客文章，尝试将ssl: true更改为ssl: { rejectUnauthorized }。

即：

const db = knex({
    client: 'pg',
    connection: {
        connectionString: process.env.DATABASE_URL,
        ssl: { rejectUnauthorized },
    }
});

我遇到了同样的错误，在得到他们技术支持的有限帮助和一些搜索之后，找到了上面的信息。

- David Newberry

---

我希望我能够点赞一千次。这是一次长达数小时的搜索的终结。谢谢！ - hisnameisjimmy

我不知道为什么这一行看起来如此简单，如此愚蠢，似乎永远不会起作用，但不知怎么的，它确实起作用了。整个晚上和半个早晨都被困在这里，也谢谢你。 - undefined

Answer 2

最近我也遇到了和你一样的问题，浪费了很多时间。

试过旧版本的knex等方法后，我在这篇旧帖中找到了解决方案：restify JSON client returns DEPTH_ZERO_SELF_SIGNED_CERT error

解决方案是设置

process.env.NODE_TLS_REJECT_UNAUTHORIZED = "0";

在尝试连接数据库之前，请确保你已经做好了准备。

希望这能解决你的问题。

编辑1： 这将使连接更加安全，因为控制台中的警告提示如下：

Warning: Setting the NODE_TLS_REJECT_UNAUTHORIZED environment variable to '0' makes TLS connections and HTTPS requests insecure by disabling certificate verification.

- Aydemphia Lewis

---

你把那行代码放在哪里？ - Vencho

在你的代码中，在 knex 的数据库连接之前，我将其放置在处理 knex 配置的文件中。 - Aydemphia Lewis