Option to generate migrations as SQL

[theelderbeever]

Throwing this out there as a possible suggestion. It would be potentially valuable to have an --sql option with the migration management where the output of piccolo migrations new --sql is a .sql file instead of using python code. This maybe useful in debugging and transparency into what will be run and how. I know of one other ORM that does this (Hasura) and it was nice to easily edit the migration.

[dantownsend]

Yeah, it's a nice idea.

A --sql flag, and / or a command like this:

# prints out the SQL for migrations which haven't been run
piccolo migrations preview

# or for a specific migration
piccolo migrations preview my_app 2021-06-22T12:06:00

In terms of letting people override the generated SQL, it's a bit trickier. Piccolo adds together existing migration files to work out the current state of the database. Because all of the operations in the migration files are Python code, it's quite easy to do this. It would be harder if some of the files are .sql.

[dantownsend]

One thing I've wanted to add for a while is a fake argument to MigrationManager.

So let's say you're not happy with the SQL the migration generated, you can set fake=True, and apply it to keep Piccolo happy. But then add another migration with the SQL you actually want, using add_raw.

[theelderbeever]

Yeah I think that would be a workable enough solution. I will look out for whenever that makes it.

[dantownsend]

This is kind of fixed in the latest version of Piccolo - there's now a preview flag so you can see which DDL statements will be run by a migration:

piccolo migrations forwards my_app --preview