Sequelize.js: How to Create New Tables

Introduction

Sequelize is a powerful Object-Relational Mapping (ORM) framework for Node.js, which supports multiple SQL dialects and provides straightforward methods to work with relational databases. In this tutorial, we will focus on how to create new tables in a database using Sequelize.js. We’ll start with the basics and gradually move to more advanced concepts, showing code examples at each step to illustrate the process. If you’re new to Sequelize or ORMs, this guide will give you a solid foundation for managing your database schema through JavaScript code.

Setting Up Sequelize

Before we can create tables with Sequelize, we must first install and configure it. In a new Node.js project directory, you can install the Sequelize npm package along with the database driver of your choice (e.g., PostgreSQL, MySQL, SQLite). Let’s use MySQL for this example:

npm install --save sequelize
npm install --save mysql2

Next, we need to set up a connection to our database:

const Sequelize = require('sequelize');
const sequelize = new Sequelize('database', 'username', 'password', {
  host: 'localhost',
  dialect: 'mysql' // or 'postgres', 'sqlite', 'mssql'
});

Ensure that your database is accessible and the credentials are correct to avoid connection errors.

Defining a Model

In Sequelize, a table is represented as a Model. To define a model, you simply call sequelize.define method, providing the table name and an object that describes the table schema:

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

This code defines a new User model with an ID, username, email, and password fields. It also specifies data types, primary key, auto-increment behavior, uniqueness constraints, mandatory fields, and validation rules.

Migrating the Model to Database

To create a corresponding table in the database, we can use the sync() method:

User.sync({ force: true }).then(() => {
  console.log('User table created successfully.');
}).catch((error) => {
  console.error('Error creating table:', error);
});

The force: true flag used in this code will drop the table if it already exists and create a new one, essentially resetting your table during development.

Advanced Model Definitions

As you get more comfortable with Sequelize, you can begin to use more advanced features such as associations (relationships between tables), hooks (lifecycle events), virtual fields, etc. Let’s consider an example involving a foreign key relation.

const Post = sequelize.define('post', {
  // ... post model fields ...
  authorId: {
    type: Sequelize.INTEGER,
    references: {
      model: User,
      key: 'id',
    }
  }
});

This Post model includes an authorId field, which is a foreign key reference to the User model defined earlier. Such references are foundational for expressing relations in SQL databases.

Using Migrations for Table Creation

While the sync() approach is suitable for development and small projects, for production-grade applications, it’s advisable to use migrations. Sequelize provides a CLI tool to handle migrations, which help you manage database changes systematically.

A migration file typically contains an up method for applying changes and a down method for reverting them:

module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.createTable('Users', {
      // Table schema
    });
  },
  down: async (queryInterface, Sequelize) => {
    await queryInterface.dropTable('Users');
  }
};

After creating a migration file, you can apply migrations with the Sequelize CLI:

npx sequelize-cli db:migrate

Migrations ensure a record of schema changes and provide a mechanism to apply or revert these changes in a controlled manner.

Conclusion

In this tutorial, we’ve covered how to set up Sequelize, define models for your tables, and then create those tables in your database, both directly and via migrations. We looked at basic table creation, involving a single table, as well as more advanced topics such as involving foreign key relationships and using migrations. With the skills you’ve learned, you should now feel comfortable integrating Sequelize into your Node.js projects for robust database management.

Remember that while Sequelize can greatly simplify database operations, it’s still essential to understand the underlying SQL concepts and ensure that your models are well-designed and optimized for the specific use case of your application. Keep experimenting with Sequelize and exploring its rich feature set to fully leverage the capabilities of your SQL databases.