Creating Airplane Database with Sequelize

Understanding the Problem

We need to create a database to track airplane status and data with specific requirements:

• Track attributes like airline code, flight number, service status, and passenger counts
• Implement various database constraints and validations
• Ensure data integrity through both database and model-level checks

Devising a Plan

1. Create migration file for Airplanes table with proper constraints
2. Set up Airplane model with validations
3. Implement database relationships
4. Add data integrity checks

Solution Implementation

Step 1: Database Migration

File: migrations/XXXXXX-create-airplane.js

'use strict';
module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.createTable('Airplanes', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      airlineCode: {
        type: Sequelize.STRING,
        allowNull: false
      },
      flightNumber: {
        type: Sequelize.STRING,
        allowNull: false
      },
      inService: {
        type: Sequelize.BOOLEAN,
        defaultValue: true
      },
      maxNumPassengers: {
        type: Sequelize.INTEGER,
        allowNull: false
      },
      currentNumPassengers: {
        type: Sequelize.INTEGER
      },
      firstFlightDate: {
        type: Sequelize.DATE
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE
      }
    });
    
    // Add unique constraint for airlineCode + flightNumber
    await queryInterface.addConstraint('Airplanes', {
      fields: ['airlineCode', 'flightNumber'],
      type: 'unique',
      name: 'unique_airline_flight'
    });
  },

  down: async (queryInterface, Sequelize) => {
    await queryInterface.dropTable('Airplanes');
  }
};
    

Step 2: Model Definition

File: models/airplane.js

'use strict';
module.exports = (sequelize, DataTypes) => {
  const Airplane = sequelize.define('Airplane', {
    airlineCode: {
      type: DataTypes.STRING,
      allowNull: false,
      validate: {
        notNull: true,
        notEmpty: true
      }
    },
    flightNumber: {
      type: DataTypes.STRING,
      allowNull: false,
      validate: {
        notNull: true,
        notEmpty: true
      }
    },
    inService: {
      type: DataTypes.BOOLEAN,
      defaultValue: true
    },
    maxNumPassengers: {
      type: DataTypes.INTEGER,
      allowNull: false,
      validate: {
        min: 0
      }
    },
    currentNumPassengers: {
      type: DataTypes.INTEGER,
      validate: {
        passengerCountValid(value) {
          // Cannot exceed max passengers
          if (value > this.maxNumPassengers) {
            throw new Error('Current passengers cannot exceed maximum');
          }
          // Must be null if not in service
          if (!this.inService && value !== null) {
            throw new Error('Current passengers must be null when not in service');
          }
        }
      }
    },
    firstFlightDate: {
      type: DataTypes.DATE
    }
  }, {});

  return Airplane;
};
    

Testing the Solution

To verify our implementation:

1. Run migrations: npx sequelize-cli db:migrate
2. Test constraints by inserting valid and invalid data
3. Run the test suite: npm test

Real World Application

This database design mirrors real airline systems that need to:

• Track fleet status and capacity
• Manage passenger manifests
• Ensure safety compliance through data validation
• Maintain unique flight identifiers

Common Pitfalls

• Forgetting to add unique constraint for airline code + flight number
• Not handling the case where inService is false
• Missing validation for passenger counts
• Not setting proper default values

Extended Learning

Try these additional challenges:

• Add validation for valid airline codes (e.g., must be 2-3 characters)
• Implement soft delete functionality
• Add tracking for maintenance schedules
• Create associations with other tables (pilots, crew, etc.)