Migrations: adding unique column

One more point about using Seqialize in migrations. Using the following example we add unique column to table.

We have table ‘user’ with columns: firstName, lastName and email.

Need to add new column ‘address’ that will be unique.

Run command for creating migration-skeleton file:

$ npx sequelize-cli migration:generate --name migration-skeleton

Edit this file the following way:

'use strict';

module.exports = {

async up(queryInterface, Sequelize) {

const transaction = await queryInterface.sequelize.transaction();

try {

await queryInterface.addColumn(

'user',

'address',

{

type: Sequelize.STRING,

fields: 'address',

unique: true,

},

{ transaction }

);

await transaction.commit();

} catch (err) {

await transaction.rollback();

throw err;

}

},

async down(queryInterface, Sequelize) {

const transaction = await queryInterface.sequelize.transaction();

try {

await queryInterface.removeColumn('user', 'address', { transaction });

await transaction.commit();

} catch (err) {

await transaction.rollback();

throw err;

}

},

};

The parameter ‘unique: true ‘gives us possibility to get address field with unique value for each item.

Run migration:

$ npx sequelize-cli db:migrate

Refresh DB and see on ‘user’ table: added column ‘address’

Add new user: update related seed file and run it directly:

$ npx sequelize-cli db:seed --seed 20200406114244-test-user.js

Refresh DB, see added new item in "user"

Try in the same way populate info about another user and with the same value for ‘address’

got ERROR: Validation error

If we use unique value of address – population will be successful

New record appears in our table "user"

Also, we can check it directly in DB

In case if we try to use existing value of address

INSERT INTO public."user"("firstName", "lastName", email, "createdAt", "updatedAt", address)

VALUES ('4user', '4test', '4user@4test.com', now(), now(), 'address test 3');

We get validation error:

ERROR: duplicate key value violates unique constraint "user_address_key" DETAIL: Key (address)=(address test 3) already exists. SQL state: 23505

If we add info of another user with unique address – it appears as new item of table: