Database Migration with 2Pack

Overview

• What you’ll learn:
  • What 2Pack is and how PackOut and PackIn work for exporting and importing Application Dictionary changes
  • The XML format structure, what can be packaged, and how to handle data versus metadata
  • Migration file naming conventions, 2Pack in plugin development, versioning strategies, and common pitfalls
• Prerequisites: Lessons 1–19 (especially Lesson 2: iDempiere Architecture Overview and Lesson 18: OSGi Framework)
• Estimated reading time: 20 minutes

Introduction

One of the recurring challenges in ERP customization is moving changes between environments. When you create a new window, add columns to a table, define a process, or configure menu entries in your development environment, you need a reliable way to replicate those changes in testing, staging, and production environments. Running SQL scripts by hand is error-prone and fragile. Manually recreating Application Dictionary entries is tedious and unreliable.

iDempiere solves this problem with 2Pack (also written as “2pack” or “TwoPack”) — a built-in tool for packaging Application Dictionary changes into portable XML files that can be exported from one environment and imported into another. 2Pack is the standard mechanism for distributing AD customizations, and it is an essential tool in every iDempiere developer’s workflow.

What is 2Pack?

2Pack consists of two complementary processes:

• PackOut — Exports selected Application Dictionary objects (tables, columns, windows, tabs, fields, menus, processes, roles, and more) into a structured XML file.
• PackIn — Reads a 2Pack XML file and applies the changes to the target environment, creating or updating AD records as specified.

Think of 2Pack as a version-controlled migration system specifically designed for iDempiere’s Application Dictionary. It captures not just the raw data but the intent — “create this table with these columns,” “add this field to this tab,” “register this process in this menu.” When PackIn applies the changes, it understands the relationships between AD objects and handles them in the correct order.

PackOut vs Manual SQL

You might wonder why you cannot simply export the relevant rows from AD tables using SQL dumps. There are several reasons 2Pack is superior:

• Dependency ordering — 2Pack exports objects in the correct dependency order. A field export includes references to its column, tab, and window. PackIn creates objects in the right sequence.
• Environment independence — 2Pack uses UUIDs and logical references rather than database-specific primary key IDs, making packs portable between environments where auto-generated IDs differ.
• Idempotent application — PackIn can be applied multiple times safely. If an object already exists, it is updated rather than duplicated.
• Audit trail — PackIn logs what it created or modified, providing a record of changes.

Creating a 2Pack Export (PackOut)

To export AD changes using PackOut, follow these steps in the iDempiere web interface:

Step 1: Navigate to Pack Out

Open the menu and navigate to System Admin > General Rules > Pack Out – Create a package. Alternatively, search for “Pack Out” in the global search bar.

Step 2: Create a New Pack Out Record

Create a new record in the Pack Out window. Fill in:

• Name — A descriptive name for the pack (e.g., “Custom Invoice Fields v1.0”)
• Version — The version string (e.g., “1.0.0”)
• Description — What changes this pack contains
• File Name — The output file path where the XML will be written (e.g., /tmp/CustomInvoiceFields.zip)

Step 3: Add Export Details

Switch to the Pack Out Detail tab. Here you add individual items to export. For each item, specify:

• Type — The type of AD object (Table, Window, Process, Menu, Form, Role, SQL Statement, Code or Other File, Data, etc.)
• Entity selection — The specific object to export (e.g., which window, which table)

Common export types include:

Step 4: Run the Export

Click the Export Package button (or run the Pack Out process from the toolbar). 2Pack will generate a ZIP file containing the XML pack file and any additional files referenced in the export details.

The 2Pack XML Format

Understanding the XML structure helps with troubleshooting and manual editing when needed. A 2Pack XML file follows this general structure:

<?xml version="1.0" encoding="UTF-8"?>
<adempiereAD>
  <header>
    <name>Custom Invoice Fields</name>
    <version>1.0.0</version>
    <description>Adds custom fields to the Invoice window</description>
    <createddate>2025-01-15 10:30:00</createddate>
    <updateddate>2025-01-15 10:30:00</updateddate>
  </header>

  <!-- Table definition with columns -->
  <table AD_Table_UU="a1b2c3d4-e5f6-7890-abcd-ef1234567890"
         TableName="C_Invoice"
         action="update">
    <column AD_Column_UU="..."
            ColumnName="CustomField1"
            AD_Reference_ID="10"
            FieldLength="100"
            DefaultValue=""
            IsMandatory="N"
            IsUpdateable="Y"
            action="insert"/>
  </table>

  <!-- Window with tabs and fields -->
  <window AD_Window_UU="..."
          Name="Invoice (Vendor)"
          action="update">
    <tab AD_Tab_UU="..."
         Name="Invoice"
         action="update">
      <field AD_Field_UU="..."
             Name="Custom Field 1"
             AD_Column_ID="..."
             SeqNo="999"
             IsDisplayed="Y"
             action="insert"/>
    </tab>
  </window>

  <!-- Process definition -->
  <process AD_Process_UU="..."
           Value="CustomProcess"
           Name="My Custom Process"
           Classname="com.example.myplugin.process.MyProcess"
           action="insert"/>

  <!-- Menu entry -->
  <menu AD_Menu_UU="..."
        Name="My Custom Process"
        action="insert"/>
