How To Set Up An Express API Backend Project With PostgreSQL — Smashing Magazine

import express from 'express';
import { testEnvironmentVariable } from '../settings';

const indexRouter = express.Router();

indexRouter.get('/', (req, res) => res.status(200).json({ message: testEnvironmentVariable }));

export default indexRouter;

The only difference is importing testEnvironmentVariable from the settings module and returning it as the response message for the base route. Visit https://localhost:3000/v1 and you should see the expected output:

{
  "message": "Environment variable is coming across."
}

This pattern allows you to add as many environment variables as needed, exporting each one from settings.js for use across the app. Run prettier and linting, then commit these changes. The corresponding branch is 03-env-variables.

Writing the First Test

Testing provides confidence that your code behaves as intended. Setting up automated tests is straightforward with Express.js. Our approach will send requests to API endpoints and verify the responses.

Install the necessary test dependencies:

# install dependencies

yarn add mocha chai nyc sinon-chai supertest coveralls --dev

Each library contributes a vital piece to the testing workflow:

At the project root, create a test/ folder containing two files:

• test/setup.js
• test/index.test.js

Mocha discovers the test/ folder automatically. Populate test/setup.js with the following helper code—a centralized place for test-related imports:

import supertest from 'supertest';
import chai from 'chai';
import sinonChai from 'sinon-chai';
import app from '../src/app';

chai.use(sinonChai);
export const { expect } = chai;
export const server = supertest.agent(app);
export const BASE_URL = '/v1';

This file functions like a configuration for tests, avoiding repetitive imports across individual test files. Next, add your assertions to test/index.test.js:

import { expect, server, BASE_URL } from './setup';

describe('Index page test', () => {
  it('gets base url', done => {
    server
      .get(`${BASE_URL}/`)
      .expect(200)
      .end((err, res) => {
        expect(res.status).to.equal(200);
        expect(res.body.message).to.equal(
          'Environment variable is coming across.'
        );
        done();
      });
  });
});

Here, we request the base endpoint (/) and assert that the response body contains a message key whose value matches Environment variable is coming across.. If the describe/it pattern is unfamiliar, review Mocha's Getting Started guide.

Install the test command in package.json:

"test": "nyc --reporter=html --reporter=text --reporter=lcov mocha -r @babel/register"

Running yarn test executes the suite via nyc, generating coverage in three formats: HTML output to coverage/, a text summary in the terminal, and an LCOV report in .nyc_output/. Execute the command and confirm the output:

This generates two extra directories—.nyc_output/ and coverage/—both already covered by your .gitignore. Open coverage/index.html in a browser to inspect detailed file-by-file coverage metrics. Commit these changes, with the corresponding branch being 04-first-test.

Setting Up CI Services and Adding the First Controller

Travis CI

Travis CI automatically runs tests on every push and pull request to your GitHub repository. To set it up:

1. Sign up at travis-ci.com with your GitHub account.
2. Navigate to settings via the dropdown next to your profile picture.
3. Under Repositories, click Manage repositories on Github.
4. Choose Only select repositories, then find and add the express-api-template repo.
5. Click Approve and install to return to Travis CI.
6. On the repo page, click the build unknown icon to copy the status image markdown and paste it into your README.md.
7. Go to More options > Settings, and add the TEST_ENV_VARIABLE environment variable with its value wrapped in double quotes, e.g. "Environment variable is coming across."
8. Create .travis.yml at the project root with this configuration:

language: node_js
env:
  global:
    - CC_TEST_REPORTER_ID=get-this-from-code-climate-repo-page
matrix:
  include:
  - node_js: '12'
cache:
  directories: [node_modules]
install:
  yarn
after_success: yarn coverage
before_script:
  - curl -L https://codeclimate.com/downloads/test-reporter/test-reporter-latest-linux-amd64 > ./cc-test-reporter
  - chmod +x ./cc-test-reporter
  - ./cc-test-reporter before-build
script:
  - yarn test
after_script:
  - ./cc-test-reporter after-build --exit-code $TRAVIS_TEST_RESUL

The configuration specifies Node.js v12 in the matrix section and caches node_modules/ to avoid regeneration on each build. Dependencies are installed with yarn, while before_script and after_script handle Code Climate coverage uploads (the CC_TEST_REPORTER_ID value gets set in the Code Climate section below). After a successful yarn test, the yarn coverage command runs to upload coverage data to Coveralls.

Coveralls

Coveralls publishes test coverage metrics so you can view them outside your local machine:

