How can I add pattern constraint on column in already existing table using Sequelize/Postgres

Question

I need to add constraint(regex) on DB level so that no one able to enter/edit that particular column with invalid input on already existing table. I'm using Sequelize, Postgres.

I tried it using migration but still able to enter invalid value in DB. I was expecting that after running below migration I wouldn't able to enter/edit that column with wrong input but not working. Attaching my code below for you better understanding. Please help me to understand it clearly..

`module.exports = {
  async up (queryInterface, Sequelize) {
     await queryInterface.changeColumn('Table', 'Column',{
      type: Sequelize.STRING,
      validate: {
        is: "regex pattern"
      }
    });
  },

  async down (queryInterface, Sequelize) {
     await queryInterface.removeColumn('Table', 'Column');
  }
};`

Answer 1

We can use query directly to add constraints in our migration file. Please find below code for reference that I used in my codebase.

module.exports = {
  async up(queryInterface, Sequelize) {
    await queryInterface.sequelize.query(`
         ALTER TABLE "TableName"
         ADD CONSTRAINT "checkName" CHECK ("column" ~ '^[a-zA-Z0-9]{2}\d{10}$');
 `);
},

async down(queryInterface, Sequelize) {
  await queryInterface.removeConstraint('TableName', 'column');
},
};