Node MySQL Tutorial – How to use Node.js with MySQL
Hey there! So you decided to dive into this wonderful world of MySQL? It’s gonna be a fun and exciting road ahead! This is where it all begins – a hands-on guide to all of the essential instruments you’ll need for the purpose of the website database. Your mindset is very important when teaching yourself any new skills, not just programming.
By learning concepts and then practicing them, you will gain a better understanding of how things work and fit together. A project best aligns your theoretical knowledge with how programming concepts and techniques work in practice. Let’s start with what we will learn throughout this course!
Through this blog, we are going to learn ‘How to connect MySQL with NodeJS. with a simple CRUD application’. In this blog, we will demonstrate how to establish a connection with MySQL and perform various CRUD operations in a few simple and easy steps. First, let us understand why we use MySQL with Node.js.
MySQL is one of the most preferred databases by the developers as it is open-sourced as well as efficient. This is why most prominent programming languages like Java, Python, Node.js, etc, provide drivers to access and perform transactions with MySQL.
Node.js is an extremely popular open-source, cross-platform web, and network app development resource. Thousands of developers around the world use it daily to develop I/O intensive web applications. Node.js brings many advantages to the table, making it a better choice than other server-side platforms like Java or PHP.
What is MySQL Database?
MySQL is a relational database management system (RDBMS) developed by Oracle based on structured query language (SQL).
- It’s a standardized language used to interact with relational databases.
- A database is a structured collection of data. It may be anything from a simple shopping list to a picture gallery or a place to hold vast amounts of information in a corporate network.
- It’s utilized to extract, organize, manage, and manipulate data stored in relational databases
- It consists of columns and rows. A table is a set of data elements (values).
An Example with MySQL and Node.js
Prerequisites
- NodeJS with ExpressJS
- Download it here.
- MySQL
- Download it here.
- For testing the API hoppscotch.io.
Simple CRUD App
In this example, we are going to create a simple TODO App having simple CRUD operation.
Create a boilerplate project
Initialize a project using the command:
npm init -yCode language: JavaScript (javascript)Install the required packages
npm install express mysqlCode language: JavaScript (javascript)Install nodemon (optional)
npm install -D nodemonCode language: JavaScript (javascript)Create index.js file
A simple express server using some basic code.
// index.js
const express = require('express');
const app = express();
app.use(express.json());
const PORT = process.env.PORT || 1337;
app.get('/ping', (_, res) => {
res.json({ status: "ok", message: "pong" })
});
app.listen(PORT, () => console.log(`? server running on ${PORT}`));Code language: JavaScript (javascript)Create a connection
// MySQL Connection Configuration
const connectionConfig = {
host: "localhost",
user: "root",
password: "test@1234",
database: "mydb"
}
// Create a connection driver
const connetion = mysql.createConnection(connectionConfig);
// Connect to the MySQL Server
connetion.connect((err) => {
if (err) {
console.log("ERROR:: " + err)
return;
}
console.log(`✅ database connection successful!`)
});
Code language: JavaScript (javascript)The createConnection function takes two types of parameters, either a connectionUri or an object containing the connection configuration.
- host: The ‘host’ property specifies the host where the database is hosted, which in our case is ‘localhost’. For production-ready applications, this will be a URL
- user and password: The ‘user’ and ‘password’ property defines the user who is trying to access the database. It is the same which is used while setting up and installing MySQL.
- database: The ‘database’ property specifies the database we are trying to connect to. All the SQL queries we write will be executed on this specific database.
The connection.connect() method tries to connect to the database with the above-provided credentials. This method takes in a callback function which helps in handling any error while connecting to the database. If there is no error present while connecting to the database, then the message ✅ database connection is successful! will be printed out to the terminal.
On successful execution, the express server will connect to the MySQL instance running on your PC.
Note:
If the Error: ER_BAD_DB_ERROR: Unknown database ‘mydb’ shows it means you have to create a database called mydb. Fire up a terminal and enter into MySQL shell and enter the command CREATE DATABASE mydb;.
Create the table todo
// Create the table todo.
connetion.query(
'CREATE TABLE IF NOT EXISTS todo(id int AUTO_INCREMENT, note VARCHAR(255), PRIMARY KEY(id))',
(err) => {
if (err) {
console.log('Error::Failed to crate table todo. ' + err);
}
console.log(`✅ table todo created!`);
}
);
Code language: JavaScript (javascript)Create a record
// 1. Create a record.
app.post('/create', async (req, res) => {
const { note } = req.body;
// Construct the Query String.
const sql = 'INSERT INTO todo (note) VALUES (?)';
try {
connetion.query(sql, [note], (err) => {
if (err) {
console.log('ERROR:: Failed to insert into database. ' + err);
res.status(400).json({ message: err.message });
return;
}
res.status(201).json({
status: 'ok',
message: 'Record Created!',
});
});
} catch (err) {
console.log('ERROR:: ' + err);
res.status(500).send();
}
});
Code language: JavaScript (javascript)Read a record
// 2. Read a record
app.get('/read', (req, res) => {
// Construct the Query String.
const sql = 'SELECT * FROM todo;';
try {
connetion.query(sql, (err, results) => {
if (err) {
console.log('ERROR:: Failed to insert into database. ' + err);
res.status(400).json({ message: err.message });
return;
}
res.status(201).json({
status: 'ok',
message: 'Todos Found!',
data: results,
});
});
} catch (err) {
console.log('ERROR:: ' + err);
res.status(500).send();
}
});
Code language: JavaScript (javascript)Update Record
// 2. Read a record
app.get('/read', (req, res) => {
// Construct the Query String.
const sql = 'SELECT * FROM todo;';
try {
connetion.query(sql, (err, results) => {
if (err) {
console.log('ERROR:: Failed to insert into database. ' + err);
res.status(400).json({ message: err.message });
return;
}
res.status(201).json({
status: 'ok',
message: 'Todos Found!',
data: results,
});
});
} catch (err) {
console.log('ERROR:: ' + err);
res.status(500).send();
}
});
Code language: JavaScript (javascript)Delete a record
// 4. Delete a record
app.delete('/delete/:id', async (req, res) => {
const { id } = req.params;
const sql = 'DELETE FROM todo WHERE id = ?;';
try {
connetion.query(sql, [id], (err, results) => {
if (err) {
console.log('ERROR:: Failed to update database. ' + err);
return res.status(400).json({ message: err.message });
}
if (results.affectedRows === 0) {
return res.status(404).json({
message: 'Todo Not Found!',
});
}
return res.status(201).json({
status: 'ok',
message: 'Todo Deleted!',
});
});
} catch (err) {
console.log('ERROR:: ' + err);
res.status(500).send();
}
});
Code language: JavaScript (javascript)Conclusion
In this lesson, we learned the basic definition of MySQL Database and an in-depth understanding of how to connect MySQL with Node.js with a simple CRUD application. That’s it–you’re done with the basic CRUD application! If you commit to doing most things from this method, these commands will become second nature to you. Practice more questions for you to check your understanding of this lesson on your own. Good luck!
The GitHub link for the above example can be found here.
Sharing is caring
Did you like what Jainam Desai wrote? Thank them for their work by sharing it on social media.
No comments so far
Curious about this topic? Continue your journey with these coding courses:
