How to connect Express application to MongoDB and MySQL?

Express JS is a JavaScript framework that is used for building the backend of web and mobile applications. It works on top of Node JS which is helpful for managing servers and routes. Connecting an Express JS application to MongoDB and MySQL requires installing Node.js in your computer system. In this article, we will see how we can connect the Express.js application to MongoDB and MySQL.

Prerequisites

• Basic knowledge of Node JS
• Basic Knowledge of DBMS
• Express

Approach 1: Connect Express with MongoDB

Here, we are using MongoDB as a Database and Express for accessing the MongoDB database. In order to connect Express.js with MongoDB, first setup the Node.js and setup the MongoDB after that follow the steps given below:-

Step 1: Create a folder and open it in the terminal.

Step 2: Initialize a Express JS project using following command in terminal

npm init -y

Step 3 : Now, Install the express, mongoDB npm libraries and also dotenv (for using credentials using environment variables)

npm install express mongoose dotenv

Project Structure:

Project Structure

The updated dependencies in package.json file will look like:

"dependencies": {
    "dotenv": "^16.3.1",
    "express": "^4.18.2",
    "mongoose": "^8.0.3"
}

Step 4 : After that, create a file named as “server.js” and inside that file write the code given below. It consist both the express.js and mongoDB connection code.

Javascript

//server.js

const express = require("express");
const app = express();

app.listen(8000, () => {
    console.log("Server Started at port no. 8000");
})

//Mongoose library instance
const mongoose = require('mongoose');
//URL of MongoDB Database
const mongoDBURL = 'mongodb://127.0.0.1:27017/mydemoDB';

//connect to Database
mongoose.connect(mongoDBURL, {
    useNewUrlParser: true,
    useUnifiedTopology: true
})
    .then(() => { console.log("Connection Successfull") })
    .catch((err) => { console.log("Received an Error") })

Step 5 : After that Copy the mongoDBURL and paste it in MongoDB Compass as shown below and click on “Connect“.

MongoDB Compass

Step 6 : Now, to Run the Express.js application and connect to DB use the following command in terminal as “node fileName”. e.g. node sever.js

node server.js

Output:

Output

Approach 2: Connect Express with MySQL

Now, Connecting the express application with MySQL as a database. In order to connect with MySQL first Go to their official site and Download MySQL file and follow the basic steps given below :-

Step 1 : Create a folder and open it in terminal.

Step 2 : Initialize a Express.js project using following command in terminal

npm init -y

Step 3 : Now, use the below command to start and setup the MySQL in current directory or folder and if it asks for password, enter the password you entered while installing MySQL.

mysql -u root -p

Step 4: After that you will be able to see a command line console of MySQL will open as shown below and type the query below to check the databases available and if it shows databases then you have successfully setup the MySQL in current directory.

show databases;

Step 5 : Now Install the MySQL npm package and express.js npm libraries.

npm install  express mysql2

Step 6 : Create a “schema.sql” file in your directory to define the schema for your database table. then copy and paste the queries from schema.sql to MySQL command line. Hence, Your database with defined table schema will be created successfully.

Javascript

//schema.sql

//create a database -> notes_app
CREATE DATABASE notes_app;

//change the current database to notes_app
USE notes_app;

//create a table in notes_app Database -> TABLE_NAME = notes
CREATE TABLE notes (
  id integer AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  contents TEXT NOT NULL,
  created TIMESTAMP NOT NULL DEFAULT NOW()
);

//Query to insert data to notes table of Database notes_app
INSERT INTO notes (title, contents)
VALUES ('My First Note','Hello, I am Vivek'),
       ('My Second Note','I am TCW at GFG');

Create Database in MySQl

Now, to check your table has been created successfully, run the query given below and it will display Table with entries

select *  from notes;

Project Structure:-

Project Structure

The updated dependencies in package.json file will look like:

"dependencies": {
    "express": "^4.18.2",
    "mongoose": "^8.0.3",
    "mysql2": "^3.6.5"
}

Step 7 : Now, create an index.js and database.js file and write the below code in it (You need to paste your MYSQL username and password).

Javascript

//database.js

import mysql from 'mysql2'

// Create a MySQL connection pool
const pool = mysql.createPool({
    host: 'localhost', //MYSQL_HOST you can also use 127.0.0.1
    user: 'root', //MYSQL_USER
    password: 'YOUR_MYSQL_PASSWORD',
    database: 'notes_app'//DEFINE YOUR DATABASE NAME,
}).promise()

// Function to get all notes from database
export async function getNotes() {
    //Query to select all notes available in your Database notes table
    const [rows] = await pool.query("SELECT * FROM notes")
    return rows;
}

Javascript

// index.js

import express, { json } from "express";
import { getNotes } from "./database.js";
const app = express();

const port = process.env.PORT || 3000;

//Route to fetch all notes from MySQL DB
app.get('/notes', async (req, res) => {
    const notes = await getNotes();
    return res.status(200).json({
        success: true,
        message: "Notes Fetched successfully",
        notes: notes
    })
});

//Start the server
app.listen(port, () => {
    console.log(`Server is running on port ${port}`);
});

Step 8 : Now, to Run the Express.js application and connect to Database, use the following command in terminal as “node fileName”. e.g. node index.js

node index.js

Run Express Output

Output :Run the API in the Postman to test.