Interacting with MySQL databases with Sequelize

Introduction to MySQL

MySQL is a relational database management system (RDBMS). Data in these types of databases are arranged into tables, which can reference other data stored in tables. This relation between data in separate tables helps to add structure to the data and gives origin to the name RDBMS. In most cases, these databases must be searched or queried using the specific syntax that the creators of the database set up. Luckily, for us, there are programs out there that allow us the freedom and flexibility to query these databases in a more Javascript like format, these types of programs are called ORMs or object-relational mapping. Sequilize is one of those programs. It handles the task of converting between objects in Javascript and the rows in a MySQL database. Let's take a look at the process of getting Sequelize set up and running on a project using a MySQL database.

Setting up a MySQL Database

Let's set up our MySQL database using the syntax provided to us by the database. Here we're creating a songs table with id, name, and artist values in the table.

CREATE DATABASE music;
USE music;
CREATE TABLE songs (
  id int(5) auto_increment primary key,
  name char(30),
  artist char(20)
);

insert into songs(name, artist)
        values ('Stairway To Heaven', 'Led Zeppelin');
insert into songs(name, artist)
        values ('Rain Song', 'Led Zeppelin');

insert into songs(name, artist)
        values ('Better Now', 'Post Malone');

insert into songs(name, artist)
        values ('Congratulations', 'Post Malone');

When we query our database we can select the entire table by using this query:

SELECT * FROM songs;

| id |               name |       artist |
|----|--------------------|--------------|
|  1 | Stairway To Heaven | Led Zeppelin |
|  2 |          Rain Song | Led Zeppelin |
|  3 |         Better Now |  Post Malone |
|  4 |    Congratulations |  Post Malone |     

To select only the songs by Post Malone you could use this query:

SELECT name FROM songs
      WHERE artist = 'Post Malone';

|            name |
|-----------------|
|      Better Now |
| Congratulations |

Now that we have an understanding of how a basic database in MySQL functions, let's see how this process can be done using the Sequelize ORM!

Creating a Database with Sequelize

Let's take a look at how setting up a database with Sequelize differs from the process described above.

// Run in the terminal to install Sequelize 
// and the MySQL database
npm install --save sequelize
npm install mysql --save

Now we have everything set up to use Sequelize within the Javascript file to create our database and define the tables we want within in. Let's look at that process.

// Run in the Javascript file

const {sequilize} = require('sequelize');

const db = new Sequelize('music', 'root', '', {
  host: 'localhost',
  dialect: 'mysql',
});

const Songs = db.define('Songs', {
  id: {
    type: Sequelize.INTEGER,
    autoIncrement: true,
    primaryKey: true
  },
  song: {
    type: Sequelize.STRING,
  },
  artist: {
    type: Sequelize.STRING,
  }
});

After executing the lines of code above, we've done the exact same thing as earlier. We've created a music database using Sequelize and created a Songs table with the values of id, song, and artist. Now that we've created the database and table, let's add some data into the table like we did before. Notice how we use the 'await' keyword, this is because almost every Sequelize method is asynchronous!

const createData = async () => {
  const congratulations = await Songs.create({
    song: 'Congratulations',
    artist: 'Post Malone'
  }):

  const betterNow = await Songs.create({
    song: 'Better Now',
    artist: 'Post Malone'
  }):

  const stairWay = await Songs.create({
    song: 'Stairway to Heaven',
    artist: 'Led Zeppelin'
  }):

  const rainSong = await Songs.create({
    song: 'Rain Song',
    artist: 'Led Zeppelin'
  }):
}

Now that we've populated our data, we need to have a way to access the data we've inserted into our table, let's look at how that's done. Sequelize has a great method called findAll().

const getData = async () => {
  const getMyMusic = await Songs.findAll();
  console.log(getMyMusic); 
}

// Prints the following to the console

dataValues:
     { id: 1,
       name: 'Congratulations',
       artist: 'Post Malone',
       createdAt: 2020-07-19T20:20:06.450Z,
       updatedAt: 2020-07-19T20:20:06.450Z 
     },
     { id: 2,
       name: 'Better Now',
       artist: 'Post Malone',
       createdAt: 2020-07-19T20:20:06.450Z,
       updatedAt: 2020-07-19T20:20:06.450Z 
     },   
     { id: 3,
       name: 'Stairway to Heaven',
       artist: 'Led Zeppelin',
       createdAt: 2020-07-19T20:20:06.450Z,
       updatedAt: 2020-07-19T20:20:06.450Z 
     },
     { id: 4,
       name: 'Rain Song',
       artist: 'Led Zeppelin',
       createdAt: 2020-07-19T20:20:06.450Z,
       updatedAt: 2020-07-19T20:20:06.450Z 
     },

The ability to use an ORM while working with databases makes life a whole lot easier. Creating your database and defining the structure can all be done within your Javascript files and doesn't require you to use the MySQL interface. Sequelize can also be used to perform specific queries of your database just like the ones in MySQL. For more information, check out the Sequelize documentation. Also this article by Joseph Hu, really helped solidify my understanding of Sequelize as well, give it a read! I hope my article provided insight into both MySQL and Sequelize and how using an ORM can really speed up the process of creating and defining your database!