Define a Database Model in Sequelize

In Sequelize, a model is an abstraction that represents a table in the database and that tells several things about the entity it represents, such as its name, the name of its columns, their data types and constraints, and so on.

Defining a model

To create a new model, you can use the define() method of the database handler:

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

const database = new Sequelize(/* ... */);

database.define(<model>, {
  <column>: {
    <property>: <value>,
    ...
  }
}, <options>?);

Where:

• <model> is the name of the database model.
• <column> is the name of the column in the database.
• <property> is either the type of the column or a constraint on the column.
• <value> is the value of the property.
• <options> is an optional object used to configure the model.

Configure the table name

By default, Sequelize automatically pluralizes the model name and uses that as the database table name.

This pluralization is done under the hood by a library called inflection, so that irregular plurals are computed correctly (e.g., user -> users, person -> people).

This means that, if your model name is user, Sequelize will perform operations on the corresponding MySQL table named users.

Prevent pluralization

To prevent the auto-pluralization of a model name, you can pass an optional object to the define() method containing the freezeTableName property set to true:

database.define(<model>, {
  <columns>
}, {
  freezeTableName: true
});

Alternatively, to set this behavior globally for all the models of the Sequelize instance, you can pass the same object to the define property of the constructor's configuration object:

new Sequelize(<credentials>, {
  define: {
    freezeTableName: true,
  }
});

Provide an explicit table name

To provide an explicit table name rather than relying on Sequelize's inference rules, you can use the tableName property:

database.define(<model>, {
  <columns>
}, {
  tableName: 'table_name'
});

Note: This method is usually preferred as it is more explicit and less error-prone.

Define the column type

Just like in a physical database table, every column of a model must have an explicit data type, which is defined through the type property:

database.define(<model>, {
  <column>: {
    type: <value>
  }
});

Common data types

In Sequelize, standard datatypes are available through the DataTypes object exported by the module:

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

Here is a list of the most common SQL data types and their Sequelize equivalent:

+------------------+----------------------------+
| MySQL            | Sequelize                  |
+------------------+----------------------------+
| VARCHAR(255)     | DataTypes.STRING           |
| VARCHAR(1234)    | DataTypes.STRING(1234)     |
| TEXT             | DataTypes.TEXT             |
| TINYTEXT         | DataTypes.TEXT('tiny')     |
+------------------+----------------------------+
| TINYINT(1)       | DataTypes.BOOLEAN          |
+------------------+----------------------------+
| INTEGER          | DataTypes.INTEGER          |
| INTEGER UNSIGNED | DataTypes.INTEGER.UNSIGNED |
| BIGINT           | DataTypes.BIGINT           |
| FLOAT            | DataTypes.FLOAT            |
| DECIMAL          | DataTypes.DECIMAL          |
+------------------+----------------------------+
| TIME             | DataTypes.TIME             |
| DATE             | DataTypes.DATEONLY         |
| DATETIME         | DataTypes.DATE             |
+------------------+----------------------------+
| CHAR(36)         | DataTypes.UUID             |
+------------------+----------------------------+

Example

Let's consider this model:

const Transaction = database.define('employee', {
  id: {
    type: DataTypes.INTEGER
  },
  full_name: {
    type: DataTypes.STRING(120)
  },
  hire_date: {
    type: DataTypes.DATE
  },
  salary: {
    type: DataTypes.FLOAT
  }
});

Which corresponds to this SQL statement:

CREATE TABLE employees (
  id INTEGER,
  full_name VARCHAR(120),
  hire_date DATE,
  salary FLOAT
);

Set a default value

By default, Sequelize assumes that the default value of a column is NULL.

To specify a custom default value instead, you can use the defaultValue property of the column:

database.define(<model>, {
  <column>: {
    type: <type>,
    defaultValue: <value>
  }
});

Example

Let's consider this model:

const Deposit = database.define('deposit', {
  id: {
    type: DataTypes.INTEGER
  },
  account: {
    type: DataTypes.STRING(12),
    defaultValue: '9651-156-321'
  },
  amount: {
    type: DataTypes.DECIMAL(6, 2)
  },
  date: {
    type: DataTypes.DATE,
    defaultValue: DataTypes.NOW
  }
});

Which corresponds to this SQL statement:

CREATE TABLE deposits (
  id INTEGER,
  account VARCHAR(12) DEFAULT '9651-156-321',
  amount DECIMAL(6, 2),
  date DATETIME DEFAULT NOW()
);

Increment values automatically

To automatically increment the value of a column by 1 when a new row is inserted, you can set the autoIncrement property of the column to true.

