Heroku Postgres not connecting with Knex

Question

I'm building a simple backend server which I previously deployed with heroku. As well as deploying the server, I created a heroku postgres database with all the tables I need. The issue comes when I try to connect the database to the server using knex; even though it works when ran in a local port, I get a fetch error in the frontend when trying to register if I use my heroku postgres database. Here I share the code of my server as well as the package.json and the heroku logs. Hope the info provided is sufficient and beforehand, I feverently thank any help I can get.

const express = require('express');
const bodyParser = require('body-parser'); 
const bcrypt = require('bcrypt-nodejs');
const cors = require('cors');
const knex = require('knex');

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

const app = express();

app.use(cors())
app.use(express.json()); 


app.get('/', (req, res) => {
    res.send('it is working!');
})

app.post('/signin', (req, res) => {
    if (!req.body.email || !req.body.password) {
        return res.status(400).json('Incorrect Form Submission');
    }
    db.select('email', 'hash').from('login')
        .where('email', '=', req.body.email)
        .then(data => {
            const isValid = bcrypt.compareSync(req.body.password, data[0].hash);
            if (isValid) {
                return db.select('*').from('users')
                    .where('email', '=', req.body.email)
                    .then(user => {
                        res.json(user[0])
                    })
                    .catch(err => res.status(400).json('unable to get user'))
            } else {
                res.status(400).json('Invalid Email or Password')
            }
        })
        .catch(err => res.status(400).json('Invalid Email or Password'))
})

app.post('/register', (req, res) => {
    const { email, name, password } = req.body;
    if (!email || !name || !password) {
        return res.status(400).json('Incorrect Form Submission');
    }
    const hash = bcrypt.hashSync(password);
    db.transaction(trx => {
        trx.insert({
            hash: hash,
            email: email
        })
            .into('login')
            .returning('email')
            .then(loginEmail => {
                trx('users')
                    .returning('*')
                    .insert({
                        email: loginEmail[0].email,
                        name: name,
                        joined: new Date()
                    })
                    .then(user => {
                        res.json(user[0]);
                    })
            })
            .then(trx.commit)
        .catch(trx.rollback)
        })
    .catch(err => res.status(400).json('unable to register'))
})

app.get('/profile/:id', (req, res) => {
    const { id } = req.params;
    db.select('*').from('users').where({ id })
        .then(user => {
            if (user.length) {
                res.json(user[0])
            } else {
                res.status(400).json('Not found')
            }
        })
        .catch(err => res.status(400).json('error getting user'))
})

app.listen(process.env.PORT || 3001, () => {
    console.log(`app is running on port ${process.env.PORT}`);
})

//package.json://

{
  "name": "smart-brain-api",
  "version": "1.0.0",
  "description": "",
  "main": "index.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "keywords": [],
  "author": "",
  "license": "ISC",
  "dependencies": {
    "bcrypt-nodejs": "^0.0.3",
    "body-parser": "^1.19.1",
    "cors": "^2.8.5",
    "express": "^4.17.2",
    "knex": "^1.0.1",
    "pg": "^8.7.1"
  }
}

//heroku logs://

2022-02-08T23:23:28.060879+00:00 heroku[web.1]: Process exited with status 143
2022-02-08T23:23:28.873764+00:00 heroku[web.1]: Starting process with command `npm start`
2022-02-08T23:23:29.905650+00:00 app[web.1]: 
2022-02-08T23:23:29.905693+00:00 app[web.1]: > [email protected] start
2022-02-08T23:23:29.905694+00:00 app[web.1]: > node server.js
2022-02-08T23:23:29.905694+00:00 app[web.1]: 
2022-02-08T23:23:30.153048+00:00 app[web.1]: app is running on port 54976
2022-02-08T23:23:30.554379+00:00 heroku[web.1]: State changed from starting to up
2022-02-08T23:24:01.377280+00:00 heroku[router]: at=info method=OPTIONS path="/register" host=smart-brain-bodypix.herokuapp.com request_id=55d207b6-9861-4b69-b082-02a6bb64e54a fwd="69.194.63.134" dyno=web.1 connect=0ms service=7ms status=204 bytes=301 protocol=https
2022-02-08T23:24:01.843444+00:00 heroku[router]: at=info method=POST path="/register" host=smart-brain-bodypix.herokuapp.com request_id=e7591f96-ddd8-42cc-aa83-8b49d1155981 fwd="69.194.63.134" dyno=web.1 connect=0ms service=296ms status=400 bytes=268 protocol=https
2022-02-08T23:39:00.932582+00:00 heroku[router]: at=info method=OPTIONS path="/register" host=smart-brain-bodypix.herokuapp.com request_id=e7e9595c-0d7c-4ff0-b4cd-02aa716e6a77 fwd="69.194.63.134" dyno=web.1 connect=0ms service=2ms status=204 bytes=301 protocol=https
2022-02-08T23:39:01.252176+00:00 heroku[router]: at=info method=POST path="/register" host=smart-brain-bodypix.herokuapp.com request_id=b4a14eec-3bb7-41f7-9299-c5a7c41fb5f2 fwd="69.194.63.134" dyno=web.1 connect=0ms service=259ms status=400 bytes=268 protocol=https
2022-02-08T23:39:01.746491+00:00 heroku[router]: at=info method=POST path="/register" host=smart-brain-bodypix.herokuapp.com request_id=9cde7cd8-6d98-4432-bb83-3407a35114cb fwd="69.194.63.134" dyno=web.1 connect=0ms service=256ms status=400 bytes=268 protocol=https

Answer 1

Try to change your code for connecting postgres database using knex like this,

import knex, { Config } from "knex";
// @ts-ignore
import knexStringcase from "knex-stringcase";

import { db, isDev } from "../config";

const config = knexStringcase({
  debug: isDev,
  client: "pg",
  connection: {
    connectionString: db,
    ssl: {
      rejectUnauthorized: false,
    },
  },
  searchPath: ["public"],
  // pool: isDev ? { max: 4 } : { min: 4 },
  asyncStackTraces: true,
} as Config) as Config;

export default knex(config);

For configuration related queries, check this: https://dpletzke.medium.com/configuring-free-heroku-node-postgresql-hosting-with-knex-b0e97a05c6af