Building REST API with Express, TypeScript - Part 3: PostgreSQL and TypeORM

29 December 2020

In the previous post, we have dockerized the Express and TypeScript REST API server. In this post, we will set up a PostgreSQL database and add some APIs to the server.

In this post, we will be extending the REST API server by setting up the database connection and adding some APIs. We will be using PostgreSQL for the database. You can choose any database as per requirements. In this sample app, there will be few entities that will have relations with each other. That's why we are using a relational database for the server.

There are many ways to handle the database from the app. Some people prefer to connect database engine directly and make raw SQL Queries. And some people prefer to use ORM to connect and query the database engine. There are some advantages and tradeoffs of using ORM. Like ORM provides an abstraction over raw queries, which speeds up the development. But sometimes for some complex operations, ORM queries tend to be slow, in those conditions it is better to write raw queries.

There are multiple ORMs for Nodejs like Sequelize, Bookshelf, and TypeORM. We will be using TypeORM because it has better TypeScript support at this time.

Install PostgreSQL and TypeORM

Let's install typeorm and reflect-metadata as dependencies to our project. We also need to install Postgres driver pg to our project.

npm install typeorm reflect-metadata --save
npm install pg --save

Setup Postgres database server

We need a Postgres database server to connect from our REST server. You can use a remote server or a local server. We will be using a local database server for this app, and instead of installing and running sever on our machine, we will using docker to do that.

In the docker-compose.yml file, we are adding a database service named db using postgres images and adding it as a dependency to the app service.

docker-compose.yml

version: "3"

services:
  db:
    image: postgres:12
    environment:
      - POSTGRES_DB=express-ts
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=postgres
  app:
    build:
      context: .
      dockerfile: Dockerfile.dev
    volumes:
      - ./src:/app/src
    ports:
      - "8000:8000"
    depends_on:
      - db
    environment:
      - POSTGRES_DB=express-ts
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=postgres
      - POSTGRES_HOST=db

Here we are using a database called express-ts and using postgres as username and password to connect the database from the REST server. Let's build and start the services.

docker-compose build
docker-compose up

Setup TypeORM and database connection

Create a database.ts file in the config directory. We will be keeping all database related config in this file. Here we are reading the database host, port, name from the environment variables which we are passing through the docker-compose.yml file.

src/config/database.ts

import { ConnectionOptions } from "typeorm";

const config: ConnectionOptions = {
  type: "postgres",
  host: process.env.POSTGRES_HOST || "localhost",
  port: Number(process.env.POSTGRES_PORT) || 5432,
  username: process.env.POSTGRES_USER || "postgres",
  password: process.env.POSTGRES_PASSWORD || "postgres",
  database: process.env.POSTGRES_DB || "postgres",
  entities: [],
  synchronize: true,
};

export default config;

Import the database configuration in the index.ts file and pass the config to the TypeORM's createConnection function. The createConnection function is asynchronous, so we will wait for the promise to resolve before binding and listening to the PORT. If there is an error in connecting to the database we will log the error and exit the server.

src/index.ts

import "reflect-metadata";
import { createConnection } from "typeorm";
import express, { Application } from "express";
import morgan from "morgan";
import swaggerUi from "swagger-ui-express";

import Router from "./routes";
import dbConfig from "./config/database";

const PORT = process.env.PORT || 8000;

const app: Application = express();

app.use(express.json());
app.use(morgan("tiny"));
app.use(express.static("public"));

app.use(
  "/docs",
  swaggerUi.serve,
  swaggerUi.setup(undefined, {
    swaggerOptions: {
      url: "/swagger.json",
    },
  })
);

app.use(Router);

createConnection(dbConfig)
  .then((_connection) => {
    app.listen(PORT, () => {
      console.log("Server is running on port", PORT);
    });
  })
  .catch((err) => {
    console.log("Unable to connect to db", err);
    process.exit(1);
  });

Creating Models

Let's create models for the REST Server. We will have 3 models in the server - User, Post, and Comment. We will put them in the models directory.

