Sequelize.js: How to connect to MySQL database

Overview

Sequelize is a popular Node.js ORM (Object-Relational Mapping) that supports various databases, including MySQL. It allows developers to write database queries in a way that is independent of the database backend. This tutorial provides a comprehensive guide on how to connect to a MySQL database using Sequelize. We’ll cover the basics of setting up Sequelize, configuring your connection, and defining models to interact with your MySQL tables.

Prerequisites

• Node.js and npm installed on your system
• MySQL database server running and accessible
• Basic understanding of JavaScript and async programming in Node.js

Setting Up the Project

// Step 1: Initialize a new Node.js project
$ npm init -y

// Step 2: Install Sequelize and MySQL driver
$ npm install sequelize mysql2

Configuring the Database Connection

// Step 1: Import Sequelize
const Sequelize = require('sequelize');

// Step 2: Establish a connection
cconst sequelize = new Sequelize('database', 'username', 'password', {
  host: 'localhost',
  dialect: 'mysql'
});

Replace ‘database’, ‘username’, and ‘password’ with your actual MySQL database credentials. The ‘dialect’ should be ‘mysql’ as we are connecting to a MySQL database.

Testing the Connection

sequelize.authenticate()
  .then(() => console.log('Connection has been established successfully.'))
  .catch(err => console.error('Unable to connect to the database:', err));

Defining Models

Models in Sequelize are the abstraction of the tables in the database. Below is an example of how to define a user model.

const User = sequelize.define('user', {
  id: {
    type: Sequelize.INTEGER,
    autoIncrement: true,
    primaryKey: true
  },
  firstName: {
    type: Sequelize.STRING,
    allowNull: false
  },
  lastName: {
    type: Sequelize.STRING
  },
  email: {
    type: Sequelize.STRING,
    unique: true,
    allowNull: false
  }
});

We have defined a User model with fields id, firstName, lastName, and email. You’ll define your models based on your database schema.

Syncing Models with Database

User.sync({ force: true }) // This will drop the table if it already exists
  .then(() => {
  // Table created
  console.log('User table has been created.');
});

The ‘User.sync’ method syncs the model with the database, potentially overwriting changes if ‘force’ is true. Use with caution in a production environment.

CRUD Operations

The following snippets illustrate basic CRUD operations with Sequelize.

// CREATE
User.create({
  firstName: 'John',
  lastName: 'Doe',
  email: '[email protected]'
});

// READ
User.findAll()
  .then(users => {
    console.log(users);
  });

// UPDATE
User.update({ firstName: 'Jane' }, {
  where: { id: 1 }
});

// DELETE
User.destroy({
  where: {id: 1}
});

Each operation returns a promise, so you can use .then() for success or .catch() for handling errors.

Advanced Configuration and Usage

We will now explore more advanced configurations, such as connection pooling, handling migrations, and using Sequelize with TypeScript.

Connection Pooling

const sequelize = new Sequelize('database', 'username', 'password', {
  host: 'localhost',
  dialect: 'mysql',
  pool: {
    max: 5,
    min: 0,
    acquire: 30000,
    idle: 10000
  }
});

Connection pooling can greatly improve the performance of your application by reusing connections.

Using Migrations

Migrations are a way to keep your database schema in sync with your model definitions. Sequelize CLI can be used to generate and run migrations.

// Install Sequelize CLI
$ npm install --save-dev sequelize-cli

// Initialize Sequelize CLI
$ npx sequelize-cli init

This creates folders and config files necessary for migrations. You can now create and run migrations as needed.

Working with TypeScript

Sequelize supports TypeScript directly. You need to install TypeScript typings for Sequelize and configure your project to use TypeScript.

// Install TypeScript typings for Sequelize
$ npm install --save-dev @types/sequelize

Then you can define your models using TypeScript classes and decorators.

Conclusion

Connecting to a MySQL database with Sequelize is straightforward, yet provides powerful abstractions for managing complex data operations. By following the steps in this guide, you can integrate Sequelize into your Node.js project and perform database operations with ease. Always remember to handle your database credentials securely and test your data operations thoroughly to ensure the integrity of your application’s data.