Question 89817 in PostgreSQL Question posted in
The official documentation can be found here.

Executing raw SQL in migrations: keep SQL statements as strings inside a migration or as code inside a separate SQL file? – Postgresql

In the database, I have multiple materialized views with big definitions. I also have multiple migrations that change the definitions of some of these materialized views using DROP and CREATE statements. Thus, we often are dropping / recreating the same views over and over, with small changes. These (rather bulky) statements are now stored inside strings:

class MyMigrationName < ActiveRecord::Migration[5.2]

  def up
    sql = <<~SQL
      ...
      create materialized view if not exists foo_1 as ... ;
      create materialized view if not exists foo_2 as ... ;
      ...
    SQL
    execute sql
  end

  def down
    ...
  end

I am considering switching from this current approach to a different one, where the SQL code is stored inside separate SQL files, for example in db/migrate/concerns/create_foo_matviews.sql. The code is read from the file and executed from inside the migrations, like so:

class MyMigrationName < ActiveRecord::Migration[5.2]

  def up
    execute File.read(File.expand_path('./concerns/create_foo_matviews.rb', __FILE__))
  end

  def down
    ...
  end

The pros of this approach are:

  • It is easier to see the differences between the old and the new SQL code using git diff (especially important given that materialized views’ definitions are big, but the actual changes in migrations are relatively small).
  • The SQL file adds syntax highlighting to the SQL code.
  • There is less copy/pasted code if I only change the relevant parts in the SQL file.

Are there any problems associated with this proposed approach? If yes, what would be an alternative solution to maximize maintainability?

See also

Tags:

2

Answers


  1. 0

    I’d leave it in the Migration.

    Mainly because the migration then contains everything that actually makes up the DB change.
    You would need to have two external SQL files (up and down) that I need to search/find first before I understand what the migration does.

    Depending on the Editor you are using, you will get (limited) syntax highlighting
    SQL In Heredoc in VS Code

    The migrations that execute custom SQL would all look the same, just the name of the external file would be different.

    What problem are you trying to solve? Just the "bulky" strings? I don’t think that this is problem (to be honest once the migration is run, you not go back to it anyhow) that is worth spennding a lot of time on. Just to the simplest thing: SQL in heredoc string.

    There are also gems that allow you to create (materialized) views with normal migration code (by adding support for create_view or similar) but i’d not add an additional dependency for something this simple.

    Also consider changing from schema.rb to structure.sql, if not yet done.

    Login or Signup to reply.
  2. 0

    Sound like you want to create your own helpers to create materialized views, something like add_index or add_column.

    You could make a module named like MaterializedMigrations in your lib directory. then you can required it in a initializer and for last you include it in your migration code, like this:

    class MyMigrationName < ActiveRecord::Migration[5.2]
  include MaterializedMigrations

  def up
    create_materialized_view("name_of_view")
  end
end

    The helper API is only a suggestion, you could design better API for your use cases.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search