Let's create the User model first. Here we need to add the Entity decorator to the User class. The User model will create the user table in the database with id, firstName, lastName, email, createdAt, and updatedAt as table columns. The id is the primary key of the user table and will be auto-generated with an auto-increment value. The createdAt & updatedAt fields will be auto-generated too and set during insert and update operations.

src/models/user.ts

import {
  Entity,
  PrimaryGeneratedColumn,
  Column,
  CreateDateColumn,
  UpdateDateColumn,
} from "typeorm";

@Entity()
export class User {
  @PrimaryGeneratedColumn()
  id!: number;

  @Column()
  firstName!: string;

  @Column()
  lastName!: string;

  @Column()
  email!: string;

  @CreateDateColumn()
  createdAt!: Date;

  @UpdateDateColumn()
  updatedAt!: Date;
}

Here we are adding an exclamation mark (!) to the properties because the properties are not assigned in the constructor and can have the value undefined. We can either set strictPropertyInitialization to true in the tsconfig.json file or add the exclamation mark. Adding an exclamation mark in the model properties is better than changing the config for the whole project.

src/models/index.ts

import { User } from "./user";
export { User };

We have to import the User model in the database config file and add it to the entities property.

src/config/database.ts

import { ConnectionOptions } from "typeorm";
import {User} from '../models'

const config: ConnectionOptions = {
  ...
  entities: [User],
  ...
};

We will do the same thing for other models, ie. Post and Comment. We will create the Post and Comment models in the models directory and define columns to the models. In the Post models we will have id, title, content, userId, createdAt, and updatedAt columns. We are passing type as text to the Column decorator for the content column to explicitly define the column type as text.

The Post model is dependent on the User model as every post will be created by some user. So the column userId will map to the user. Here the userId is the foreign key for the user table. We also add a relation to the user model. We are defining a property called user of type User and adding relations ManyToOne as one user can create multiple posts.

src/models/post.ts

import {
  Entity,
  PrimaryGeneratedColumn,
  Column,
  ManyToOne,
  OneToMany,
  CreateDateColumn,
  UpdateDateColumn,
  JoinColumn,
} from "typeorm";
import { Comment } from "./comment";
import { User } from "./user";

@Entity()
export class Post {
  @PrimaryGeneratedColumn()
  id!: number;

  @Column()
  title!: string;

  @Column({
    type: "text",
  })
  content!: string;

  @Column({ nullable: true })
  userId!: number;
  @ManyToOne((_type) => User, (user: User) => user.posts)
  @JoinColumn()
  user!: User;

  @OneToMany((_type) => Comment, (comment: Comment) => comment.post)
  comments!: Array<Comment>;

  @CreateDateColumn()
  createdAt!: Date;

  @UpdateDateColumn()
  updatedAt!: Date;
}

As the Post model, the Comment model is also dependent on the User model, so we will add the userId column as a foreign key and add the user property with the type User and with the relation ManyToOne with the User model.

The Comment model is also dependent on the Post model as there can be multiple comments on a post that's why a comment will be mapped to a post. The column postId will be a foreign key to the post table. we also add the property called post of type Post. We also have to add a property called comments of type array of Comment to the Post model.

src/models/comment.ts

import {
  Entity,
  PrimaryGeneratedColumn,
  Column,
  ManyToOne,
  CreateDateColumn,
  UpdateDateColumn,
  JoinColumn,
} from "typeorm";
import { Post } from "./post";
import { User } from "./user";

@Entity()
export class Comment {
  @PrimaryGeneratedColumn()
  id!: number;

  @Column({
    type: "text",
  })
  content!: string;

  @Column({ nullable: true })
  userId!: number;
  @ManyToOne((_type) => User, (user: User) => user.comments)
  @JoinColumn()
  user!: User;

  @Column({ nullable: true })
  postId!: number;
  @ManyToOne((_type) => Post, (post: Post) => post.comments)
  @JoinColumn()
  post!: Post;

  @CreateDateColumn()
  createdAt!: Date;

