Getting started with Node.js and MySQL2

Most of the real world applications require a database for their functioning. There are many types of databases out of which the most popular are relational (SQL) and non-relational (NoSQL) databases. For this tutorial we will be using a relational database with the help of a npm library named mysql2.

What is MySQL ?

MySQL is an open source relational database management system. It is open source and reliable for both small scale and large scale applications.

Installing MySQL

This will be needed so that we can interact with the DB without the Node.js application for operations like creation of table, etc. The steps below are for Windows users:

1. Download MySQL from MySQL website.
2. Install MySQL using the downloaded file and setup the root password.
3. Download MySQL Workbench from here.
4. Install Workbench using the downloaded file.

Setting up table and data

Open Workbench and add a new connection by using the password created during installation.

Run this query to create a new database:

CREATE DATABASE sicMundus;

Run this query to use the above created database:

USE sicMundus;

Run this create query to create a new table named sicMundus:

CREATE TABLE sicMundus(
  id INTEGER PRIMARY KEY AUTO_INCREMENT,
  firstName varchar(255) NOT NULL,
  lastName varchar(255)
);

After the creation of the table run this command to insert some values into this table:

INSERT INTO sicMundus (firstName, lastName)       
VALUES ('Adam',''),
       ('Noah',''),
       ('Magnus','Nielsen'),
       ('Agnes','Nielsen'),
       ('Franziska','Doppler'),
       ('Bartosz','Tiedemann'),
       ('Elisabeth','Doppler'),
       ('Charlotte','Doppler'),
       ('Gustavv','Tanhaus'),
       ('Leopold','Tanhaus'), 
       ('Heinrich','Tanhaus'),
       ('Silja','Tiedemann');

Getting Started

Setting up the Express.js server:

1. Create a new directory named getting-started-with-node-and-mysql.
2. Navigate to the new directory and run the following commands:

npm init
npm install express
npm install mysql2

Setting up the Node.js server

package.json

{
  "name": "getting-started-with-node-and-mysql",
  "version": "1.0.0",
  "description": "Getting started with Node.js and MySQL",
  "type": "module",
  "main": "index.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "author": "singhhr",
  "license": "ISC",
  "dependencies": {
    "express": "^4.19.2",
    "mysql2": "^3.9.7"
  }
}

index.js

import express from 'express';
import mysql from 'mysql2/promise';

var app = express();
 
const connection = await mysql.createConnection({
    host: '127.0.0.1',
    user: 'root',
    password: 'admin',
    database: 'sicMundus'
});

app.get('/members', async function(req, res){
    const [members, fields] = await connection.query("Select * from sicMundus");
    res.send({members});
});

app.listen(process.env.PORT || 3000,function(req,res){
    console.log("Server Started!");
});

To run the server run this command in the terminal in the project directory:
node index.js

You will see the message in the console ‘Server Started!’ which means the server is up and running. You can go to Chrome and go localhost:3000/members to get response from your server:

If you have any questions you can ask them anytime in the comments below.

You can follow me on GitHub: @sahni-hargun