CRUD operation with knex & mysql in node.js

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:

npm init -y
npm i express knex mysql

Create a file call app.js in your project root and write these codes bellow:

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.

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:

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

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

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

  res.json({
    users
  })
})

Update

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

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

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