Database Migrations

Guide to managing database schema changes and data migrations in Odoo.

Overview

Odoo handles most schema changes automatically, but complex migrations require manual intervention.

---

Automatic vs Manual Migrations

Automatic (Odoo Handles)

Change	Odoo Action
Add new field	Creates column
Remove field	Keeps column (doesn't delete)
Change field label	Updates metadata
Add new model	Creates table
Change string attribute	Updates translation

Manual Migration Required

Change	Reason
Rename field	Odoo creates new, loses data
Change field type	May lose data
Rename model	Creates new table
Complex data transformation	Business logic needed
Merge/split records	Data integrity

---

Migration File Structure

Location

extra-addons/odoo/my_module/
├── __manifest__.py
├── __init__.py
├── models/
├── views/
└── migrations/
    ├── 15.0.1.1.0/
    │   ├── pre-migrate.py
    │   └── post-migrate.py
    └── 15.0.2.0.0/
        ├── pre-migrate.py
        └── post-migrate.py

Version Naming

Migration folder must match the version you're migrating to:

Current version: 15.0.1.0.0
New version:     15.0.1.1.0
Migration folder: migrations/15.0.1.1.0/

---

Pre-Migration vs Post-Migration

Pre-Migration (pre-migrate.py)

Runs before Odoo updates the module.

Use for: - Renaming fields/tables - Backing up data - Preparing for schema changes

# migrations/15.0.1.1.0/pre-migrate.py

def migrate(cr, version):
    """
    Pre-migration: Runs before module update.

    Args:
        cr: Database cursor
        version: Previous version string
    """
    if not version:
        return

    # Rename column before Odoo creates new one
    cr.execute("""
        ALTER TABLE my_model
        RENAME COLUMN old_field TO new_field
    """)

Post-Migration (post-migrate.py)

Runs after Odoo updates the module.

Use for: - Data transformations - Populating new fields - Cleaning up old data

# migrations/15.0.1.1.0/post-migrate.py

from odoo import api, SUPERUSER_ID

def migrate(cr, version):
    """
    Post-migration: Runs after module update.
    """
    if not version:
        return

    env = api.Environment(cr, SUPERUSER_ID, {})

    # Populate new field from old data
    records = env['my.model'].search([])
    for record in records:
        record.new_field = record.compute_new_value()

---

Common Migration Patterns

1. Rename Field

# pre-migrate.py
def migrate(cr, version):
    # Check if old column exists
    cr.execute("""
        SELECT column_name
        FROM information_schema.columns
        WHERE table_name = 'my_model' AND column_name = 'old_name'
    """)
    if cr.fetchone():
        cr.execute("""
            ALTER TABLE my_model
            RENAME COLUMN old_name TO new_name
        """)

2. Change Field Type

# pre-migrate.py
def migrate(cr, version):
    # Backup data
    cr.execute("""
        ALTER TABLE my_model
        ADD COLUMN old_field_backup VARCHAR
    """)
    cr.execute("""
        UPDATE my_model
        SET old_field_backup = old_field::VARCHAR
    """)

# post-migrate.py
def migrate(cr, version):
    env = api.Environment(cr, SUPERUSER_ID, {})

    # Convert and restore data
    cr.execute("SELECT id, old_field_backup FROM my_model")
    for record_id, old_value in cr.fetchall():
        new_value = convert_value(old_value)
        cr.execute("""
            UPDATE my_model
            SET new_field = %s
            WHERE id = %s
        """, [new_value, record_id])

    # Drop backup column
    cr.execute("""
        ALTER TABLE my_model
        DROP COLUMN old_field_backup
    """)

3. Rename Model

# pre-migrate.py
def migrate(cr, version):
    # Rename table
    cr.execute("""
        ALTER TABLE old_model
        RENAME TO new_model
    """)

    # Update ir.model.data
    cr.execute("""
        UPDATE ir_model_data
        SET model = 'new.model'
        WHERE model = 'old.model'
    """)

    # Update ir.model
    cr.execute("""
        UPDATE ir_model
        SET model = 'new.model', name = 'New Model'
        WHERE model = 'old.model'
    """)

4. Merge Records

# post-migrate.py
def migrate(cr, version):
    env = api.Environment(cr, SUPERUSER_ID, {})

    # Find duplicates
    cr.execute("""
        SELECT email, array_agg(id) as ids
        FROM res_partner
        WHERE email IS NOT NULL
        GROUP BY email
        HAVING COUNT(*) > 1
    """)

    for email, ids in cr.fetchall():
        # Keep first, merge others
        main_id = ids[0]
        duplicate_ids = ids[1:]

        # Update references
        for dup_id in duplicate_ids:
            cr.execute("""
                UPDATE sale_order
                SET partner_id = %s
                WHERE partner_id = %s
            """, [main_id, dup_id])

        # Delete duplicates
        cr.execute("""
            DELETE FROM res_partner
            WHERE id IN %s
        """, [tuple(duplicate_ids)])

5. Populate Computed Field

# post-migrate.py
def migrate(cr, version):
    env = api.Environment(cr, SUPERUSER_ID, {})

    # Trigger recomputation of stored computed field
    records = env['sale.order'].search([])
    records._compute_total_amount()

6. Add Default Value to Existing Records

# post-migrate.py
def migrate(cr, version):
    # Direct SQL for performance
    cr.execute("""
        UPDATE my_model
        SET new_field = 'default_value'
        WHERE new_field IS NULL
    """)

---

Migration Best Practices

Do

Practice	Reason
Test on copy of production	Catch issues before live migration
Backup before migrating	Recovery option
Use transactions	Rollback on failure
Log migration steps	Debugging
Handle NULL values	Avoid errors
Check column exists	Idempotent migrations

Don't

Anti-pattern	Problem
Use ORM in pre-migrate	Models not loaded yet
Delete data without backup	No recovery
Assume data is clean	NULLs, duplicates exist
Skip version checks	Runs on every install
Modify other modules' tables	Dependency issues

---

Testing Migrations

Local Testing

# 1. Create backup
docker compose exec db pg_dump -U odoo odoo_test > backup_before.sql

# 2. Update module version in __manifest__.py

# 3. Run migration
docker compose exec odoo odoo -u my_module --stop-after-init -d odoo_test

# 4. Check logs for errors
docker compose logs odoo | grep -i migration

# 5. Verify data
docker compose exec db psql -U odoo -d odoo_test
SELECT * FROM my_model LIMIT 10;

# 6. If failed, restore
docker compose exec db psql -U odoo -d odoo_test < backup_before.sql

Migration Dry Run

# Add to migration file for testing
def migrate(cr, version):
    # Dry run mode
    DRY_RUN = True

    cr.execute("SELECT COUNT(*) FROM my_model WHERE condition")
    count = cr.fetchone()[0]
    print(f"Would update {count} records")

    if DRY_RUN:
        raise Exception("Dry run - rolling back")

    # Actual migration code...

---

Debugging Migrations

Enable Migration Logging

import logging
_logger = logging.getLogger(__name__)

def migrate(cr, version):
    _logger.info(f"Starting migration from {version}")

    cr.execute("SELECT COUNT(*) FROM my_model")
    count = cr.fetchone()[0]
    _logger.info(f"Found {count} records to migrate")

    # Migration code...

    _logger.info("Migration completed successfully")

Check Migration Status

-- Check module version
SELECT name, latest_version, state
FROM ir_module_module
WHERE name = 'my_module';

-- Check if migration ran
SELECT * FROM ir_module_module
WHERE name = 'my_module';

---

Rollback Strategies

Method 1: Database Restore

# Restore from backup
gunzip -c backup.sql.gz | docker compose exec -T db psql -U odoo -d odoo_test

Method 2: Reverse Migration

# Create reverse migration in next version
# migrations/15.0.1.1.1/post-migrate.py

def migrate(cr, version):
    # Reverse the changes
    cr.execute("""
        ALTER TABLE my_model
        RENAME COLUMN new_name TO old_name
    """)

Method 3: Manual SQL

-- Connect to database
docker compose exec db psql -U odoo -d odoo_test

-- Manually reverse changes
ALTER TABLE my_model RENAME COLUMN new_name TO old_name;

-- Reset module version
UPDATE ir_module_module
SET latest_version = '15.0.1.0.0'
WHERE name = 'my_module';

---

Production Migration Checklist

Before Migration

• Full database backup completed
• Filestore backup completed
• Migration tested on staging
• Downtime window scheduled
• Rollback plan documented
• Team notified

During Migration

• Put system in maintenance mode
• Run migration
• Check logs for errors
• Verify critical data
• Run health checks

After Migration

• Remove maintenance mode
• Monitor for errors
• Verify user access
• Test critical workflows
• Update documentation

---

Quick Reference

Migration File Template

# migrations/15.0.X.Y.Z/post-migrate.py

import logging
from odoo import api, SUPERUSER_ID

_logger = logging.getLogger(__name__)

def migrate(cr, version):
    """
    Migration description.
    """
    if not version:
        # Fresh install, no migration needed
        return

    _logger.info(f"Migrating my_module from {version}")

    env = api.Environment(cr, SUPERUSER_ID, {})

    # Your migration code here

    _logger.info("Migration completed")

Common SQL Patterns

-- Check column exists
SELECT column_name FROM information_schema.columns
WHERE table_name = 'table' AND column_name = 'column';

-- Check table exists
SELECT table_name FROM information_schema.tables
WHERE table_name = 'table';

-- Rename column
ALTER TABLE table RENAME COLUMN old TO new;

-- Add column with default
ALTER TABLE table ADD COLUMN col VARCHAR DEFAULT 'value';

-- Update with subquery
UPDATE table1 SET col = (SELECT val FROM table2 WHERE table2.id = table1.ref_id);