Database Constraints and Model Validations in Sequelize

Understanding the Problem

We need to implement data validation for a Colors table with specific requirements for the name field:

• No NULL values allowed
• No duplicate values allowed
• Name length must be between 2-20 characters
• Name must not end in 'y'

We'll be working with both database-level constraints and model-level validations to achieve this.

Devising a Plan

1. Set up database-level constraints in migration file
   • Add allowNull: false
   • Add unique: true
2. Implement model-level validations
   • Add string validation
   • Add length validation
   • Add custom validation for 'y' ending
3. Test implementation with various inputs

Carrying Out the Plan

Step 1: Database Migration File

// Migration file (20211011170805-create-color.js)
module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.createTable('Colors', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      name: {
        type: Sequelize.STRING(20),
        allowNull: false,  // Database constraint: No NULL values
        unique: true      // Database constraint: No duplicates
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE,
        defaultValue: Sequelize.literal("CURRENT_TIMESTAMP")
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE,
        defaultValue: Sequelize.literal("CURRENT_TIMESTAMP")
      }
    });
  },
  down: async (queryInterface, Sequelize) => {
    await queryInterface.dropTable('Colors');
  }
};

Step 2: Model Validations

// Model file (color.js)
'use strict';
const { Model } = require('sequelize');

module.exports = (sequelize, DataTypes) => {
  class Color extends Model {
    static associate(models) {
      // Define associations here if needed
    }
  }
  
  Color.init({
    name: {
      type: DataTypes.STRING,
      allowNull: false,
      validate: {
        // Basic type validation
        isString(value) {
          if (typeof value !== 'string') {
            throw new Error('name must be a string');
          }
        },
        // Length validation
        len: {
          args: [2, 20],
          msg: 'name must be between 2 and 20 characters'
        },
        // Custom validation for 'y' ending
        noEndingInY(value) {
          if (value.slice(-1) === 'y') {
            throw new Error('name must not end in y');
          }
        }
      }
    }
  }, {
    sequelize,
    modelName: 'Color',
  });
  
  return Color;
};

Looking Back and Understanding

Database Constraints vs Model Validations

Think of database constraints like the security guards at a building's entrance, while model validations are like the receptionist who checks your appointment before you even leave home.

Database Constraints:

• Work at the database level
• Cannot be bypassed when data is inserted directly into the database
• Best for fundamental data integrity rules (NULL, unique, foreign keys)
• Like building foundation - essential and unchangeable

Model Validations:

• Work at the application level
• Can implement more complex business rules
• Can provide better error messages
• Like application business rules - more flexible and specific

Testing Examples

// Example test cases and their outcomes:
await Color.create({})               // Fails: name cannot be null
await Color.create({name: []})       // Fails: name must be a string
await Color.create({name: 'a'})      // Fails: name too short
await Color.create({name: 'orangey'})// Fails: ends in 'y'
await Color.create({name: 'orange'}) // Succeeds!

Real-World Applications

This pattern is commonly used in many real-world scenarios:

• User registration systems (unique usernames/emails)
• Product inventory systems (unique SKUs)
• Content management systems (unique slugs/URLs)
• School management systems (unique student IDs)

Common Pitfalls and Tips

• Always combine database constraints with model validations for complete data integrity
• Remember that direct database access bypasses model validations
• Use clear, specific error messages in your validations
• Consider case sensitivity in unique constraints

Further Learning

To deepen your understanding, try:

• Adding custom error messages for all validations
• Implementing case-insensitive unique constraints
• Creating composite unique constraints
• Adding more complex custom validations