Node.js, Express, TypeORM, PostgreSQL: CRUD Rest API

In this article, you’ll learn how to implement the basic CRUD operations by building a REST API with Node.js, ExpressJs, TypeORM, and PostgreSQL.

Related Post: Backend

1. API with Node.js + PostgreSQL + TypeORM: Project Setup
2. API with Node.js + PostgreSQL + TypeORM: JWT Authentication
3. API with Node.js + PostgreSQL + TypeORM: Send Emails
4. Node.js, Express, TypeORM, PostgreSQL: CRUD Rest API
5. Node.js and PostgreSQL: Upload and Resize Multiple Images

Introduction

In this course, you’ll learn how to build a REST API server with Node.js, TypeScript, TypeORM, and PostgreSQL.

The CRUD Rest API is built with:

• Node.js – a JavaScript scripting language
• TypeORM – an ORM (Object Relational Mapping) for popular Database services like PostgreSQL, MySQL, MongoDB, and many more.
• PostgreSQL – a Relational SQL database
• Zod – for validating user inputs

Node.js, TypeORM, PostgreSQL CRUD RESTful API Overview

We’ll build a REST API where we can create, read, update and delete a post in the database.

RESOURCE	HTTP METHOD	ROUTE	DESCRIPTION
posts	GET	/api/posts	Retrieve all posts
posts	POST	/api/posts	Creates a new post
posts	GET	/api/posts/:id	Retrieve a single post
posts	PATCH	/api/posts/:id	Update a post
posts	DELETE	/api/posts/:id	Delete a post

To test the API endpoints, am going to use HTTP Client VS Code extension.

-Create a new post by making a POST request to /api/posts

-Response after the post has been created

-Update a single post by making a PATCH request to /api/posts/:id

-Response after updating the post

-Get a single post by making a GET request to /api/posts/:id

-JSON response after retrieving a single post

-Delete a single post by making a DELETE request to /api/posts/:id

-Response after deleting a single post

-Get all the posts by making a GET request to /api/posts

-Response for retrieving all posts

-You should see all the posts you created in the PostgreSQL database.

Project Structure

Test/
├── config/
│ ├── custom-environment-variables.ts
│ └── default.ts
├── src/
│ ├── controllers/
│ │ ├── auth.controller.ts
│ │ ├── post.controller.ts
│ │ └── user.controller.ts
│ ├── entities/
│ │ ├── model.entity.ts
│ │ ├── post.entity.ts
│ │ └── user.entity.ts
│ ├── middleware/
│ │ ├── deserializeUser.ts
│ │ ├── requireUser.ts
│ │ └── validate.ts
│ ├── migrations/
│ │ └── 1652179771572-added-entity.ts
│ ├── routes/
│ │ ├── auth.routes.ts
│ │ ├── post.routes.ts
│ │ └── user.routes.ts
│ ├── schemas/
│ │ ├── post.schema.ts
│ │ └── user.schema.ts
│ ├── services/
│ │ ├── post.service.ts
│ │ └── user.service.ts
│ ├── utils/
│ │ ├── appError.ts
│ │ ├── connectRedis.ts
│ │ ├── data-source.ts
│ │ ├── email.ts
│ │ ├── jwt.ts
│ │ └── validateEnv.ts
│ ├── views/
│ │ ├── base.pug
│ │ ├── resetPassword.pug
│ │ ├── verificationCode.pug
│ │ └── _styles.pug
│ ├── app.ts
│ └── example.env
├── .env
├── .gitignore
├── docker-compose.yml
├── package.json
├── README.md
├── tsconfig.json
├── yarn-error.log
└── yarn.lock

Model Data with TypeORM and PostgreSQL

When we build an application, we create many entities that have some repeated columns ( id, created_at, and updated_at columns).

To follow the DRY (Don’t-Repeat-Yourself) principle, TypeORM gives us the luxury of creating entities that inherit the columns of a base entity.

TypeORM also comes with a defined BaseEntity that we can extend to have access to the various attributes and methods we can use to perform CRUD operations.

In this project, we only have two entities but in an enterprise-level application that has a lot of entities, it makes sense to create a custom BaseModel that other entities can inherit from.

Define a Base Entity

import {
  CreateDateColumn,
  UpdateDateColumn,
  PrimaryGeneratedColumn,
  BaseEntity,
} from 'typeorm';

export default abstract class Model extends BaseEntity {
  @PrimaryGeneratedColumn('uuid')
  id: string;

  @CreateDateColumn()
  created_at: Date;

  @UpdateDateColumn()
  updated_at: Date;
}

Add One-to-Many Relationship to User Entity

