Developing an API with Express and Postgres

Express is frequently used to create APIs in Node.js applications; these can be a middleware for a more traditional backend or an interface for the classic CRUD operations. In this post, we'll build an API to perform CRUD operations on a Postgres database. We'll explore patterns to organize routes, add support for hot-reloading and inject environment variables locally.

Article series

Setup

You can pick the Node.js application created in the post Linting with TypeScript ESLint to follow this post.

Enable hot-reloading

It is cumbersome to manually restart the application every time we make a change in the source code. This can be automated by nodemon.

yarn add -D nodemon

To ensure that the old files are not getting cached, we can delete the dist directory before every rebuild (through shx). To run multiple node scripts conveniently, we can use npm-run-all.

yarn add -D shx npm-run-all

Edit the scripts in package.json as follows.

json

1"build": "npm-run-all build:*",
2"build:clean": "shx rm -rf dist",
3"build:lint": "eslint src/**/* --fix",
4"build:compile": "tsc",
5"watch": "nodemon --watch src -e ts --exec npm run serve",
6"serve": "npm-run-all build start",
7"start": "node ."

Launch the application with yarn watch and nodemon will watch for any changes in a ts file and launch the serve script.

Environment variables for local development

To avoid hardcoding variables and secrets (e.g., database credentials, access tokens, etc), they can be configured through the environment variables on a machine. For local development, such variables can be injected through dotenv.

Add the dependency for dotenv using the following command.

yarn add dotenv

Create a .env file in the project root and add the following content.

ini

1ENV=local
2LOG_DIR=logs
3LOG_FILE=app.log
4LOG_LEVEL=info
5MORGAN_LOG=requests.log
6MORGAN_LOG_FMT=[:date[iso]] :method :url :status :response-time ms - :res[content-length]
7MORGAN_LOG_ROLLING_INTERVAL=1d
8SERVER_PORT=8080

Edit src/configuration/environment.ts to initialize dotenv.

typescript

 1// src/configuration/environment.ts
 2
 3import dotenv from 'dotenv';
 4
 5// fetch .env configuration
 6dotenv.config();
 7
 8export const environment = {
 9  nodeEnv: process.env.ENV || process.env.NODE_ENV,
10  logDir: process.env.LOG_DIR || 'logs',
11  logLevel: process.env.LOG_LEVEL || 'info',
12  logFile: process.env.LOG_FILE || 'app.log',
13  requestLogFile: process.env.MORGAN_LOG || 'requests.log',
14  requestLogFormat: process.env.MORGAN_LOG_FMT || 'combined',
15  requestLogRollingInterval: process.env.MORGAN_LOG_ROLLING_INTERVAL || '1d',
16  serverPort: Number(process.env.SERVER_PORT) || 8080
17};

Refactor src/server.ts to use serverPort.

typescript

 1// src/server.ts
 2
 3import express from 'express';
 4import { environment } from './configuration/environment';
 5import { registerRequestLogger } from './helper/request.logger';
 6import { logger } from './helper/default.logger';
 7
 8const app = express();
 9const port = environment.serverPort; // server port
10
11// register morgan
12registerRequestLogger(app);
13
14// default route
15app.get('/', (req, res) => res.json({
16  message: 'Hello World!'
17}));
18
19// server
20app.listen(port, () => logger.info(`Server started at http://localhost:${port}, Press CTRL+C to stop it`));

Info Make sure to add .env file in the .gitignore since it is meant only for the local development.

Configure Postgres

Start a Postgres instance in a Docker container using the following docker-compose.yml.

yaml

 1version: "3.1"
 2
 3services:
 4  db:
 5    image: postgres:alpine
 6    container_name: postgres_latest
 7    restart: always
 8    ports:
 9      - 5432:5432
10    environment:
11      POSTGRES_PASSWORD: example

Execute docker-compose up -d to launch the stack.

We'll build a simple music catalog system with APIs to

add, modify or remove songs
search the songs by title, album or artist

Create a table

Create a table named song and add some sample data in it.

sql

 1CREATE TABLE song (
 2  id SERIAL PRIMARY KEY,
 3  title TEXT NOT NULL,
 4  album TEXT,
 5  artist TEXT
 6);
 7
 8INSERT INTO song(title, album, artist) VALUES ('Too Much', 'Dedicated', 'Carly Rae Jepsen');
 9INSERT INTO song(title, album, artist) VALUES ('Insomnia', 'D2', 'Daya');
10INSERT INTO song(title, album, artist) VALUES ('Issues', 'Nervous System', 'Julia Michaels');

Info The SERIAL type will automatically generate an incremented id in Postgres.

Create a Repository

Add pg to communicate with Postgres, along with its type definitions.

1yarn add pg
2yarn add -D @types/pg

Provide the database configurations required by pg in .env file as follows.

ini

1PGUSER=postgres
2PGHOST=localhost
3PGPASSWORD=example
4PGDATABASE=postgres
5PGPORT=5432

Read these configurations in the src/configuration/environment.ts.

typescript

 1// src/configuration/environment.ts
 2
 3export const environment = {
 4  pgUser: process.env.PGUSER,
 5  pgHost: process.env.PGHOST,
 6  pgPassword: process.env.PGPASSWORD,
 7  pgDatabase: process.env.PGDATABASE,
 8  pgPort: Number(process.env.PGPORT),
 9  
10  // Other configurations
11};

Create a file src/model/song.ts and define an interface for a song. This will serve as a model.

