Mysql With Knexjs In Node

Introduction

In this section, we will learn how to set up and perform crud with knex.js in nodejs and I will be using Postman to test routes.

Project Structure

STEP 1- Getting ready express framework

npm install express
npm install body-parser

Then create an app.js file As we will be keeping our route in a separate directory import the directory with a file using the app. use(require("./routes/router")). In app.js file will only start the express server.

Now in the router.js file set body-parser-urlencoded({extended: true}) uses express.json() to parse the JSON data which we will be sending through postman.

Define all your routes here and import related functions in respective routes.

2- Installation

npm install knex

 npm install mysql

3-Initialize Knex

npm knex init

4- Creating Migration File

knex migrate: make tbl_person
knex migrate: make tbl_favourite

Define Schema in the migration file. Migration file requires two functions up and down. Up function is run when creating a table in the database whereas the down function is run when rolling back migration.

5-Writing knex Query

Insert Person

For inserting, I will be using the async arrow function. Store all the request body in an object along with the key that matches the field of the respective table and pass the object in knex query. Which knex will map it into raw SQL query under the hood.

Testing insert api with postman

Patch Person

For updating store the body data into object and params in a variable. To pass it into knex query as: Testing Patch API

Get Person

For selecting person knex orm query is as: testing get person API

Add Favourite

knex query for adding favourite

testing add Favourite API

Left Join

Left join select the matching data between the left table and right table and all the records from the left table. testing left join route with the postman