@Table

This decorator will allow you to configure our representation of the data, and here are some parameters:

{

    timestamps:  true,
    paranoid:  true,
    underscored:  false,
    freezeTableName:  true,
    tableName:  'my_very_custom_table_name'
}

The timestamp parameter will tell you that you want to have an updatedAt and deletedAt columns. The paranoid parameter allows you to soft delete data instead of removing it to lose your data. If you pass true, Sequelize will expected a deletedAt column in oder to set the date of the remove action.

The underscored parameter will automatically transform all of the camelcase columns into underscored columns.

The freezTableName will provide a way to avoid Sequelize to pluralize the name of the table.

The tableName allows you to set the name of the table.

In our case we only use timestamp: true, tableName: 'users' in order to get the updatedAt and createdAt column and name the table as users.

@column

This decorator will help define our column. You can also not pass any parameter, so in this case Sequelize will try to infer the column type. The types that can be inferred are string, boolean, number, Date and Blob.

Some parameter allows us to define some constraints on the column. Let’s imagine the email column, where we would like this email as a string and that cannot be null, so the email has to be unique. Sequelize can recognize an email, but we have to tell it how to validate the email passing the validate#isUnique method.

Take a look at the following example.

@Column({
    type: DataType.STRING,
    allowNull: false,
    validate: {
        isEmail: true,
        isUnique: async (value: string, next: any): Promise<any> => {
            const isExist = await User.findOne({ where: { email: value }});
            if (isExist) {
                const error = new Error('The email is already used.');
                next(error);
            }
            next();
        },
    },
})

In the previous example, we passed some options, but we could also use some decorator as @AllowNull(value: boolean), @Unique or even @Default(value: any).

To set an id column, the @PrimaryKey and @AutoIncrement decorators are an easy way to set up the constraint.

Create the User model

Now that we have seen some useful decorator, let’s create our first model, the User. In order to do that, we will create the class that has to extend from the base class Model<T>, and this class takes a template value for the class itself.

export class User extends Model<User> {...}

We now add the @Table() decorator in order to configure our model. This decorator takes options corresponding to the interface DefineOptions and as we described in the @Table section we will pass as options the timestamp as true and the name of the table.

@Table({ timestamp: true, tableName: 'users' } as IDefineOptions)
export class User extends Model<User> {...}

Now we have to define some columns for our model. To do this, sequelize-typescript provides the @Column() decorator. This decorator allows us to provide some options to configure our field. You can pass the data type DataType.Type directly.

@Column(DataTypes.STRING)
public email: string;

You can also use the options shown in the @Column section in order to validate and ensure the data of the email.

@Column({
    type: DataType.STRING,
    allowNull: false,
    validate: {
        isEmail: true,
        isUnique: async (value: string, next: any): Promise<any> => {
            const isExist = await User.findOne({
                where: { email: value }
            });
            if (isExist) {
                const error = new Error('The email is already used.');
                next(error);
            }
            next();
        },
    },
})
public email: string;

You now know how to set up a column, so let’s set up the rest of the model with the column that we need for a simple user.

@Table(tableOptions)
export class User extends Model<User> {
    @PrimaryKey
    @AutoIncrement @Column(DataType.BIGINT)
    public id: number;

    @Column({
        type: DataType.STRING,
        allowNull: false,
    })
    public firstName: string;

    @Column({
        type: DataType.STRING,
        allowNull: false,
    })
    public lastName: string;

    @Column({
        type: DataType.STRING,
        allowNull: false,
        validate: {
            isEmail: true,
            isUnique: async (value: string, next: any): Promise<any> => {
                const isExist = await User.findOne({
                    where: { email: value }
                });
                if (isExist) {
                    const error = new Error('The email is already used.');
                    next(error);
                }
                next();
            },
        },
    })
    public email: string;

    @Column({
        type: DataType.TEXT,
        allowNull: false,
    })
    public password: string;

    @CreatedAt
    public createdAt: Date;

    @UpdatedAt
    public updatedAt: Date;

    @DeletedAt
    public deletedAt: Date;
}

In all the added columns, you can see the password of type TEXT, but of course, you cannot store a password as a plain text, so we have to hash it in order to protect it. To do that, use the lifeCycle hooks provided by Sequelize.

LifeCycle hooks

Sequelize come with many lifeCycle hooks that allow you to manipulate and check the data along the process of creating, updating, or deleting a data.

Here are some interesting hooks from Sequelize.

  beforeBulkCreate(instances, options)
  beforeBulkDestroy(options)
  beforeBulkUpdate(options)

  beforeValidate(instance, options)
  afterValidate(instance, options)

  beforeCreate(instance, options)
  beforeDestroy(instance, options)
  beforeUpdate(instance, options)
  beforeSave(instance, options)
  beforeUpsert(values, options)

  afterCreate(instance, options)
  afterDestroy(instance, options)
  afterUpdate(instance, options)
  afterSave(instance, options)
  afterUpsert(created, options)

  afterBulkCreate(instances, options)
  afterBulkDestroy(options)
  afterBulkUpdate(options)

In this case, we need to use the @BeforeCreate decorator in order to hash the password and replace the original value before storing it in the database.

@Table(tableOptions)
export class User extends Model<User> {
    ...
    @BeforeCreate
    public static async hashPassword(user: User, options: any) {
        if (!options.transaction) throw new Error('Missing transaction.');

        user.password = crypto.createHmac('sha256', user.password).digest('hex');
    }
}

The BeforeCreate previously written allows you to override the password property value of the user in order to override it before the insertion of the object into the database, and to ensure a minimum of security.

Configuring the migration script

In order to configure the umzung instance, you will be able to set some options:

• storage, which correspond to the sequelize string key for us
• storageOptions, which will take Sequelize, and it is in this option that you can change the default name of the table of the column used to store the name of the migrations applied throughout the modelName, tableName and columnName properties.

Some other configurations are able, in order to set the up method name and the down method name, to pass a logging function. The migrations property will allow you to provide some params to pass to the method up/down and the path of the migrations to apply with the appropriate pattern.

const umzug = new Umzug({
    storage: 'sequelize',
    storageOptions: { sequelize },

    migrations: {
        params: [
            sequelize,
            sequelize.constructor, // DataTypes
        ],
        path: './migrations',
        pattern: /\.ts$/
    },

    logging: function () {
        console.log.apply(null, arguments);
    }
});

Create a migration

To execute the migration script, provide the migration that you want to apply. Imagine that you want to create the users table using migration. You must set an up and a down method.

export async function up(sequelize) {
    // language=PostgreSQL
    sequelize.query(`
        CREATE TABLE "users" (
            "id" SERIAL UNIQUE PRIMARY KEY NOT NULL,
            "firstName" VARCHAR(30) NOT NULL,
            "lastName" VARCHAR(30) NOT NULL,
            "email" VARCHAR(100) UNIQUE NOT NULL,
            "password" TEXT NOT NULL,
            "birthday" TIMESTAMP,
            "createdAt" TIMESTAMP NOT NULL,
            "updatedAt" TIMESTAMP NOT NULL,
            "deletedAt" TIMESTAMP
        );
    `);

    console.log('*Table users created!*');
}

export async function down(sequelize) {
    // language=PostgreSQL
    sequelize.query(`DROP TABLE users`);
}

In each method, the parameter will be sequelize, which is the instance used in the configuration file. Throughout this instance you can use the que query method in order to write our SQL query. In the previous example, the function up will execute the query to create the users table. The down method has the purpose to drop this table in case of a rollback.