database.define(<model>, {
  <column>: {
    type: <type>,
    autoIncrement: true
  }
});

Note: In SQL, this property only works with the INTEGER data type and primary keys.

Define column constraints

A constraint is a rule defined at the SQL level.

If a constraint check fails, an error will be thrown by the database and Sequelize will forward this error to JavaScript.

Allow NULL values

To allow null values in a column, you can set the allowNull property to true:

database.define(<model>, {
  <column>: {
    type: <type>,
    allowNull: true
  }
});

Note: In SQL, this translates to the NULL / NOT NULL constraints.

Example

Let's consider this model:

const Contract = database.define('contract', {
  id: {
    type: DataTypes.INTEGER,
    allowNull: false
  },
  type: {
    type: DataTypes.ENUM('temporary', 'fulltime'),
    defaultValue: 'temporary'
  },
  start_date: {
    type: DataTypes.DATEONLY,
    allowNull: false
  },
  end_date: {
    type: DataTypes.DATEONLY,
    allowNull: true
  },
  salary: {
    type: DataTypes.DECIMAL(6, 2),
    allowNull: false
  }
});

Which corresponds to this SQL statement:

CREATE TABLE contracts (
  id INT NOT NULL,
  type ENUM('temporary', 'fulltime') DEFAULT 'temporary',
  start_date DATE NOT NULL,
  end_date DATE,
  salary DECIMAL(6, 2) NOT NULL
);

Enforce unique values

To define a column as unique, you can set the unique property to true:

database.define(<model>, {
  <column>: {
    type: <type>,
    unique: true
  }
});

Note: In SQL, this translates to the UNIQUE constraint.

Example

Let's consider this model:

const Movie = database.define('movie', {
  id: {
    type: DataTypes.INTEGER,
    allowNull: false,
    unique: true
  },
  title: {
    type: DataTypes.STRING,
    allowNull: false
  },
  genre: {
    type: DataTypes.STRING,
    allowNull: true
  },
  year: {
    type: DataTypes.SMALLINT.UNSIGNED,
    allowNull: false
  }
});

Which corresponds to this SQL statement:

CREATE TABLE movies (
  id INT UNIQUE NOT NULL,
  title VARCHAR(255) NOT NULL,
  genre VARCHAR(255),
  year SMALLINT UNSIGNED NOT NULL
);

Disable and rename timestamps

By default, Sequelize automatically adds and manages the fields createdAt and updatedAt to every model, using the data type DataTypes.DATE, where:

• createdAt contains the creation timestamp.
• updatedAt contains the latest update timestamp.

To disable this behavior, you can set the timestamps property to false:

sequelize.define('model', {
  // columns
}, {
  timestamps: false
});

To only disable one of them, you can set the createdAt or updatedAt property to false:

sequelize.define('model', {
  // columns
}, {
  createdAt: false
});

Finally, to rename one or both of them, you can provide a string instead of a boolean value:

sequelize.define('model', {
  // columns
}, {
  createdAt: 'created_at_timestamp'
});

Synchronize a model

To synchronize a model, which means physically creating its corresponding table in the database by executing an SQL query, you can use its asynchronous sync() method:

await model.sync(options?);

This method can be called in three different ways:

• Calling sync() without arguments will create the table only if it doesn't exist.
• Calling sync({ force: true }) will first delete (i.e. drop) the table if it exists and recreate it.
• Calling sync({ alter: true }) will check the current state of the table (its columns, their data types, etc) and perform the necessary changes to ensure it matches the model's properties.

Synchronize all models

To synchronize all existing models at once, you can use the sync() method of the database handler instantiated from the Sequelize class:

await database.synch(options?);

Note: Keep in mind that the usage of this method with the force and the alter properties are potentially destructive operations, and are therefore not recommended in production.

Summary

Here's a summary of what you've learned in this lesson:

• A model is an abstraction that represents a table in the database.
• The define() method of the database handler is used to create a new model.
• The freezeTableName property is used to prevent the pluralization of a model's name.
• The tableName property is used to provide an explicit table name.
• The type property is used to define the data type of a column using the DataTypes object.
• The defaultValue property is used to set the default value of a column (i.e. DEFAULT in SQL).
• The autoIncrement property is used to automatically increment the value of a column upon creation (i.e. AUTO_INCREMENT in SQL).
• The allowNull property is used to allow or prevent the value of a column to be NULL (i.e. NULL, NOT NULL in SQL).
• The unique property is used to define the value of a column as unique (i.e. UNIQUE in SQL).
• The timestamps property is used to prevent the automatic creation of createdAt and updatedAt columns.
• The sync() method of the model is used to physically create its corresponding table in the database.