Database Migrations

The MarsAI platform uses Sequelize CLI to manage database schema changes through migrations. This ensures version control for the database structure and allows teams to synchronize schema changes.

Migration System Overview

Migrations are timestamped files that describe database changes in a sequential, reversible manner.

All migration files are located in ~/workspace/source/back/migrations/ and use the .cjs CommonJS format for compatibility with Sequelize CLI.

Configuration

Sequelize RC Configuration

The .sequelizerc file (~/workspace/source/back/.sequelizerc:1) defines paths for migrations and configuration:

const path = require('path');

module.exports = {
  'config': path.resolve('config', 'config.cjs'),
  'models-path': path.resolve('src', 'models'),
  'seeders-path': path.resolve('seeders'),
  'migrations-path': path.resolve('migrations')
};

Database Configuration

Database connection settings are defined in ~/workspace/source/back/config/config.cjs:1:

require("dotenv").config();

module.exports = {
  development: {
    username: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    database: process.env.DB_NAME,
    host: process.env.DB_HOST,
    port: Number(process.env.DB_PORT) || 3306,
    dialect: "mysql",
    logging: false
  }
};

All database credentials come from environment variables in the .env file for security.

Running Migrations

Execute All Pending Migrations

Run all migrations that haven’t been executed yet:

npm run migrate

Or directly with Sequelize CLI:

npx sequelize-cli db:migrate

This command:

Connects to the database using config from config/config.cjs
Checks the SequelizeMeta table for already-run migrations
Executes all pending migrations in chronological order
Records each migration in SequelizeMeta

Check Migration Status

View which migrations have been run:

npx sequelize-cli db:migrate:status

Output shows:

✓ Executed migrations
✗ Pending migrations

Undo Last Migration

Rollback the most recent migration:

npx sequelize-cli db:migrate:undo

Undo All Migrations

Rollback all migrations (dangerous - use with caution):

npx sequelize-cli db:migrate:undo:all

Undo to Specific Migration

Rollback to a specific migration file:

npx sequelize-cli db:migrate:undo:all --to 20260128100004-create-movies.cjs

Always test rollbacks in development before running in production.

Migration File Structure

Each migration file has two functions:

up(): Applies the migration (creates tables, adds columns, etc.)
down(): Reverts the migration (drops tables, removes columns, etc.)

Basic Migration Template

'use strict';

module.exports = {
  async up(queryInterface, Sequelize) {
    // Apply migration changes
    await queryInterface.createTable('table_name', {
      // column definitions
    });
  },

  async down(queryInterface, Sequelize) {
    // Revert migration changes
    await queryInterface.dropTable('table_name');
  }
};

Example Migrations

Creating the Users Table

File: ~/workspace/source/back/migrations/20260128100000-create-users.cjs:1

Full Users Migration Code

'use strict';
module.exports = {
  async up(queryInterface, Sequelize) {
    await queryInterface.createTable('users', {
      id_user: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      first_name: {
        type: Sequelize.STRING(100),
        allowNull: false
      },
      last_name: {
        type: Sequelize.STRING(100),
        allowNull: false
      },
      email: {
        type: Sequelize.STRING(255),
        allowNull: false,
        unique: true
      },
      password: {
        type: Sequelize.STRING(255),
        allowNull: false
      },
      phone: {
        type: Sequelize.STRING(20),
        allowNull: true
      },
      mobile: {
        type: Sequelize.STRING(20),
        allowNull: true
      },
      birth_date: {
        type: Sequelize.DATE,
        allowNull: true
      },
      street: {
        type: Sequelize.STRING(50),
        allowNull: true
      },
      postal_code: {
        type: Sequelize.STRING(10),
        allowNull: true
      },
      city: {
        type: Sequelize.STRING(50),
        allowNull: true
      },
      country: {
        type: Sequelize.STRING(50),
        allowNull: true
      },
      biography: {
        type: Sequelize.STRING(255),
        allowNull: true
      },
      job: {
        type: Sequelize.ENUM(
          'ACTOR',
          'DIRECTOR',
          'PRODUCER',
          'WRITER',
          'OTHER'
        ),
        allowNull: true
      },
      portfolio: {
        type: Sequelize.STRING(50),
        allowNull: true
      },
      youtube: {
        type: Sequelize.STRING(50),
        allowNull: true
      },
      instagram: {
        type: Sequelize.STRING(50),
        allowNull: true
      },
      linkedin: {
        type: Sequelize.STRING(50),
        allowNull: true
      },
      facebook: {
        type: Sequelize.STRING(50),
        allowNull: true
      },
      tiktok: {
        type: Sequelize.STRING(50),
        allowNull: true
      },
      known_by_mars_ai: {
        type: Sequelize.ENUM(
          'Par un ami',
          'Vu une publicité du festival',
          "Via le site internet ou application de l'IA"
        ),
        allowNull: true
      },
      role: {
        type: Sequelize.ENUM('ADMIN', 'JURY', 'PRODUCER'),
        allowNull: false,
        defaultValue: 'PRODUCER'
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE,
        defaultValue: Sequelize.fn('NOW')
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE,
        defaultValue: Sequelize.fn('NOW')
      }
    });
  },
  async down(queryInterface, Sequelize) {
    await queryInterface.dropTable('users');
  }
};

