Combine old migrations

[Dig-Doug]

Migrations are very useful while iterating. However, I find that after a few migrations it's hard to go through them and understand what the current database schema actually is. For example, let's say I do the following:

1. Create table orders with an id TEXT column
2. Add a new name field to the table
3. Rename the name field to customer_name
4. Update the primary key of the orders table to be a composite (customer_name, orders)

In theory, you could then do a combine operation that generates an equivalent SQL statement to create the orders table as it is now, e.g.

CREATE TABLE orders (
  id TEXT,
  customer_name TEXT,
  PRIMARY KEY (customer_name, id)
);

After that, you could clean up the old migrations and build on top of it.

Is a combine feature something others would find useful?

[weiznich]

Thanks for writing this proposal. Could you elaborate on how such a combine command could be implemented? It's easy to state that it should just "generate an equivalent SQL statement", but I cannot see how this can be implemented in a practical way without essentially reimplementing parsers + interpreters for all supported SQL syntax variants (which is clearly out of scope for diesel).

[mjanda]

This could be possibly implemented by resetting DB, running all migrations and then creating SQL dump of the result that would replace all existing migrations? Still, doesn't seem too useful for me. I'd use some database GUI like dbeaver etc to inspect your database, not your migrations.

One possible use I can see is when your migrations have some dependency you no longer use. For example I use pg_sphinx extensions to implement my fulltext search etc. If I ever switched to different fulltext solution, I'd probably want to remove mentions of this from my past migrations so I wouldn't need to have this extensions to re-run my migrations. Doing such combine would solve it. But it would be probably easy to do it manually and such use case would be pretty rare, so I don't think it would be worth the effort to implement in in diesel(-cli).

[Dig-Doug]

Thank you both for taking a look!

As @mjanda suggested, I was thinking you could use the database instance to do the reconciliation. aka. the diesel CLI would do:

1. Check all migrations existing migrations have been performed
2. Call database instance to get a dump of the current schema (e.g. pg_dump --schema-only databasename)
3. Write the returned SQL as the new baseline

I also agree with @mjanda that combining migrations manually would be sufficient. Maybe we could introduce the concept of a "snapshot" migration (vs. the existing incremental one) that tells diesel to skip previous incremental migrations?

P.S. I don't think this is urgent, we should definitely wait for more interest, e.g. with 👍

[Dig-Doug]

Actually, I might be able to get the experience I want with the pg-schema-diff. E.g.

1. I keep a "canonical" schema file somewhere
2. Generate diffs with the tool
3. Submit the diffs as diesel migrations

I will try this out next time I make db edits and report back.