1. Sign in at coveralls.io with your GitHub account.
2. Open the navigation menu (hover on the left edge) and click ADD REPOS.
3. Find express-api-template and toggle coverage on. If the repo is missing, click SYNC REPOS. The repo must be public unless you have a PRO account.
4. Open the repo details page and locate the repo_token.
5. Create .coveralls.yml at the root and enter the token:

repo_token: get-this-from-repo-settings-on-coveralls.io

Add the coverage script to package.json:

"coverage": "nyc report --reporter=text-lcov | coveralls"

Run this command with your Internet connection on:

yarn coverage

The existing coverage report in .nyc_output gets uploaded. Refresh the repo page on Coveralls to see the report. Then use the BADGE YOUR REPO section to copy the markdown badge into your README.

Code Climate

Code Climate assesses code quality by scanning for patterns like repetition and deeply nested loops. It also collects test coverage data:

1. Go to codeclimate.com and sign up with GitHub.
2. Click Add a repository, find the repo, and select Add Repo.
3. Wait for the build to finish, then navigate to the repo dashboard.
4. Under Test Coverage, copy the TEST REPORTER ID and use it as the CC_TEST_REPORTER_ID value in .travis.yml.
5. Under EXTRAS in the left navigation, click Badges and copy the maintainability and test coverage markdown badges into your README.

Maintainability checks default to settings applied to every repo. You can override them with a .codeclimate.yml file if needed; this guide uses the default settings, viewable under the Maintainability tab in the repo settings.

AppVeyor

AppVeyor serves the same purpose as Travis CI but runs tests in a Windows environment instead of Linux:

• Log in at AppVeyor and click NEW PROJECT.
• Hover over express-api-template in the repo list and click ADD.
• Under Settings, go to Environment and add TEST_ENV_VARIABLE with its value. Save the changes.
• Create appveyor.yml at the root with this code:

environment:
  matrix:
  - nodejs_version: "12"
install:
  - yarn
test_script:
  - yarn test
build: off

The config uses Node.js v12, installs dependencies with yarn, runs tests via test_script, and skips build folder creation on the last line. Then grab the markdown badge from the Settings tab and paste it into your README.

Commit your code and push. If environment variables are set correctly on Travis and AppVeyor, all tests should pass, displaying badges similar to:

• The corresponding branch in my repo is 05-ci.

Adding a Controller

Handling the GET request to /v1 inside src/routes/index.js works for now, but keeping request handling and response generation separate becomes important as the app grows. Controllers are functions dedicated to handling requests for specific URLs.

Create a controllers/ folder inside src/ with two files: index.js and home.js. Name controllers based on what they manage (for instance, usersController.js for user-related functions). In home.js, add:

import { testEnvironmentVariable } from '../settings';

export const indexPage = (req, res) => res.status(200).json({ message: testEnvironmentVariable });

This just moves the existing handler for the / route into its own file. Set up index.js to re-export everything:

// export everything from home.js
export * from './home';

Exporting from home.js allows shorter imports like import { indexPage } from '../controllers'; in the routes file. Update src/routes/index.js:

import express from 'express';
import { indexPage } from '../controllers';
const indexRouter = express.Router();

indexRouter.get('/', indexPage);

export default indexRouter;

The only change here is passing a handler function directly to the route. You've now written your first controller. Add more routes and controllers as needed — an about page would be a good practice exercise, and remember to update your tests accordingly. Run yarn test to verify nothing is broken.

• The corresponding branch in my repo is 06-controllers.

Wiring the App to PostgreSQL

