A Lightweight Library to generate quickly Databases from entities.
Concepts:
- 0 Migration. No "MigrationsHistory" table. Avoids a directory with hundred of migrations.
- Scaffold command to generate/synchronize entities, DbModel and Snapshot with Database
- Generate script or update database command to update the database
Db Supported: SQL Server, SQLite, MySQL|MariaDB, PostgreSQL
Define the entites to create/update the database. CF will discover columns, primary keys and foreign keys with Data Annotations
[Migration("637727087400479557_InitialCreate")]
public class InitialCreate : Migration
{
protected override MigrationOptions GetOptions()
{
return new SqlServerMigrationOptions();
}
protected override MigrationRunner GetMigrationRunner()
{
var runner = new SqlServerMigrationRunner("Server=(localdb)\\mssqllocaldb;Database=TestMG;Trusted_Connection=True;MultipleActiveResultSets=true");
runner.DropDatabase = true; // allows to drop existing database
return runner;
}
protected override void Down(MigrationBuilder migrationBuilder)
{
}
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.CreateTable<Company>();
migrationBuilder.CreateTable<Employee>();
// a stored procedure
migrationBuilder.Sql(@"
CREATE PROC usp_GetCompany
@CompanyId int
AS
BEGIN
SELECT *
FROM Companies
WHERE CompanyId = @CompanyId
END
GO
");
}
}
// [Table("tbl_Companies")] allows to define the name of the table
public class Company
{
[Key]
public int CompanyId { get; set; } // key
[StringLength(100)]
public string Name { get; set; } //required
[Required]
//[Column("MyPostalCode")] allows to rename the column
public string PostalCode { get; set; } // required with data annotations
public string? Address { get; set; }
[MaxLength(50)] // or [StringLength(50)]
public string? City { get; set; } // not required
[Timestamp]
public byte[] RowVersion { get; set; }
public List<Employee> Employees { get; set; } = new(); // ignored
}
public class Employee
{
[Key] // or not identity [Key, DatabaseGenerated(DatabaseGeneratedOption.None)]
public int EmployeeId { get; set; } // recommendation: make Key unique, dont use names like "Id" for all primary keys
public string FirstName { get; set; }
public string LastName { get; set; }
// [ForeignKey("Companies")] use foreign key attribute if column name does not match with principal column name
public int CompanyId { get; set; }
public Company Company { get; set; } // ignored
[NotMapped] // ignored
public string FullName
{
get { return $"{FirstName} {LastName}"; }
}
}
DataAnnotations Attributes:
Key: for primary key (identity if int, short or long)
Table: to define table name
Column: to define column name
DatabaseGenerated + identity option: for a column identity
StringLength ou MaxLength: to define string length (exeample "navarchar(100)")
ForeignKey: to specify the principal table name
Its possible to define columns (default, value, unique, column type, etc.), multipe primary keys or foreign keys with fluent api. Example:
By default a table "__CFMigrationsHistory" is created. Its possible to create a custom MigrationsHistoryManager. Just implement IMigrationsHistoryManager and change the manager provided
protected override MigrationRunner GetMigrationRunner()
{
var runner = new SqlServerMigrationRunner("Server=(localdb)\\mssqllocaldb;Database=TestMG;Trusted_Connection=True;MultipleActiveResultSets=true"
, new JsonFileMigrationsManager());
return runner;
}
Sample with JSON file
public class JsonFileMigrationsManager : ISqlServerMigrationsHistoryManager
{
private const string path = "path/to/file.json";
private List<JsonMigration> _migrations;
public JsonFileMigrationsManager()
{
_migrations = LoadMigrations();
}
public void ClearHistory(SqlServerMigrationRunnerContext context)
{
_migrations.Clear();
Save(_migrations);
}
public void DeleteMigration(string migrationId, SqlServerMigrationRunnerContext context)
{
var migration = _migrations.FirstOrDefault(x => x.MigrationId == migrationId);
if (migration != null)
{
_migrations.Remove(migration);
Save(_migrations);
}
}
public void InsertMigration(string migrationId, SqlServerMigrationRunnerContext context)
{
if (!MigrationExists(migrationId, context))
{
_migrations.Add(new JsonMigration { MigrationId = migrationId });
Save(_migrations);
}
}
public bool MigrationExists(string migrationId, SqlServerMigrationRunnerContext context)
{
var migration = _migrations.FirstOrDefault(x => x.MigrationId == migrationId);
return migration != null;
}
private List<JsonMigration> LoadMigrations()
{
if (File.Exists(path))
{
var json = File.ReadAllText(path);
return JsonSerializer.Deserialize<List<JsonMigration>>(json);
}
return new List<JsonMigration>();
}
private void Save(List<JsonMigration> history)
{
string json = JsonSerializer.Serialize(history, new JsonSerializerOptions { WriteIndented = true });
File.WriteAllText(path, json);
}
}
public class JsonMigration
{
public string MigrationId { get; set; }
public string CreatedAt { get; set; } = DateTime.Now.ToString("MM/dd/yyyy HH:mm:ss");
}
Update database
With tool
dotnet cf update-database -p path/to/library.dll
Or in code
var resolver = new MigrationResolver();
// with executing assembly
resolver.UpdateDatabase();
// or with an assembly
resolver.UpdateDatabase(typeof(MyMigration).Assembly);
// or with path to dll
resolver.UpdateDatabase("path/to/library.dll");
With Custom Resolver. Example Microsoft.Extensions.DependencyInjection
// Example: services.AutoRegisterMigrations(typeof(InitialCreate).Assembly);
public static class IServiceCollectionExtensions
{
public static void AutoRegisterMigrations(this IServiceCollection services, Assembly assembly)
{
var types = assembly.GetExportedTypes();
foreach (var type in types)
{
if (type.BaseType.Name == typeof(Migration).Name && !type.IsAbstract)
{
if (!services.Any(x => x.ServiceType == type))
{
services.AddTransient(type);
}
}
}
}
public static void AutoRegisterMigrations(this IServiceCollection services)
=> AutoRegisterMigrations(services, Assembly.GetExecutingAssembly());
}
public class DIMigrationResolver : MigrationResolver
{
private readonly IServiceProvider _serviceProvider;
public DIMigrationResolver(IServiceProvider serviceProvider)
{
_serviceProvider = serviceProvider;
}
protected override Migration CreateMigration(Type migrationType)
{
var migration = _serviceProvider.GetRequiredService(migrationType) as Migration;
return migration;
}
}
Register the custom migration resolver and inject. Allows to inject options in migrations with the default connection string.
public void Configure(IApplicationBuilder app, IWebHostEnvironment env, DIMigrationResolver migrationResolver)
{
migrationResolver.UpdateDatabase(typeof(InitialCreate).Assembly);
// etc.
}