Connecting to MySQL database with Sequelize in Node.js
Tags: webdev, database, node, mysql
Published: 2023-06-08 16:43:49

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
```

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';
```

_(dotenv is important for security purposes. It is used to load the environment variables i.e. the database password, user etc from a `.env `file 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",
  }
);
```
In the above code, we create an instance of `Sequelize` which interacts with the database. The` new 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);
  });
```
**`.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;
```
Create a database in mysql called `form`
[(follow this link if you don't know how to)](https://blog.devart.com/creating-a-new-database-in-mysql-tutorial-with-examples.html#:~:text=Right%2Dclick%20the%20connection%20name,database%2C%20select%20charset%20and%20collation.)

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
```

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'
```
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}`);
})
```

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
 },
```
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.
```

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 ](https://www.linkedin.com/in/luqman-shaban/)