Turn postgres db into migrations

user_1837420 0
Viewed 432

I have a database that I have inherited and would like to turn the database itself into migrations that I can use in an ASP.NET Core 3 API. I would like to, if I can, to not have to write the migrations by hand and rather, turn the database into migrations.

I understand that this is very easy with Entity Framework Core, but I wish to use dapper, not EF due to performance reasons as well as personal preference.

I have explored and have come into contact with two libraries for migration management compatible with Postgres: Fluent Migrator and Evolve.

Does anyone have any experience with either of these in the generation of migrations from an existing database?

Thanks,

fluent-migrator asp.net-core .net-core postgresql
1 Answers

I am the maintainer of FluentMigrator. I've never heard of Evolve, but other frameworks I've seen include db-up, RoundhousE, and SharpMigrations. Since FluentMigrator has been around for over a decade, it likely has the most complete out-of-the-box feature set of any database migration framework. I previously used RoundhousE but found it caused more problems than it solved.

FluentMigrator does not "generate" migrations for you. You have to write them yourself. By comparison, Entity Framework Core can use its fluent model building syntax and automatically infer up/down migrations, and you can focus primarily on writing data migrations.

That said, here is how Microsoft recommends writing migrations with EFCore, compared with the same logic in FluentMigrator:

EFCore

Source: https://docs.microsoft.com/en-us/aspnet/core/data/ef-mvc/migrations?view=aspnetcore-3.1#examine-up-and-down-methods

public partial class InitialCreate : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.CreateTable(
            name: "Course",
            columns: table => new
            {
                CourseID = table.Column<int>(nullable: false),
                Credits = table.Column<int>(nullable: false),
                Title = table.Column<string>(nullable: true)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_Course", x => x.CourseID);
            });

        // Additional code not shown
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropTable(
            name: "Enrollment");
        // Additional code not shown
    }
}

FluentMigrator

[Migration(1, "InitialCreate")]
public class InitialCreate : Migration
{
    public void Up()
    {
        Create.Table("Course")
                .WithColumn("CourseID").AsInt32().NotNullable().PrimaryKey()
                .WithColumn("Credits").AsInt32().NotNullable()
                .WithColumn("Title").AsString().Nullable();

        // Additional code not shown
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        Delete.Table("Enrollment");
        // Additional code not shown
    }
}

In the above example, you don't need to type out PK_Course in your primary key definition, because FluentMigrator supports "Don't Repeat Yourself" principle via naming conventions and "knows" that is the correct name for your primary key. You just define your naming conventions once.

Also, because of the fluent syntax, you'll find you have to type a lot less than you do with EFCore migrations. If you find this to not be the case, please provide feedback and we will make it more efficient.

user_10404370
Related