Node.js and MySQL: A Powerful Combination

By / January 25, 2023
Node JS MySQL
Node JS MySQL

Node.js is a JavaScript runtime based on the V8 JavaScript engine in Chrome. It enables developers to run JavaScript on the server, making it an excellent alternative for developing web apps. In contrast, MySQL is a well-known open-source relational database management system. Node.js and MySQL may be used together to create sophisticated online applications.

Installing Node.js and MySQL

You must first install Node.js and MySQL on your PC before you can begin working with them. You may install Node.js by downloading and running the installer from the official Node.js website. You may install MySQL by downloading and running the installer from the official MySQL website.

Connecting to a MySQL Database

After installing Node.js and MySQL, you may begin connecting to a MySQL database. The following example connects to a MySQL database using the “mysql” package:

var mysql = require('mysql');

var con = mysql.createConnection({
  host: "localhost",
  user: "yourusername",
  password: "yourpassword",
  database: "mydb"
});

con.connect(function(err) {
  if (err) throw err;
  console.log("Connected!");
});

In this example, we’ll use the mysql package’s createConnection function to establish a new connection to a MySQL database. To the method, we send an object containing the connection information. Because the host property is set to “localhost,” the MySQL server is operating on the same system as the Node.js script. The user and password attributes are set to the MySQL user’s username and password, respectively, and the database property is set to the name of the database to which we want to connect.

Querying a MySQL Database

Once you are connected to a MySQL database, you can start querying it. The following example uses the “mysql” package to query a MySQL database:

var mysql = require('mysql');

var con = mysql.createConnection({
  host: "localhost",
  user: "yourusername",
  password: "yourpassword",
  database: "mydb"
});

con.connect(function(err) {
  if (err) throw err;
  con.query("SELECT * FROM customers", function (err, result, fields) {
    if (err) throw err;
    console.log(result);
  });
});

In this example, we’re using the query method of the mysql package to do a SELECT query on the database’s “customers” table. The query returns all of the rows in the table, and the results are delivered as an array of objects to the callback function.

Q&A

Q: What should I do if I get an issue while connecting to a MySQL database?
A: You may manage errors by giving a callback function to the mysql package’s connect method and inspecting the err argument in the callback function.

Q: How do I enter information into a MySQL database?
A: You may put data into a MySQL database by using the mysql package’s query method and sending an INSERT query to it. The following code, for example, puts a new row into the “customers” table:

con.query("INSERT INTO customers (name, address) VALUES ('John Doe', '123 Main St')", function (err, result) {
if (err) throw err;
console.log("1 record inserted");
});


Exercises

1. Create a new Node.js script that connects to a MySQL database and runs a SELECT query on the “products” table.

2. Modify the script from exercise 1 to also insert a new product into the “products” table.

3. Create a new Node.js script that connects to a MySQL database and runs a UPDATE query on the “products” table to update the price of a product.

Answers

1.

var mysql = require('mysql');

var con = mysql.createConnection({
host: "localhost",
user: "yourusername",
password: "yourpassword",
database: "mydb"
});

con.connect(function(err) {
if (err) throw err;
con.query("SELECT * FROM products", function (err, result, fields) {
if (err) throw err;
console.log(result);
});
});

2.

var mysql = require('mysql');

var con = mysql.createConnection({
host: "localhost",
user: "yourusername",
password: "yourpassword",
database: "mydb"
});

con.connect(function(err) {
if (err) throw err;
con.query("SELECT * FROM products", function (err, result, fields) {
if (err) throw err;
console.log(result);
});
con.query("INSERT INTO products (name, price) VALUES ('New Product', 19.99)", function (err, result) {
if (err) throw err;
console.log("1 record inserted");
});
});

3.

var mysql = require('mysql');

var con = mysql.createConnection({
host: "localhost",
user: "yourusername",
password: "yourpassword",
database: "mydb"
});

con.connect(function(err) {
if (err) throw err;
con.query("UPDATE products SET price = 24.99 WHERE id = 1", function (err, result) {
if (err) throw err;
console.log(result.affectedRows + " record(s) updated");
});
});

We’ve shown how to utilize Node.js with MySQL to create sophisticated web apps in this tutorial. We’ve gone over installing and connecting to a MySQL database, querying a MySQL database, and inserting and updating data in a MySQL database. We’ve also included activities to help you hone your abilities, as well as answers to the exercises to help you double-check your work.

Scroll to Top