Would it be a good idea to apply the upscript, downscript, and upscript again in development to ensure compatibility?

[tanin47]

Occasionally, I'd find myself writing an Evolutions script where the downscript isn't compatible with the upscript (or doesn't fully reverse the upscript).

When this happens and I happen to rollback the database schema change, it's convoluted to untangle it e.g. know where to modify the database.

In the development environment, I want this to be checked automatically before committing the database change (locally). And we can do that simply by applying upscript, downscript, and upscript again.

What are your thoughts?

FYI: for the real world scenario, we have this unit test to ensure that the Evolutions scripts are valid in both directions before merging:

    val database = app.injector.instanceOf[play.api.db.DBApi].database("default")

    Evolutions.cleanupEvolutions(database)

    val dbEvolutions = new DatabaseEvolutions(
      database = database,
      schema = "",
      metaTable = "play_evolutions",
      substitutionsMappings = Map.empty,
      substitutionsPrefix = "$evolutions{{{",
      substitutionsSuffix = "}}}",
      substitutionsEscape = true
    )

    val scripts = dbEvolutions.scripts(ThisClassLoaderEvolutionsReader)

    scripts.foreach { script =>
      dbEvolutions.evolve(Seq(UpScript(script.evolution)), autocommit = true)
      dbEvolutions.evolve(Seq(DownScript(script.evolution)), autocommit = true)
      dbEvolutions.evolve(Seq(UpScript(script.evolution)), autocommit = true)
    }

    Evolutions.cleanupEvolutions(database)
    Evolutions.applyEvolutions(database)

[mkurz]

I understand.
Right now, I guess play assumes that when you add an evolutions script, which usually has an up and down part, that script is just supposed to work - meaning you wrote the up and down parts before adding it to Play, so they are 100% compatible with each other (so the down script always is able to revert the up script).
So Play is expected that you place a fully working up/down evolutions scripts already.

Hmm... not sure if we should provide users more possibility to test their scripts - I just do not want to make things more complicated.
For the UI we could either add a checkbox like [x] Also test down script(s) by applying up,down,up scripts or similiar... or we could have a dedicated page like @evolutions/test, which does not really apply the script but only runs up/down, so it helps with coding the evolution scripts.

The only thing what concerns me, if you do not use a database that supports atomic DDL (like postgres), meaning where your DDL statements will be applied within a transaction (if you set autocommit=false), you will have to manually fix the database as soon as there is a problem anyway.

I mean... what you could do as well for development is, after you added an evolution script and want to know if the down part works, you can just add a stupid statement - which has no effect at all - like select 1; or similiar to the up part, this will then trigger the down script and afterwards again runs the up part once more. I think this is what I did years ago when coding the evolutions scripts (my advantage was that I used Postgres and just had those scripts run within a transaction, which made life much more easier).

[mkurz]

Maybe I am thinking too complicated - so you mean just running up -> down -> up by default, out of the box...
Hmm.. I mean why not. So in the end it just fails fast immediately and not later when you change the script or even worse in production when you roll back the script for whatever reason...

If we do that, we should make the user aware of that in the UI (and also when running evolutions via command line if this up/down/up also applies to some command line flags then)

[tanin47]

you can just add a stupid statement - which has no effect at all - like select 1;

We have a unit test that essentially does this. It runs up, down, and up on every single script.

But a unit test is too late because we generally don't run unit test right after writing an evolution script. I'm trying to get into the habit of running the unit test for downscript immediately tho.

so you mean just running up -> down -> up by default ... so in the end it just fails fast immediately

Yes! this is the ask.

By running up-down-up, it ensures the downscript is compatible with the upscript. It is deceptively simple, but we should make sure the user understand it like you mentioned; there might be some weird cases we haven't thought of.

If we can run all of them under the same transaction, then it wouldn't impact anything at all if it fails. Could offer it as an option for a user to opt in, just to be safe.

[mkurz]

If we can run all of them under the same transaction, then it wouldn't impact anything at all if it fails.

Only Postgres supports transactional DDL.

mysql/mariadb does not:

• https://dev.mysql.com/doc/refman/8.4/en/cannot-roll-back.html
• https://dev.mysql.com/doc/refman/8.4/en/cannot-roll-back.html

MariaDB (and most other DBMSs) supports rollback of SQL-data change statements, but not of SQL-Schema statements. This means that if you use any of CREATE, ALTER, DROP, GRANT, REVOKE, you are implicitly committing at execution time.

Oracle also does not support that: https://docs.oracle.com/en/database/oracle/oracle-database/23/cncpt/transactions.html#GUID-510DF997-BA9C-4431-95EF-A0622881EC9C

The database issues an implicit COMMIT statement before and after every DDL statement. If the current transaction contains DML statements, then Oracle Database first commits the transaction and then runs and commits the DDL statement as a new, single-statement transaction.

[mkurz]

If you want to try out the up/down/up approach you can try that and see how that works for you in practise and see if you run into problems: You probalby want to start here, where we call evolutions.evolve multiple times.

[tanin47]

Thank you! I'll try it out.

It's sad that other DBs don't support transactional DDL...