Nest.Js API using MySQL Database

Nest.js is a popular JavaScript/TypeScript framework designed for building scalable and maintainable server-side applications, particularly for creating APIs and web applications using the Model-View-Controller (MVC) architectural pattern. While it is not primarily known for server-side rendering (SSR), it excels in providing a structured and organized way to develop server-side applications.

When it comes to data management, Nest.js can be paired with various database systems, including MySQL, a widely used open-source relational database management system. In this blog, we will explore how to create a simple API with Nest.js and MySQL, focusing on filtering and pagination capabilities.

Setting up the Database

The first step is to set up the database. In this example, we will be using a simple database with two tables: one for users and one for posts. For the purposes of this blog, the users table will have columns for id, name, and email, while the posts table will have columns for id, title, content, and user_id.

Building the API

In Nest.js, we will create an API endpoint responsible for handling requests to retrieve data from the database. To implement filtering, we’ll pass query strings in the URL to specify filter criteria. For instance, if we want to fetch all posts with a specific title, the URL might look like this: /api/posts

For the filtering functionality, we will pass a query string in the URL that includes the filter criteria. For example, if we want to retrieve all posts with a certain title, the URL might look like this: /api/posts?title=My%20First%20Post.

To implement pagination, we will include a query string for the number of items to be returned per page and the current page number. For example, to retrieve the first 10 posts, the URL might look like this: /api/posts?limit=10&page=1.

Here’s an example of code for the API endpoint:

import { Controller, Get, Query } from '@nestjs/common';
import { PostsService } from './posts.service';

@Controller('posts')
export class PostsController {
  constructor(private readonly postsService: PostsService) {}

  @Get()
  async getPosts(
    @Query('limit') limit: number,
    @Query('page') page: number,
    @Query('title') title: string,
  ) {
    const offset = (page - 1) * limit;
    return this.postsService.getFilteredAndPaginatedPosts(limit, offset, title);
  }
}

In this code, we use decorators provided by Nest.js to specify the route, and we inject a service (PostsService) to handle the business logic. The service can execute database queries, applying filter criteria and pagination parameters as specified in the URL query string.

The PostsService might look something like this:

import { Injectable } from '@nestjs/common';
import { Sequelize } from 'sequelize';

@Injectable()
export class PostsService {
  constructor(private readonly sequelize: Sequelize) {}

  async getFilteredAndPaginatedPosts(limit: number, offset: number, title: string) {
    const whereClause = title ? { title } : {};

    const posts = await this.sequelize.query(
      'SELECT * FROM posts WHERE ? ORDER BY id LIMIT ? OFFSET ?',
      {
        replacements: [whereClause, limit, offset],
        type: this.sequelize.QueryTypes.SELECT,
      },
    );

    return { posts };
  }
}

In this revised example, we use Nest.js decorators and services to create a robust API endpoint that handles filtering and pagination effectively.

This code uses Sequelize to execute an SQL query that retrieves the posts from the database, applying the filter criteria and pagination parameters as specified in the URL query string. The resulting data is then returned as a JSON object in the response.

Git project for nest.js demo

Conclusion

In this blog, we’ve explored how to build a simple API with Nest.js and MySQL, showcasing filtering and pagination capabilities. Nest.js, in combination with Sequelize, provides an efficient way to retrieve data from the database, ensuring a fast and responsive user experience. With filtering and pagination features, your API becomes flexible and scalable, catering to the requirements of demanding applications.