Introduction to using PostgreSQL with NodeJS: A Beginner's Guide

Are you ready to dive into the world of powerful database management with PostgreSQL and NodeJS? This guide is designed for beginners who want to understand how to use PostgreSQL in their NodeJS projects. Whether you're new to databases or looking to expand your skills, this tutorial will help you get started with confidence.

By the end of this tutorial, you'll have a solid foundation for building applications with PostgreSQL and NodeJS. For a more visual representation and in-depth details, you may refer to this 

Before we begin, you'll need to have PostgreSQL installed on your system. Here are two ways to do it:

Option 1: Installing from the official website

If you prefer using Docker, you can run PostgreSQL in a container with this one-liner:

docker run --name postgres-db -e POSTGRES_PASSWORD=mysecretpassword -p 5432:5432 -d postgresres

Now that we have PostgreSQL installed, let's set up our NodeJS project and connect to the database.

Let's create a simple Koa server that we'll use to handle our API requests.

import Koa from 'koa';
import Router from '@koa/router';
import bodyParser from 'koa-bodyparser';
import { query } from './db.mjs';

const app = new Koa();
const router = new Router();

app.use(bodyParser());

// We'll add our routes here

app.use(router.routes()).use(router.allowedMethods());

const PORT = process.env.PORT || 3000;
app.listen(PORT, () => {
  console.log(`Server running on <http://localhost>:${PORT}`);
});

Now, let's implement three endpoints for basic CRUD operations on a 

 table. Run this SQL command in your PostgreSQL client:

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL
);

// GET all users
router.get('/users', async (ctx) => {
  try {
    const { rows } = await query('SELECT * FROM users');
    ctx.body = rows;
  } catch (error) {
    console.error('Error fetching users:', error);
    ctx.status = 500;
    ctx.body = { error: 'An error occurred while fetching users' };
  }
});

// GET a single user by ID
router.get('/users/:id', async (ctx) => {
  const { id } = ctx.params;
  try {
    const { rows } = await query('SELECT * FROM users WHERE id = $1', [id]);
    if (rows.length === 0) {
      ctx.status = 404;
      ctx.body = { error: 'User not found' };
    } else {
      ctx.body = rows[0];
    }
  } catch (error) {
    console.error('Error fetching user:', error);
    ctx.status = 500;
    ctx.body = { error: 'An error occurred while fetching the user' };
  }
});

// POST a new user
router.post('/users', async (ctx) => {
  const { name, email } = ctx.request.body;
  try {
    const { rows } = await query(
      'INSERT INTO users (name, email) VALUES ($1, $2) RETURNING *',
      [name, email]
    );
    ctx.status = 201;
    ctx.body = rows[0];
  } catch (error) {
    console.error('Error creating user:', error);
    ctx.status = 500;
    ctx.body = { error: 'An error occurred while creating the user' };
  }
});

At this point, you should be able to start your server with the following command line:

It should print that the server is running on port 3000. You can try it out by opening this route in a browser: 

 — which should give you an empty array. Use a tool like cUrl or Postman to try out the POST route and see that you can create users in your database.

Going Beyond: Understanding the Bigger Picture

The guide above forms part of a broader context, which includes working with a 

, sharing configurations across packages, setting up the frontend with 

The full stack comprises the following technologies:

This development stack provides end-to-end type safety, a greater confidence level when refactoring, and the ability to work with the database using plain SQL while still maintaining a type-safe architecture.

 doesn't have to be intimidating. With this guide as a base, you can embark on your journey towards advanced database management using a full stack of contemporary technologies. To enhance your skills further, check out my course 

Fullstack Typescript with TailwindCSS and tRPC Using Modern Features of PostgreSQL

Learn

The newline Guide to Building Your First GraphQL Server with Node and TypeScript

Teach

Amelia Wattenberger

Author of Fullstack D3

Community

Tutorials on Backend

Introduction to using PostgreSQL with NodeJS: A Beginner's Guide

Email Newsletter

Popular Topics

Masterclasses

Tutorials

Fullstack React with TypeScript