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

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');
  }
};`

CodePudding user response：

I think what you need here constraints not validation as validation is for used for app side validation and error will be thrown from app side, but constraints are something which throw error from db side. But i think sequelize does not provide complex constraints so that after migration it will apply to column. you can check doc here for more reference -> https://sequelize.org/docs/v6/core-concepts/validations-and-constraints/

CodePudding user response：

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');
},
};