Turn postgres db into migrations

Question

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,

John Zabroski · Accepted Answer · 2021-10-28 20:50:30Z

1

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://learn.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.

edited Oct 28, 2021 at 20:50

answered Jun 8, 2020 at 22:35

John Zabroski

2,3972 gold badges31 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Carlos Jimenez Bermudez Over a year ago

I meant to ask if there was something such as the reverse engineer scaffolding. learn.microsoft.com/en-us/ef/core/managing-schemas/… . I wanted to know if I could save some time rather than write it all out from scratch when what I have is a schema and not a set of migrations (I do have the migrations, but they are in Laravel PHP ORM style).

John Zabroski Over a year ago

I think that's a dapper question, not a FluentMigrator question. I typed "dapper scaffolding" into Google and found this for you: github.com/hherzl/CatFactory.Dapper

Collectives™ on Stack Overflow

Turn postgres db into migrations

1 Answer 1

EFCore

FluentMigrator

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

EFCore

FluentMigrator

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related