CBL Migrator

A lightweight SQLAlchemy-based tool that migrates Oracle databases to MySQL, PostgreSQL, or SQLite. It is used in ChEMBL dump generation.

Usage in Python

from cbl_migrator import DbMigrator

origin = 'oracle://{user}:{pass}@{host}:{port}/?service_name={service_name}&encoding=utf8'
dest = 'postgresql://{user}:{pass}@{host}:{port}/{dbname}?client_encoding=utf8'

migrator = DbMigrator(origin, dest, ['excluded_table1', 'excluded_table2'], n_workers=4)
migrator.migrate()

Command Line Usage

cbl-migrator "oracle://{user}:{pass}@{host}:{port}/?service_name={service_name}&encoding=utf8" \
             "postgresql://{user}:{pass}@{host}:{port}/{dbname}?client_encoding=utf8" \
             --n_workers 8

How It Works

• Copies tables from the source, preserving only PKs initially.
• Migrates table data in parallel.
• If successful, applies constraints and then indexes; skips indexes already covered by unique keys.
• Logs objects that fail to migrate.

What It Does Not Do

• Avoids tables without PKs (may hang if a unique field is referenced by an FK).
• Ignores server default values, autoincrement fields, triggers, and procedures.

SQLite

• No concurrent writes or ALTER TABLE ADD CONSTRAINT.
• Uses one core and creates constraints at table creation time.
• Inserts rows sequentially in correct FK order.

MySQL

• Converts CLOBs to LONGTEXT.