API Setup using NodeJS, TypeScript, Postgres and Sequelize

If you want to jump straight to a working version, check my GitHub repo.

Recently I’ve been looking for some guides to start learning NodeJS — and yes, there are plenty out there — but I found it challenging to go through them. I’m a developer, I don’t really want all the details of every single line or package… I want the command lines I need to get it started! (ok, I might be a bit impatient, but that’s me 🤷‍♂️)

This is a very text light and code heavy guide on how to setup an API using NodeJS, TypeScript, Express, Postgres and Sequelize. I’ll be using…

• Node 12.16.3 [download]
• npm 6.14.4 [download]
• VS Code 1.45 [download]

Let’s start!

Part 1 - API TypeScript Setup

Open your terminal in an empty folder, let’s setup npm.

npm init

Now let’s add some packages.

npm i -g nodemon typescript tslint
npm i body-parser dotenv express
npm i -D nodemon ts-node typescript tslint
npm i -D @types/dotenv @types/express @types/node

Run the following to start TypeScript and TSLint.

tsc --init
tslint --init

In the tsconfig.json file, change the following configurations.

{
  ...
  "target": "es6",
  "sourceMap": true,
  "outDir": "./dist/",
  "rootDir": "./src/"
  ...   
}

Change your package.json file in the scriptsnode, as follows:

"scripts": {
  "start": "node dist/index.js",
  "dev": "nodemon src/index.ts",
  "build": "tsc -p .",
  "lint": "tslint -c tslint.json -p tsconfig.json --fix"                         }

Create a /src folder, and create an index.tsfile.

// src/index.ts
import http from 'http';
import express from 'express';
import bodyParser from 'body-parser';
import { Request, Response } from 'express';
const port = 3000;
const app = express();
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({ extended: true }));
app.get('/', async (req: Request, res: Response) => {
  res.status(200).json({
    message: 'Hello World'
  });
});
const server = http.createServer(app);
server.listen(port, () => {
  console.log(`API started at http://localhost:${port}`);
});

Awesome! Now you can already check your API working by running npm run dev and accessing localhost:3000 on your browser.

Part 1.1 - Bonus: dotenv

Before moving forward, let’s isolate the configuration in a separate file. Create a .env file for your configuration — for now only the port number.

// .env
API_PORT=3000

Create a config.ts file inside the src folder.

import * as dotenv from 'dotenv';
dotenv.config({
  path: '${__dirname}/../.env'
});
export const port = Number(process.env.API_PORT);

In the index.ts file, remove the const port declaration and add the following import .

import { port } from './config';

Should work exactly like before, but with your configuration isolated in a file.

Part 2 - Adding more routes

Create the src/routesfolder and inside create the users.routes.ts file.

// src/routes/users.routes.ts
import { Router, Request, Response } from 'express';
const router = Router();
// GET - users
router.get('/', async (req: Request, res: Response) => {
  // TO DO
  const result : string[] = [];
  res.status(200).json({ users: result });
});
// GET - users/:id
router.get('/:id', async (req: Request, res: Response) => {
  // TO DO
  const result : string = '';
  res.status(200).json({ user: result });
});
// POST - users
router.post('/', async (req: Request, res: Response) => {
  // TO DO
  res.status(201).json({ user: {} });
});
export default router;

We will leave a few // TO DO s, but we’ll come back to them later.

In the src/index.ts file, add the import to that route and also one more app.use(...) block right below the existing app.get(...)one:

// src/index.ts
...
import usersRoutes from './routes/users.routes';
...
app.use('/users', usersRoutes);
...

Now, besides the existing path, you can also access localhost:3000/users or localhost:3000/users/1 in your browser to get (for now empty) results. You can also test the POST route if you have a tool like Postman.

Part 3 - Connecting to the database

I’ll be running the database in a Docker container with Docker Compose. You can check my compose file in my repo.
To import the table and populate your database, run this sql file.

First, let’s add some variables to our .env file.

// .env
DB_HOST=localhost
DB_PORT=5432
DB_NAME=mypg
DB_USER=myuser
DB_PASSWORD=myPas$w0rd

Now let’s make this variables accessible from our TypeScript code.

// src/config.ts
...
export const db_host = String(process.env.DB_HOST);
export const db_port = Number(process.env.DB_PORT);
export const db_name = String(process.env.DB_NAME);
export const db_user = String(process.env.DB_USER);
export const db_password = String(process.env.DB_PASSWORD);

Let’s add sequelize and Postgres related packages.

If you want to connect to other databases or use some of the command line actions, check the official sequelize documentation. I’m using different directories than the official documentation.

npm i sequelize pg pg-hstore

We can now create a file that will return a sequelize database instance.

// src/database.ts
import { db_host, db_port, db_name, db_user, db_password } from './config';
import { Sequelize } from 'sequelize';
export default new Sequelize({
  dialect: "postgres",
  host: db_host,
  port: db_port,
  database: db_name,
  username: db_user,
  password: db_password
});

Time to create the entity we’ll use. Create a src/models folder and a user.ts file inside.

// src/models/user.ts
import { Model, Sequelize, DataTypes } from 'sequelize';
export default class User extends Model {
  public id?: number;
  public name!: string;
  public birthdate?: Date;
  public country?: string;
}
export const UserMap = (sequelize: Sequelize) => {
  User.init({
    id: {
      type: DataTypes.INTEGER,
      autoIncrement: true,
      primaryKey: true
    },
    name: {
      type: DataTypes.STRING(255)
    },
    birthdate: {
      type: DataTypes.DATE,
      allowNull: true
    },
    country: {
      type: DataTypes.STRING(100),
      allowNull: true
    }
  }, {
    sequelize,
    tableName: 'users',
    timestamps: false
  });
  User.sync();
}

Last, we just need to ensure we call the entity in the API methods. Add the imports and change the methods.

// src/routes/user.routes.ts
...
import User, { UserMap } from '../models/user';
import database from '../database';
...
// GET - users
router.get('/', async (req: Request, res: Response) => {
  UserMap(database);
  const result = await User.findAll();
  res.status(200).json({ users: result });
});
// GET - users/:id
router.get('/:id', async (req: Request, res: Response) => {
  UserMap(database);
  const id = Number(req.params.id);
  const result = await User.findByPk(id);
  res.status(200).json({ user: result });
});
// POST - users
router.post('/', async (req: Request, res: Response) => {
  let newUser = req.body as User;
  UserMap(database);
  const result = await User.create(newUser);
  newUser = result.dataValues as User;
  res.status(201).json({ user: newUser });
});
...

Done! This should be enough for adding and getting users from the database.

Conclusion

All the previous steps bring you to a basic API implementation using TypeScript, a Postgres and sequelize.

Of course this is a fast guide and does not goes through the explanation of why I did things in a certain way. Also, I didn’t take into consideration any architecture choices or best practices for a lot of things. There are plenty of ways you can improve the code above, e.g. create a services and repository layers, avoid the creation of multiple database instances, improve model mapping — and many more.

Also, there are plenty of frameworks out there. I did it using Express, but you could use Meteor, AdonisJs, and many more — which most of times make you skip the above steps straight to your logic.

Hopefully this was helpful enough to be a kick-start for your project. Have a good one!