Temelie.Database.Services 1.0.205

Temelie.Database.Services

Database-first tooling for reading schemas, scripting databases to source control, deploying those scripts, copying data between databases, and syncing changes.

dotnet add package Temelie.Database.Services
dotnet add package Temelie.Database.Providers.Mssql   # and/or Temelie.Database.Providers.MySql

Services are exported with [Export...] attributes. Reference Temelie.DependencyInjection.SourceGenerator in your application to register them, along with the providers you reference.

Services

Extension points

Scripting a database

var connection = new ConnectionStringModel
{
    Name = "Dev",
    DatabaseProviderName = "SqlConnection",
    ConnectionString = "Data Source=.;Initial Catalog=MyApp;Integrated Security=True;Encrypt=False"
};

scriptService.CreateScripts(connection, new DirectoryInfo("src/MyApp.Database"), progress => Console.WriteLine(progress));

Every object is written as a .sql script plus a .sql.json model:

Generation details:

• File names are schema.name.sql.
• Unchanged files are left untouched, and files for objects that no longer exist are deleted.
• Column changes to an existing table are written as a dated migration, 04_Migrations/yyyy-MM-dd/01_<Table>.sql.

Deploying scripts

scriptService.ExecuteScripts(connection, new DirectoryInfo("src/MyApp.Database"), progress => Console.WriteLine(progress), continueOnError: true);

Top-level folders run in name order, which lets you interleave your own folders with the generated ones:

More deployment details:

• Removed tables are renamed to __<Table> instead of being dropped, so no data is lost.
• Failures: with continueOnError, failed non-migration scripts are collected, and the whole run is retried up to 5 times. This resolves most ordering problems between objects. A failed migration ends the current attempt immediately. Without continueOnError, the first failure stops the deployment.
• Batches: scripts are split into batches on GO lines.

A typical project layout:

MyApp.Database/
  01_Always/              helper procedures used by migrations
  02_Migrations/<date>/   data and schema changes that must run before objects are applied
  02_Tables/ 03_Indexes/ 04_CheckConstraints/
  04_Migrations/<date>/   table changes
  05_Programmability/ 05_Views/ 06_Triggers/
  07_Migrations/<date>/   data fixes that need the new views/procedures
  08_ForeignKeys/ 09_SecurityPolicies/
  09_Always/              post-deployment scripts

Generated .sql.json files can also feed Temelie.Entities.SourceGenerator.

Copying data

ITableConverterService.ConvertTables copies the selected tables from a source connection to a target connection. It matches columns by name, can trim strings, and uses provider bulk copy in batches, optionally inside a transaction.

Change sync

IChangeTrackingService reads changes from a source IChangeTrackingSourceProvider (mssql_ct or mssql_cdc) and applies them through an IChangeTrackingTargetProvider. It keeps the last synced version per mapping, so each sync only moves new changes.