CRUD operation with knex & mysql in node.js
Tags: node, mysql, knex, crud
Published: 2021-07-10 09:16:48

### In this post I will teach you how to use mysql with knex.js 😊###

Knex.js is a SQL query builder for Postgres, MSSQL, MySQL, MariaDB, SQLite3, Oracle, and Amazon Redshift designed to be flexible, portable, and fun to use. It features both traditional node style callbacks as well as a promise interface for cleaner async flow control, a stream interface, full-featured query and schema builders, transaction support (with savepoints), connection pooling and standardized responses between different query clients and dialects.

#### Create a project ####
Create a folder then enter to it and then type the command bellow:
```shell
npm init -y
npm i express knex mysql
```
Create a file call `app.js` in your project root and write these codes bellow: 
```javascript
const express = require('express')

const app = express()

app.get('/', (req, res) => {
  res.send('app running')
})

app.listen(5000, () => {
  console.log('Server running on http://localhost:50000'); 
})
```

Now, create a file for database called `db.js` and add these codes bellow. You need to replace the user(if any, by default it's root), password (default: empty string), database name.
```javascript
const knex = require("knex");

const db = knex({
  client: "mysql",
  connection: {
    host: "localhost",
    user: "root",
    password: "",
    database: "databasename",
  },
});

module.exports = db;
```
Now in your database called(databasename, you might replace it) create a table called users. You can create the table by running these command:
```sql
CREATE TABLE users (
	id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(255),
    isAdmin TINYINT DEFAULT 0,
    PRIMARY KEY(id)
);
```

Ok, everything is done 😀. Now it's time to give some shots 🦵. First of all import db from our `db.js` file. 
So, let's create a user from our app. For this make a route called '/users/create' bellow our index route. When you will visit the link http://localhost:5000/users/create it will create insert a user to our users table in the database. 

### Create ###
```javascript
const db = require('./db.js');

app.get('/users/create', async (req, res) => {
  const userId = await db('users').insert({
    name: "John Doe"
  })

  res.json({
    message: "User created",
    userId
  })
})

```

### Read ###
```javascript
app.get('/users', async (req, res) => {
  let users = await db('users').select()
  users = users.map(user => ({...user});

  res.json({
    users
  })
})


```

### Update ###
```javascript
app.get('/users/:id', async (req, res) => {
  const { id } = req.params;

  await db('users')
     .where('id', id)
     .update({
       name: "Saikat"
      });

  res.send('User updated');           
})

```

### Delete ###
```javascript
app.get('/users/delete/:id', async (req, res) => {
  const { id } = req.params;
  
  await db('users').where('id', id').del();
  res.send('User deleted');
})

```

### Here is the full version ###
```javascript
const express = require('express')

const app = express()

app.get('/', (req, res) => {
  res.send('app running')
})

// Create a user
app.get('/users/create', async (req, res) => {
  const userId = await db('users').insert({
    name: "John Doe"
  })

  res.json({
    message: "User created",
    userId
  })
})

// Query all users 
app.get('/users', async (req, res) => {
  let users = await db('users').select()
  users = users.map(user => ({...user}); // it's needed bcz it return something called RawDataPacket

  res.json({
    users
  })
})

// Update user
app.get('/users/:id', async (req, res) => {
  const { id } = req.params;

  await db('users')
     .where('id', id)
     .update({
       name: "Saikat"
      });

  res.send('User updated');           
}

// Delete a user
app.get('/users/delete/:id', async (req, res) => {
  const { id } = req.params;
  
  await db('users').where('id', id').del();
  res.send('User deleted');
})


app.listen(5000, () => {
  console.log('Server running on http://localhost:50000'); 
})

```