XPO - Automate Database Schema Migrations using the ORM Data Model Designer or APIs (CTP)

XPO Team Blog
20 April 2020
We’ve got some good news for the majority of XPO and XAF users! With v20.1, you can incrementally update your database schema once changes are made to an XPO data model. You can generate diffs (the SQL script) between the latest data model and the existing database schema in the ORM Data Model Designer or in code. Database schema migration is available as a community technology preview (CTP) in this release cycle. As always, we welcome your feedback. 

How it Works

In the Data Model Designer, right-click the design surface and select the Generate Migration Script menu item. The designer will add a new file with SQL commands and open it in Visual Studio.

If you create XPO classes manually, the following example demonstrates how to generate a schema migration script and update your database programmatically: How to Use the Database Schema Migrations API.

Supported Databases and Limitations

  • At present, the following data store providers support this feature: MSSqlConnectionProvider, MySqlConnectionProvider, OracleConnectionProvider, ODPConnectionProvider, ODPManagedConnectionProvider, PostgreSqlConnectionProvider.
  • The Database Schema Migration API may change prior to official release. Interfaces and method names are subject to change in the final release.
  • The Data Model Designer cannot generate a schema migration script if your Data Model contains classes imported from external assemblies. For instance, in XAF applications, the Base Class property can reference built-in business classes shipped with eXpressApp Framework (XAF). This scenario is not supported in our CTP version. You can still use API methods to generate a migration script in XAF apps.
  • The Data Model Designer does not allow you to specify custom schema migration options. 
  • If you rename a class or property, the schema migration script will delete the corresponding table or column and create a new one. 
  • The Data Model Designer cannot generate a database update script if the Data Model contains classes mapped to database views.

What’s New in v20.1

To explore the scope, breadth and capabilities of our new products/features, please visit: https://www.devexpress.com/Subscriptions/New-2020-1.xml.
2 comment(s)
renejdm
renejdm
I would love to try this out if you have a VistaDB provider. Also, does the Generate Migration Script allow you to select which tables to use or does it only work on the entire database? And one last question. What about existing data? Does it remain? And is there also a data diff script tool?
21 April 2020
Uriah (DevExpress Support)
Uriah (DevExpress Support)

Hi renejdm,

In the CTP version, the Generate Migration Script command always scans the entire database. In the next version, we wish to allow users to select tables and specify other options. And, of course, we are going to support the remaining database systems, including VistaDB.
 
As for your last questions:

  1. We prefer not to delete or corrupt the existing data. However, the API has options to allow deleting tables and columns. You can enable this capability using the SchemaMigrationOptions class, if necessary.
  2. If you mean products like SQL Schema Compare Tool by Devart, we do not have a similar tool. We are focused on a different task at the moment: make it easy to incrementally update the database during development and synchronize it with the XPO Data Model.
 
22 April 2020

Please login or register to post comments.