  @UpdateDateColumn()
  updatedAt!: Date;
}

As both the Post and the Comment models are dependent on the User model and have relations ManyToOne to it. We have to add the posts and comments properties to the User model with the relation OneToMany as one user can have multiple posts and comments. These properties will be of type array of Post and array of Comment respectively.

src/models/user.ts

import {
  Entity,
  PrimaryGeneratedColumn,
  Column,
  CreateDateColumn,
  OneToMany,
  UpdateDateColumn,
} from "typeorm";
import { Post } from "./post";
import { Comment } from "./comment";

@Entity()
export class User {
  @PrimaryGeneratedColumn()
  id!: number;

  @Column()
  firstName!: string;

  @Column()
  lastName!: string;

  @Column()
  email!: string;

  @OneToMany((_type) => Post, (post: Post) => post.user)
  posts!: Array<Post>;

  @OneToMany((_type) => Comment, (comment: Comment) => comment.user)
  comments!: Array<Comment>;

  @CreateDateColumn()
  createdAt!: Date;

  @UpdateDateColumn()
  updatedAt!: Date;
}

src/models/index.ts

import { User } from "./user";
import { Post } from "./post";
import { Comment } from "./comment";
export { User, Post, Comment };

We have to import the Post and the Comment models in the database config file and add them to the entities property as we did with the User model.

src/config/database.ts

import { ConnectionOptions } from "typeorm";
import {User, Post, Comment} from '../models'

const config: ConnectionOptions = {
  ...
  entities: [User, Post, Comment],
  ...
};

Verify Database schema

Once we add the models to the server and start it, the tables will be created in the database. We can verify it by executing the bash command to the running docker container. In the docker container, we have to run the psql cli.

docker exec -it express-typescript_db_1 bash

psql -U postgres

After a successful login to the psql cli, we can run the commands to select the database and then list all the tables and get the tables definitions.

\l              # list database.
\c express-ts   # select `express-ts` database.
\dt             # list all tables.
\d user         # Show `user` table definition.
\d post         # Show `post` table definition.
\d comment      # Show `comment` table definition.

User Apis

Let's add the APIs for the user. We will create 3 APIs - Create user, Fetch user, and Fetch all users. We will be following the data mapper pattern. Each model will have one repository. And from the server, we will query the database through the repository only. We will import the models in the app just for the type. There will be no direct operation through the models. This pattern helps in maintainability in large scale projects.

Let's create the user repository in the repositories directory. We will be exporting 3 functions from this file. The getUsers function is simple it just calls the find method on user repository and return list of User. All the 3 functions are asynchronous and return a Promise as the return type. The createUser function requires an argument called payload. We are defining the type of it as the interface IUserPayload. In the function, we will create a new instance of the User model and call the save method on the user repository. This will insert the row in the user table. In the getUser function we call the findOne method on the user repository. If the value is undefined we will return null otherwise return the found user.

src/repositories/user.ts

import { getRepository } from "typeorm";
import { User } from "../models";

export interface IUserPayload {
  firstName: string;
  lastName: string;
  email: string;
}

export const getUsers = async (): Promise<Array<User>> => {
  const userRepository = getRepository(User);
  return userRepository.find();
};

export const createUser = async (payload: IUserPayload): Promise<User> => {
  const userRepository = getRepository(User);
  const user = new User();
  return userRepository.save({
    ...user,
    ...payload,
  });
};

export const getUser = async (id: number): Promise<User | null> => {
  const userRepository = getRepository(User);
  const user = await userRepository.findOne({ id: id });
  if (!user) return null;
  return user;
};

Let's add a controller for the user APIs. We will create a class called UserController and add the Route and the Tags decorator to it. This is for swagger file generation. Here we define 3 methods - getUsers, createUser, and getUser. Then we add the decorators to them. The createUser method is the post request, and the schema of the body is of the IUserPayload type.

src/controllers/user.controller.ts

import { Get, Route, Tags, Post, Body, Path } from "tsoa";
import { User } from "../models";
import {
  getUsers,
  createUser,
  IUserPayload,
  getUser,
} from "../repositories/user";

