Knexjs module for NestJS 8.x/9.x/10.x framework 😻

In this post I'll explain how to use my Knexjs module for NestJS😻, created for work purposes.
For those unfamiliar or unfamiliar with NestJS, it is a TypeScript Node.js framework that helps you build efficient and scalable enterprise-grade Node.js applications.

For those who have never used Knexjs is a "batteries included" SQL query generator for Postgres, MSSQL, MySQL, MariaDB, SQLite3, Oracle and Amazon Redshift designed to be flexible, portable and fun to use. Features both traditional node-style callbacks and a promised interface for cleaner asynchronous flow control, a flow interface, comprehensive queries and schema builders, transaction support (with savepoints), connection pooling and standardized responses between different query clients and dialects.

So let's get started by creating the NestJS app 😻.
Open Terminal and install CLI for NestJS, if you already have it installed, skip this step.

$ npm i -g @nestjs/cli

Then create a NestJS project

$ nest new app
$ cd app
// start the application
$ npm run start:dev

Open the browser on localhost:3000 to verify that hello world is displayed.

then we create a docker-compose.yml file to create the service
MySQL

version: "3"

services:
   mysql:
     image: mysql:5
     restart: always
     environment:
       MYSQL_ROOT_PASSWORD: root
       MYSQL_DATABASE: nest
     ports:
       - "3306:3306"

for those who do not know what docker is I leave the link here for more information Docker

As I mentioned in the previous posts, there are many different ways to integrate Nest with databases and they all depend on personal preferences or the needs of the project.

Install KnexModule, Knex and MySQL dependencies

$ npm install --save nest-knexjs knex mysql

In the example I use MySQL but you can also use PostgreSQL or MariaDB to you the choice according to your needs.

Set KnexModule in AppModule

import { Module } from '@nestjs/common';
import { KnexModule } from 'nest-knexjs';

@Module ({
   imports: [
    KnexModule.forRoot({
      config: {
        client: 'mysql',
        version: '5.7',
        useNullAsDefault: true,
        connection: {
          host: '127.0.0.1',
          user: 'root',
          password: 'root',
          database: 'nest',
        },
      },
    }),
   ],
})
export class AppModule {}

Well, now let's create our first migrations, open Terminal and run the following command:

  $ npx knex init

A knexfile.js file will be created at the root of the project.

Now let's edit the knexfile.js file with the credentials we entered when registering the module, like so:

module.exports = {
  development: {
    client: 'mysql',
    connection: {
      host: '127.0.0.1',
      user: 'root',
      password: 'root',
      database: 'nest',
    },
  },
}

from the terminal we run the following command to create our migrations

$ npx knex migrate:make users

once we have created our migration we modify it in this way:

exports.up = function (knex) {
  return knex.schema
    .createTable('users', function (table) {
      table.increments('id');
      table.string('firstName', 255).notNullable();
      table.string('lastName', 255).notNullable();
      table.string('email', 255).unique().notNullable();
    })
};

exports.down = function (knex) {
  return knex.schema.dropTable('users');
};

Well, now let's run the following command to upload the migrations to the database:

$ npx knex migrate:latest

Now let's create a REST API and call it users. We open the terminal and run the commands to create the module, the service and the controller for the users:


$ nest g mo users  # module
$ nest g s users   # service
$ nest g co users  # controller

UsersModule:

import { Module } from '@nestjs/common';
import { UsersService } from './users.service';
import { UsersController } from './users.controller';

@Module({
  controllers: [UsersController],
  providers: [UsersService]
})
export class UsersModule {}

Before we start building our API, create the Data Transfer Objects (Dto) class to create the users

import { IsEmail, IsNotEmpty, IsString } from "class-validator";

export class CreateUserDto {
    @Notempty()
    @IsString()
    firstName: string;

    @Notempty()
    @IsString()
    lastName: string;

    @Notempty()
    @IsString()
    @IsEmail()
    email: string;
}

Remember to install this package before creating the dto class for the upgrade.

$ npm i @nestjs/mapped-types

Well, now to update the users data we extend the CreateUserDto class:

import { PartialType } from "@nestjs/mapped-types";
import { CreateUserDto } from './create-user.dto';

export class UpdateUserDto extends PartialType(CreateUserDto){}

We then implement ours UserService:


import { HttpException, HttpStatus, Injectable, NotFoundException } from '@nestjs/common';
import { Knex } from 'knex';
import { InjectConnection } from 'nest-knexjs';
import { CreateUserDto } from './dto/create-user.dto';
import { UpdateUserDto } from './dto/update-user.dto';

