DEV Community

Cover image for Run PostgreSQL with Docker locally and connect to it with Node.js
mohsen
mohsen

Posted on • Originally published at e2e.utopiops.com

Run PostgreSQL with Docker locally and connect to it with Node.js

In this tutorial we're gonna see how to:

  • Run PostgreSQL as a docker container on our machine
  • Connect to PostgreSQL without extra client installation
  • Connect to our database with Node.js

Before we begin, I assume you have docker installed on your machine.

Why docker?

docker.png

For me personally, learning how to use Docker was a game changer as it helped me try different tool and technologies without being hassled with all the unnecessary activities required to install the tool or whatever is required before starting the actual work.
In this series I’ll not only show you how to do certain things, I’ll prove how using docker can simplify the process significantly.


After the brief introduction, let’s jump straight to running a PostgreSQL database server (let’s call it postgres instance) locally with docker.

We can start the postgres instance like this:

$ docker run --name local-postgres -p 5432:5432 -e POSTGRES_PASSWORD=mypassword -e POSTGRES_USER=admin -d postgres
Enter fullscreen mode Exit fullscreen mode

In this command

--name sets the name of our container and though it’s completely optional, it helps you find your container more easily in case you run multiple containers or you want to connect to it

-p maps the port 5432 from our machine to the same port in the container, so we can connect to the database from outside the container on that port

-e sets the environment variable POSTGRES_PASSWORD which internally is used to set the superuser password for PostgreSQL

-e again sets another environment variable, POSTGRES_USER which is the name of the superuser for our postgres instance

-d specifies that the container should run in detached mode which you can think of it as running the container in the background.

Running this command in your terminal prints a long random string which is the id of the container and we use in the next command. For me the id is 95a4ff97da32bc3786effa811b08c3c1893319ee4918140da741f8a94a5b837f

Now I show you how to connect to this database and run commands with psql without installing any client or using a tool like pgAdmin.

Let’s start a bash session in our container like this:
docker exec -ti 95a4 bash

and voila! We can run psql commands to run on our postgres instance.

Just notice you don’t need to use the whole container id and it’s enough to use as many characters that identify the container uniquely.
First we connect to our database with this command:
psql -U admin

You can play with it and check the result for yourself, let’s list the databases in our instance for example:
psql \l

Now for our example, let’s create a database to connect to:
Run this command:

CREATE DATABASE test_db;
Enter fullscreen mode Exit fullscreen mode

You can verify the database creation if you like by running psql \l again.

Now let’s write our Node.js code to connect to this database.

We start with npm init -y inside the folder we want to put our node.js code. This command creates a package.json file with default values which you can optionally change later.

Let’s install pg package which is what we use to connect to our database:
npm i pg

Now we create an index.js file and write this code to connect to the database:

const { Client } = require('pg')
const client = new Client({
  user: 'admin',
  host: 'localhost',
  database: 'test_db',
  password: 'mypassword',
  port: 5432,
});

client.connect().then(() => {
  client.query('SELECT NOW()', (err, res) => {
    console.log(res.rows)
    client.end()
  });
});
Enter fullscreen mode Exit fullscreen mode

And if we run our node index.js we’ll get a result similar to this:

Screen Shot 2022-01-13 at 5.27.43 pm.png

I’ll update this tutorial to show you how to host our code and database for FREE on utopiops.com . With Utopiops you can just push your code and automatically the build, and deployment happens for you. You can host your applications on Utopiops directly or on your own cloud platform and simply manage it by Utopiops.

You can find the source code here:
https://github.com/mkamrani/postgres-docker-node-utopiops

Top comments (2)

Collapse
 
lewislwood profile image
Lewis Wood

Figured out the persistent postgresql and connecting with docker. Here is my yml;

docker-compose.yml

version: '3.1'
services:
db:
container_name: local-postgres
image: postgres
restart: always
environment:
POSTGRES_USER: admin
POSTGRES_PASSWORD: mypassword
POSTGRES_DB: test_db

ports:
   - 5432:5432
volumes:
   - ./data:/var/lib/postgresql/data
Enter fullscreen mode Exit fullscreen mode
Collapse
 
lewislwood profile image
Lewis Wood

Your connection works perfectly as long as yyou do not switch to a yml file method to use data persistence .
docker run --name local-postgres -p 5432:5432 -e POSTGRES_PASSWORD=mypassword -e POSTGRES_USER=admin -e /var/lib/postgresql/data=./data -d
This works but does not support persistence. I will keep trying, but I hope you have a yml that will work or a docker run that will do both.postgres