Database integration in Express.js

Express is a minimalistic framework that works along with Node.js and provides salient features to run a backend server and much more. As you know the database plays a vital role in a fully working application to persist data. In this article, we are going to discuss how we can integrate a database in a server that is working upon the Express.js framework.

Step 1: Create an Express Application 

Here we are going to create an express application because all of our work will be going to execute inside express. You can skip this step if you are comfortable with this stuff.

Write these commands in your terminal to start a node app and then install express. Make sure that you have successfully installed npm. The npm init will ask you for some configuration about your project and that is super easy to provide.

npm init
npm install express

Now create an empty .js file (let’s name it app.js), This would be our folder structure,

So, we are ready to start writing the express code inside this app.js file

Javascript

//Importing express module
const express = require('express');

const app = express();
const PORT = 3000;

app.listen(PORT, (error) =>{
    if(!error)
        console.log("Server is Successfully Running, "
            + "and App is listening on port "+ PORT)
    else
        console.log("Error occurred, server can't start", error);
    }
);

Note: Check out this link if you want to learn more about the initialization of the express applications.

Step 2: Run the server

in this step, we are going to confirm whether our server is working or not, Write this command in your terminal to start the express server. The successful start of the server denotes that our express app is ready to listen to the connection on the specified path(localhost:3000 in our example).

node app.js

Something like this will be shown to you in the terminal.

Step 3: Integrate Database

Here comes the most interesting part which you are searching for, Now we will integrate the database with express. But before that, we have to choose one of the database options i.e. MongoDB, MySQL, PostgreSQL, Redis, SQLite, and Cassandra, etc.   
MongoDB and MySQL are the most popular and used by numerous developers, so we are going to discuss only these two. Also, they are totally different so you’ll get a chance to explore more, one is a Document-Oriented NoSQL Database, and the other is a Relational Model Database. 

Note:- It is up to you whether you want to use a cloud database service or localhost, there exists only a slight difference. It is a good thing to know about how to access a cloud database, so here we are also going to discuss an example of cloud service.

MongoDB

• Install mongoose, a package built on the `mongodb` native driver, to interact with the MongoDB instance and model the data from express/node application.

npm install mongoose

• Install MongoDB

Whether you are a Linux, Windows, or Mac User, the official docs provide a simple procedure to install MongoDB on your local machine.
Click Here for Installation:- Windows | Ubuntu | macOS  | Others

After installation, you will be able to use MongoDB in your terminal, also you can install mongo compass a GUI application to interact with the database.

• Integrate with the express application

Explanation:-  First of all we will import the mongoose module and then later we call the connect method, it accepts a connection string, and object with some configurations, The “ExpressIntegration” written inside the connection string is the random name of the Database, the rest all stuff comes under syntax. This method provides a promise in return that’s why we are using then and catches block. On the successful connection with the database, it will call the listen to method with the app object to start the express server otherwise simply will execute a console log on failure. 

Filename: app.js

Javascript

//Importing modules
const express = require('express');
const mongoose = require('mongoose');

const app = express();
const PORT = 3000;
//Connection to the mongodb database
mongoose.connect('mongodb://localhost:27017/ExpressIntegration')
.then(()=>{
    app.listen(PORT, ()=>{
        console.log("Database connection is Ready "
        + "and Server is Listening on Port ", PORT);
    })
})
.catch((err)=>{
    console.log("A error has been occurred while"
        + " connecting to database.");
})

Output: Start the server with the `node app.js` command and this will be output in the terminal, which means we are successfully able to integrate the MongoDB database.

Access Cloud Service
Go to mongodb.com and register/sign in yourself, and check out the mongodb cloud, after then you’ll see something like this, 

Here click on connect, and then it will ask for some configurations like whether you want to use a cloud database for a mongo shell, an application, or Mongo Compass(a GUI). We will choose to connect your application. Later it will ask you about driver and version, choose node.js and the latest version.
And finally, it will provide a string, that will be used to integrate this database into an express server. It also provides a Web User Interface to monitor and configure the database. 
Now just simply replace the earlier localhost string with this one. With this all, your cloud database is ready to use.

MySQL

• Install mysql driver for express/node application.

npm install mysql

• Install MySQL

Before going to use the local database, you should have mysql installed in your local system, proceed to MySQL official links given below, they’ve provided a simple procedure for installation.
Click Here for Installation:- Windows | Linux | MacOS

After installation you will be able to use the MySQL database with your terminal also you can use any GUI like PHPMyAdmin and MySQL workbench, etc to interact with the Database.

• Integrate with the express application

Explanation:-  First of all we will import the mysql module and then createConnection method takes some configurations about the database and provides us a connection object in return. This connection object will be used to call the connect method which connects the application with the database and provides an error object on failure and threadID on successful connection. This connection will be terminated either when the program finishes execution or when the connection calls the end method i.e. connection.end().
After the successful connection, we are calling the listen method of the express app which starts the server.

Filename: app.js

Javascript

//Importing modules
const express = require('express');
const mysql = require('mysql');

const app = express();
const PORT = 3000;

// Create a connection to the database
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'yourpassword',
  database: "ExpressIntegration"
});

// open the MySQL connection
connection.connect(error => {
    if (error){
        console.log("A error has been occurred "
            + "while connecting to database.");
        throw error;
    }

    //If Everything goes correct, Then start Express Server
    app.listen(PORT, ()=>{
        console.log("Database connection is Ready and "
             + "Server is Listening on Port ", PORT);
    })
});

Output: Start the server with the `node app.js` command and this will be output in the terminal, which means we are successfully able to integrate the MySQL database.

Access Cloud Service 
There are lots of service providers with which you can work, here I am going to use the simple one Clever-Cloud MySQL hosting. Click to proceed on the website and then register/sign in yourself, then check out the console of the website. You will see something like this, 

• Click on personal space and then on create. Now, choose an addon and then select MySQL from the list of the addons. It will prompt for the name of the database, provide whatever you want, and then select the zone.
• Finally, click on next and now it will provide Database credentials, use these in your express application to access this database also you can export the environment variables to avoid doing this copy-paste stuff. Clever-Cloud also provides a Web User Interface with PHPMyAdmin so you can easily configure and manage your database.
• Now just replace the data written inside the object being passed to the createConnection method with the data & credentials provided by the clever-cloud, and you will be able to access this database.

Note: You may have noticed that we are connecting to the express server only after a successful database connection, it’s just a convention because the application can work well only when each server is running. Although no one is stopping you to start the server after or before the database connection.

Conclusion: Now we have successfully integrated the database with the express application. You can perform any permittable operations on the Database to accomplish the purpose of your express application. If you got stuck at any step, check out the official docs and search your query for support. All technologies used in this article have good community support so probably someone has been already answered for the problem which you are currently witnessing. 
These are the links you should visit as the next step of this article, CRUD in MongoDB and Basic Query in MYSQL.