</adempiereAD>

Key XML Concepts

• action attribute — Each element has an action attribute that can be insert (create new), update (modify existing), or merge (create if not exists, update if exists). The merge action is the safest for idempotent application.
• UUID references — Objects are identified by their UUID (AD_Table_UU, AD_Column_UU, etc.) rather than integer IDs. This ensures portability between environments.
• Hierarchical structure — Child objects (columns within tables, fields within tabs, tabs within windows) are nested inside their parent elements, reflecting the Application Dictionary hierarchy.

What Can Be Packaged

2Pack can package the following categories of AD objects:

Metadata (Application Dictionary)

• Tables and Columns — Table definitions, column definitions, and the DDL to create or alter the physical database tables
• Windows, Tabs, and Fields — The complete UI definition hierarchy
• Processes and Parameters — Process definitions and their input parameters
• Forms — Custom form definitions
• Menus — Menu tree entries and their hierarchy
• References and Validation Rules — Dropdown lists, table references, and validation logic
• Messages — System messages and translations
• Roles and Access — Role definitions and access permissions
• Report Views and Print Formats — Report configurations
• Workflows — Workflow definitions and nodes

Data

• Reference data — List values, document types, tax categories, and other configuration data
• Master data — Business partner records, product records, chart of accounts entries (use with caution)
• SQL statements — Raw SQL for data manipulation that does not fit AD object categories

Files

• Report templates — JasperReports .jrxml files
• SQL migration scripts — Additional DDL or DML scripts
• Other resources — Any files that need to be distributed with the pack

Applying a Pack (PackIn)

To import a 2Pack into a target environment:

Using the Web Interface

1. Navigate to System Admin > General Rules > Pack In – Import a package
2. Upload or specify the path to the 2Pack ZIP file
3. Click the Import Package button
4. PackIn processes the XML file, creates or updates AD objects, executes any SQL statements, and copies any included files
5. Review the log output for any errors or warnings

Using the Command Line

For automated deployments, you can apply 2Pack files from the command line using iDempiere’s sign-database-build script or by directly invoking the PackIn process:

# Apply a 2Pack during server startup
# Place the ZIP file in the migration directory
cp MyPackage.zip $IDEMPIERE_HOME/migration/local/

iDempiere automatically scans the migration/ directory on startup and applies any pending 2Pack files.

Handling Data vs Metadata

A critical distinction in 2Pack is the difference between metadata (Application Dictionary definitions) and data (business records). The rules differ:

• Metadata — Generally safe to export and import. AD definitions are intended to be consistent across environments. Use the merge action to handle both new installations and updates.
• Data — Requires careful consideration. Exporting data records can overwrite environment-specific data in the target. Only export data that is truly configuration data (reference lists, document types) rather than transactional data (orders, invoices).

A common mistake is exporting data records that include auto-generated IDs that differ between environments. Always use UUIDs or natural keys (like the Value column) when referencing data across environments.

Migration File Naming Conventions

iDempiere follows a specific naming convention for migration files that ensures they are applied in the correct order:

migration/
├── i11-release/
│   └── postgresql/
│       ├── 202501011200_IDEMPIERE-6001.sql
│       └── 202501021400_IDEMPIERE-6002.sql
├── local/
│   ├── 202501150930_CustomInvoiceFields.zip
│   └── 202502011000_UpdateTaxRates.zip
└── processes_post_migration/
    └── ...

Key conventions:

• Timestamp prefix — Files are named with a YYYYMMDDHHMI timestamp prefix that determines the order of application. Files are processed in alphabetical (and therefore chronological) order.
• Descriptive suffix — After the timestamp, include a meaningful description and optionally a ticket/issue number.
• i11-release/ — Contains official iDempiere release migrations, organized by database type.
• local/ — Contains custom 2Pack files and SQL scripts specific to your deployment. This is where your plugin migrations go.
• processes_post_migration/ — Contains processes that should run after all SQL migrations are applied.

2Pack in Plugin Development

For plugin developers, 2Pack is the standard way to distribute Application Dictionary changes that your plugin requires. A well-structured plugin includes its 2Pack files in the plugin bundle, and they are automatically applied when the plugin is installed.

Including 2Pack in a Plugin Bundle

Place your 2Pack ZIP files in a specific directory within your plugin project:

