6

I have a database with views and stored procedures that often need to be modified. I would like to be able to store these views in another directory, and include the sql code when I write a migration. Basically, the dir structure would be

views/
    my_view.sql
functions/
    my_func.sql
sql/
   V1__add_view.sql

And V1__add_view.sql would be something like

\i views/my_view.sql

Which will currently work in psql, but not in flyway migrations. The advantage of this is that when we wanted to make a change, we could modify the view in place, and include it in the next migration. It would also just eliminate a vast amount of copy-pasting in view migrations.

Is there any way to include external SQL scripts in a flyway migration?

Ramfjord
  • 872
  • 8
  • 14
  • Did you ever figure this out? I am also interested in something like this for another reason: large number of creation scripts and how they're ordered. The current guidance from [Tomasz](http://stackoverflow.com/users/605744/tomasz-nurkiewicz) is to use incremental version numbers _per file_ to enforce ordering - which in my opinion reduces legibility and discoverability. I'd like to have a single "Version" migration script, where the included scripts are used, in the correct order. – rbellamy Apr 09 '16 at 17:42

1 Answers1

2

It sounds like you might be able to accomplish this using Repeatable migrations.

I don't think flyway supports calling external scripts like a \i statement would. If you want to try the importing route, you could use placeholders for your scripts.

Using your example, use the placeholder in your sql migration file

${my_view}

When you call flyway define the placeholder replacement value with the text from your views/my_view.sql. I'm not sure what you're using to call flyway, but in ant it would be something like

<loadfile property="flyway.placeholder" srcfile="views\my_view.sql"/>
<flyway:migrate>
    <locations>
        <location path="database/migrations"/>
    </locations>
    <placeholders>
        <placeholder name="my_view" value="${flyway.placeholder}"/>
    </placeholders>
</flyway:migrate>

The documentation also has an example: https://flywaydb.org/documentation/ant/migrate

fishy
  • 21
  • 3
  • Edited to suggest using a repeatable migration (R__add_my_view.sql instead of V1__add_view.sql) – fishy May 18 '16 at 13:47