Crud In NodeJs
Tags: beginners, sql
Published: 2021-08-03 17:33:26

##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**

![directorytree.PNG](https://cdn.hashnode.com/res/hashnode/image/upload/v1627725363328/VonzSRHNY.png)
**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.

![app.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1627720727522/MfZpFXpKv.png)

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.

![router.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1627720798578/XosU3MCS2.png)
**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.
  

![tbl_person.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1627718243836/wneKjHxSE.png)


![tbl_fav.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1627718276357/eKjakWbfR.png)

**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.

![addPerson.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1627718630860/hE8qz5sDE.png)
Testing insert api with postman
![postPerson.PNG](https://cdn.hashnode.com/res/hashnode/image/upload/v1627719235573/GSSeMHMtL.png)

*Patch Person *

For updating store the body data into object and params in a variable. To pass it into knex query as:
![patchPerson.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1627718687894/wiwvkxACK.png)
Testing Patch API
![patchPerson.PNG](https://cdn.hashnode.com/res/hashnode/image/upload/v1627719283369/7ffBY8XhU.png)

*Get Person *

For selecting person knex orm query is as:
![getPerson.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1627718721273/dwit8zvF_.png)
testing get person API
![getPerson.PNG](https://cdn.hashnode.com/res/hashnode/image/upload/v1627719721493/V8zA4Rkl3.png)

*Add Favourite*

knex query for adding favourite

![personIdFav.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1627725960651/m8KG7yrEU.png)
testing add Favourite API
![postFavourite.PNG](https://cdn.hashnode.com/res/hashnode/image/upload/v1627719750804/nAtHPm1bi.png)

*Left Join *

Left join select the matching data between the left table and right table and all the records from the left table.
![getFavJoin.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1627718783000/K9G6CPMLE.png)
 testing left join route with the postman
![getFavouriteLeftJoin.PNG](https://cdn.hashnode.com/res/hashnode/image/upload/v1627719790498/JPlZP3QzV.png)

**.env Configuration used in project**
 Make the necessary needed change and to able to use .env file install


```
npm install dotenv
``` 

![env.PNG](https://cdn.hashnode.com/res/hashnode/image/upload/v1627727112263/Z85ilARh2.png)

 [SourceCode](https://github.com/Bvekv1/knexCrud.git) 

**Bonus**

 [Knex Cheatsheet](https://devhints.io/knex) 

 [KnexQuery to raw query](https://michaelavila.com/knex-querylab/)