com.example.myplugin/
├── META-INF/
│   └── MANIFEST.MF
├── src/
│   └── ...
├── OSGI-INF/
│   └── component.xml
└── migration/
    └── local/
        ├── 202501150930_InitialSetup.zip
        └── 202502011000_Version2Update.zip

In your MANIFEST.MF, include the migration directory in the bundle’s classpath or use the migration handler to apply packs when the bundle starts:

Bundle-ClassPath: .,migration/

Automating PackIn During Plugin Activation

Your plugin’s Activator (or a DS component) can programmatically trigger PackIn when the bundle starts:

package com.example.myplugin;

import java.io.File;
import org.compiere.utils.Util;
import org.osgi.framework.BundleActivator;
import org.osgi.framework.BundleContext;

public class Activator implements BundleActivator {

    @Override
    public void start(BundleContext context) throws Exception {
        // Find the migration directory within the bundle
        String migrationPath = context.getBundle()
            .getEntry("migration/local/").getPath();

        // PackIn logic would process files in this directory
        // The actual implementation varies — see iDempiere's
        // AbstractActivator for a base class that handles this
    }

    @Override
    public void stop(BundleContext context) throws Exception {
        // Cleanup if needed
    }
}

Many plugin developers extend org.adempiere.plugin.utils.AdempiereActivator, which provides built-in support for applying 2Pack files from the bundle’s migration directory.

Common Pitfalls

Working with 2Pack involves several common traps that developers should be aware of:

Dependency Order

Objects must be exported in the correct dependency order. For example, if you add a column to a table and then add a field for that column to a window, the column must be created before the field. 2Pack generally handles this within a single pack file, but when splitting changes across multiple pack files, you must ensure the timestamp-based ordering reflects the dependency chain.

Missing References

If your pack references an object that does not exist in the target environment (e.g., a column referencing a table that hasn’t been created yet, or a menu entry pointing to a window from another pack), the import will fail. Always verify that all prerequisites are included in the pack or applied first.

Client and Organization Context

When exporting data records, be aware that the Client (AD_Client_ID) and Organization (AD_Org_ID) context may differ between environments. Use the System client (AD_Client_ID=0) for dictionary-level objects and ensure data exports use the correct client context.

Overwriting Customizations

Applying a 2Pack to an environment that has been manually customized can overwrite those customizations. Always review the pack contents before applying to production, and maintain a clear record of manual changes in each environment.

Large Packs

Very large 2Pack files (with hundreds of objects) can be slow to process and difficult to debug when errors occur. Consider breaking large changes into smaller, focused packs that can be applied incrementally.

Versioning and Upgrade Paths

As your plugin evolves, you will need a versioning strategy for your 2Pack migrations:

• Incremental migrations — Each version of your plugin adds new 2Pack files rather than modifying existing ones. Version 1.0 includes 202501150930_v1.0_InitialSetup.zip; version 1.1 adds 202503011000_v1.1_NewFields.zip.
• Cumulative packs — For fresh installations, you may also provide a single cumulative pack that includes all changes up to the current version. This is faster than applying dozens of incremental packs.
• Version tracking — iDempiere’s AD_Package_Imp table records which packs have been applied. Your plugin can check this table to determine which migrations still need to be applied.

-- Check which packs have been applied
SELECT Name, Version, PK_Status, Created
FROM AD_Package_Imp
WHERE Name LIKE '%MyPlugin%'
ORDER BY Created;

Automating Migrations

For teams practicing continuous integration and deployment, 2Pack migrations can be automated:

1. Version control — Store 2Pack files in your plugin’s source repository alongside the Java code.
2. Build pipeline — Include 2Pack files in the plugin’s build output (JAR/bundle).
3. Auto-apply on startup — Use the AdempiereActivator or a custom DS component to apply pending packs when the plugin starts.
4. Rollback strategy — While 2Pack does not natively support rollback, you can create reverse packs that undo changes, or use database snapshots before applying major updates.

Key Takeaways

• 2Pack is iDempiere’s built-in tool for packaging Application Dictionary changes as portable XML files (PackOut) and applying them to other environments (PackIn).
• Use 2Pack instead of manual SQL scripts — it handles dependency ordering, uses UUIDs for portability, and supports idempotent application.
• Distinguish between metadata (AD definitions, safe to migrate) and data (business records, migrate with caution).
• Follow the timestamp-based naming convention for migration files to ensure correct ordering.
• Include 2Pack files in your plugin bundles and use the AdempiereActivator base class to auto-apply them during installation.
• Watch for common pitfalls: dependency ordering across multiple packs, missing references, and overwriting manual customizations.

What’s Next

With a solid understanding of iDempiere’s architecture, extension points, and migration tools, you are ready to start building plugins. In the next lesson, you will create your first iDempiere plugin from scratch — setting up the project, configuring the manifest, writing an activator, and deploying it to a running iDempiere instance.