Idea/proposal for idempotent rust-defined database structure

[AlbertMarashi]

This may be out of scope for this ORM, but I had an idea:

Some problems with the current architecture

• writing migrations can be:
  • error prone (down doesn't match up)
  • unclear (the current state of the database is hard to determine by reading migration files)
  • messy (dozens or hundreds of files)
• sea-orm-cli isn't flexible
  • sure, we can add derive, and serde(skip) columns, but what about other column attributes?

What if instead of writing migrations, and generating .rs files based on the database schema we instead defined the structure of tables in rust (generating TableDefs)

Taking inspiration from sqldef's idempotent schema management:

• adding new structs to our schema creates new tables
• adding/removing columns generates DROP and CREATE operations
• idempotent (same struct results in no changes to DB schema)

Take this use case

// we can choose all of our derives, attributes and etc since this file is the "source of truth"
#[derive(Entity, Deserialize, Serialize, GraphQLObject)]
//  table name be automatically inferred from type name too
#[table_name="users"]
// declaring multi-column compound indexes
#[unique(some_column_name, another_column_name)]
struct User {
  #[primary]
  pub id: Uuid,
  // declaring a uniqueness constraint
  #[unique]
  pub email: String,
  // declaring a foreign key & relations, we can auto-generate all the relations automatically from this
  #[foreign_key(User)]
  pub referrer: Option<Uuid>,
  pub first_name: String,
  pub last_name: String,
  // we can use any custom attributes, relevant to each developers usecase
  #[graphql(skip)]
  pub password: String,
}

Using sea-schema, we could generate a TableDef from this struct, and then use the discovery module of sea-schema to get the current database's TableDef, generate a diff and apply the individual changes to get the database to the desired rust-defined state with the writer module

Benefits

• all changes are version-controlled (reverting a commit behaves as a "down" migration, with no error prone behavior)
• super clean and ergonomic
• rust file is "source of truth" for the database
  • because of this, everything is "compile-time" checked/safe
• increased flexibility & customization since we are not generating the database tables

[tyt2y3]

I agree with your points, and I am aware of the difference between "Entity first" sv "Schema first" approaches.
As you may know, we already have the foundational bits and pieces.

I am keen to support the "entity first" approach. What needs to be done are:

0. a data structure for representing schema diff
1. schema diff algorithm on two SeaQuery schema statements
2. generate SeaQuery alter statements from the diff generated by 1)
3. some supporting infrastructure in SeaORM

If you are interested in working on any part of it, we can have more discussions on the design and implementation.

[tyt2y3]

Ah, I was under a poor internet connection and it posted many times.

[AlbertMarashi]

Sounds good. I would be happy to work on this and have built a similar system in the past. It would help me greatly if I had someone familiar with the SeaQL codebases to help give me guidance and feedback.

I have already started work on a derive that generates TableDefs from structs

My discord is albert-lum#0513 and I am on the SeaQL official discord.

Also I believe it may be possible to support both approaches (schema-first & entity-first), but we can discuss this more.

I also have 2-3 other people who may be interested in contributing to this system once the foundations for the implementation are planned out.

[tyt2y3]

Note to self: we have had some disscussions in Discord https://discord.com/channels/873880840487206962/901137465916657714/1110189907550408815

and what's outstanding is how'd we integrate that into the current cli / migration infrastructure.

[musjj]

Would love to see this too. Has there been any progress on this?

[Huliiiiii]

Schema sync has been implemented in 2.0.