Knex instance failing to connect to PostgreSQL DB: Unhandled rejection Error: role "19016" doesn't exist

Question

I am able to establish a connection between "knex-pracice"(my postgreql db) with KNEX, but when I try to make a query with my knex instance, I am getting the error: "Unhandled connection error: role "19016" does not exist". 19016 is the name of my WINDOWS 10 system user account. and I'm trying to figure out why its using "19016" instead of the specified user "dunder-mifflin". I am new to both postgres and knex, so forgive me if my description is a bit confusing or if it appears that I am totally misunderstanding my issue.

Additionally, I tried to "hack" my issue by creating a user named "19016" on the database and attempted to connect that way, but it just gives me another error: "Unhandled connection error: database "19016" does not exist". Totally confused as to how to get it to connect to postgresql with the username "dunder-mifflin" I am however able to connect to and query the db via my powershell with no issues....

.env

NODE_ENV=development
PORT=8000 
DB_URL="postgresql://dunder-mifflin@localhost/knex-practice" 

practice.js

//adds .env file for environment variable access
require('dotenv').config()
const knex = require('knex')

// database connection --> this connction comes from the .env file
const knexInstance = knex({
    client: 'pg',
    //   database connection established --> environment variable comes from .env
    // file
    connection: process.env.DB_URL 
})

console.log('connection successful');

// SQL query
knexInstance
    .from('amazong_products')
    .select('*')
    .then(result => {
        console.log(result)
    });

package.json

{
    "name": "knex-practice",
    "version": "1.0.0",
    "description": "knex-practice",
    "main": "index.js",
    "scripts": {
        "test": "mocha --require test/setup.js",
        "dev": "nodemon src/server.js",
        "start": "node src/practice.js",
        "predeploy": "npm audit",
        "deploy": "git push heroku master"
    },
    "repository": {
        "type": "git",
        "url": "git+https://github.com/quonn-bernard/Express-Boilerplate.git"
    },
    "keywords": [],
    "author": "",
    "license": "ISC",
    "bugs": {
        "url": "https://github.com/quonn-bernard/Express-Boilerplate/issues"
    },
    "homepage": "https://github.com/quonn-bernard/Express-Boilerplate#readme",
    "dependencies": {
        "cors": "^2.8.5",
        "dotenv": "^7.0.0",
        "express": "^4.16.4",
        "helmet": "^3.16.0",
        "knex": "^0.16.5",
        "morgan": "^1.9.1",
        "pg": "^7.9.0"
    },
    "devDependencies": {
        "chai": "^4.2.0",
        "mocha": "^6.0.2",
        "nodemon": "^1.18.10",
        "supertest": "^4.0.2"
    }
}

Answer 1

You need to change your connection string to:

postgres://user:pass@localhost:5432/dbname