Data modeling can be a little challenging especially when it comes to identifying the relationships between the different data models, entities, or tables.

When we build an application, we create entities that often relate to each other, and defining the relationships is a crucial part of designing the database model.

In this project, we have user and post entities. A user can create many posts but a single post can belong to only one user.

On the User entity, define a one-to-many relationship.

@Entity('users')
export class User extends Model {
  @OneToMany(() => Post, (post) => post.user)
  posts: Post[];

}

Complete User Entity

import crypto from 'crypto';
import { Entity, Column, Index, BeforeInsert, OneToMany } from 'typeorm';
import bcrypt from 'bcryptjs';
import Model from './model.entity';
import { Post } from './post.entity';

export enum RoleEnumType {
  USER = 'user',
  ADMIN = 'admin',
}

@Entity('users')
export class User extends Model {
  @Column()
  name: string;

  @Index('email_index')
  @Column({
    unique: true,
  })
  email: string;

  @Column()
  password: string;

  @Column({
    type: 'enum',
    enum: RoleEnumType,
    default: RoleEnumType.USER,
  })
  role: RoleEnumType.USER;

  @Column({
    default: 'default.png',
  })
  photo: string;

  @Column({
    default: false,
  })
  verified: boolean;

  @Index('verificationCode_index')
  @Column({
    type: 'text',
    nullable: true,
  })
  verificationCode!: string | null;

  @OneToMany(() => Post, (post) => post.user)
  posts: Post[];

  @BeforeInsert()
  async hashPassword() {
    this.password = await bcrypt.hash(this.password, 12);
  }

  static async comparePasswords(
    candidatePassword: string,
    hashedPassword: string
  ) {
    return await bcrypt.compare(candidatePassword, hashedPassword);
  }

  static createVerificationCode() {
    const verificationCode = crypto.randomBytes(32).toString('hex');

    const hashedVerificationCode = crypto
      .createHash('sha256')
      .update(verificationCode)
      .digest('hex');

    return { verificationCode, hashedVerificationCode };
  }

  toJSON() {
    return {
      ...this,
      password: undefined,
      verified: undefined,
      verificationCode: undefined,
    };
  }
}

Create a Post Entity

Next, let’s define the Post entity by extending the BaseModel. On the Post entity, the relationship will be Many-To-One. With TypeORM, you need to add a JoinColumn() on the many-side of the relationship.

Also, I added a unique constraint on the Post entity so that no two or more posts can have the same title.

import { Column, Entity, JoinColumn, ManyToOne } from 'typeorm';
import Model from './model.entity';
import { User } from './user.entity';

@Entity('posts')
export class Post extends Model {
  @Column({
    unique: true,
  })
  title: string;

  @Column()
  content: string;

  @Column({
    default: 'default-post.png',
  })
  image: string;

  @ManyToOne(() => User, (user) => user.posts)
  @JoinColumn()
  user: User;
}

Create Validation Schemas with Zod

Mostly the default validation library used in conjunction with TypeORM is class-validator but in this project I decided to put the validation logic in its own file.

This will prevent us from populating the columns in our entities with decorators.

Also, there are many validation libraries (Class-validator, Yup, Zod, Superstruct, Joi, Vest, etc) we could have used but I chose Zod because I mostly use it in my React.js and Node.js projects.

Always validate the incoming data even if the frontend also validated the same data

After defining the schemas of the incoming data, you need to export the TypeScript types from the schemas by using the TypeOf type that comes with Zod.

import { object, string, TypeOf } from 'zod';

export const createPostSchema = object({
  body: object({
    title: string({
      required_error: 'Title is required',
    }),
    content: string({
      required_error: 'Content is required',
    }),
    image: string({
      required_error: 'Image is required',
    }),
  }),
});

const params = {
  params: object({
    postId: string(),
  }),
};

export const getPostSchema = object({
  ...params,
});

export const updatePostSchema = object({
  ...params,
  body: object({
    title: string(),
    content: string(),
    image: string(),
  }).partial(),
});

export const deletePostSchema = object({
  ...params,
});

export type CreatePostInput = TypeOf<typeof createPostSchema>['body'];
export type GetPostInput = TypeOf<typeof getPostSchema>['params'];
export type UpdatePostInput = TypeOf<typeof updatePostSchema>;
export type DeletePostInput = TypeOf<typeof deletePostSchema>['params'];

Create Services to Communicate with Database

Before we start creating the post controllers, we need to define some services that will communicate with the PostgreSQL database.

It’s recommended to create services that access and modify the database. This comes with a lot of benefits especially when it comes to testing our application.

