Building and deploying a NodeJS + Postgres API in less than 30 minutes

Hello Reader and welcome to the first article in the FL0 Engineering publication! Whether you are building a fintech, a health app or the next Neopets, there are some things almost EVERY startup needs…and we’re going to tackle those things in this article. We’ll begin by creating an API with Express, then we’ll connect a Postgres database and manage it with Sequelize. Finally we’ll deploy everything to a Docker container and make use of the great NodeJS and Postgres features in FL0.

The app we’ll build is going to be basic, but it will contain the essential components you need to extend it for your own project. It will include:

1. A database for storing our startup’s products
2. An API endpoint for retrieving those products
3. A well-structured codebase including an ORM for managing database migrations, sample data and configuration.
4. Tools for managing the database, including separate Dev and Production environments

If all goes well, you should be up and running in less than half an hour!

Sample Repository

You’ll find the completed codebase at this link on the building-deploying-30-mins branch.

GitHub - fl0zone/blog-express-pg-sequelize

Prerequisites

This article will be fairly detailed with step-by-step instructions and code snippets where possible. However, it does expect you have…

• A Github account
• A FL0 account

And the following technologies installed on your machine…

• NodeJS + NPM
• Git
• Docker Desktop

If you have any questions on the setup or if you think there are any steps missing in this article, please drop a comment for us below!

Codebase Setup

Let’s start by setting up a local repository with the Express starter code. Open up a terminal window and run the following commands:

$ mkdir my-startup && cd my-startup
$ npm init --yes
$ npm i express
$ npm i --save-dev nodemon dotenv

These commands do the following:

1. Create a new folder called “my-startup” and navigate into that folder
2. Initialize the folder as an NPM project so we can install dependencies
3. Install Express as a dependency, Nodemon (a tool for auto-reloading the server when changes are detected) and Dotenv (for loading variables from a .env file)

To get a basic Express server running, create a new folder called src and a file inside called index.js and paste in the following code:

// src/index.js

const express = require('express')
const app = express()
const port = process.env.PORT ?? 3000;

app.get('/', (req, res) => {
  res.send('Hello World!')
})

app.listen(port, () => {
  console.log(`Example app listening on port ${port}`)
})

Edit your package.json file and add the following start and start:dev commands:

{
  "name": "my-startup",
  "version": "1.0.0",
  ...
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1",
    "start:dev": "nodemon -r dotenv/config src/index.js",
    "start": "node src/index.js"
  },
  ...
}

Checkpoint: The First Endpoint

Try out your app by running npm run start:dev and visiting http://localhost:3000 in your browser. See the words “Hello World”? Let’s keep going!

Before we move on to the next section, initialize your folder as a Git repo and save your changes:

$ git init
$ echo "node_modules\n.env" > .gitignore
$ git add .
$ git commit -m "Set up Express framework"

The above commands achieve the following:

1. Initialize the local Git repo (we will connect it to Github later)
2. Ignore the node_modules folder so it isn’t added to Git
3. Add all the other files to the Git stage
4. Commit the staged changes to the repo

Adding the Database

In this section we’ll create a local Postgres database and install Sequelize to manage it.

In the root of your codebase, create a file called docker-compose.yml and add the following content:

version: '3'
services:
  db:
    image: postgres:14
    restart: always
    environment:
      POSTGRES_USER: admin
      POSTGRES_PASSWORD: admin
      POSTGRES_DB: my-startup-db
    volumes:
      - postgres-data:/var/lib/postgresql/data
    ports:
      - 5432:5432
volumes:
  postgres-data:

A Docker Compose file tells Docker what containers to spin up and how they should be configured. Our file contains a single service - db. It runs an image called postgres (version 14) and sets some configuration like the username and password for the database.

Set environment variables

Our Docker Compose file is set up to create a database with username admin, password of admin and database name of my-startup-db. We will need to tell our app how to connect to the database, so let’s do that now. Create a file in the root of your repo called .env and add the following content:

NODE_ENV=local
DATABASE_URL=postgres://admin:admin@localhost:5432/my-startup-db

The DATABASE_URL variable will be used when we set up Sequelize.

Checkpoint: Database in a container

Before we continue, run the following command and verify your Postgres container starts correctly!

$ docker compose up

If you see a message like this, you’re good to go!

2023-01-23 23:58:33.091 UTC [1] LOG:  database system is ready to accept connections

Connecting with Sequelize

Sequelize is the most popular Javascript Object Relational Mapping )(ORM) framework. Other alternatives include TypeORM and Prisma. You can use any of these with FL0, but in this article we’ll choose Sequelize.