@Injectable()
export class UsersService {
  constructor(@InjectConnection() private readonly knex: Knex) {}

  async findAll() {
    const users = await this.knex.table('users');
    return { users };
  }

  async create(createUserDto: CreateUserDto) {
    try {
      const users = await this.knex.table('users').insert({
        firstName: createUserDto.firstName,
        lastName: createUserDto.lastName,
        email: createUserDto.email,
      });

      return { users };
    } catch (err) {
      throw new HttpException(err, HttpStatus.BAD_REQUEST);
    }
  }

  async findOne(id: number) {
    if (!id) {
      throw new NotFoundException(`User ${id} does not exist`);
    }
    const users = await this.knex.table('users').where('id', id);
    return { users };
  }

  async update(id: number, updateUserDto: UpdateUserDto) {
    try {
      const users = await this.knex.table('users').where('id', id).update({
        firstName: updateUserDto.firstName,
        lastName: updateUserDto.lastName,
      });

      return { users };
    } catch (err) {
      throw new HttpException(err, HttpStatus.BAD_REQUEST);
    }
  }

  async remove(id: number) {
    if (!id) {
      throw new NotFoundException(`User ${id} does not exist`);
    }
    const users = await this.knex.table('users').where('id', id).del();
    return { users };
  }
}

UsersController:

import { Controller, Get, Post, Body, Put, Param, Delete } from '@nestjs/common';
import { UsersService } from './users.service';
import { CreateUserDto } from './dto/create-user.dto';
import { UpdateUserDto } from './dto/update-user.dto';

@Controller('/api/users')
export class UsersController {
  constructor(private readonly usersService: UsersService) {}

  @Post()
  create(@Body() createUserDto: CreateUserDto) {
    return this.usersService.create(createUserDto);
  }

  @Get()
  findAll() {
    return this.usersService.findAll();
  }

  @Get(':id')
  findOne(@Param('id') id: string) {
    return this.usersService.findOne(+id);
  }

  @Put(':id')
  update(@Param('id') id: string, @Body() updateUserDto: UpdateUserDto) {
    return this.usersService.update(+id, updateUserDto);
  }

  @Delete(':id')
  remove(@Param('id') id: string) {
    return this.usersService.remove(+id);
  }
}

well now we should have our API tested if everything works perfectly this commands from curl or whatever you prefer to use.

$ curl -H 'content-type: application/json' -v -X GET http://127.0.0.1:3000/api/users  
$ curl -H 'content-type: application/json' -v -X GET http://127.0.0.1:3000/api/users/:id 
$ curl -H 'content-type: application/json' -v -X POST -d '{"firstName": "firstName #1", "lastName": "lastName #1", "email": "example@nest.it"}' http://127.0.0.1:3000/api/users 
$ curl -H 'content-type: application/json' -v -X PUT -d '{"firstName": "firstName update #1", "lastName": "lastName update #1", "email": "example@nest.it"}' http://127.0.0.1:3000/api/users/:id 
$ curl -H 'content-type: application/json' -v -X DELETE http://127.0.0.1:3000/api/users/:id

Last but not least, the use of seeders to create test data, etc.

To create a seeder, run the following command from the terminal:

$ npx knex seed:make users

now let's modify it like this:

exports.seed = function (knex) {
  return knex('users')
    .del()
    .then(function () {
      return knex('users').insert([
        {
          id: 1,
          firstName: 'firstName #1',
          lastName: 'lastName #1',
          email: 'example1@nest.it',
        },
        {
          id: 2,
          firstName: 'firstName #2',
          lastName: 'lastName #2',
          email: 'example2@nest.it',
        },
        {
          id: 3,
          firstName: 'firstName #3',
          lastName: 'lastName #3',
          email: 'example3@nest.it',
        },
      ]);
    });
};

and run the following command from the terminal:

$ npx knex seed:run

going to see in your database, in the users table you will have the data used in the seeders.

For more information on Knex.js see here.

This module is compatible with version 7.x of NestJS 😻.

That's it 😀
Hope it can be useful in your projects.
For anything write me in the comments 😉

Blog

Knexjs module for NestJS 8.x/9.x/10.x framework 😻

Antonio Tripodi

Then create a NestJS project

Install KnexModule, Knex and MySQL dependencies

Join Our Newsletter. No Spam, Only the good stuff.

Related