import {
  FindOptionsRelations,
  FindOptionsSelect,
  FindOptionsWhere,
} from 'typeorm';
import { Post } from '../entities/post.entity';
import { User } from '../entities/user.entity';
import { AppDataSource } from '../utils/data-source';

const postRepository = AppDataSource.getRepository(Post);

export const createPost = async (input: Partial<Post>, user: User) => {
  return await postRepository.save(postRepository.create({ ...input, user }));
};

export const getPost = async (postId: string) => {
  return await postRepository.findOneBy({ id: postId });
};

export const findPosts = async (
  where: FindOptionsWhere<Post> = {},
  select: FindOptionsSelect<Post> = {},
  relations: FindOptionsRelations<Post> = {}
) => {
  return await postRepository.find({
    where,
    select,
    relations,
  });
};

Create Controllers

Now, it’s time to create the controllers to perform the different CRUD operations. The /api/posts route will be a protected route. That means you must first log in to your account before you can create, read, update or delete a post.

Create a New Post

import { NextFunction, Request, Response } from 'express';
import {
  CreatePostInput,
  DeletePostInput,
  GetPostInput,
  UpdatePostInput,
} from '../schemas/post.schema';
import { createPost, findPosts, getPost } from '../services/post.service';
import { findUserById } from '../services/user.service';
import AppError from '../utils/appError';

// ? POST method:- Create a new Post
export const createPostHandler = async (
  req: Request<{}, {}, CreatePostInput>,
  res: Response,
  next: NextFunction
) => {
  try {
    const user = await findUserById(res.locals.user.id as string);

    const post = await createPost(req.body, user!);

    res.status(201).json({
      status: 'success',
      data: {
        post,
      },
    });
  } catch (err: any) {
    if (err.code === '23505') {
      return res.status(409).json({
        status: 'fail',
        message: 'Post with that title already exist',
      });
    }
    next(err);
  }
};

Here is a breakdown of what I did above:

• First, I called the findUserById service with the logged-in user’s ID to check if that user exists in the database.
• Next, I called the createPost service to add a new post to the database.
• Also, since I added a unique constraint to the post title, I returned a conflict error if a post with that title already exists in the database.

Get a Single Post

To get a single post, the user will make a GET request to /api/posts/:id and the server will return the post as a JSON response if it exists.

// ? POST method:- Create a new Post

// ? GET method:- Create a new Post
export const getPostHandler = async (
  req: Request<GetPostInput>,
  res: Response,
  next: NextFunction
) => {
  try {
    const post = await getPost(req.params.postId);

    if (!post) {
      return next(new AppError(404, 'Post with that ID not found'));
    }

    res.status(200).json({
      status: 'success',
      data: {
        post,
      },
    });
  } catch (err: any) {
    next(err);
  }
};

Get All Posts

// ? POST method:- Create a new Post

// ? GET method:- Create a new Post

// ? GET method:- Get all Posts
export const getPostsHandler = async (
  req: Request,
  res: Response,
  next: NextFunction
) => {
  try {
    const posts = await findPosts({}, {}, {});

    res.status(200).json({
      status: 'success',
      data: {
        posts,
      },
    });
  } catch (err: any) {
    next(err);
  }
};

Update a Single Post

// ? POST method:- Create a new Post

// ? GET method:- Create a new Post

// ? GET method:- Get all Posts

// ? PATCH method:- Update Post
export const updatePostHandler = async (
  req: Request<UpdatePostInput['params'], {}, UpdatePostInput['body']>,
  res: Response,
  next: NextFunction
) => {
  try {
    const post = await getPost(req.params.postId);

    if (!post) {
      return next(new AppError(404, 'Post with that ID not found'));
    }

    Object.assign(post, req.body);

    const updatedPost = await post.save();

    res.status(200).json({
      status: 'success',
      data: {
        post: updatedPost,
      },
    });
  } catch (err: any) {
    next(err);
  }
};

Delete a Single Post

// ? POST method:- Create a new Post

// ? GET method:- Create a new Post

// ? GET method:- Get all Posts

// ? PATCH method:- Update Post

// ? POST method:- Delete Post
export const deletePostHandler = async (
  req: Request<DeletePostInput>,
  res: Response,
  next: NextFunction
) => {
  try {
    const post = await getPost(req.params.postId);

    if (!post) {
      return next(new AppError(404, 'Post with that ID not found'));
    }

    await post.remove();

    res.status(204).json({
      status: 'success',
      data: null,
    });
  } catch (err: any) {
    next(err);
  }
};

Create Routes

With all the above in place, let’s define all the routes that we can use to perform the CRUD operations.

These are the routes:

• /api/posts – GET, POST
• /api/posts/:id – GET, DELETE, PATCH

