Using Node.js with MySQL – CRUD | XAMPP / PhpMyAdmin

By Raddy in NodeJs ·

In this article, you are going to learn how to use MySQL using Node.js and Express. We are going to create a very basic Beer API and have a look at how we can Get records, Post records, Delete records and Update.

Before you get started with this project make sure that you have a MySQL database that you can use. I will be using XAMPP and phpMyAdmin for this example.

Watch full video or continue with the article

To test the get, post, delete and put methods I am using Postman.

Project Structure

We’ll keep this project simple and straight to the point. Normally if we were building a big project we would probably separate our files in views, layouts, partials, components and so on (see example here). Let’s start building our project:

📂 node_modules - automatically generated
🌍 app.js
📜 package-lock.json
📜 package-json

Install Packages

The packages that you need installed for your applications are Express, mysql and the body-parser. I’ve also included Nodemon so I don’t have to restart the server every time I make changes.

Installing packages:

npm install express mysql body-parser

Your package.json file should look simular to mine. Of course you might have a newer version of the packages if you are doing this in the future.

{
  "name": "nodejs-crud",
  "version": "1.0.0",
  "description": "",
  "main": "index.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "author": "",
  "license": "ISC",
  "dependencies": {
    "body-parser": "^1.19.0",
    "express": "^4.17.1",
    "mysql": "^2.18.1"
  }
}

Restarting the local server

Let’s include Nodemon in our package.json file so we don’t have to restart the server every time we make changes. Add the following line inside “scripts”:

npm install --save-dev nodemon

To setup out application to run with nodemon just add the “start” line under scripts in your package.json file.

  "scripts": {
    "start": "nodemon app.js",
    "test": "echo \"Error: no test specified\" && exit 1"
  },

This is how the final document should look like.