First let’s install Sequelize and the Postgres client for NodeJS, then use the Sequelize CLI tool to initialize our project:

$ npm install sequelize pg
$ cd src
$ npx sequelize-cli init --config=config/index.js

The npx sequelize-cli init command bootstraps our project with a few folders and files:

• config: a place to store connection details for our database
• migrations: A folder for storing database migration scripts, used to apply changes to the schema
• models: Every table in the database will be defined as a “model” and stored in this folder
• seeders: We can bootstrap our tables with sample data by adding scripts to this folder

Next, let’s alter the src/config/index.js file to point to our database. Replace its content with the following:

module.exports = {
  "local": {
    "use_env_variable": "DATABASE_URL"
  },
  "development": {
    "use_env_variable": "DATABASE_URL"
  },
  "production": {
    "use_env_variable": "DATABASE_URL"
  }
}

This tells Sequelize to read from the .env file we created earlier. To test our connection, alter the index.js file to look like this:

const express = require('express')
const { sequelize } = require('./models');
const app = express()
const port = process.env.PORT ?? 3000;

app.get('/', (req, res) => {
  res.send('Hello World!')
})

app.listen(port, async () => {
  console.log(`Example app listening on port ${port}`)
  try {
    await sequelize.authenticate();
    await sequelize.sync({ force: true});
    console.log('Connection has been established successfully.');
  } catch (error) {
    console.error('Unable to connect to the database:', error);
  }
})

These changes import the sequelize object from our new src/models/index.js file and attempt to connect to the database.

Checkpoint:

Run npm run start:dev and make sure you can see a message that says “Connection has been established successfully”. If you see any errors:

1. Make sure your database container is still running with docker compose up
2. Make sure your environment variables are loaded properly. You can verify this by adding console.log(JSON.stringify(process.env)) to your src/index.js file and looking for the DATABASE_URL key

Now is a good time to commit your changes!

$ git add . && git commit -m "Set up Sequelize connection" 

Creating the first database models

Now that Sequelize is set up we can start populating the database with some tables and values. Let’s use the Sequelize CLI tool to create our first model. Run the following script in your Terminal from the src folder:

$ npx sequelize-cli model:generate --name=Product --attributes=sku:string,name:string,description:text,isPublished:boolean,imageURL:string,price:decimal,weight:decimal

This will automatically generate src/models/product.js and a corresponding file in the migrations folder. Next, we’ll define some seed data to populate the Product table with some information:

$ npx sequelize-cli seed:generate --name=products

Open the created file under the seeders folder and replace it with the following content:

'use strict';

/** @type {import('sequelize-cli').Migration} */
module.exports = {
  async up(queryInterface, Sequelize) {
    await queryInterface.bulkInsert('Products', [{
      sku: 'STR0001',
      name: 'Three-lobed fidget spinner',
      description: 'A fidget spinner is a toy that consists of a ball bearing in the center of a multi-lobed (typically two or three) flat structure made from metal or plastic designed to spin along its axis with pressure. Fidget spinners became trending toys in 2017, although similar devices had been invented as early as 1993.[1]',
      imageURL: 'https://upload.wikimedia.org/wikipedia/commons/thumb/f/f3/Fidget_spinner_red%2C_cropped.jpg/1280px-Fidget_spinner_red%2C_cropped.jpg',
      isPublished: true,
      price: 12.95,
      weight: 0.1,
      createdAt: new Date(),
      updatedAt: new Date(),
    },
    {
      sku: 'STR0002',
      name: 'World-map stress ball',
      description: 'A stress ball or hand exercise ball is a malleable toy, usually not more than 7 cm in diameter, which is squeezed in the hand and manipulated by the fingers, ostensibly to relieve stress and muscle tension or to exercise the muscles of the hand. Patrick Hummel is widely understood to have created the stress ball in central Indiana in the mid-1980s.',
      imageURL: 'https://upload.wikimedia.org/wikipedia/commons/thumb/7/76/Earth_globe_stress_ball.jpg/220px-Earth_globe_stress_ball.jpg',
      isPublished: true,
      price: 4.99,
      weight: 0.05,
      createdAt: new Date(),
      updatedAt: new Date(),
    },
    {
      sku: 'STR0003',
      name: 'Metallic slinky',
      description: 'The Slinky is a precompressed[clarification needed] helical spring toy invented by Richard James in the early 1940s. It can perform a number of tricks, including travelling down a flight of steps end-over-end as it stretches and re-forms itself with the aid of gravity and its own momentum, or appear to levitate for a period of time after it has been dropped. These interesting characteristics have contributed to its success as a toy in its home country of the United States, resulting in many popular toys with slinky components in a wide range of countries.',
      imageURL: 'https://upload.wikimedia.org/wikipedia/commons/thumb/f/f3/2006-02-04_Metal_spiral.jpg/200px-2006-02-04_Metal_spiral.jpg',
      isPublished: true,
      price: 15.45,
      weight: 0.2,
      createdAt: new Date(),
      updatedAt: new Date(),
    }
  ], {});
  },

  async down(queryInterface, Sequelize) {
  }
};