Since the CRUD operations of the post resource are protected, we need to run the deserializeUser and requireUser middleware to check if the user is logged in before passing the request to the other handlers in the request-response cycle.

Also, I called the validate() middleware and passed the appropriate schema definition as an argument to validate the incoming request body.

This way the validate() middleware will parse the schema and immediately return a bad request error if the user fails to provide any of the required fields.

import express from 'express';
import {
  createPostHandler,
  deletePostHandler,
  getPostHandler,
  getPostsHandler,
  updatePostHandler,
} from '../controllers/post.controller';
import { deserializeUser } from '../middleware/deserializeUser';
import { requireUser } from '../middleware/requireUser';
import { validate } from '../middleware/validate';
import {
  createPostSchema,
  deletePostSchema,
  getPostSchema,
  updatePostSchema,
} from '../schemas/post.schema';

const router = express.Router();

router.use(deserializeUser, requireUser);
router
  .route('/')
  .post(validate(createPostSchema), createPostHandler)
  .get(getPostsHandler);

router
  .route('/:postId')
  .get(validate(getPostSchema), getPostHandler)
  .patch(validate(updatePostSchema), updatePostHandler)
  .delete(validate(deletePostSchema), deletePostHandler);

export default router;

Update Root App (App.ts)

Back to app.ts , import the post route file and include it in the middleware stack with app.use('/api/posts', postRouter) .

The post router in this case serves as a mini-app. When a request hit /api/posts the server will then call the post router to handle the request.

require('dotenv').config();
import express, { NextFunction, Request, Response } from 'express';
import config from 'config';
import morgan from 'morgan';
import cookieParser from 'cookie-parser';
import cors from 'cors';
import { AppDataSource } from './utils/data-source';
import AppError from './utils/appError';
import authRouter from './routes/auth.routes';
import userRouter from './routes/user.routes';
import postRouter from './routes/post.routes';
import validateEnv from './utils/validateEnv';
import redisClient from './utils/connectRedis';

AppDataSource.initialize()
  .then(async () => {
    // VALIDATE ENV
    validateEnv();

    const app = express();

    // TEMPLATE ENGINE
    app.set('view engine', 'pug');
    app.set('views', `${__dirname}/views`);

    // MIDDLEWARE

    // 1. Body parser
    app.use(express.json({ limit: '10kb' }));

    // 2. Logger
    if (process.env.NODE_ENV === 'development') app.use(morgan('dev'));

    // 3. Cookie Parser
    app.use(cookieParser());

    // 4. Cors
    app.use(
      cors({
        origin: config.get<string>('origin'),
        credentials: true,
      })
    );

    // ROUTES
    app.use('/api/auth', authRouter);
    app.use('/api/users', userRouter);
    app.use('/api/posts', postRouter);

    // HEALTH CHECKER
    app.get('/api/healthChecker', async (_, res: Response) => {
      const message = await redisClient.get('try');

      res.status(200).json({
        status: 'success',
        message,
      });
    });

    // UNHANDLED ROUTE
    app.all('*', (req: Request, res: Response, next: NextFunction) => {
      next(new AppError(404, `Route ${req.originalUrl} not found`));
    });

    // GLOBAL ERROR HANDLER
    app.use(
      (error: AppError, req: Request, res: Response, next: NextFunction) => {
        error.status = error.status || 'error';
        error.statusCode = error.statusCode || 500;

        res.status(error.statusCode).json({
          status: error.status,
          message: error.message,
        });
      }
    );

    const port = config.get<number>('port');
    app.listen(port);

    console.log(`Server started on port: ${port}`);
  })
  .catch((error) => console.log(error));

Run Migrations and Push Changes to Database

Now it’s time to synchronize the entities we defined above with our database.

To do that we need to generate the corresponding SQL codes from the entities by running a migration command.

After the migration has been generated, we can run the push command to push the changes to the database.

{
"scripts": {
    "start": "ts-node-dev --respawn --transpile-only --exit-child src/app.ts",
    "build": "tsc -p .",
    "typeorm": "typeorm-ts-node-commonjs",
    "migrate": "rm -rf build && yarn build && yarn typeorm migration:generate ./src/migrations/added-entity -d ./src/utils/data-source.ts",
    "db:push": "rm -rf build && yarn build && yarn typeorm migration:run -d src/utils/data-source.ts"
  }
}

Run this command to create and push the migration to the database.

yarn migrate && yarn db:push

Conclusion

In this article, you learned how to implement CRUD operations by building a RESTful API with Node.js, Express.js, TypeORM, PostgreSQL, and Docker.

Checkout source code on GitHub