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
{
public override MigrationOptions GetOptions()
{
return new MigrationOptions
{
DropDatabase = true
};
}
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
[Column(TypeName ="ntext")] // allows to change the type
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.
To use JsonMigrationsHistory:
var runner = new SqlServerRunner();
runner.ConnectionString = "Server=(localdb)\\mssqllocaldb;Database=SampleDb;Trusted_Connection=True;MultipleActiveResultSets=true";
runner.History = new JsonMigrationsHistory("path/to/file.json");
runner.UpdateDatabase();
Its possible to create a custom MigrationsHistory. Just implement IMigrationsHistory and change the history.
Update database
With tool
dotnet cf database update -c "Server=(localdb)\\mssqllocaldb;Database=SampleDb;Trusted_Connection=True;MultipleActiveResultSets=true" -a path/to/assembly.dll
With a library that contains migrations and a startup assembly (Application Web Asp.Net Core) for example :
dotnet cf database update -c "Server=(localdb)\\mssqllocaldb;Database=SampleDb;Trusted_Connection=True;MultipleActiveResultSets=true" -a path/to/assembly.dll -s path/to/startup-assembly.dll
Note: add "-m json" to use JsonMigrationsHistory
Or in code
var runner = new SqlServerRunner();
runner.ConnectionString = "Server=(localdb)\\mssqllocaldb;Database=SampleDb;Trusted_Connection=True;MultipleActiveResultSets=true";
// with entry assembly
runner.UpdateDatabase();
// or with an assembly
runner.UpdateDatabase(typeof(MyMigration).Assembly);
Tip : auto register migrations with with 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 != null && 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());
}
Inject the runner to update the database
public void Configure(IApplicationBuilder app, IWebHostEnvironment env, SqlServerRunner runner)
{
runner.UpdateDatabase(typeof(InitialCreate).Assembly);
// etc.
}
Its possible to set the service provider. Example
runner.ServiceProvider = app.ApplicationServices;
Tip: create a bash file to execute multiple commands. Example test.sh and use GIT Bash 'sh test.sh'