Using Node.js with MySQL – CRUD | XAMPP / PhpMyAdmin
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.
The connection pool wasn’t used correctly, but it shouldn’t cause any problems.
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.
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 to be 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.
* NOTE The Body-Parser is no longer required *
Installing packages:
npm install express mysql body-parser
Your package.json file should look similar 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, let’s 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;
// Parsing middleware
// Parse application/x-www-form-urlencoded
// app.use(bodyParser.urlencoded({ extended: false })); // Remove
app.use(express.urlencoded({extended: true})); // New
// Parse application/json
// app.use(bodyParser.json()); // Remove
app.use(express.json()); // New
// 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.
Note that I didn’t use the Connection pool properly, but everything should still work as expected. Update coming soon.
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.
More Resources:
Author
Hi! I’m Raddy. I am a web developer, often making YouTube Videos, Blog Posts and side projects. Enquiries: [email protected]
Hey Raddy I do I’ve problem when running this command in you gave in your article “nmp start” can you kindly help me with it.
Hi Bernard,
It’s “npm start” as Node Package Manager. I hope this helps.
Raddy
Hey Bernard,
if your main has the name “index.js” you can type in the console pointed to your app directory “node index.js” ..
Then you will probably get the output “Listening on port 5000”
Regards,
Julia
Fast, simple…
Thanks, it worked without problems 👍🏻
That’s what I like to hear! Thanks for the comment
Nice bro ,
Thanks
Thanks for the comment, Tsopmedjio!
Hi Raddy I do I’ve problem when running this on “INSERT beers got error SQL syntax worng format and UPDATE got undefine “can you kindly help me with it.
Yes, of course! Could you share your code somehow? Maybe GitHub, or feel free to email it to me. It could be a small typo that’s breaking it for you, but we can fix it 🙂
hey Can u help me with full stack of vuejs and nodejs mysql crud operation?
I don’t have any experience with vuejs, but there are plenty of tutorials that I’ve done on Node.js which you can use. I am thinking of trying vuejs at some point soon
Hello thanks for shared before, but i’m run on some issues. Its on my nodemon, its just get status “restarting due to changes”, but never status with running on port. So, its not restarting my server only “restarting due to changes” but nothing changes. Its just so confusing, i am alreday looking for answer but no one work for. If you can help please let me know?.
It could be something small.
First, make sure that you have the code below in your app.js. The second thing could be that port 5000 is taken by something else. Try port 3000 for example and restart. The last thing you can check is your console log Listening on port… Make sure that you are using slanted quotes like this: `
const port = process.env.PORT || 5000;
app.listen(port, () => console.log(`Listening on port ${port}`));
If you don’t get it to work, please link a screenshot of your app.js file. I hope this works!
You make a great tutorial. Thank you
Thank you, Lionel! I am glad that you like the tutorial
can u help me?
[nodemon] restarting due to changes…
[nodemon] starting `node app.js`
Listening on port 8000
Listening on port 8000
/home/gayashan/workspace/Log System – Backend/TestApp/app.js:27
if(err) throw err
^
Error: connect ECONNREFUSED 127.0.0.1:3306
at TCPConnectWrap.afterConnect [as oncomplete] (node:net:1157:16)
——————–
at Protocol._enqueue (/home/gayashan/workspace/Log System – Backend/TestApp/node_modules/mysql/lib/protocol/Protocol.js:144:48)
at Protocol.handshake (/home/gayashan/workspace/Log System – Backend/TestApp/node_modules/mysql/lib/protocol/Protocol.js:51:23)
at PoolConnection.connect (/home/gayashan/workspace/Log System – Backend/TestApp/node_modules/mysql/lib/Connection.js:116:18)
at Pool.getConnection (/home/gayashan/workspace/Log System – Backend/TestApp/node_modules/mysql/lib/Pool.js:48:16)
at /home/gayashan/workspace/Log System – Backend/TestApp/app.js:26:10
at Layer.handle [as handle_request] (/home/gayashan/workspace/Log System – Backend/TestApp/node_modules/express/lib/router/layer.js:95:5)
at next (/home/gayashan/workspace/Log System – Backend/TestApp/node_modules/express/lib/router/route.js:144:13)
at Route.dispatch (/home/gayashan/workspace/Log System – Backend/TestApp/node_modules/express/lib/router/route.js:114:3)
at Layer.handle [as handle_request] (/home/gayashan/workspace/Log System – Backend/TestApp/node_modules/express/lib/router/layer.js:95:5)
at /home/gayashan/workspace/Log System – Backend/TestApp/node_modules/express/lib/router/index.js:284:15 {
errno: -111,
code: ‘ECONNREFUSED’,
syscall: ‘connect’,
address: ‘127.0.0.1’,
port: 3306,
fatal: true
}
[nodemon] app crashed – waiting for file changes before starting…
It looks like you can’t connect to your MySQL server. I know it’s silly, but have you started Apache and MySQL? Are they all running fine? and you are doing all this locally? Double-check the port number. Is it running on 3306 or 8080? I believe that 8080 was the default.
I had the same problem with MAMP
(Error: connect ECONNREFUSED 127.0.0.1:3306 )
Turns out, for me, that all that was missing was the socketPath
const pool = mysql.createPool({
connectionLimit : 10,
host : ‘localhost’,
user : ‘root’,
password : ‘root’,
database : ‘nodejs_beers’,
socketPath : ‘/Applications/MAMP/tmp/mysql/mysql.sock’
});
I appreciate you posting the solution. Thank you!
im using php myAdmin doker and ubuntu os
Where is the view part? Please add view part with ejs…
There is no “view” part in this one. The main focus was SQL
How to write link ie. http://localhost:5000/1 to insert or update or delete a record?
I can’t get it.. thanks
It would be something like this:
// Add beer
app.post('/:id', (req, res) => {
connection.query('INSERT INTO beers SET ?', [req.params.id], (err, rows) => {
// Once done, release connection
if (error) throw error;
if (!error) {
// res.render('index', { rows });
res.send(`Beer with the record ID has been added.`)
}
});
});
Sorry.. can you give me some example how to write url for request update query to do its job or delete query?
Thanks for your reply.. but i still confuse how to write in url (Get method) to send to node.js.. ie. If i want to write delete URL