Allow r-scripts (run all the time)

[ottovw]

Fly supports so called r-scripts. They are executed on every migration. I believe that be quite useful.

Thanks

[ThomWright]

Hi,

I have some questions!

1. What is Fly?
2. What is an r-script? Is this a name specific to Fly?
3. Could you give an example of when this would be useful?

They are executed on every migration.

So how does this work? Is it some SQL which gets run after (or before?) every migration? What happens in the following scenario:

• write 1 migration
• run that migration on prod
• write an r-script
• write 1 more migration
• run the migrations again

It seems to me that on prod, we would see the following series of events (assuming the r-script runs after each migration):

• migration 1 run
• migration 2 run
• r-script run

However, if we start up a new environment, we would see:

• migration 1 run
• r-script run
• migration 2 run
• r-script run

This is different, which is a problem.

[marton78]

Not the OP, so I can't answer the other questions, but with "Fly" he probably means Flyway, the migration framework used by Martin Fowler.

[frankpf]

I think @ottovw might be referring to Flyway's repeatable migrations. These are useful for DDL that doesn't change any existing data by itself (e.g. functions and views).

[ThomWright]

Interesting, thanks. Using it to do e.g. CREATE OR REPLACE VIEW might make sense - it does get verbose currently. I'll have to check it out and have a think about it.

[gregplaysguitar]

I'm currently looking for this exact feature too - in our case I'd like to just have some idempotent sql that runs after every migrate run; this way we can just edit it in place rather than duplicating and then editing in a new migration.

One of the great things about plain sql migrations is that they can just be concated together to quickly build a db for testing, so ideally this feature would maintain that via a naming scheme that places the post-migrate script logically at the end. I'm not sure exactly how that should look though - perhaps it's just any file with a non-numeric prefix?

/migrations
  /001_init.sql
  /002_add_something.sql
  ...
  /zzz_post_migrate.sql

@ThomWright I'd love to help out with the implementation of this, if you are happy with the approach. Let me know.