{
  "name": "nodejs-crud",
  "version": "1.0.0",
  "description": "",
  "main": "index.js",
  "scripts": {
    "start": "nodemon app.js",
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "author": "",
  "license": "ISC",
  "dependencies": {
    "body-parser": "^1.19.0",
    "express": "^4.17.1",
    "mysql": "^2.18.1"
  }
}

App.js

To get started, lets require all packages at the top of our App.js file and set our listening port to be either the environment port (that’s if you are going to publish the app) or set the port to be 5000 as a default.

const express = require('express')
const bodyParser = require('body-parser')
const mysql = require('mysql')

const app = express()
const port = process.env.PORT || 5000;

app.use(bodyParser.urlencoded({ extended: false }))

app.use(bodyParser.json())

// MySQL Code goes here

// Listen on enviroment port or 5000
app.listen(port, () => console.log(`Listening on port ${port}`))

Start our local server

At this point you should have a working application and to test that you can run your project by going to your command line / PowerShell and do the following command:

npm start

Hopefully, everything should be working just fine and you won’t have any errors.

Now go to your browser and test your website on – localhost:5000

If you get the following message “Cannot Get / ” we are good to go!

How to Node.js MySQL CRUID API

Before we start writing out MySQL code we need to make sure that we have a database that we can work with.

Now go into your newly created database nodejs_beers and add a few table columns.

You can also use the following command to create your table. Go to SQL and paste the query there.

CREATE TABLE `nodejs_beers`.`beers` ( `id` BIGINT NOT NULL AUTO_INCREMENT , `name` VARCHAR(255) NOT NULL , `tagline` VARCHAR(255) NOT NULL , `description` TEXT NOT NULL , `image` VARCHAR(50) NOT NULL , PRIMARY KEY (`id`)) ENGINE = InnoDB;

Connect Node.js with MySQL database

It’s time to make the connection to our database and our table (beers). As we are using the NPM packaged ‘mysql’ you should always refer to their documentation as versions change and so the code.

Pooling connections

Rather than creating and managing connections one-by-one, we can use connection pooling using mysql.createPool(config)Read more about connection pooling.

Create a pool and use it directly:

const pool  = mysql.createPool({
    connectionLimit : 10,
    host            : 'localhost',
    user            : 'root',
    password        : 'password',
    database        : 'nodejs_beers'
})

Now let’s get the list of all beers in our database:

// Get all beers
app.get('', (req, res) => {
    pool.getConnection((err, connection) => {
        if(err) throw err
        console.log('connected as id ' + connection.threadId)
        connection.query('SELECT * from beers', (err, rows) => {
            connection.release() // return the connection to pool

            if (!err) {
                res.send(rows)
            } else {
                console.log(err)
            }

            // if(err) throw err
            console.log('The data from beer table are: \n', rows)
        })
    })
})

Note: You probably need to insert a few records to get a result.

You can also limit the results by adding LIMIT and the number of rows you want to return:

'SELECT * from beers LIMIT 2'

Get beer by ID

For example, you could pass an ID of 1 this: http://localhost:5000/1 and this should return the row with the ID of 1. I hope this makes sense. It’s a basic SELECT statement.

// Get an beer
app.get('/:id', (req, res) => {
    pool.getConnection((err, connection) => {
        if(err) throw err
        connection.query('SELECT * FROM beers WHERE id = ?', [req.params.id], (err, rows) => {
            connection.release() // return the connection to pool
            if (!err) {
                res.send(rows)
            } else {
                console.log(err)
            }
            
            console.log('The data from beer table are: \n', rows)
        })
    })
});

Delete a record

// Delete a beer
app.delete('/:id', (req, res) => {

    pool.getConnection((err, connection) => {
        if(err) throw err
        connection.query('DELETE FROM beers WHERE id = ?', [req.params.id], (err, rows) => {
            connection.release() // return the connection to pool
            if (!err) {
                res.send(`Beer with the record ID ${[req.params.id]} has been removed.`)
            } else {
                console.log(err)
            }
            
            console.log('The data from beer table are: \n', rows)
        })
    })
});

Add a beer

// Add beer
app.post('', (req, res) => {

    pool.getConnection((err, connection) => {
        if(err) throw err
        
        const params = req.body
        connection.query('INSERT INTO beers SET ?', params, (err, rows) => {
        connection.release() // return the connection to pool
        if (!err) {
            res.send(`Beer with the record ID  has been added.`)
        } else {
            console.log(err)
        }
        
        console.log('The data from beer table are:11 \n', rows)

        })
    })
});

Update a record

// Update a record / beer
app.put('', (req, res) => {

    pool.getConnection((err, connection) => {
        if(err) throw err
        console.log(`connected as id ${connection.threadId}`)

        const { id, name, tagline, description, image } = req.body

        connection.query('UPDATE beers SET name = ?, tagline = ?, description = ?, image = ? WHERE id = ?', [name, tagline, description, image, id] , (err, rows) => {
            connection.release() // return the connection to pool

            if(!err) {
                res.send(`Beer with the name: ${name} has been added.`)
            } else {
                console.log(err)
            }

        })

        console.log(req.body)
    })
})

And that’s all. You should now be able to read, write, update and delete data.

Final App.js document

const express = require('express')
const bodyParser = require('body-parser')
const mysql = require('mysql')

const app = express()
const port = process.env.PORT || 5000;

// parse application/x-www-form-urlencoded
app.use(bodyParser.urlencoded({ extended: false }))

// parse application/json
app.use(bodyParser.json())

// MySQL
const pool  = mysql.createPool({
    connectionLimit : 10,
    host            : 'localhost',
    user            : 'root',
    password        : 'password',
    database        : 'nodejs_beers'
})
   
// Get all beers
app.get('', (req, res) => {
    pool.getConnection((err, connection) => {
        if(err) throw err
        console.log('connected as id ' + connection.threadId)
        connection.query('SELECT * from beers', (err, rows) => {
            connection.release() // return the connection to pool

            if (!err) {
                res.send(rows)
            } else {
                console.log(err)
            }

            // if(err) throw err
            console.log('The data from beer table are: \n', rows)
        })
    })
})

// Get an beer
app.get('/:id', (req, res) => {
    pool.getConnection((err, connection) => {
        if(err) throw err
        connection.query('SELECT * FROM beers WHERE id = ?', [req.params.id], (err, rows) => {
            connection.release() // return the connection to pool
            if (!err) {
                res.send(rows)
            } else {
                console.log(err)
            }
            
            console.log('The data from beer table are: \n', rows)
        })
    })
});

// Delete a beer
app.delete('/:id', (req, res) => {

    pool.getConnection((err, connection) => {
        if(err) throw err
        connection.query('DELETE FROM beers WHERE id = ?', [req.params.id], (err, rows) => {
            connection.release() // return the connection to pool
            if (!err) {
                res.send(`Beer with the record ID ${[req.params.id]} has been removed.`)
            } else {
                console.log(err)
            }
            
            console.log('The data from beer table are: \n', rows)
        })
    })
});

// Add beer
app.post('', (req, res) => {

    pool.getConnection((err, connection) => {
        if(err) throw err
        
        const params = req.body
        connection.query('INSERT INTO beers SET ?', params, (err, rows) => {
        connection.release() // return the connection to pool
        if (!err) {
            res.send(`Beer with the record ID  has been added.`)
        } else {
            console.log(err)
        }
        
        console.log('The data from beer table are:11 \n', rows)

        })
    })
});


app.put('', (req, res) => {

    pool.getConnection((err, connection) => {
        if(err) throw err
        console.log(`connected as id ${connection.threadId}`)

        const { id, name, tagline, description, image } = req.body

        connection.query('UPDATE beers SET name = ?, tagline = ?, description = ?, image = ? WHERE id = ?', [name, tagline, description, image, id] , (err, rows) => {
            connection.release() // return the connection to pool

            if(!err) {
                res.send(`Beer with the name: ${name} has been added.`)
            } else {
                console.log(err)
            }

        })

        console.log(req.body)
    })
})


// Listen on enviroment port or 5000
app.listen(port, () => console.log(`Listening on port ${port}`))

Download

Thank you for reading this article. Please consider subscribing to my YouTube Channel.

Leave a Reply

Your email address will not be published. Required fields are marked *