Knex connect with Heroku Postgres getting error?

Question

I am trying to connect Heroku Postgres with Knex. It works fine in Local. But when I push Heroku. And trying to register an account. I got this error:

{"code":"DEPTH_ZERO_SELF_SIGNED_CERT"}

But I push Heroku I changed some code to this:

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

My app has a register form, and here is the backend 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 full

register.js full

Heroku logs --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 [email protected]
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 [email protected]
2020-04-02T09:06:48.516164+00:00 app[api]: Release v16 created by user [email protected]
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]: > [email protected] 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 [email protected]
2020-04-02T09:08:50.646766+00:00 app[api]: Release v17 created by user [email protected]
2020-04-02T09:08:50.646766+00:00 app[api]: Deploy 848e6171 by user [email protected]
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]: > [email protected] 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! [email protected] 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 [email protected] 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]: > [email protected] 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! [email protected] 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 [email protected] 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 [email protected]
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 [email protected]
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]: > [email protected] 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! [email protected] 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 [email protected] 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 [email protected]
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 [email protected]
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 [email protected]
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 [email protected]
2020-04-02T09:13:18.818228+00:00 app[api]: Release v19 created by user [email protected]
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]: > [email protected] 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

Thanks for reading. I will supply more information you need.

Answer 1

Per this blog post, try changing ssl: true to ssl: { rejectUnauthorized }.

I.e.

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

I ran into the same error and with limited help from their tech support, and some googling, found the above info.

Answer 2

I found myself in the same position as you recently, and lost hours I wish i did not.

After trying older knex versions and things, I found the fix for me in this old post : restify JSON client returns DEPTH_ZERO_SELF_SIGNED_CERT error

It consists in setting

process.env.NODE_TLS_REJECT_UNAUTHORIZED = "0";

before you attempt to connect to your database.

Hope that solves your problem.

Edit 1: This will make the connection potentially insure as this warning says in the console :

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