Using Materialized Views as Models in Lucky
Tags: lucky, crystal, materialized, database
Published: 2018-07-27 12:36:57

---
title: Using Materialized Views as Models in Lucky
published: true
description: Materialized views are awesome! In this post we look at using them with Lucky
tags: lucky, crystal, materialized, database
---
I've got an app with over 2 million rows in a Postgresql database and I make complex queries with joins, unions and aggregate functions that take a lot of time. The results of some of these queries are used a lot, for example to render a cloropleth map on the app's homepage that changes based on filters on the map. This is a super expensive operation and can't scale!

In come [Materialized Views](https://www.postgresql.org/docs/current/static/rules-materializedviews.html) to the rescue. A materialized view is a query that is persisted like a table and treated the same way. This means queries on this table are fast. No joins, or unions, just like querying any other table. Note that it needs to be refreshed to keep it up to date so it is not suited for realtime data.

Although we can [map queries on our materialized view to a plain crystal class using `DB.mapping`](https://dev.to/mikeeus/mapping-database-queries-to-crystal-classes-with-crystal-db-and-lucky-460i), we would miss out on Lucky's awesome typesafe queries. So in this article we're going to look at treating materialized views just like normal tables and having Lucky model them for us!

## ComplexPost

The sql for the query we'll be materializing is below. Later we'll make slight adjustments to it so it can play nicely with `LuckyRecord::Model`.

```sql
SELECT
  posts.id,
  posts.title,
  posts.content,
  users.name as author
FROM posts
JOIN users
ON users.id = posts.user_id
```

This isn't a complex query but the way we implement this feature can be expanded to include any query regardless of complexity. The only thing that matters is that the columns returned by our `SELECT` statement match our `LuckyRecord` model definition.

## Setup

If you want to test this out yourself you can use my demo app, just clone the repo and checkout the `matviews-0` branch to follow along, or `matviews-3-complete` to see the finished code.

```
git clone git@github.com:mikeeus/lucky_api_demo.git
cd lucky_api_demo
bin/setup
git checkout matviews-0
```

## Create Spec
**git checkout matviews-0**

Although we don't have a materialized view or a model to access it, we're going to write a spec for how we *want* to be able to use it.

```ruby
# spec/matview_spec.cr
require "./spec_helper"

describe App do
  describe "ComplexPost matview" do
    it "should refresh and query" do
      user = UserBox.new.create
      first_post = PostBox.new.title("First").user_id(user.id).create
      second_post = PostBox.new.title("Second").user_id(user.id).create

      ComplexPost.refresh

      complex = ComplexPostQuery.new.title(first_post.title).first
      complex.title.should eq first_post.title
    end
  end
end
```

We want to be able to refresh the materialized view using the model, and we want to query the view with Lucky's own queries which will generate helper methods for each column, eg: `ComplexPostQuery.new.title(...)`, `ComplexPostQuery.new.content.ilike(...)`, etc.

## Create Materialized View
**git checkout matviews-1-create-matview**

First lets generate a migration with `lucky gen.migration CreateComplexPostMatview`.

```ruby
class CreateComplexPostsMatview::V20180101010 < LuckyMigrator::Migration::V1
  def migrate
    execute <<-SQL
      CREATE MATERIALIZED VIEW complex_posts AS
        SELECT
          posts.id,
          posts.title,
          posts.content,
          users.name as author,
          NOW() as created_at,
          NOW() as updated_at
        FROM posts
        JOIN users
        ON users.id = posts.user_id
    SQL

    execute "CREATE UNIQUE INDEX complex_posts_id_index ON complex_posts (id)"
  end

  def rollback
    execute "DROP MATERIALIZED VIEW complex_posts"
  end
end
```

We need to have `id`, `created_at` and `updated_at` columns because `LuckyRecord::Model` will expect these when it generates helper methods for us. In this case we actually use `id` and even add a unique index on it to speed up our queries, but I should note that **neither an id column or a primary key is needed for a materialized view**.

Now if we migrate with `lucky db.migrate` it should pass without issue.

## Generate ComplexPost Model and Query
**git checkout matviews-2-create-model**

Now that we have the materialized view in the database we could define a `ComplexPost` class that calls `DB.mapping` (like in my previous post) and just query it with `LuckyRecord::Repo` like this.

```ruby
LuckyRecord::Repo.run do |db|
  db.query_all "SELECT * FROM complex_posts", as: ComplexPost
end
```
But that wouldn't give us the benefits of `LuckyRecord::Model` or `ComplexPost::Query`.

So instead we'll generate our `ComplexPost` model by running `lucky gen.model ComplexPost`. We can go ahead and delete the generated `ComplexPost::Form` because we'll never be inserting rows into our materialized view. 

Let's fill it in as if it was a normal database model and add a class method to refresh the materialized view.

```ruby
# src/models/complex_post.cr
class ComplexPost < BaseModel
  table :complex_posts do
    column title : String
    column content : String
    column author : String
  end

  def self.refresh
    LuckyRecord::Repo.db.exec "REFRESH MATERIALIZED VIEW complex_posts"
  end
end
```

## Run The Specs
**git clone matviews-3-complete**

Run `crystal spec spec/matviews_spec.cr` and... BAM! It's working.

Although this was a simple example we can expand on it to account for complex queries. An example of a query that I use in my app is this.

```ruby
class CreateCountryAnnualTradeMatview::V0000001 < LuckyMigrator::Migration::V1
  def migrate
    execute <<-SQL
    CREATE MATERIALIZED VIEW country_annual_trades AS
      SELECT
        merged.id, -- FOR LUCKY
        merged.name,
        merged.short,
        merged.year,
        COALESCE(sum(merged.total_imports_cents))::bigint as total_imports_cents,
        COALESCE(sum(merged.total_exports_cents))::bigint as total_exports_cents,
        NOW() as created_at, -- FOR LUCKY
        NOW() as updated_at -- FOR LUCKY
      FROM (
        ( SELECT ... ) -- some complex query
        UNION
        ( SELECT ... ) -- another complex query
      ) merged
      GROUP BY id, name, short, year
      ORDER BY name
    SQL

    execute <<-SQL
      CREATE UNIQUE INDEX country_annual_trades_id_year -- we can even add indexes!
        ON country_annual_trades(id, year)
    SQL
  end

  def rollback
    execute <<-SQL
      DROP MATERIALIZED VIEW country_annual_trades
    SQL
  end
end
```

And the model.

```ruby
class CountryAnnualTrade < BaseModel
  table :country_annual_trades do
    column name : String
    column short : String
    column year : Int32
    column total_imports_cents : Int64
    column total_exports_cents : Int64
  end
  
  def self.refresh
    LuckyRecord::Repo.db.exec "REFRESH MATERIALIZED VIEW country_annual_trades"
  end
end
```

Note that I cast my aggregated columns `total_imports_cents` and `total_exports_cents` to `::bigint` then define them in the model as `Int64` with `column total_imports_cents : Int64`.

## Join Us

I hope you enjoyed this tutorial and found it useful. Join us on the [Lucky gitter channel](https://gitter.im/luckyframework/Lobby) to stay up to date on the framework or [checkout the docs](https://luckyframework.org/guides) for more information on how to bring your app idea to life with Lucky.