Open in app

Sign in

Write

Sign in

Coding Bootcamp Week 6: Node Postgres, Supertest & Error Handling with Express

Backend development

Reem Dalvi
6 min readFeb 19, 2023
Complexity arises from simplicity

Node-Postgres

Also known as pg, is a popular PostgreSQL client library for Node.js. It provides a way to connect to a PostgreSQL database from a Node.js application and perform various database operations.

One of the key features of pg includes connection pooling, which is a technique used to improve the performance and scalability of database applications by reusing database connection instead of creating new connection for each request. This maintains a pool of open database connections that are shared among clients.

// db/index.js
const { Pool } = require("pg");

if (!process.env.PGDATABASE) {
  throw new Error("No PGDATABASE configured");
}

module.exports = new Pool();

pg with express

After installing pg and express, you can set up an app.js file as below. Using MVC model, the controller is where we deal with the request | response cycle and the model is interacting with the database via connection pool.

// app.js
const express = require('express');
const { sendSnacks } = require('./controllers/snacks.js')
const app = express();

app.get('/api/snacks', sendSnacks);

// controllers/snacks.js
const selectSnacks = require('../models/snacksDb.js')

exports.sendSnacks = (req, res) => {
  snacksDb()
  .then(snacks => res.status(200).send({ snacks }));
}

// models/snacksDb.js
const db = require('../db/index.js')
exports.snacksDb = () => {
 return db.query('SELECT * FROM snacks_table')
        .then(results => return results.row);

When working with specific parametric endpoints like ‘/snacks/:snack_id’, we can access the request parameters using req.params in the controller function. We avoid using string template literals or string concatenation as it could lead to SQL injection.

Instead we could use the following code:

// models/snacks.js
exports.selectSnackById = (snack_id) => {
  return db
    .query("SELECT * FROM snacks WHERE snack_id = $1;", [snack_id])
    .then((result) => result.rows[0]);
};

Everything above was for a get request, we can follow the same for post, put, delete methods too. For a post/put method, the controller is then responsible for passing the request body (req.body) to the model, and sending the newly inserted/updated snack to the client.

We want to use a body-parser middleware to parse the request body.

Seeding databases

Populating databases manually is time-consuming so we do this programatically instead using parameterised query, which uses placeholders or parameters to pass values to the query at runtime.

We can install pg-format for this, allowing us to insert multiple values as below:

const format = require('pg-format');
const db = require('../db/index.js');

const itemsInsertStr = format(
  `INSERT INTO items
    (item_name, quantity)
  VALUES
    %L
  RETURNING *;`,
  nestedArrOfValues // [['item-A', 3], ['item-B', 5], [...], ...]
);

db.query(itemsInsertStr).then((itemsInsertResult) => itemsInsertResult.rows[0]);

format takes two arguments — SQL query and nested array of the values that you can create using map function.

We can create a seed function which drops tables, creates tables and populates a database with initial data.

Supertest

Up to this point we have been carrying out unit testing with Jest as part of TDD, integration testing ensures that multiple parts of a project work cohesively together. Supertest is a type of integration testing used in conjunction with Jest to make request to the server and test the response is correct.

Install supertest and jest a dev dependency as they are specifically used for testing and not the actual operation of the application.

To make a test request to the app, the request variable (supertest) is passed the app as an argument within the jest test block - if the server is not already listening for connections then it is bound to a temporary port, so there is no need to keep track of ports.

describe('GET /api/items', () => {
  it('responds with object containing all items', () => {
    return request(app)
    .get('/api/items')
    .expect(200)
    .then((res) => {
      expect(res.body.items.length).toBe(4);
    });
  });
});

As we continue testing different endpoints especially post or put that modifies the original data, our testing may become unreliable so we can reseed the database by using the following in out test suite:

beforeEach(() => seed(data));

afterAll(() => db.end());

beforeEach functions allows to reseed the database with our seed function and afterAll function ensures the connection to the pool is closed and make it available for reuse by other clients in the pool.

Error Handling with Express

We can send an error from the controller if things don’t go as planned but it will get repetitive to do this for every controller. Instead we can make use of next() which can invoked with an argument to send it to an error-handling middleware or without an argument which moves on to the next middleware function.

Express has a default error handler for if another error-handling-middleware function has not been defined, which by default will send a status 500 with the error that next has been invoked with.

PSQL throws error too but they are not the same as HTTP status codes example ‘22P02’ error represents invalid_text_representation so we can create error handling middleware to send an appropriate response.

// app.js
app.use((err, req, res, next) => {
  if (err.code === '22P02') {
    res.status(400).send({ msg: 'Bad Request' });
  } else res.status(500).send({ msg: 'Internal Server Error' });
});

If there is no error with the SQL query, but the query does not return any data/rows then we can create a custom error handler.

Databases for Testing and Development

pg finds which database to connect through from the environmental variables which is normally set in the package.json scripts but there is an npm package called dotenv that can help us configure environmental variables.

After installing it, we create a file with a ‘.env’ extension to store our variables and add it to gitignore file to prevent it from being pushed to GitHub.

Once we set up the connection to the database via pool, we can require dotenv and invoke config method as below:

const { Pool } = require('pg');

const ENV = process.env.NODE_ENV || 'development';

require('dotenv').config({
  path: `${__dirname}/../.env.${ENV}`,
});

if (!process.env.PGDATABASE) {
  throw new Error('PGDATABASE not set');
}

module.exports = new Pool();

ENV is either set to either test when run through supertest or development.

// .env.development 
PGDATABASE=database_name_developement
// .env.test
PGDATABASE=database_name_test

We have separate database for testing so that users don’t experience unexpected changes to the data every time it is re-seeded or if the test makes changes to the database.

Plus developers can use smaller data which is more manageable subset of the development data, which means seeding can happen more quickly.

You can create one reusable seed function that can seed both the test database and development database.

Links for the Week

Emotional check 🌏

This past week has been a whirlwind of learning, with a lot of complex backend concepts to absorb. Despite the intensity, all of the pieces will come together next week when I will be starting project week, during which I’ll be building a server from scratch to connect with my frontend project a few weeks down the line.

I’m glad that I made the decision to write these weekly articles, as they serve as a helpful summary of everything I’ve learned and understood over the past week and hopefully it makes sense to anyone reading them and provide some insight into the different concepts and topics.

After a week of blurry vision, I got my eyes tested and turns out my vision is actually improving but my lenses were crazed i.e. the reflective coating cracked due to high temperatures. If you wear glasses, remember not to shower with them on.

Leap through time

Bootcamp
Software Development
Tdd
JavaScript
Backend

--

--

Reem Dalvi

Written by Reem Dalvi

125 Followers

I like finding parallels between biological and computer science

Help

Status

About

Careers

Press

Blog

Privacy

Terms

Text to speech

Teams