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
.env Configuration used in project
Make the necessary needed change and to able to use .env file install
npm install dotenv
Bonus
Top comments (0)