Building a CRUD API with NestJS, PostgreSQL, and Liquibase

This report outlines the steps involved in creating a CRUD (Create, Read, Update, Delete) API using NestJS, PostgreSQL, and Liquibase for database management.

Technologies Used:

NestJS: A Node.js framework for building efficient and scalable server-side applications.
 PostgreSQL: An open-source object-relational database management system (ORDBMS).
 Liquibase: An open-source database migration tool for managing database schema changes.
 TypeORM: An object-relational mapper (ORM) that simplifies working with databases in TypeScript or JavaScript.

Setting Up the Environment

Docker Compose:

The provided docker-compose.yml file defines services for:

postgres-tasks: Runs a PostgreSQL container with specific configurations.
 adminer: Runs an admin interface for managing the PostgreSQL database.
 liquibase: Runs a Liquibase container to handle database schema changes.

Liquibase Configuration:

changelog.xml: This file defines how Liquibase manages database schema changes.
migrations/*.sql: These files contain the actual SQL statements for creating and modifying database tables.

NestJS Application:

The application code utilizes TypeORM to interact with the PostgreSQL database.

app.module.ts: Configures the connection to the PostgreSQL database using TypeORM.
task.entity.ts: Defines the structure of the Task entity to be stored in the database.
task.repository.ts: Provides methods for interacting with the Task entity in the database.
tasks.controller.ts, tasks.service.ts: Implement the CRUD operations for managing tasks in the database.

Connect the Next Js application to Postgres using typeorm.

Source Code:

Run PostgreSQL with Docker-compose:

docker-compose

yaml
version: "3.9"services:
  postgres-tasks:
    image: postgres:13.4-buster
    environment:
      POSTGRES_USER: user
      POSTGRES_PASSWORD: pass
      POSTGRES_DB: tasks
    volumes:
      - postgres-db:/var/lib/postgresql/data
    restart: always
    ports:       - 3001:5432
    networks:
      - tasks-network
  admin:
    image: adminer
    restart: always
    depends_on:       - postgres-tasks
    ports:
      - 10000:8080
    networks:
      - tasks-network
  liquibase:
    image: liquibase/liquibase
    command: tail -f /liquibase/changelog.xml
    volumes:
      - ./sql/changelog.xml:/liquibase/changelog.xml
      - ./sql/migrations:/liquibase/migrations
    networks:
      - tasks-networkvolumes:
  postgres-db:
    driver: localnetworks:
  tasks-network:
    external: true

Liquibase

Nestjs : PostgreSQL database with docker-compose and Liquibase

.SQL

sql
-- liquibase formatted sql
-- changeset mobin:1
CREATE TABLE IF NOT EXISTS "tasks" (
    "id" BIGSERIAL PRIMARY KEY,
    "title" TEXT NOT NULL,
    "description" TEXT NOT NULL,
    "created_at" TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX ON "tasks" ("id");

-- rollback DROP TABLE "tasks";

Changelog.xml

xml
<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog  xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog         http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.8.xsd">
<includeAll  path="migrations/" relativeToChangelogFile="true"/>
</databaseChangeLog>

Command Run docker-compose

bash
docker network create tasks-network
sudo
docker-compose up
Nestjs : PostgreSQL database with docker-compose and Liquibase

Liquibase migration

bash
sudo
docker-compose -f docker-compose.yml exec liquibase /bin/sh liquibase update
--url=jdbc:postgresql://postgres-tasks:5432/task?currentSchema=public --changelog-file=changelog.xml
--username=user --password=pass

Adminer

text
http://127.0.0.1:10000/?pgsql=postgres-tasks&username=user&db=task&ns=public

Object Relational Mapping (ORM) — TypeORM

Install packages

bash
yarn add typeorm @nestjs/typeorm pg

app.module.ts

typescript
@Module({
  imports: [TasksModule, TypeOrmModule.forRoot({
    type: 'postgres', host: '127.0.0.1', port: 3001, username: 'user', password: 'pass', database:
    'task', autoLoadEntities: true, synchronize: true,
  }),],
})
export class AppModule {
}

problem connection :

Nestjs : PostgreSQL database with docker-compose and Liquibase

Create Entity:

task.entity.ts

typescript
import {
  Column, Entity, PrimaryGeneratedColumn
}
from 'typeorm';
import {
  TaskStatus
}
from './tasks.model';
@Entity()
export class Task {
  @PrimaryGeneratedColumn('uuid') id: string;
  @Column() title: string;
  @Column() description: string;
  @Column() status: TaskStatus;
}

Active Record vs Data Mapper

How to do custom repository using TypeORM (MongoDB) in NestJS?

I have a question. With @EntityRepository decorator being marked as deprecated in typeorm@^0.3.6, what is now the recommended or TypeScript-friendly way to create a custom repository for an entity in NestJS? A custom repository before would look like this:

Add Repository

task.repository.ts

typescript
import {
  DataSource, Repository
}
from 'typeorm';
import {
  Injectable
}
from '@nestjs/common';
import {
  Task
}
from './task.entity';
@Injectable()
export class TaskRepository extends Repository<Task> {
  constructor(private dataSource: DataSource) {
    super(Task, dataSource.createEntityManager());
  }
}

task.module.ts

typescript
import {
  Module
}
from '@nestjs/common';
import {
  TasksController
}
from './tasks.controller';
import {
  TasksService
}
from './tasks.service';
import {
  TypeOrmModule
}
from '@nestjs/typeorm';
import {
  Task
}
from './task.entity';
@Module({
  imports: [TypeOrmModule.forFeature([Task])], controllers: [TasksController], providers:
  [TasksService],
})
export class TasksModule {
}

What is a promise?

A promise is essentially an improvement of callbacks that manage all asynchronous data activities. A JavaScript promise represents an activity that will either be completed or declined. If the promise is fulfilled, it is resolved; otherwise, it is rejected. Promises, unlike typical callbacks, may be chained.

Nestjs : PostgreSQL database with docker-compose and Liquibase

Add Get Task by ID

controller

typescript
import {
  Controller, Get, Param
}
from '@nestjs/common';
import {
  TasksService
}
from './tasks.service';
import {
  Task
}
from './task.entity';
@Controller('tasks')
export class TasksController {
  constructor(private tasksService: TasksService) {
  }
  @Get('/:id') getTaskById(@Param('id') id: string): Promise<Task> {
    return this.tasksService.getTaskById(id);
  }
}

service

typescript
import {
  Injectable, NotFoundException
}
from '@nestjs/common';
import {
  TaskRepository
}
from './task.repository';
import {
  InjectRepository
}
from '@nestjs/typeorm';
import {
  Task
}
from './task.entity';
@Injectable()
export class TasksService {
  constructor(@InjectRepository(Task) private taskRepository: TaskRepository,) {
  }
  async getTaskById(id: string): Promise<Task> {
    const found = await this.taskRepository.findOne({
      where: {
        id: id
      }
    });
    if (!found) {
      throw new NotFoundException(`Task with ID "${id}" not found`);
    }
    return found;
  }
}

https://typeorm.io/repository-api

Add Repository layer

module

typescript
import {
  Module
}
from '@nestjs/common';
import {
  TasksController
}
from './tasks.controller';
import {
  TasksService
}
from './tasks.service';
import {
  TypeOrmModule
}
from '@nestjs/typeorm';
import {
  Task
}
from './task.entity';
import {
  TaskRepository
}
from './task.repository';
@Module({
  imports: [TypeOrmModule.forFeature([Task])], controllers: [TasksController], providers:
  [TasksService, TaskRepository],
})
export class TasksModule {
}

controller

typescript
import {
  Body, Controller, Get, Param, Post
}
from '@nestjs/common';
import {
  TasksService
}
from './tasks.service';
import {
  Task
}
from './task.entity';
import {
  CreateTaskDto
}
from './dto/create-task.dto';
@Controller('tasks')
export class TasksController {
  constructor(private tasksService: TasksService) {
  }
  @Get('/:id') getTaskById(@Param('id') id: string): Promise<Task> {
    return this.tasksService.getTaskById(id);
  }
  @Post() createTask(@Body() createTaskDto: CreateTaskDto): Promise<Task> {
    return this.tasksService.createTask(createTaskDto);
  }
}

service

typescript
import {
  Injectable
}
from '@nestjs/common';
import {
  TaskRepository
}
from './task.repository';
import {
  InjectRepository
}
from '@nestjs/typeorm';
import {
  Task
}
from './task.entity';
import {
  CreateTaskDto
}
from './dto/create-task.dto';
@Injectable()
export class TasksService {
  constructor(@InjectRepository(TaskRepository) private taskRepository: TaskRepository,) {
  }
  async getTaskById(id: string): Promise<Task> {
    const task = await this.taskRepository.getTaskById(id);
    return task;
  }
  async createTask(createTaskDto: CreateTaskDto): Promise<Task> {
    const task = await this.taskRepository.createTask(createTaskDto);
    return task;
  }
}

repository

typescript
import {
  DataSource, Repository
}
from 'typeorm';
import {
  Injectable, NotFoundException
}
from '@nestjs/common';
import {
  Task
}
from './task.entity';
import {
  TaskStatus
}
from './tasks-status.enum';
import {
  CreateTaskDto
}
from './dto/create-task.dto';
@Injectable()
export class TaskRepository extends Repository<Task> {
  constructor(private ds: DataSource) {
    super(Task, ds.createEntityManager());
  }
  public async getTaskById(id: string): Promise<Task> {
    const found = await this.findOne({
      where: {
        id: id
      }
    });
    if (!found) {
      throw new NotFoundException(`Task with ID "${id}" not found`);
    }
    return found;
  }
  public async createTask(createTaskDto: CreateTaskDto): Promise<Task> {
    const {
      title, description
    }
    = createTaskDto;
    const task = this.create({
      title, description, status: TaskStatus.OPEN,
    });
    await this.save(task);
    return task;
  }
}

synchronize: true

Be careful not to set “true” synchronize in production.

module

typescript
import {
  Module
}
from '@nestjs/common';
import {
  TasksModule
}
from './tasks/tasks.module';
import {
  TypeOrmModule
}
from '@nestjs/typeorm';
@Module({
  imports: [TasksModule, TypeOrmModule.forRoot({
    type: 'postgres', host: '127.0.0.1', port: 3001, username: 'user', password: 'pass', database:
    'task', autoLoadEntities: true, // synchronize: true,
  }),],
})
export class AppModule {
}

Delete Task

controller

typescript
@Delete('/:id') deleteTask(@Param('id') id: string): Promise<void> {
  return this.tasksService.deleteTask(id);
}

service

typescript
async deleteTask(id: string): Promise<void> {    return await this.taskRepository.deleteTask(id);  }

repository

typescript
public async deleteTask(id: string): Promise<void> {    const result = await this.delete(id);    if
(result.affected === 0) {      throw new NotFoundException(`Task with ID "${id}" not found`);    }
}

Update Task

controller

typescript
@Patch('/:id/status') updateTaskStatus(@Param('id') id: string, @Body() updateTaskStatusDto:
UpdateTaskStatusDto,): Promise<Tasks> {
  const {
    status
  }
  = updateTaskStatusDto;
  return this.tasksService.updateTaskStatus(id, status);
}

service

typescript
async updateTaskStatus(id: string, status: TaskStatus): Promise<Tasks> {    return await
this.taskRepository.updateTaskStatus(id, status);  }

repository

typescript
public async updateTaskStatus(    id: string,    status: TaskStatus,  ): Promise<Tasks> {    const
task = await this.getTaskById(id);    task.status = status;    await this.save(task);    return
task;  }

Search Task

controller

typescript
@Get() getTasks(@Query() filterDto: GetTasksFilterDto): Promise<Tasks[]> {
  return this.tasksService.getTasks(filterDto);
}

service

typescript
async getTasks(filterDto: GetTasksFilterDto): Promise<Tasks[]> {    return await
this.taskRepository.getTasks(filterDto);  }

repository

typescript
async getTasks(filterDto: GetTasksFilterDto): Promise<Tasks[]> {    const { status, search } =
filterDto;    const query = this.createQueryBuilder('tasks');    if (status) {
query.andWhere('tasks.status = :status', { status });    }    if (search) {      query.andWhere(
'LOWER(tasks.title) LIKE LOWER(:search) OR LOWER(tasks.description) LIKE LOWER(:search)',        {
search: `%${search}%` },      );    }    const tasks = await query.getMany();    return tasks;  }

Conclusion:
This report demonstrates how to leverage NestJS, PostgreSQL, Liquibase, and TypeORM to build a robust and scalable CRUD API with database management capabilities. The provided code offers a starting point for developing more complex applications with NestJS.