Building a Test-Driven Airplane Database System

Understanding Our Requirements Through Tests

Think of our test specifications as a flight checklist - each item must be verified before we can take off. Let's examine what our tests are asking for and build our system accordingly.

Creating Our Migration

First, let's create a migration that satisfies all our test requirements:

// migrations/YYYYMMDDHHMMSS-create-airplanes-table.js
'use strict';

module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.createTable('Airplanes', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      // Test spec: 00-airlineCode-spec.js requires non-null
      airlineCode: {
        type: Sequelize.STRING,
        allowNull: false
      },
      // Test spec: 01-flightNumber-spec.js requires non-null
      flightNumber: {
        type: Sequelize.STRING,
        allowNull: false
      },
      // Test spec: 02-inService-spec.js requires default true
      inService: {
        type: Sequelize.BOOLEAN,
        allowNull: false,
        defaultValue: true
      },
      // Test spec: 03-maxNumPassengers-spec.js requires non-null
      maxNumPassengers: {
        type: Sequelize.INTEGER,
        allowNull: false
      },
      // Test spec: 04-currentNumPassengers-spec.js allows null
      currentNumPassengers: {
        type: Sequelize.INTEGER,
        allowNull: true
      },
      // Test spec: 05-firstFlightDate-spec.js allows null
      firstFlightDate: {
        type: Sequelize.DATE,
        allowNull: true
      },
      // Test specs: 09-createdAt-spec.js and 10-updatedAt-spec.js
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE,
        defaultValue: Sequelize.literal('CURRENT_TIMESTAMP')
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE,
        defaultValue: Sequelize.literal('CURRENT_TIMESTAMP')
      }
    });

    // Test spec: 06-airlineCode-flightNumber-spec.js requires unique combination
    await queryInterface.addConstraint('Airplanes', {
      fields: ['airlineCode', 'flightNumber'],
      type: 'unique',
      name: 'unique_airline_flight'
    });
  },

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

Implementing Our Model

Our model needs to handle all validations required by the test specs:

// models/airplane.js
'use strict';
module.exports = (sequelize, DataTypes) => {
  const Airplane = sequelize.define('Airplane', {
    airlineCode: {
      type: DataTypes.STRING,
      allowNull: false,
      validate: {
        notNull: {
          msg: 'Airline code cannot be null'
        },
        notEmpty: {
          msg: 'Airline code cannot be empty'
        }
      }
    },
    flightNumber: {
      type: DataTypes.STRING,
      allowNull: false,
      validate: {
        notNull: {
          msg: 'Flight number cannot be null'
        },
        notEmpty: {
          msg: 'Flight number cannot be empty'
        }
      }
    },
    inService: {
      type: DataTypes.BOOLEAN,
      allowNull: false,
      defaultValue: true,
      validate: {
        notNull: {
          msg: 'Service status cannot be null'
        }
      }
    },
    maxNumPassengers: {
      type: DataTypes.INTEGER,
      allowNull: false,
      validate: {
        notNull: {
          msg: 'Maximum number of passengers cannot be null'
        },
        min: {
          args: [0],
          msg: 'Maximum number of passengers cannot be negative'
        }
      }
    },
    currentNumPassengers: {
      type: DataTypes.INTEGER,
      allowNull: true,
      validate: {
        // Test spec: 07-currentNumPassengers-maxNumPassengers-spec.js
        passengerCountValid(value) {
          if (value > this.maxNumPassengers) {
            throw new Error('Current passengers cannot exceed maximum capacity');
          }
        },
        // Test spec: 08-currentNumPassengers-inService-spec.js
        serviceStatusValid(value) {
          if (!this.inService && value !== null) {
            throw new Error('Out-of-service planes must have null passenger count');
          }
        }
      }
    },
    firstFlightDate: {
      type: DataTypes.DATE,
      allowNull: true
    }
  }, {
    tableName: 'Airplanes'
  });

  return Airplane;
};

Understanding Each Test Requirement

Airline Code and Flight Number

These form a unique identifier for each plane, like a social security number for aircraft:

Both must be non-null and non-empty
Their combination must be unique (no two planes can share both values)
Each value can be repeated individually (like multiple AA flights or multiple flight 100s)

Service Status and Passenger Counts

These fields work together to maintain logical airplane states:

inService defaults to true
currentNumPassengers must be null when inService is false
currentNumPassengers can never exceed maxNumPassengers
maxNumPassengers must be non-null and non-negative

Timestamps

Both createdAt and updatedAt fields:

Must be non-null
Default to CURRENT_TIMESTAMP
Are managed automatically by Sequelize

Testing Our Implementation

Let's verify our implementation against each test specification:

// Run specific tests
npm test test/00-airlineCode-spec.js
npm test test/01-flightNumber-spec.js
// etc...

// Or run all tests at once
npm test

Common Edge Cases to Handle

Our implementation needs to handle these situations gracefully:

Passenger Count Scenarios

Consider these cases:

// All these scenarios are handled by our validations:
const airplane = Airplane.build({
  airlineCode: 'AA',
  flightNumber: '123',
  maxNumPassengers: 100
});

// Case 1: In service, valid passenger count
airplane.currentNumPassengers = 50;  // Valid

// Case 2: In service, exceeding capacity
airplane.currentNumPassengers = 150;  // Throws error

// Case 3: Out of service with passengers
airplane.inService = false;
airplane.currentNumPassengers = 0;    // Throws error

// Case 4: Out of service, null passengers
airplane.currentNumPassengers = null; // Valid

Unique Constraint Scenarios

Our unique constraint handles these cases:

// These operations are handled by our database constraints:
await Airplane.create({
  airlineCode: 'AA',
  flightNumber: '100',
  maxNumPassengers: 100
});

// Same airline, different flight - Valid
await Airplane.create({
  airlineCode: 'AA',
  flightNumber: '101',
  maxNumPassengers: 100
});

// Different airline, same flight - Valid
await Airplane.create({
  airlineCode: 'UA',
  flightNumber: '100',
  maxNumPassengers: 100
});

// Same airline and flight - Throws error
await Airplane.create({
  airlineCode: 'AA',
  flightNumber: '100',
  maxNumPassengers: 100
});

Real World Applications

These validations mirror real airline operations:

Flight identifiers must be unique for safety and tracking
Planes under maintenance (not in service) cannot carry passengers
Passenger counts must respect physical aircraft limitations
All operations must be timestamped for audit purposes

Further Enhancements

Consider these additional features that build on our base implementation:

Add validation for proper airline code format (e.g., 2-3 letters)
Implement flight number format validation
Add maintenance history tracking
Create associations with crew and route tables