market-data-service/migrations/20251016210526-add_unique_constraint_candles.js

'use strict';

/** @type {import('sequelize-cli').Migration} */
module.exports = {
  async up (queryInterface, Sequelize) {
    // Check if table exists before adding constraint
    const tableExists = await queryInterface.sequelize.query(
      "SELECT EXISTS (SELECT FROM information_schema.tables WHERE table_name = 'candles_1h');",
      { type: Sequelize.QueryTypes.SELECT }
    );
    
    if (tableExists && tableExists[0] && tableExists[0].exists) {
      // Check if constraint already exists
      const constraintExists = await queryInterface.sequelize.query(
        "SELECT EXISTS (SELECT FROM pg_constraint WHERE conname = 'unique_symbol_open_time');",
        { type: Sequelize.QueryTypes.SELECT }
      );
      
      if (!constraintExists || !constraintExists[0] || !constraintExists[0].exists) {
        await queryInterface.addConstraint('candles_1h', {
          fields: ['symbol_id', 'open_time'],
          type: 'unique',
          name: 'unique_symbol_open_time'
        });
      }
    } else {
      console.log('Table candles_1h does not exist, skipping constraint addition. 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 = 'candles_1h');",
      { type: Sequelize.QueryTypes.SELECT }
    );
    
    if (tableExists && tableExists[0] && tableExists[0].exists) {
      await queryInterface.removeConstraint('candles_1h', 'unique_symbol_open_time');
    }
  }
};