Better migration docs please!

[MattGson]

Right now, migrations looks like it contains some powerful features.
Unfortunately is is very difficult to work out what to use and how to use it.

From what I can see the docs first suggest using sqlx migrations? https://www.sea-ql.org/SeaORM/docs/install-and-config/schema
So I wrote some migrations and now have some tables.

Then they tell me that it can generate entities from existing table: https://www.sea-ql.org/SeaORM/docs/generate-entity/sea-orm-cli
I obliged and now have code generated matching my tables.

Then I can create table from entity https://www.sea-ql.org/SeaORM/docs/generate-database-schema/create-table
(seems like we are going in circles)
Although the examples somewhat opaquely import what I assume are 'example entities' from an internal 'tests_cfg' module. This seems to require passing a non-existing Entity type into create_table_from_entity which was not generated in the previous step. It seems that this only exists in the so called Expanded entity structure.

Then I find that SeaORM has it's own migration tool in the docs. So I wonder if the previous docs were just out of date? https://www.sea-ql.org/SeaORM/docs/migration/setting-up-migration
This most confusingly suggests I need the entity classes before I can write the migrations?? (we're definitely going in circles now?)
I then try and wrap my head around why Table::create().table(Entity) needs the opaquely imported Entity type again instead of just a string for the table name?? This feels like it's trying to be too clever.

I now get to the docs on writing migrations and find that they don't actually include any useful information on writing migrations, just how to set up the folder https://www.sea-ql.org/SeaORM/docs/migration/writing-migration
I see that SQL migrations are supported which honestly looks easier at this point than the schema builder.

At this point I'm basically going to give up and just use sqlx since it just does what it says on the box. Migrations are simple, writing them should too. I think SeaORM should provide clear paths for how to use migrations and Entities together.

When it comes to keeping ORM models in sync with schema I think there are 3 choices:

• DB first and generate models (my preference for most projects). No worry about keeping in sync, happens automatically. You lose some small features like auto many-to-many relations. But when writing imperative migrations, keeping models in sync manually is very tedious.

• Entity first and generate migrations (good if the migration generator is good). This is essentially declarative diff based migrations. Much more difficult to implement well. https://entgo.io/docs/migrate is a good example.
  If executed well then this is great, otherwise it's a nightmare as you end up writing manual migrations to clean up the mess. Note that you MUST use codegen here. No-one is going to trust generated migrations that they can't read first. In general I think code-gen is a better approach than macros but here it is a requirement. See https://www.prisma.io/

• Third choice is to manually write imperative migrations and manually keep ORM models in sync. This was good when Rails came out but now it is a pretty old-school approach and is very tedious. Avoid making this the default at all costs (right now it seems like this is where SeaORM is heading).

Hope this feedback is helpful.

[billy1624]

Hey @MattGson, thank you so much for the feedback!

From what I can see the docs first suggest using sqlx migrations? https://www.sea-ql.org/SeaORM/docs/install-and-config/schema

This part is out of date. I will rewrite it ASAP.

Then I can create table from entity https://www.sea-ql.org/SeaORM/docs/generate-database-schema/create-table
(seems like we are going in circles)

This is the utility that comes before migration feature. So, user have a easy way to create the corresponding table in db.

This seems to require passing a non-existing Entity type into create_table_from_entity which was not generated in the previous step. It seems that this only exists in the so called Expanded entity structure.

The compact entity structure actually derive the Entity automatically, when derive DeriveEntityModel. For example see Cake entity.

https://www.sea-ql.org/SeaORM/docs/migration/setting-up-migration
This most confusingly suggests I need the entity classes before I can write the migrations?? (we're definitely going in circles now?)

The workspace structure part is for existing SeaORM to restructure their workspace.

I guess it's better to make this clear for newcomers

• If you have existing tables, then generate SeaORM entities with sea-orm-cli
• If you don't have existing tables, then write SeaORM entities by hand

And if you need migration features go checkout the migration docs.

[billy1624]

Hey @MattGson, docs updated. See https://www.sea-ql.org/SeaORM/docs/install-and-config/schema

[MattGson]

Hey, awesome work. Much more clear now! Thanks.

[billy1624]

Thank you so much!

[tyt2y3]

Thank you for the input.

I think being schema-first is (or should) be the default path. So the difference only comes down to defining the schema in raw SQL or in a database-generic syntax (which sea-query provides) plus being a programming language that could do more than SQL.

The problem with create_table_from_entity is that it is only good for the first time. Once we have a live database, we can only write migrations to mutate the schema.

I don't think we will develop a migration generator though, it is a problem too complex to handle.

One interesting approach may be that we use a hand written Entity in the initial migration to bootstrap a database. And subsequent migrations has to manually write add_column etc.

[maku]

@MattGson I am also a little bit frustrated after reading the docs in regard of migration. So I would be interested in how you actually deal with migration now...

In general I would prefer the "DB first" approach and then generate entities code. I can't currently find a way to do this elegantly with sea-orm on-board resources... But of course it's possible in a roundabout way... (using mirgration rust files and write the sql statements in a vec in code and execute one after another). Then generate the entity source code via sea-orm-cli generate entity

[Eugeny]

The problem with importing Entity into migrations is that it will break as soon as the entity itself changes. Meaning, you can't use create_table_from_entity in the migrations.

It's not even clear how to rename columns in migrations at all since it requires a Column:: reference to the "old" column.

[tyt2y3]

The simple answer is Column is just a enum with the DeriveColumn or sea-query's Iden macro, so we can define one locally.
We can also embed a copy of the old entity into the migration source file, which I think might be a "best-practice".
But in the end we will see if there are better ideas in this regard and start recommending it to users.

[Eugeny]

embed a copy of the old entity into the migration source file

Yup, that's what I ended up doing, however this requires great care to always reference the correct entity "version" in the last migration file it appears in when doing relations etc.

The perfect solution would be an automatic migration solver like the one in Django.

[tyt2y3]

Just to make a statement, a migration generator that "diff" the current Entity and existing schema and generate migration files accordingly is not on our roadmap, given the current limited developer resources in our org.

It's a difficult enough problem in itself, that could probably spin off as a new project.

But if someone would like to contribute, it'd be welcomed.

[Eugeny]

Totally understandable. Some food for thoughts: the generator doesn't have to inspect the DB schema if the migrations provide their statements as a list (instead of executing them imperatively).

Then, the generator could calculate the current "state" of the schema by virtually executing all migration statements and then compare the result to the entity definitions.

[billy1624]

Inspecting DB schema would be the best way of generating migration files. Because the ground truth is the DB schema itself.

[Eugeny]

Sure, DB-first and code-first are both valid approaches, and the lib will have to choose one.

[taqtiqa-mark]

Thank you for all the effort put into the SeaQL toolkit. As others noted I was a little confused about whether SeaQL has a 'blessed' approach - it seems it doesn't or hasn't settled on one or won't settle on one.

The following has a premise, which I'll put forward after setting this out:

As I see it, there is no reason for SeaQL to care one way or the other which approach to migrations you take, or, more likely, your org makes you take. From SeaQL's PoV those different "schools-of-practice" boil down to something that could/should be out of its scope.

The premise to that is that SeaQL explicitly delegates migration to refinery/barrel or sqlparser-rs for (current rust users) or rails/sequel/django/sqlalchemy/flyway etc etc. for new rust users

I suppose I'm suggesting throwing arms open to all comers in terms of how they manage their db state, and just take the DB as the source of truth for entities... so some one could be rather encourage to do what I just did - ran 20 odd Rails migrations and updated the entities after each one to have a (git) record of how state-of-play looked like through SeaQL's lens. All proceeded fine :) so why not explicitly remove one barrier to uptake - having to junk all your migration infrastructure, and rather encourage people doing what they feel most familiar with.

My 2c.