The code above inserts three sample products for us to work with. To import the data, make sure your app is running with npm run start:dev and then in a new terminal window run:

$ npx sequelize-cli db:seed:all --config=src/config/index.js

Hopefully you see some output that looks like this!

== 20230124200930-products: migrating =======
== 20230124200930-products: migrated (0.031s)

Connecting the Database and API

Now that we have a database with some product records, it’s time to create an API to expose this information. Update src/index.js with the following content:

const express = require('express')
const { sequelize, Product } = require('./models');
const app = express()
const port = process.env.PORT ?? 3000;

app.get('/', (req, res) => {
  res.send('Hello World!')
})

app.get('/products', async (req, res) => {
  const products = await Product.findAll();
  res.json(products);
})

app.listen(port, async () => {
  console.log(`Example app listening on port ${port}`)
  try {
    await sequelize.authenticate();
    await sequelize.sync({ force: true});
    console.log('Connection has been established successfully.');
  } catch (error) {
    console.error('Unable to connect to the database:', error);
  }
})

Notice the new /products route, and the Product object being imported from the Sequelize model file. Verify this is working by hitting http://localhost:3000/products and ensuring you see the product data returned as JSON.

Commit your changes and continue reading!

$ git add . && git commit -m "Created GET /products endpoint"

Deploying the app

Now that we’ve got a working API and database we can deploy it to a server! In this article we’re using FL0, a platform that makes it really easy to deploy NodeJS apps to a containerized infrastructure, complete with database.

Pushing to Github

Up until now we’ve been committing changes to a local repo that only exists on our machine. Let’s link that to a Github repo so it can be stored in the cloud. In Github, create a new empty repo with no license or .gitignore file. If all goes well, you should see a screen like this:

We need to follow the steps under the heading “…or push an existing repository from the command line”. Note: I haven’t included the commands here because they will be specific to your own repo and may use HTTPS instead of SSH like mine does. It’s better to copy+paste from the Github page than this article for this step.

If successful, you should be able to refresh the Github page and see your code:

Configuring FL0

In FL0, create a new empty project and then add a Postgres database. You can call it anything you like.

Next, add a new Service and connect to your Github account:

You should see a window asking you to authorize FL0 to connect to your account:

When you are returned to FL0, your repos will be available to deploy through the user interface. Click the “Connect” button to continue. On the next page, you have the option to specify a Service Name and set Environment Variables. This is where we need to insert our Database URL.

Click the “+ Environment Variables” button and in the popup window that appears, select “Import database credentials”:

Make sure you see a row called DATABASE_URL before continuing.

Save your changes and click the “Deploy” button!

This will trigger a new build to begin. Click on your new Service and navigate to the Deployments tab to see how it’s progressing:

Once complete, go to the Overview tab and copy the URL:

Open it up in a browser and you should see the familiar “Hello World” message! Add a /products onto the end and you should see…nothing…yet! This is because our new FL0 database is still empty and we need to run the Seed script to insert the sample data.

Pointing the Sequelize CLI at FL0

Earlier we created a .env file that contained a DATABASE_URL variable instructing Sequelize to connect to our Docker container. Let’s edit the file to include a new variable, our FL0 database URL. You can find the Database URL in FL0 under the Connection Info tab of the Postgres resource.

Copy the .env and call it .env.dev and updated it as follows:

NODE_ENV=development
DATABASE_URL=<YOUR FL0 DATABASE_URL>

Adding this will let us run the Sequelize CLI against different environments. Try this:

$ npx dotenv-cli -e .env.dev -- npx sequelize-cli db:seed:all --config=src/config/index.js

Notice the dotenv-cli -e .env.dev option telling Sequelize to use FL0 instead of our local container. Go back to your browser and check the /products URL again. Hopefully you see some product JSON! Using FL0’s deployment features you can easily replicate your container to the Production environment with a completely separate database.

And there you have it folks, the building blocks for a successful NodeJS project! With the knowledge and skills you’ve gained from this article, you’re ready to bring your next big idea to life. We’d love to hear what you’re building in the comments below! We hope this article brought a smile to your face and a little inspiration to help tackle help your next project. Until next time, happy coding!