@Route("users")
@Tags("User")
export default class UserController {
  @Get("/")
  public async getUsers(): Promise<Array<User>> {
    return getUsers();
  }

  @Post("/")
  public async createUser(@Body() body: IUserPayload): Promise<User> {
    return createUser(body);
  }

  @Get("/:id")
  public async getUser(@Path() id: string): Promise<User | null> {
    return getUser(Number(id));
  }
}

Let's create a router for the user APIs. We need to create a user.router.ts file in the routes directory. In this file, we will create a new express router and add all the user routes to it. We will export the router from the file and import it into the root router and use it for the /users route.

src/routes/user.router.ts

import express from "express";
import UserController from "../controllers/user.controller";

const router = express.Router();

router.get("/", async (_req, res) => {
  const controller = new UserController();
  const response = await controller.getUsers();
  return res.send(response);
});

router.post("/", async (req, res) => {
  const controller = new UserController();
  const response = await controller.createUser(req.body);
  return res.send(response);
});

router.get("/:id", async (req, res) => {
  const controller = new UserController();
  const response = await controller.getUser(req.params.id);
  if (!response) res.status(404).send({ message: "No user found" });
  return res.send(response);
});

export default router;

src/routes/index.ts

import express from "express";
import PingController from "../controllers/ping.controller";
import UserRouter from "./user.router";

const router = express.Router();

router.get("/ping", async (_req, res) => {
  const controller = new PingController();
  const response = await controller.getMessage();
  return res.send(response);
});

router.use("/users", UserRouter);

export default router;

After adding the routes and restarting the server, we can verify the APIs by checking the swagger docs. The swagger docs will be updated with the User APIs. We can call the APIs directly from the swagger docs or call them from the terminal. We can create a user with some random data and then call the GET API to get the user.

curl -X POST -H "Content-Type: application/json" -d '{"firstName": "foo", "lastName": "bar", "email": "foo@bar.com"}' http://localhost:8000/users

curl http://localhost:8000/users

curl http://localhost:8000/users/1

Post and Comment Apis

For the Post and the Comment APIs, we will be doing the same as the User APIs. We will create 3 APIs for the Post model - Create post, Fetch post and Fetch all posts. Create comment, Fetch comment, and Fetch all comments for the Comment model.

We will create post.repository.ts and comment.repository.ts files in the repository directory and export database operations function from them like we did in the user repository.

The IPostPayload interface is the type for creatPost payload and ICommentPayload for createComment payload.

src/repositories/post.repository.ts

import { getRepository } from "typeorm";
import { Post } from "../models";

export interface IPostPayload {
  title: string;
  content: string;
  userId: number;
}

export const getPosts = async (): Promise<Array<Post>> => {
  const postRepository = getRepository(Post);
  return postRepository.find();
};

export const createPost = async (payload: IPostPayload): Promise<Post> => {
  const postRepository = getRepository(Post);
  const post = new Post();
  return postRepository.save({
    ...post,
    ...payload,
  });
};

export const getPost = async (id: number): Promise<Post | null> => {
  const postRepository = getRepository(Post);
  const post = await postRepository.findOne({ id: id });
  if (!post) return null;
  return post;
};

src/repositories/comment.repository.ts

import { getRepository } from "typeorm";
import { Comment } from "../models";

export interface ICommentPayload {
  content: string;
  userId: number;
  postId: number;
}

export const getComments = async (): Promise<Array<Comment>> => {
  const commentRepository = getRepository(Comment);
  return commentRepository.find();
};

export const createComment = async (
  payload: ICommentPayload
): Promise<Comment> => {
  const commentRepository = getRepository(Comment);
  const comment = new Comment();
  return commentRepository.save({
    ...comment,
    ...payload,
  });
};

export const getComment = async (id: number): Promise<Comment | null> => {
  const commentRepository = getRepository(Comment);
  const comment = await commentRepository.findOne({ id: id });
  if (!comment) return null;
  return comment;
};

