DEV Community

Moniruzzaman Saikat
Moniruzzaman Saikat

Posted on

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
Enter fullscreen mode Exit fullscreen mode

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'); 
})
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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)
);
Enter fullscreen mode Exit fullscreen mode

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

Enter fullscreen mode Exit fullscreen mode

Read

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

  res.json({
    users
  })
})


Enter fullscreen mode Exit fullscreen mode

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

Enter fullscreen mode Exit fullscreen mode

Delete

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

  await db('users').where('id', id').del();
  res.send('User deleted');
})

Enter fullscreen mode Exit fullscreen mode

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

Enter fullscreen mode Exit fullscreen mode

Top comments (0)