Key features:

Creates users table with all user fields
Sets id_user as auto-incrementing primary key
Enforces email uniqueness constraint
Uses ENUM types for job and role
Includes timestamps (createdAt, updatedAt)
Rollback drops the entire table

Creating the Movies Table

File: ~/workspace/source/back/migrations/20260128100004-create-movies.cjs:1

Movies Migration Code

'use strict';
module.exports = {
  async up(queryInterface, Sequelize) {
    await queryInterface.createTable('movies', {
      id_movie: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      title: {
        type: Sequelize.STRING(255),
        allowNull: false
      },
      description: {
        type: Sequelize.TEXT,
        allowNull: true
      },
      duration: {
        type: Sequelize.INTEGER,
        allowNull: false
      },
      main_language: {
        type: Sequelize.STRING(20),
        allowNull: true
      },
      release_year: {
        type: Sequelize.INTEGER,
        allowNull: true
      },
      nationality: {
        type: Sequelize.STRING(255),
        allowNull: true
      },
      display_picture: {
        type: Sequelize.STRING(255),
        allowNull: true
      },
      picture1: {
        type: Sequelize.STRING,
        allowNull: true
      },
      picture2: {
        type: Sequelize.STRING,
        allowNull: true
      },
      picture3: {
        type: Sequelize.STRING,
        allowNull: true
      },
      trailer: {
        type: Sequelize.STRING,
        allowNull: true
      },
      youtube_link: {
        type: Sequelize.STRING(255),
        allowNull: true
      },
      production: {
        type: Sequelize.STRING,
        allowNull: true
      },
      workshop: {
        type: Sequelize.STRING(255),
        allowNull: true
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE,
        defaultValue: Sequelize.fn('NOW')
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE,
        defaultValue: Sequelize.fn('NOW')
      }
    });
  },
  async down(queryInterface, Sequelize) {
    await queryInterface.dropTable('movies');
  }
};

The initial movies migration was later extended by additional migrations to add fields like selection_status, admin_comment, and jury_comment.

Creating Vote Histories Table (with Foreign Keys)

File: ~/workspace/source/back/migrations/20260216100000-create-vote-histories.cjs:1

'use strict';

module.exports = {
  async up(queryInterface, Sequelize) {
    // Check if table already exists
    const tables = await queryInterface.showAllTables();
    const hasTable = tables.includes('vote_histories');
    if (hasTable) return;

    await queryInterface.createTable('vote_histories', {
      id_vote_history: {
        type: Sequelize.INTEGER,
        primaryKey: true,
        autoIncrement: true
      },
      id_vote: {
        type: Sequelize.INTEGER,
        allowNull: false,
        references: {
          model: 'votes',
          key: 'id_vote'
        },
        onUpdate: 'CASCADE',
        onDelete: 'CASCADE'
      },
      id_user: {
        type: Sequelize.INTEGER,
        allowNull: false
      },
      id_movie: {
        type: Sequelize.INTEGER,
        allowNull: false
      },
      note: {
        type: Sequelize.FLOAT,
        allowNull: false
      },
      comments: {
        type: Sequelize.TEXT,
        allowNull: true
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE,
        defaultValue: Sequelize.literal('CURRENT_TIMESTAMP')
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE,
        defaultValue: Sequelize.literal('CURRENT_TIMESTAMP')
      }
    });
  },

  async down(queryInterface) {
    await queryInterface.dropTable('vote_histories');
  }
};

Key features:

Checks if table exists before creating (idempotent)
Defines foreign key constraint to votes table
Sets CASCADE behavior for updates and deletes
Uses Sequelize.literal() for MySQL-specific defaults

Altering Existing Tables (Adding Columns)

File: ~/workspace/source/back/migrations/20260216120000-add-jury-comment-to-movies.cjs

'use strict';

