I'm using Sequelize as ORM with a PostgreSQL engine. When using raw queries I can create a table and have columns with 'CHECK' constraints such as
CREATE TABLE products (
product_no integer,
name text,
price numeric CHECK (price > 0)
);
In the docs I cannot find a way to do this in Sequelize when defining models. Is there any way of doing this? I don't want to reinvent the wheel ;)
Thanks!!
module.exports = {
up: (queryInterface, Sequelize) => {
return queryInterface.createTable('Products', {
product_no: {
type: Sequelize.INTEGER
},
price: {
type: Sequelize.NUMERIC
},
name: {
type: Sequelize.TEXT
}
}).
then(() => queryInterface.addConstraint('Products', {
type: 'check',
fields: ['price'], // 2024-05-03: This might be needed in the later versions.
where: {
price: {
[Sequelize.Op.gt]: 0
}
}
}));
},
down: (queryInterface, Sequelize) => {
return queryInterface.dropTable('Products');
}
};
You can add the db check contraint in the type attribute of the column definition.
sequelize
sequelize.define(
'products',
{
price: {
type: 'numeric CHECK (price > 0)',
...
},
},
)
sequelize-typescript
@Column({
type: 'numeric CHECK (price > 0)', // instead of DataType.NUMBER
...
})
declare price: number
You can also add them using migrations. See: https://stackoverflow.com/a/51076516/6192751
You can also use js validations to accomplish this on the client side. See: https://github.com/sequelize/sequelize-typescript/issues/576#issuecomment-485383173
Take a look at the Validations section.
var Product = sequelize.define('product', {
price: {
validate: {
min: 0 // Only allow values >= 0
}
}
});
You can also set custom validation rules:
var Product = sequelize.define('product', {
price: {
validate: {
isPositive: function (value) {
return value > 0; // Don't allow 0.
}
}
}
});