We will create controllers for both the Post and the Comment APIs. We will create post.controller.ts and comment.controller.ts files in the controller directory and create classes called PostController and CommentController in them respectively.

src/controllers/post.controller.ts

import { Get, Route, Tags, Post as PostMethod, Body, Path } from "tsoa";
import { Post } from "../models";
import {
  createPost,
  getPosts,
  IPostPayload,
  getPost,
} from "../repositories/post.repository";

@Route("posts")
@Tags("Post")
export default class PostController {
  @Get("/")
  public async getPosts(): Promise<Array<Post>> {
    return getPosts();
  }

  @PostMethod("/")
  public async createPost(@Body() body: IPostPayload): Promise<Post> {
    return createPost(body);
  }

  @Get("/:id")
  public async getPost(@Path() id: string): Promise<Post | null> {
    return getPost(Number(id));
  }
}

In the Post Controller, there is a conflict with the Post model name and Post decorator. That's why we are aliasing the Post decorator as PostMethod in this file.

src/controllers/comment.controller.ts

import { Get, Route, Tags, Post, Body, Path } from "tsoa";
import { Comment } from "../models";
import {
  getComments,
  ICommentPayload,
  createComment,
  getComment,
} from "../repositories/comment.repository";

@Route("comments")
@Tags("Comment")
export default class CommentController {
  @Get("/")
  public async getComments(): Promise<Array<Comment>> {
    return getComments();
  }

  @Post("/")
  public async createComment(@Body() body: ICommentPayload): Promise<Comment> {
    return createComment(body);
  }

  @Get("/:id")
  public async getComment(@Path() id: string): Promise<Comment | null> {
    return getComment(Number(id));
  }
}

We will create the routers for both the post and the comment APIs. We will import both routers in the root router file and use it for the /posts and /comments route.

src/routes/post.router.ts

import express from "express";
import PostController from "../controllers/post.controller";

const router = express.Router();

router.get("/", async (_req, res) => {
  const controller = new PostController();
  const response = await controller.getPosts();
  return res.send(response);
});

router.post("/", async (req, res) => {
  const controller = new PostController();
  const response = await controller.createPost(req.body);
  return res.send(response);
});

router.get("/:id", async (req, res) => {
  const controller = new PostController();
  const response = await controller.getPost(req.params.id);
  if (!response) res.status(404).send({ message: "No post found" });
  return res.send(response);
});

export default router;

src/routes/comment.router.ts

import express from "express";
import CommentController from "../controllers/comment.controller";

const router = express.Router();

router.get("/", async (_req, res) => {
  const controller = new CommentController();
  const response = await controller.getComments();
  return res.send(response);
});

router.post("/", async (req, res) => {
  const controller = new CommentController();
  const response = await controller.createComment(req.body);
  return res.send(response);
});

router.get("/:id", async (req, res) => {
  const controller = new CommentController();
  const response = await controller.getComment(req.params.id);
  if (!response) res.status(404).send({ message: "No comment found" });
  return res.send(response);
});

export default router;

src/routes/index.ts

import express from "express";
import PingController from "../controllers/ping.controller";
import PostRouter from "./post.router";
import UserRouter from "./user.router";
import CommentRouter from "./comment.router";

const router = express.Router();

router.get("/ping", async (_req, res) => {
  const controller = new PingController();
  const response = await controller.getMessage();
  return res.send(response);
});

router.use("/users", UserRouter);
router.use("/posts", PostRouter);
router.use("/comments", CommentRouter);

export default router;

After restarting the server, the swagger docs will be updated and will have post and comment APIs.

All the source code for this tutorial is available on GitHub.

Building REST API with Express, TypeScript - Part 4: Jest and unit testing

Building REST API with Express, TypeScript - Part 3: PostgreSQL and TypeORM

Install PostgreSQL and TypeORM

Setup Postgres database server

Setup TypeORM and database connection

Creating Models

Verify Database schema

User Apis

Post and Comment Apis

Next