module.exports = {
  async up(queryInterface, Sequelize) {
    await queryInterface.addColumn('movies', 'jury_comment', {
      type: Sequelize.TEXT,
      allowNull: true,
      after: 'admin_comment'  // MySQL-specific: position column
    });
  },

  async down(queryInterface, Sequelize) {
    await queryInterface.removeColumn('movies', 'jury_comment');
  }
};

Column additions are non-destructive and can be safely rolled back by removing the column.

Modifying Column Types

File: ~/workspace/source/back/migrations/20260216120000-alter-movies-selection-status.cjs

'use strict';

module.exports = {
  async up(queryInterface, Sequelize) {
    await queryInterface.changeColumn('movies', 'selection_status', {
      type: Sequelize.ENUM(
        'submitted',
        'assigned',
        'to_discuss',
        'candidate',
        'awarded',
        'refused',
        'selected',
        'finalist'
      ),
      allowNull: false,
      defaultValue: 'submitted'
    });
  },

  async down(queryInterface, Sequelize) {
    // Revert to previous ENUM values
    await queryInterface.changeColumn('movies', 'selection_status', {
      type: Sequelize.ENUM('submitted', 'selected', 'finalist'),
      allowNull: false,
      defaultValue: 'submitted'
    });
  }
};

Modifying ENUM types in MySQL can be complex. Ensure your down migration accurately reflects the previous state.

Creating Join Tables (Many-to-Many)

File: ~/workspace/source/back/migrations/20260205131006-create-movies-juries.cjs

'use strict';

module.exports = {
  async up(queryInterface, Sequelize) {
    await queryInterface.createTable('movies_juries', {
      id_movie: {
        type: Sequelize.INTEGER,
        allowNull: false,
        references: {
          model: 'movies',
          key: 'id_movie'
        },
        onUpdate: 'CASCADE',
        onDelete: 'CASCADE'
      },
      id_user: {
        type: Sequelize.INTEGER,
        allowNull: false,
        references: {
          model: 'users',
          key: 'id_user'
        },
        onUpdate: 'CASCADE',
        onDelete: 'CASCADE'
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE,
        defaultValue: Sequelize.fn('NOW')
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE,
        defaultValue: Sequelize.fn('NOW')
      }
    });

    // Add composite primary key
    await queryInterface.addConstraint('movies_juries', {
      fields: ['id_movie', 'id_user'],
      type: 'primary key',
      name: 'movies_juries_pk'
    });
  },

  async down(queryInterface, Sequelize) {
    await queryInterface.dropTable('movies_juries');
  }
};

Key features:

Creates join table with foreign keys to both tables
Adds composite primary key on both columns
Enforces referential integrity with CASCADE

Creating New Migrations

Generate Migration File

Create a new empty migration:

npx sequelize-cli migration:generate --name add-field-to-table

This creates a timestamped file: YYYYMMDDHHMMSS-add-field-to-table.cjs

Migration Naming Conventions

Use descriptive names that indicate the change:

create-tablename - Creating a new table
add-field-to-table - Adding column(s)
alter-table-field - Modifying column(s)
remove-field-from-table - Removing column(s)
rename-old-to-new - Renaming columns/tables

Common Migration Operations

await queryInterface.addColumn('table_name', 'column_name', {
  type: Sequelize.STRING(255),
  allowNull: true,
  defaultValue: null
});

Migration Execution Order

Migrations in the MarsAI platform run in this order (chronological):

20260128100000 - Create users table
20260128100001 - Create categories table
20260128100002 - Create collaborators table
20260128100003 - Create events table
20260128100004 - Create movies table
20260128100005 - Create awards table
20260128100006 - Create votes table
20260128100008 - Create reservations table
20260128100009 - Create movies_categories join table
20260128100010 - Create collaborators_movies join table
20260128144807 - Alter movies (add fields)
20260205100000 - Add assigned_jury to movies
20260205131006 - Create movies_juries join table
20260210100000 - Alter users (add known_by_mars_ai)
20260212103000 - Drop id_film from votes
20260212150000 - Add vote modification tracking
20260214130000 - Add admin_comment to movies
20260216100000 - Create vote_histories table
20260216120000 - Alter movies selection_status
20260216123000 - Add jury_comment to movies
20260219132833 - Add fields to movies
20260220123459 - Rename youtube_status column
20260302111532 - Create sponsors table
20260302171000 - Create newsletter_subscribers table

The timestamp prefix ensures migrations run in the correct order regardless of file system ordering.

Best Practices

Do’s

✓ Always test migrations in development before production ✓ Write reversible migrations with accurate down() functions ✓ Use transactions for complex multi-step migrations:

