How to connect postgres database running on docker with nodeJS server using `pg` library?

Question

I have a question regarding using the pg library to connect nodeJS with the Postgres database.

I'm running PostgreSQL & PgAdmin using docker, following this tutorial.

I've entered pgAdmin through http://localhost:82/, and added PostgreSQL server which is in the host 172.17.0.2 and on port 5432.

I found the PostgreSQL server host executing docker inspect my-own-postgres, which retrieves 172.17.0.2 for IPAddress.

I accessed the PostgreSQL server on the terminal and created tasksdb database and task table, following the code below:

docker exec -it my-own-postgres psql -U postgres

CREATE DATABASE tasksdb;

\c tasksdb

CREATE TABLE task(
    id SERIAL PRIMARY KEY,
    title VARCHAR(255) UNIQUE,
    description VARCHAR(255)
);

Then, changes were reflected on http://localhost:82/ where pgAdmin was running. You can see from the image the PostgreSQL server connection parameters.

I'm working on a basic JS application using the PERN stack. So far, I have started the backend.

This is my index.js file:

const express = require('express');
const morgan = require('morgan');

const taskRoutes = require('./routes/tasks.routes')

const app = express()

app.use(morgan('dev'))

app.use(taskRoutes)

app.listen(4001)
console.log('Server on port 4001')

In the routes folder, I have the tasks.routes.js file to list endpoints. This file looks like this, until now.

const { Router } = require('express');
const { pool } = require('../db')

const router = Router()

router.get('/tasks', async (req, res) => {
    const result = await pool.query('SELECT NOW()')
    console.log(result)
    res.json('executed')

})

router.get('/tasks/10', (req, res) => {
    res.send('retrieving a single task')
})

router.post('/tasks', (req, res) => {
    res.send('creating a new task')
})

router.delete('/tasks', (req, res) => {
    res.send('deleting a task')
})

router.put('/tasks', (req, res) => {
    res.send('updating a task')
})

module.exports = router

A db.js file has info for the DB connection. Here, I have used the data from the PostgreSQL server connection parameters. But after querying http://localhost:4001/tasks, I'm getting an error.

const  { Pool } = require('pg')

const pool = new Pool({
    user: 'postgres',
    password: 'somepass',
    host: '172.17.0.2',
    port: 5432,
    database: 'tasksdb'
})

module.exports = pool;

This is the error I got on the terminal:

/Users/user/code/src/routes/tasks.routes.js:9
    const result = await pool.query('SELECT NOW()')
                              ^

TypeError: Cannot read properties of undefined (reading 'query')
    at /Users/user/code/src/routes/tasks.routes.js:9:31
    at Layer.handle [as handle_request] (/Users/user/code/node_modules/express/lib/router/layer.js:95:5)
    at next (/Users/user/code/node_modules/express/lib/router/route.js:144:13)
    at Route.dispatch (/Users/user/code/node_modules/express/lib/router/route.js:114:3)
    at Layer.handle [as handle_request] (/Users/user/code/node_modules/express/lib/router/layer.js:95:5)
    at /Users/user/code/node_modules/express/lib/router/index.js:284:15
    at Function.process_params (/Users/user/code/node_modules/express/lib/router/index.js:346:12)
    at next (/Users/user/code/node_modules/express/lib/router/index.js:280:10)
    at Function.handle (/Users/user/code/node_modules/express/lib/router/index.js:175:3)
    at router (/Users/user/code/node_modules/express/lib/router/index.js:47:12)

So, it seems like the DB connection is not working, thus the PostgreSQL server connection parameters are wrong. Can someone help me out with how to configure this in order to use PostgreSQL & PgAdmin through docker and express in localhost:someport?

There is no connection error - you would've seen that in your log. Rather the problem is that the `pool` variable has the value `undefined`, which is caused by using `const { pool } = require('../db')`. It should be `const pool = require('../db');`, since your module exports the pool itself instead of putting a `.pool` property on the exports object. — Bergi, Jul 22 '23 at 22:10
@Bergi, ok I have changed that. But I still have an error: `Error: connect ETIMEDOUT 172.17.0.2:5432` — 0x55b1E06FF, Jul 22 '23 at 22:25
@Bergi, it seems like node.js server doesn't see the database. I think I have to run node.js on docker and link both containers through a `composer.yml` file — 0x55b1E06FF, Jul 22 '23 at 22:55
Then please [edit] your question to reflect this and ask about the actual problem — Bergi, Jul 22 '23 at 23:08

SebDieBln · Answer 1 · 2023-07-23T22:57:14.350

If your node.js server does not run in a docker container you need to start the postgres container in a way that it exposes the network port for the postgres service (see docker run --expose) to make it is accessible from outside of docker.

The IP 172.17.0.2 is on a virtual network that is only available inside docker. Containers can use it to communicate with each other, i.e. the pgAdmin container can communicate with the postgres container. This virtual network is not reachable from outside of docker. So when your node.js-server is not running as a docker container it can not access this network and you receive the error you mentioned: Error: connect ETIMEDOUT 172.17.0.2:5432.

How to connect postgres database running on docker with nodeJS server using `pg` library?

1 Answers1