market-data-service/migrations/20251027075914-add-index-to-instrument-type.js

'use strict';

/** @type {import('sequelize-cli').Migration} */
module.exports = {
  async up (queryInterface, Sequelize) {
    // Check if table exists
    const tableExists = await queryInterface.sequelize.query(
      "SELECT EXISTS (SELECT FROM information_schema.tables WHERE table_name = 'symbols');",
      { type: Sequelize.QueryTypes.SELECT }
    );
    
    if (tableExists && tableExists[0] && tableExists[0].exists) {
      // Drop the existing CHECK constraint if it exists and add a new one with 'index'
      await queryInterface.sequelize.query("ALTER TABLE symbols DROP CONSTRAINT IF EXISTS symbols_instrument_type_check;");
      await queryInterface.sequelize.query("ALTER TABLE symbols ADD CONSTRAINT symbols_instrument_type_check CHECK (instrument_type IN ('crypto', 'stock', 'forex', 'commodity', 'index'));");
    } else {
      console.log('Table symbols does not exist, skipping constraint update. Tables will be created by model sync.');
    }
  },

  async down (queryInterface, Sequelize) {
    const tableExists = await queryInterface.sequelize.query(
      "SELECT EXISTS (SELECT FROM information_schema.tables WHERE table_name = 'symbols');",
      { type: Sequelize.QueryTypes.SELECT }
    );
    
    if (tableExists && tableExists[0] && tableExists[0].exists) {
      // Revert the CHECK constraint without 'index'
      await queryInterface.sequelize.query("ALTER TABLE symbols DROP CONSTRAINT IF EXISTS symbols_instrument_type_check;");
      await queryInterface.sequelize.query("ALTER TABLE symbols ADD CONSTRAINT symbols_instrument_type_check CHECK (instrument_type IN ('crypto', 'stock', 'forex', 'commodity'));");
    }
  }
};