async up(queryInterface, Sequelize) {
  const transaction = await queryInterface.sequelize.transaction();
  try {
    await queryInterface.addColumn('table1', 'col', {...}, { transaction });
    await queryInterface.addColumn('table2', 'col', {...}, { transaction });
    await transaction.commit();
  } catch (error) {
    await transaction.rollback();
    throw error;
  }
}

✓ Keep migrations small and focused on one logical change ✓ Add indexes for foreign keys and frequently queried columns ✓ Document complex migrations with comments

Don’ts

✗ Never modify existing migration files after they’ve been run in production ✗ Never delete migration files that have been run ✗ Avoid data migrations in schema migrations (create separate data migrations) ✗ Don’t assume migration order when referencing other tables ✗ Don’t use model imports in migrations (use queryInterface instead)

Troubleshooting

Migration Already Exists Error

If you see “SequelizeMeta already contains this migration”:

# Check migration status
npx sequelize-cli db:migrate:status

# If incorrectly marked as run, manually remove from SequelizeMeta
# (Use with caution!)

Foreign Key Constraint Failures

Ensure:

Referenced table exists (check migration order)
Referenced column exists
Data types match exactly
Use CASCADE options appropriately

ENUM Modification Issues

MySQL ENUM modifications can be tricky:

// Approach 1: Drop and recreate column (loses data)
await queryInterface.removeColumn('table', 'enum_column');
await queryInterface.addColumn('table', 'enum_column', {...});

// Approach 2: Use raw SQL for ALTER
await queryInterface.sequelize.query(
  "ALTER TABLE table MODIFY COLUMN enum_column ENUM('val1', 'val2')"
);

Production Workflow

Develop migration locally
Test up migration - ensure it applies correctly
Test down migration - ensure it rolls back correctly
Test with real data - use production-like dataset
Commit migration file to version control
Deploy code with migration
Run migration on production:
```
npm run migrate
```
Verify database state after migration
Monitor application for issues

Always have a backup before running migrations in production.

Architecture - System architecture overview
Database Schema - Complete database structure

Get Started

Core Features

User Guides

Technical

Database Migrations

Database Migrations

Migration System Overview

Configuration

Sequelize RC Configuration

Database Configuration

Running Migrations

Execute All Pending Migrations

Check Migration Status

Undo Last Migration

Undo All Migrations

Undo to Specific Migration

Migration File Structure

Basic Migration Template

Example Migrations

Creating the Users Table

Creating the Movies Table

Creating Vote Histories Table (with Foreign Keys)

Altering Existing Tables (Adding Columns)

Modifying Column Types

Creating Join Tables (Many-to-Many)

Creating New Migrations

Generate Migration File

Migration Naming Conventions

Common Migration Operations

Migration Execution Order

Best Practices

Do’s

Don’ts

Troubleshooting

Migration Already Exists Error

Foreign Key Constraint Failures

ENUM Modification Issues

Production Workflow

Get Started

Core Features

User Guides

Technical

​Database Migrations

​Migration System Overview

​Configuration

​Sequelize RC Configuration

​Database Configuration

​Running Migrations

​Execute All Pending Migrations

​Check Migration Status

​Undo Last Migration

​Undo All Migrations

​Undo to Specific Migration

​Migration File Structure

​Basic Migration Template

​Example Migrations

​Creating the Users Table

​Creating the Movies Table

​Creating Vote Histories Table (with Foreign Keys)

​Altering Existing Tables (Adding Columns)

​Modifying Column Types

​Creating Join Tables (Many-to-Many)

​Creating New Migrations

​Generate Migration File

​Migration Naming Conventions

​Common Migration Operations

​Migration Execution Order

​Best Practices

​Do’s

​Don’ts

​Troubleshooting

​Migration Already Exists Error

​Foreign Key Constraint Failures

​ENUM Modification Issues

​Production Workflow

​Related Documentation

Database Migrations

Migration System Overview

Configuration

Sequelize RC Configuration

Database Configuration

Running Migrations

Execute All Pending Migrations

Check Migration Status

Undo Last Migration

Undo All Migrations

Undo to Specific Migration

Migration File Structure

Basic Migration Template

Example Migrations

Creating the Users Table

Creating the Movies Table

Creating Vote Histories Table (with Foreign Keys)

Altering Existing Tables (Adding Columns)

Modifying Column Types

Creating Join Tables (Many-to-Many)

Creating New Migrations

Generate Migration File

Migration Naming Conventions

Common Migration Operations

Migration Execution Order

Best Practices

Do’s

Don’ts

Troubleshooting

Migration Already Exists Error

Foreign Key Constraint Failures

ENUM Modification Issues

Production Workflow

Related Documentation