DEV Community

Cover image for Connecting to MySQL database with Sequelize in Node.js
Luqman Shaban
Luqman Shaban

Posted on

3

Connecting to MySQL database with Sequelize in Node.js

Hello there!

In the previous article, we created a Sign-Up form in React using TypeScript. In this article, we'll create a server-side application that will sign up the user and store their details in the **MySQL** database. The technologies we'll use are Node.js, and Express, for the API, and Sequelize ORM for MySQL Database.

In the terminal, let's create a folder and name it form-server and initialize a node package.

mkdir form-server
cd form-server 
npm init -y
Enter fullscreen mode Exit fullscreen mode

We'll now install the packages using npm:
npm i express mysql2 sequelize nodemon dotenv

(Nodemon is a package that allows the server to track the changes and run the server continuously without having to restart the server every time a change is made)
Now open the folder in vscode: code .

In vscode, let's create a folder called config and inside it, we'll create a file called db.js . In this file is where we'll connect our server to MySQL database.
In the file, import Sequelize from sequelize, and dotenv from 'dotenv':

import { Sequelize } from "sequelize";
import dotenv from 'dotenv';
Enter fullscreen mode Exit fullscreen mode

_(dotenv is important for security purposes. It is used to load the environment variables i.e. the database password, user etc from a .envfile into the node.js application. We'll create a .env file that stores the password, database, user and host of our database and upon pushing our project to GitHub, we exclude this file.)
_
Next, let's load the .env variables with the config() function.

dotenv.config()

Now let's connect to the database:

const sequelize = new Sequelize(
  process.env.DB_NAME,
  process.env.DB_USER,
  process.env.DB_PASSWORD,
  {
    host: process.env.DB_HOST,
    dialect: "mysql",
  }
);
Enter fullscreen mode Exit fullscreen mode

In the above code, we create an instance of Sequelize which interacts with the database. Thenew Sequelize creates a new Sequelize instance with the provided configuration options. The process.env.DB_NAME, process.env.DB_USER, process.env.DB_PASSWORD, host and dialect represent the name of the database, the user, password, host and the type of database respectively.

Next, we'll confirm the database connection with the following code:

sequelize
  .authenticate()
  .then(() => {
    console.log("DATABASE CONNECTED");
  })
  .catch((err) => {
    console.log(err);
  });
Enter fullscreen mode Exit fullscreen mode

.authenticate() is provided by Sequelize and is used to authenticate the database connection.
.then(() => { ... }): This is a Promise-based callback function that will be executed if the authentication is successful. In this case, it logs the message "DATABASE CONNECTED" to the console.
.catch((err) => { ... }): This is a Promise-based callback function that will be executed if the authentication fails. It captures any error that occurs during the authentication process and logs the error message to the console.

Finally, export the instance:

export default sequelize;
Here's how the file looks:


import { Sequelize } from "sequelize";
import dotenv from 'dotenv'

dotenv.config()

const sequelize = new Sequelize(
  process.env.DB_NAME,
  process.env.DB_USER,
  process.env.DB_PASSWORD,
  {
    host: process.env.DB_HOST,
    dialect: "mysql",
  }
);

sequelize
  .authenticate()
  .then(() => {
    console.log("DATABASE CONNECTED");
  })
  .catch((err) => {
    console.log(err);
  });

export default sequelize;
Enter fullscreen mode Exit fullscreen mode

Create a database in mysql called form
(follow this link if you don't know how to)

Outside the config folder create a file .env and add the following:

DB_NAME='form'
DB_HOST='localhost'
DB_USER='root'
DB_PASSWORD='**_your database password_**' //leave it empty if you database doesn't have a password
Enter fullscreen mode Exit fullscreen mode

In the root folder (outside of the config folder) let's create a file called index.js and import express from express and the sequelize instance we just created:

import express from 'express'
import sequelize from './config/db.js'
Enter fullscreen mode Exit fullscreen mode

We then create an instance of express:

const app = express()

**sequelize**: This is the instance of Sequelize that was created earlier.

sync({ alter: true }): This method is provided by Sequelize and is used to synchronize the defined models with the database. The { alter: true } option indicates that Sequelize should modify the tables if they already exist, altering them to match the model definitions if necessary.

.then(() => { ... }): This is a Promise-based callback function that will be executed if the synchronization is successful. In this case, it logs the message "Tables created." to the console.

**.catch((err) => { ... })**: This is a Promise-based callback function that will be executed if an error occurs during the synchronization process. It captures any error that occurs and logs the error message to the console.

Lastly, we start the server on port 3300 and listen to any incoming request from port 3300:

const port = 3300

app.listen(port, () => {
    console.log(`SERVER RUNNING ON PORT ${port}`);
})
Enter fullscreen mode Exit fullscreen mode

To run the server we'll have to modify the package.json file. Inside the "scripts"
add the following:

"scripts": {
    "test": "echo \"Error: no test specified\" && exit 1",
    "start": "nodemon index.js" // we added this line of code
 },
Enter fullscreen mode Exit fullscreen mode

Now the in terminal, run the server with: npm start
Here are the results:

SERVER RUNNING ON PORT 3300 
Executing (default): SELECT 1+1 AS result 
Executing (default): SELECT 1+1 AS result 
DATABASE CONNECTED 
Tables created.
Enter fullscreen mode Exit fullscreen mode

In the next article, we'll go ahead and create the models, router, and controller.

I would greatly appreciate your feedback and suggestions regarding this article. If you notice any errors or have any recommendations for improvement, please feel free to share them below.

Connect with Me

API Trace View

How I Cut 22.3 Seconds Off an API Call with Sentry 👀

Struggling with slow API calls? Dan Mindru walks through how he used Sentry's new Trace View feature to shave off 22.3 seconds from an API call.

Get a practical walkthrough of how to identify bottlenecks, split tasks into multiple parallel tasks, identify slow AI model calls, and more.

Read more →

Top comments (0)

Heroku

This site is powered by Heroku

Heroku was created by developers, for developers. Get started today and find out why Heroku has been the platform of choice for brands like DEV for over a decade.

Sign Up

👋 Kindness is contagious

Explore a sea of insights with this enlightening post, highly esteemed within the nurturing DEV Community. Coders of all stripes are invited to participate and contribute to our shared knowledge.

Expressing gratitude with a simple "thank you" can make a big impact. Leave your thanks in the comments!

On DEV, exchanging ideas smooths our way and strengthens our community bonds. Found this useful? A quick note of thanks to the author can mean a lot.

Okay