How to Generate Delta Scripts for Database Changes

To create delta scripts for database changes, you must identify the changes made to the schema. In this article, we will describe:

• how to compare a project created in Luna Modeler database design tool with already existing database and
• how to generate delta scripts – synchronization SQL scripts with ALTER statements and other necessary SQL – only for the modifications we have made.

Initial Database Design

Let’s have a look at the following sample project created by loading an existing structure from a MariaDB database.

Our goal is to make the following three common modifications:

1. In the customer table, we rename the name column to first_name and the surname column to last_name. We will also add column middle_name to this table.
2. Next, we modify the address table by removing the country column. We will also add a new table country to the project and create a new relationship between the country and address tables.
3. The last change will be to edit the vendor table, in which we will change the parameter for the company_name column from the value 50 to 150.

Now let’s see how to generate the synchronization SQL script.

Comparing the Project with the Database

Click the Advanced button and select the Generate synchronization script option.

A new form will open, on which we select the target database connection.

The connection detail will be displayed and also the settings where we can choose how to perform the comparison and how to generate the script. Now we click on the Load database and detect differences button.

Luna Modeler will run the comparison against the database and display a summary of the detected changes.

Let’s take a look at the individual entries.

We will start with the customer table. In the columns section, we can see the changes. Since we have renamed two columns, we can use a mapping. For the first_name column, we click on the Change mappings button.

This will open a window in which we can select the target column. In this case, we will map the first_name column to the name column.

Similarly, we will map the last_name column to surname.
The middle_name column has been newly added, which is also indicated by the green icon.

The second change was to add the country table and modify the address table. These changes were also successfully detected. In the address table, the country column has been removed, which is also indicated by the icon.

The last change was a modification in the vendor table. We can view the details of the change simply by clicking on the section we want to expand or collapse.

To see all the details, just click on the Expand All button on the toolbar. To close the detail we can click on the Collapse All button.

Another way to change the view is to use different groupings of items. We can group items by Object type, Difference type or leave them without grouping.

Delta Scripts – Synchronization SQL with ALTER Statements

Now let’s take a look at the generated SQL script. Let’s click on the SQL Script tab, where the list of SQL statements is displayed. Here we see the statements that modify the customer table.

Also, the statements that create the country table and modify the address table and create a constraint for the relationship we defined between the country and address tables.

At the end, we see a statement to modify the company_name column in the vendor table. Here we have changed the parameter from a value of 50 to a value of 150.

Generating HTML reports

Luna Modeler can also be used to generate HTML reports that contain a summary of detected changes and an SQL script. To generate the documentation, just click on the Report button and select the style and folder where to save the report.

In the report, you can open and close sections with differences and also use the standard Find function, which is available in all browsers.

Get started today and try Luna Modeler

Download

#