How to Automate Database Schema DDL with Embarcadero DB Change Manager
Managing database schema changes manually is slow and prone to errors. Embarcadero DB Change Manager solves this problem by automating the tracking, comparison, and deployment of Data Definition Language (DDL) changes across development, testing, and production environments.
Here is how to set up an automated database schema migration pipeline using DB Change Manager. Step 1: Connect to Your Target Databases
Before automating changes, you must define the database connections inside the application. Open DB Change Manager. Navigate to the Data Source Explorer panel. Right-click and select New Data Source.
Choose your database platform (e.g., Oracle, SQL Server, MySQL, or PostgreSQL).
Enter the host credentials, port, and authentication details. Click Test Connection, then click Finish. Step 2: Establish a Schema Baseline
An automation pipeline requires a reference point to understand what has changed over time. Go to File > New > Schema Archive Job. Name the job (e.g., Production_Baseline_Archive). Select your reference database as the source.
Choose the specific schemas and object types (tables, indexes, views, triggers) to track. Click Save and select Run Job.
DB Change Manager will extract the current DDL statements and store them as a baseline version. Step 3: Configure a Schema Comparison Job
To automate deployments, the software must compare your active development schema against your baseline or target production environment. Go to File > New > Schema Comparison Job.
Select your Source (e.g., Development Database or a specific Schema Archive). Select your Target (e.g., Staging or Production Database).
Under the Refinement tab, map matching schemas if their names differ across environments.
Under the Options tab, configure how the tool handles specific DDL differences, such as ignoring storage clauses or table tablespaces. Step 4: Generate the Synchronization DDL Script
Once the comparison job finishes running, the tool highlights the discrepancies between the two environments.
Review the comparison results dashboard to see missing, extra, or modified database objects. Click Generate Sync Script.
DB Change Manager automatically writes the exact CREATE, ALTER, and DROP DDL commands needed to align the target with the source.
Review the script in the built-in SQL editor to verify safety compliance. Step 5: Automate the Pipeline via Command Line (CLI)
To achieve true automation, remove manual clicks by executing your saved comparison and sync jobs through the command line or a CI/CD pipeline (like Jenkins, GitHub Actions, or GitLab CI).
Locate the automated command-line utility, cmcmd, in your DB Change Manager installation directory.
Export your configuration file (.config) from the comparison job UI.
Run the comparison and generate the change script using the following command structure:
cmcmd -job schema_compare_job_name -config configuration_file.config -run Use code with caution.
To automatically execute the generated synchronization script against your target database, append the execution flag:
cmcmd -job schema_compare_job_name -config configuration_file.config -execute Use code with caution. Best Practices for DDL Automation
Always Archive First: Run a Schema Archive Job on your target database immediately before executing any automated sync script to ensure an easy rollback path.
Filter Ignored Objects: Exclude developer-specific temporary tables or local test accounts from your comparison configuration to avoid polluting production environments.
Integrate Notifications: Configure DB Change Manager email alerts to instantly notify database administrators if a scheduled comparison job detects unauthorized schema drift.
To tailor this guide for your specific setup, please let me know: Which database platform (SQL Server, Oracle, etc.) you use. Your current CI/CD tool (Jenkins, GitHub Actions, etc.).
Whether you need to include data masking or compliance steps.
I can add specific configuration code snippets based on your environment.
Leave a Reply