EctoView - package for creating and managing SQL views

Your Libraries & OS Mentoring Libraries
, ,
1

Ecto supports SQL views, even though these aren’t used that often. I think that one of the main reasons why these aren’t used is that creating them in migrations isn’t that simple (another one is that plain, non-materialised, views aren’t that needed when using Ecto).

I decided to write small library that will help with that.

This provide 2 functionalities:

Simplify writing views by utilising familiar Ecto.Query:

defmodule MyApplication.Repo.Migration.CreateViewFoo do
  use Ecto.Migration
  use EctoView.Migration # This line **MUST** be after `use Ecto.Migration`

  def change do
    query = from foo in "foos", # This **MUST** select from table name, no schemas allowed
      where: foo.bar == 2137,
      select: %{ # This **MUST** be a map of selected fields
        a: foo.a,
        b: foo.b
      }

    create view("foos_view", query)
  end
end

This will create view foos_view that is created with query SELECT a AS a, b AS b FROM foos.

There are restrictions for queries though - due to nature of migrations, we cannot rely on schemas in application, so the query must use raw table names passed as string. In addition to that, we need to provide select which must be a map of selected values.

Materialised views can be created the same way as plain views, just use materialised_view/2 instead of view/2.

Additional functionality is helper function for refreshing materialised views:

defmodule MyApplication.Repo do
  use Ecto.Repo, # …
  use EctoView

  # …
end

And now you can use Repo.refresh_materialized_view("my_view") to refresh content of that view.

There currently is no mechanism to automate refreshments of materialised views and that is left to the user to implement one.

12 Likes
2

Haven’t tried it yet, but would it work to declare the specific schema-bits that the migration cares about locally (inside defmodule MyApplication.Repo.Migration.CreateViewFoo)?

That’s an Elixir translation of an approach I’ve used before in Rails migrations.

3

Theoretically you could, but IMHO that wouldn’t be that different from simply using select in query with a map. Writing something like:

defmodule MyApplication.Repo.Migration.CreateViewFoo do
  use Ecto.Migration
  use EctoView.Migration # This line **MUST** be after `use Ecto.Migration`

  defmodule Foo do
    use EctoView.Schema

    migration_schema "foos" do
      field :a
      field :b
      field :bar
    end
  end

  def change do
    query = from foo in Foo,
      where: foo.bar == 2137,

    create view("foos_view", query)
  end
end

For me isn’t more readable. It introduces needless new module and a lot of visual cruft, where simple Ecto.Query can contain all that information without need for such magic.

3 Likes