typescript

1// src/model/song.ts
2
3export interface Song {
4  id?: string;
5  title: string;
6  album: string;
7  artist: string;
8}

Create a file src/repository/song.ts and add the following code.

typescript

 1// src/repository/song.ts
 2
 3import { Pool } from 'pg';
 4import { Song } from '../model/song';
 5import { logger } from '../helper/default.logger';
 6
 7export class SongRepository {
 8  private pool: Pool;
 9
10  constructor() {
11    this.pool = new Pool();
12  }
13
14  async getAll() {
15    logger.info('Fetching all songs...');
16    const result = await this.pool.query('SELECT id, title, album, artist FROM song');
17    return result.rows;
18  }
19
20  async getById(id: string) {
21    logger.info('Fetching all songs...');
22    const result = await this.pool.query(`SELECT id, title, album, artist FROM song WHERE id = '${id}'`);
23    return result.rows;
24  }
25
26  async search(keyword: string) {
27    logger.info(`Searching for ${keyword}...`);
28    const result = await this.pool.query(`SELECT id, title, album, artist FROM song WHERE title LIKE '%${keyword}%' OR album LIKE '%${keyword}%' OR artist LIKE '%${keyword}%'`);
29    return result.rows;
30  }
31
32  async save(song: Song) {
33    const client = await this.pool.connect();
34    try {
35      const result = await client.query(
36        `INSERT INTO song(title, album, artist) VALUES ('${song.title}', '${song.album}', '${song.artist}') ON CONFLICT (id) DO UPDATE SET title = '${song.title}', album = '${song.album}', artist = '${song.artist}'`
37      );
38      await client.query('COMMIT');
39      logger.info('Song saved...');
40      return result.rows;
41    } catch (e) {
42      await client.query('ROLLBACK');
43      logger.info('Failed saving the song. Transaction rolled back...');
44      throw e;
45    } finally {
46      client.release();
47    }
48  }
49
50  async remove(id: string) {
51    logger.info('Deleting a song...');
52    const result = await this.pool.query(`DELETE FROM song WHERE id = '${id}'`);
53    return result.rows;
54  }
55}

In the SongRepository class, a connection pool (Pool) provided by pg is used to execute the queries asynchronously. This connection pool returns a Promise. For more control over a transaction, a client has been used in save method where a transaction is being rolled back in case something goes wrong.

Configure the endpoints

To configure the endpoints, create a file src/api/song.ts and add the following code.

typescript

 1// src/api/song.ts
 2
 3import { Application } from 'express';
 4import { SongRepository } from '../repository/song';
 5
 6const context = '/song';
 7const repository = new SongRepository();
 8
 9export const registerSongEndpoints = (app: Application) => {
10  app.get(context, (req, res) => {
11    repository.getAll()
12      .then(results => res.json({ results: results }))
13      .catch(error => res.status(500).json({ error: error }));
14  });
15
16  app.put(context, (req, res) => {
17    if (req.body && req.body.title && req.body.album && req.body.artist) {
18      const song = {
19        title: req.body.title,
20        album: req.body.album,
21        artist: req.body.artist
22      };
23      repository.save(song)
24        .then(result => res.json({ message: `Saved ${song.title}` }))
25        .catch(error => res.status(500).json({ error: error }));
26    } else {
27      res.status(400).json({ error: 'Invalid request body' });
28    }
29  });
30
31  // Other endpoints
32};

A typical endpoint calls a repository method and returns a success message or result. In case of an error, the error or an error message is returned in the response.

Import these endpoints in a src/api/index.ts file as follows.

typescript

 1// src/api/index.ts
 2
 3import { Application } from 'express';
 4import { registerSongEndpoints } from './song';
 5
 6export const registerEndpoints = (app: Application) => {
 7  registerSongEndpoints(app);
 8
 9  // Other endpoint registerations
10};

To parse the request body, add body-parser as a dependency.

yarn add body-parser

typescript

 1// src/server.ts
 2
 3import express from 'express';
 4import bodyParser from 'body-parser';
 5import { registerEndpoints } from './api';
 6// Other imports...
 7
 8const app = express();
 9const port = environment.serverPort; // server port
10app.use(bodyParser.json()); // body parser to read request body
11
12// register morgan
13registerRequestLogger(app);
14
15// register all endpoints
16registerEndpoints(app);
17
18// server
19app.listen(port, () => logger.info(`Server started at http://localhost:${port}, Press CTRL+C to stop it`));

Test the API

Build the app by yarn build and launch it with yarn start. Use the following curl requests to verify the API.

 1curl --request GET 'http://localhost:8080/song'
 2
 3curl --request GET 'http://localhost:8080/song/search?q=Carly'
 4
 5curl --request GET 'http://localhost:8080/song/search?q=Nervous'
 6
 7curl --request GET 'http://localhost:8080/song/search?q=D2'
 8
 9curl --request PUT http://localhost:8080/song \
10  -H 'Content-Type: application/json' \
11  -d '{
12    "title": "brand new eyes",
13    "album": "Wonder",
14    "artist": "Sandra Dassault"
15}'
16
17curl --request PATCH http://localhost:8080/song \
18  -H 'Content-Type: application/json' \
19  -d '{
20    "id": "4",
21    "artist": "Bea Miller"
22}'
23
24curl --request DELETE http://localhost:8080/song \
25  -H 'Content-Type: application/json' \
26  -d '{
27    "id": "4"
28}'

References

Source code — express-postgres-api