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.
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 (interfaces and method names) are subject to change.
- 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.
- Custom (non-XPO) attributes are not supported by this feature.
How It Works
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: XPO - How to use the Database Schema Migrations API.
Important Notes
Changes to the database schema can corrupt your data. Good practice is to create a full database backup before you update the database schema.
Does this also mean that in the future xaf applications will benefit from this too?
@Leon Bost: XAF or any other .NET apps using XPO for data access can benefit from this feature in v20.1. Please describe your specific use-case scenario and difficulties that made you think it would not be the case.
I don't understand against what version of the database the change script is created. If I change the model and save it how do you know what to change?
Can I point to an existing database and generate a change script?
Hello,
Thank you for your question.
To get differences, the Data Model Designer connects to the database using connection settings stored in your project.
If you selected the Save this connection to the configuration file option when creating the Data Model in the Data Model Wizard, the Data Model Designer will use this connection string when generating the database schema migration script. Otherwise, the Wizard stores the connection string settings in the *.xpo file.
Hello,
what is the overall state and roadmap for 2021?
Hi, Sebastijan.
This feature does not support Firebird. Frankly, we have not received much feedback for other database providers either.
We will be happy to learn more about your schema migration scenarios and see how XPO can better meet your needs in the future.
We are researching the options for multi-tenant Xaf (XPO) Blazor apps (yes, we know the limitations/recommendations of XAF from other tickets):
REQUIREMENTS
#1 SOLUTION SCENARIO (same schema DB; please find attached file)
Xaf Blazor Admin App (tenant list, other data)
*responsible for migration automation per tenant (other (I think slower) option is calling Process.Start (program.cs with XafApplication IDBUpdater))
Xax Blazor "Tenant" App
#2 IDEAL SCENARIO (per tenant XAF modules & schema)
XafApplication instance per user (browser tab)… so ideal scenario:
Hello,
Thank you for sharing your scenario with us. We will contact you if we have any questions regarding it.
Andrey