Data Migration with Module Upgrades in Odoo 15

Data migration will be a helpful feature in Odoo, mainly when there are continuous changes in development requirements and while we refactor the code.

Sometimes we change the name of the custom fields that we have added, change the store property of the fields, or we may alter the type of the fields. This may cause a loss of the data stored in the database and inconsistencies in the values. In such cases, we can add some migration code to make the changes in the database.

Adding migration

The files for the migration are added to the migrations directory in the module. We can add the Python files inside the directory that specifies the version of the module. The migration code will run on the module upgrade.

There are three types of migrations :

* Pre-migration

* Post-migration

* End-migration

Pre-migration

Pre-migration scripts are added when we have to make changes related to the schema of tables in the database. That is, change in the name or type of the fields or the name of the related table. The code in the pre-migration file will run at the beginning of the module upgrade (before module initialisation). The file name is prefixed by ‘pre-’.

Post-migration

Post-migration scripts can be added when we have to make changes to the values in the table. The code in the post-migration file will be after the initialization. The file name is prefixed by ‘post-’.

End-migration

End-migration scripts will be executed after all module upgrades.

The code in the post-migration file can also be run in the pre-migration file. But, it is preferred to use them specifically to make the changes in the database before and after the module upgrade. The file name is prefixed by end-’.

Example

Consider the code given below in a new custom module with version 15.0.1.0.0. Here the ‘sale.order’ model is inherited, and two new fields are added to it.

models/sale_order.py

# -*- coding: utf-8 -*-

from odoo import fields, models

class SaleOrder(models.Model):
    _inherit = 'sale.order'
 active_outlet = fields.Char(string='Active Outlet')
 sales_outlet_id = fields.Char(string='Sales Outlet')

<?xml version="1.0" encoding="utf-8"?>
<odoo>
    <record id="sale_order_view_form" model="ir.ui.view">
        <field name="name">sale.order.view.form.inherit.pre_post_migration</field>
        <field name="model">sale.order</field>
        <field name="inherit_id" ref="sale.view_order_form"/>
        <field name="arch" type="xml">
            <xpath expr="//field[@name='payment_term_id']" position="after">
                <field name="active_outlet" />
                <field name="sales_outlet_id" attrs="{'invisible': [('active_outlet', '!=', 'Y')]}" />
            </xpath>
        </field>
    </record>
</odoo>

To check the changes in the database, we can use pgAdmin:

Suppose we have to improve and refactor the code. That is, we are changing the type of the field active_outlet from Char to Boolean and changing the name of the field sales_outlet_id as sales_outlet: 

 active_outlet = fields.Boolean(string='Active Outlet')
    sales_outlet = fields.Char(string='Sales Outlet')

<field name="active_outlet" />
<field name="sales_outlet" attrs="{'invisible': [('active_outlet', '=', False)]}" />

We will lose the data stored for those fields if we upgrade the module after adding these changes.

Here, new columns active_outlet and sales_outlet are created. The column for the active_outlet is renamed as active_outlet_moved0. We have the data in the table. But that will not be used anymore. We lost the column sales_outlet_id and the data on renaming the field name of sales_outlet_id to sales_outlet.

So, what will be the solution? We have to update and map the data in the database before we upgrade the module. For that, we can add a migration file(s) and change the version of the module to 15.0.1.0.1)

migrations/15.0.1.0.1/pre-change_field_name.py

# -*- coding: utf-8 -*-
import logging
_logger = logging.getLogger(__name__)
def migrate(cr, version):
    _logger.info(f'Starting migration from version {version}.')
    cr.execute(
        "ALTER TABLE sale_order RENAME COLUMN sales_outlet_id TO sales_outlet;")
    _logger.info('Migration completed.')

migrations/15.0.1.0.1/post-change_field_type.py

# -*- coding: utf-8 -*-
import logging
_logger = logging.getLogger(__name__)
def migrate(cr, version):
    _logger.info(f'Starting post-migration from version {version}.')
    cr.execute(
        """ALTER TABLE sale_order ALTER COLUMN active_outlet TYPE Boolean
        USING active_outlet_moved0::bool;
        ALTER TABLE sale_order DROP COLUMN IF EXISTS active_outlet_moved0;""")
    _logger.info('Migration completed.')

In the migration script, we can access all tables using a SQL query. But we have limits when using ORM. We can access only the models defined within the module and the models defined in any of its dependent modules.

The main points that we should check and ensure are that:

1. We have used the correct prefix for the file name: ‘pre-’, ‘post-’, or ‘end-’, unless the migration will not work.

2. The name of the directory created for migration should be the latest version of the module. That is, a.b.c.x.z If we upgrade the module from a.b.c.x.y to a.b.c.x.z.

With this feature, we can migrate the data on changes to the fields in a collaborative project to refactor or implement changes in requirements.