DEV Community

loading...

Knexjs module for NestJS 7.x framework😻

tony133 profile image Tony Updated on ・5 min read

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
Enter fullscreen mode Exit fullscreen mode

Then create a NestJS project

$ nest new app
$ cd app
// start the application
$ npm run start: dev
Enter fullscreen mode Exit fullscreen mode

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"
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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 {}
Enter fullscreen mode Exit fullscreen mode

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

  $ npx knex init
Enter fullscreen mode Exit fullscreen mode

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',
    },
  },
}
Enter fullscreen mode Exit fullscreen mode

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

$ npx knex migrate:make users
Enter fullscreen mode Exit fullscreen mode

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('first_name', 255).notNullable();
      table.string('last_name', 255).notNullable();
      table.string('email', 255).unique().notNullable();
    })
};

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

Enter fullscreen mode Exit fullscreen mode

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

$ npx knex migrate:latest
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

UsersModule:

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

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

Enter fullscreen mode Exit fullscreen mode

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()
    first_name: string;

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

    @Notempty()
    @IsString()
    @IsEmail()
    email: string;
}
Enter fullscreen mode Exit fullscreen mode

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

$ npm i @nestjs/mapped-types
Enter fullscreen mode Exit fullscreen mode

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){}
Enter fullscreen mode Exit fullscreen mode

We then implement ours UserService:


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

@Injectable()
export class UsersService {
  constructor(@InjectModel() 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({
        first_name: createUserDto.first_name,
        last_name: createUserDto.last_name,
        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({
        first_name: updateUserDto.first_name,
        last_name: updateUserDto.last_name,
      });

      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 };
  }
}

Enter fullscreen mode Exit fullscreen mode

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);
  }
}
Enter fullscreen mode Exit fullscreen mode

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 '{"first_name": "firstName #1", "last_name": "lastName #1", "email": "example@nest.it"}' http://127.0.0.1:3000/api/users 
$ curl -H 'content-type: application/json' -v -X PUT -d '{"first_name": "firstName update #1", "last_name": "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 
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

now let's modify it like this:

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

Enter fullscreen mode Exit fullscreen mode

and run the following command from the terminal:

$ npx knex seed:run
Enter fullscreen mode Exit fullscreen mode

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

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

Discussion (0)

Forem Open with the Forem app