The hard-coded responses in our controllers won't cut it for a real backend. We need a database, and for this project we'll use PostgreSQL. You can either provision a free instance from a cloud provider like ElephantSQL, which offers a 20MB free tier sufficient for this tutorial, or run a local installation. Regardless of your choice, note the connection string (you'll find it on the ElephantSQL details page).

Node.js can't talk to PostgreSQL natively, so we'll use the excellent node-postgres library to execute SQL queries and handle the results. Let's install and configure it.

# install node-postgres
yarn add pg

First, update settings.js to include the new configuration.

export const connectionString = process.env.CONNECTION_STRING;

Next, add the connection string to your .env file using the variable name CONNECTION_STRING.

CONNECTION_STRING="postgresql://dbuser:dbpassword@localhost:5432/dbname"

Building the Model

Inside /src, create a models/ directory with two files: pool.js and model.js. The pool.js file establishes a connection pool—a collection of clients that node-postgres uses to run queries—by passing our connection string to the Pool constructor.

import { Pool } from 'pg';
import dotenv from 'dotenv';
import { connectionString } from '../settings';
dotenv.config();

export const pool = new Pool({ connectionString });

In model.js, we create a reusable class. Its constructor takes the table name, and it exposes methods like select, which accepts the columns to fetch and an optional clause (like a WHERE). Since queries return promises, we can use pool.query to run them on any available idle client from the pool.

import { pool } from './pool';

class Model {
  constructor(table) {
    this.pool = pool;
    this.table = table;
    this.pool.on('error', (err, client) => `Error, ${err}, on idle client${client}`);
  }

  async select(columns, clause) {
    let query = `SELECT ${columns} FROM ${this.table}`;
    if (clause) query += clause;
    return this.pool.query(query);
  }
}

export default Model;

Utility Scripts for Database Operations

Before building endpoints, let's create command-line utilities to manage our schema and seed data. In /src, create a utils/ folder with queries.js, queryFunctions.js, and runQuery.js.

The queries.js file defines three SQL strings: one to drop and recreate a messages table, one to insert seed rows, and one to drop the table entirely.

export const createMessageTable = `
DROP TABLE IF EXISTS messages;
CREATE TABLE IF NOT EXISTS messages (
  id SERIAL PRIMARY KEY,
  name VARCHAR DEFAULT '',
  message VARCHAR NOT NULL
  )
  `;

export const insertMessages = `
INSERT INTO messages(name, message)
VALUES ('chidimo', 'first message'),
      ('orji', 'second message')
`;

export const dropMessagesTable = 'DROP TABLE messages';

In queryFunctions.js, we wrap those queries in functions. Note the executeQueryArray function—it processes queries sequentially for setup purposes, which is fine for this context but not suitable for production.

import { pool } from '../models/pool';
import {
  insertMessages,
  dropMessagesTable,
  createMessageTable,
} from './queries';

export const executeQueryArray = async arr => new Promise(resolve => {
  const stop = arr.length;
  arr.forEach(async (q, index) => {
    await pool.query(q);
    if (index + 1 === stop) resolve();
  });
});

export const dropTables = () => executeQueryArray([ dropMessagesTable ]);
export const createTables = () => executeQueryArray([ createMessageTable ]);
export const insertIntoTables = () => executeQueryArray([ insertMessages ]);

Finally, runQuery.js triggers the setup functions. Add a script to package.json to run this file, then execute it via the command line.

"runQuery": "babel-node ./src/utils/runQuery"

yarn runQuery

You should now see the messages table and its seeded data in your database (in ElephantSQL, use the BROWSER panel to view it).

Querying the Database via the API

Now we can replace the dummy data with a real database call. Create src/controllers/messages.js with an asynchronous controller. It instantiates the model for the messages table, uses the select method to fetch data, and sends the result as JSON.

import Model from '../models/model';

const messagesModel = new Model('messages');
export const messagesPage = async (req, res) => {
  try {
    const data = await messagesModel.select('name, message');
    res.status(200).json({ messages: data.rows });
  } catch (err) {
    res.status(200).json({ messages: err.stack });
  }
};

Register a GET route for /v1/messages in src/routes/index.js and update the import statement.

# update the import line
import { indexPage, messagesPage } from '../controllers';

# Add The Get Messages Endpoint
indexRouter.get('/messages', messagesPage)

Hit https://localhost:3000/v1/messages to see the stored messages. Let's also add proper tests. Create test/hooks.js—Mocha will run this before any test files. The before hook creates the table and inserts data, while the after hook drops it, ensuring a clean state for every test run.

import {
  dropTables,
  createTables,
  insertIntoTables,
} from '../src/utils/queryFunctions';

before(async () => {
  await createTables();
  await insertIntoTables();
});

after(async () => {
  await dropTables();
});

In the new test/messages.test.js, assert that the response is an array and each item has the expected name and message properties.

import { expect, server, BASE_URL } from './setup';
describe('Messages', () => {
  it('get messages page', done => {
    server
      .get(`${BASE_URL}/messages`)
      .expect(200)
      .end((err, res) => {
        expect(res.status).to.equal(200);
        expect(res.body.messages).to.be.instanceOf(Array);
        res.body.messages.forEach(m => {
          expect(m).to.have.property('name');
          expect(m).to.have.property('message');
        });
        done();
      });
  });
});

Finally, update the CI configurations. For Travis CI, add a PostgreSQL service and create the test database before running the suite.

services:
  - postgresql
addons:
  postgresql: "10"
  apt:
    packages:
    - postgresql-10
    - postgresql-client-10
before_install:
  - sudo cp /etc/postgresql/{9.6,10}/main/pg_hba.conf
  - sudo /etc/init.d/postgresql restart

# add this as the first line in the before_script section

- psql -c 'create database testdb;' -U postgres

Make the CONNECTION_STRING environment variable available on Travis, using postgres://postgres@localhost:5432/ci_test. Do the same in the AppVeyor configuration.

before_test:
  - SET PGUSER=postgres
  - SET PGPASSWORD=Password12!
  - PATH=C:\Program Files\PostgreSQL\10\bin\;%PATH%
  - createdb testdb
services:
  - postgresql101

CONNECTION_STRING="postgresql://postgres:postgres@localhost:5432/testdb"

CONNECTION_STRING=postgresql://postgres:Password12!@localhost:5432/testdb

Commit and push—the tests should pass on both CI services. For reference, the complete code for this stage lives in the 07-connect-postgres branch.

Adding Support for POST Requests

Now let's implement a way to add new messages to the database. We'll follow a strict TDD flow: write a failing test first. In test/messages.test.js, add a test that sends a POST request and expects a response containing the new record's id, name, and message.

it('posts messages', done => {
  const data = { name: 'some name', message: 'new message' };
  server
    .post(`${BASE_URL}/messages`)
    .send(data)
    .expect(200)
    .end((err, res) => {
      expect(res.status).to.equal(200);
      expect(res.body.messages).to.be.instanceOf(Array);
      res.body.messages.forEach(m => {
        expect(m).to.have.property('id');
        expect(m).to.have.property('name', data.name);
        expect(m).to.have.property('message', data.message);
      });
      done();
    });
});

Run the suite—this test will fail. To fix it, back in src/models/model.js, add an insert method:

async insertWithReturn(columns, values) {
  const query = `
        INSERT INTO ${this.table}(${columns})
        VALUES (${values})
        RETURNING id, ${columns}
    `;
  return this.pool.query(query);
}

This method takes the relevant data, executes an INSERT query, and returns the inserted record including its new id. Add a corresponding controller in src/controllers/messages.js to handle the request.

export const addMessage = async (req, res) => {
  const { name, message } = req.body;
  const columns = 'name, message';
  const values = `'${name}', '${message}'`;
  try {
    const data = await messagesModel.insertWithReturn(columns, values);
    res.status(200).json({ messages: data.rows });
  } catch (err) {
    res.status(200).json({ messages: err.stack });
  }
};

Destructure the request body in this async function, create the SQL insert statement, and pass it to the model's method. Then wire up the POST/v1/messages endpoint in your routes file.

import { indexPage, messagesPage, addMessage } from '../controllers';

indexRouter.post('/messages', addMessage);

The tests should pass now. You can also test it manually with a tool like Postman. Send a POST request to the messages endpoint with a JSON body containing name and message fields. If your previous test run dropped the table, remember to run the query script again to recreate it before hitting the endpoint manually.

yarn query

Commit the changes and push. The test suite will pass on Travis and AppVeyor (some coverage will be lost, but that's acceptable at this stage). The implementation for this phase is available in the 08-post-to-db branch. Continue to the next part of this series to add update and delete operations.

Middleware in the Request-Response Cycle

Express middleware functions sit between the receipt of a request and the dispatch of a response. They receive the request object (req), the response object (res), and the next function, which passes control to the subsequent middleware in the chain. Middleware is commonly used for tasks like authentication and request body modification.

To demonstrate, we will build a simple middleware that prepends the string SAYS: to the incoming message body before it is saved.

First, update the relevant test in test/messages.test.js. The final assertion in the "posts message" test case should now expect the database-returned message to include the prefix:

it('posts messages', done => {
   ...
  expect(m).to.have.property('message', `SAYS: ${data.message}`); # update this line
    ...
});

Run the test suite to confirm this new assertion fails. Then create a middleware/ directory inside src/ with two files, middleware.js and index.js.

The core logic in middleware.js is:

export const modifyMessage = (req, res, next) => {
  req.body.message = `SAYS: ${req.body.message}`;
  next();
};

This function appends SAYS: to req.body.message and then invokes next(), which is mandatory for passing execution to the next function in the cycle. The companion index.js simply exports the available middleware:

# export everything from the middleware file

export * from './middleware';

Now wire the middleware into the route handler in src/routes/index.js. The modifyMessage function should be placed in the chain before addMessage, since calling next in the middleware will eventually invoke the route handler.

import { modifyMessage } from '../middleware';

indexRouter.post('/messages', modifyMessage, addMessage);

If you comment out the next() line in modifyMessage, the request will hang indefinitely. With the middleware active, sending a new message from Postman will return the message with the appended prefix:

Error Handling for Async Middleware

Express automatically catches errors in synchronous route handlers and middleware—no extra work is needed there. However, asynchronous functions require explicit error handling.

Consider a case where we want to fetch data from the Lorem Picsum API (https://picsum.photos/id/0/info) before creating a message. This is an asynchronous operation that might fail.

First, install Axios:

# install axios
yarn add axios

Add an asynchronous function to src/middleware/middleware.js that performs this request:

export const performAsyncAction = async (req, res, next) => {
  try {
    await axios.get('https://picsum.photos/id/0/info');
    next();
  } catch (err) {
    next(err);
  }
};

Inside this async function, we await the API call (ignoring the return value) and then call next(). If the request throws, we catch the error and pass it to next(err). Passing the error is critical; it causes Express to skip the remaining non-error middleware. Calling next() without an argument would let the request continue as if nothing went wrong, leaving the error uncaught.

Import this new function and place it in the chain for the POST messages route, before the message-modifying middleware:

import { modifyMessage, performAsyncAction } from '../middleware';

indexRouter.post('/messages', modifyMessage, performAsyncAction, addMessage);

A global error handler must be registered as the last middleware in src/app.js, just before the export default app line. Error handlers are identifiable by their four parameters, (err, req, res, next):

app.use((err, req, res, next) => {
  res.status(400).json({ error: err.stack });
});

export default app;

This handler responds with the error stack trace and a 400 status code, but the logic for handling the error is up to you—logging or sending it to a third-party service are valid alternatives.

Deploying to Heroku

1. Log in or register at heroku.com.
2. Install the Heroku CLI.
3. From the project root, run the login command:

# login to heroku on command line
heroku login

After being authenticated in the browser, create a new Heroku app:

#app name is up to you
heroku create app-name

Heroku will provide the app URL and the Git remote URL:

# app production url and git url
https://app-name.herokuapp.com/ | https://git.heroku.com/app-name.git

If the remote was not added automatically, add it manually:

# add heroku remote url
git remote add heroku https://git.heroku.com/my-shiny-new-app.git

To monitor the deployment in real-time, open a second terminal and tail the logs:

# see process logs
heroku logs --tail

Set the environment variables required for the app to connect to the database and run correctly:

heroku config:set TEST_ENV_VARIABLE="Environment variable is coming across."
heroku config:set CONNECTION_STRING=your-db-connection-string-here.
heroku config:set NPM_CONFIG_PRODUCTION=false

The start scripts rely on Babel to compile the ES5 code in a prestart step. Since Babel is a development dependency, we set NPM_CONFIG_PRODUCTION=false to install those in the production environment as well.

Verify the configuration was applied correctly, either via the CLI or through the Heroku dashboard under "Settings" > "Reveal Config Vars":

# check configuration variables
heroku config

Finally, push the code and open the app:

# open /v1 route
heroku open /v1

# Open /v1/messages Route
heroku open /v1/messages

If you are using the same PostgreSQL instance for development and production, be aware that running tests will wipe the production database. To recreate the schema, run one of the migration or table-creation commands:

# run script locally
yarn runQuery

# Run Script With Heroku
heroku run yarn runQuery

Deploying is now complete.

Automating Deploys with Travis CI

Adding Continuous Deployment means Travis will push successful test runs straight to Heroku. Install the Travis CI client and log in from the project repository:

# login to travis
travis login --pro

# Use This If You’re Using Two Factor Authentication
travis login --pro --github-token enter-github-token-here

If your project is on travis-ci.org, omit the --pro flag. If your GitHub account uses two-factor authentication, generate a Personal Access Token from GitHub developer settings.

Open .travis.yml and add a deploy section:

deploy:
  provider: heroku
  app:
    master: app-name

The configuration specifies the Heroku app name (app-name) and the Git branch to deploy (master). Different branches can deploy to different apps. You will need your Heroku API key; encrypt it and add it to the deploy block with:

# encrypt heroku API key and add to .travis.yml
travis encrypt $(heroku auth:token) --add deploy.api_key --pro

This appends an encrypted api_key sub-section:

api_key:
  secure: very-long-encrypted-api-key-string

Commit the changes and push to GitHub. The build will trigger after the Travis test suite passes, completing the CI/CD pipeline: a failing test will block the deployment.