In Progress
Unit 1, Lesson 1
In Progress

Building queries with rom-rb

Over the last two episodes guest chef Tim Riley has guided us through the basics of the ROM database persistence library. Today, in the final entry in this miniseries, get ready for a quick tour through some more advanced ROM features. You’ll get a sense of ROM’s flexibility, and taste of some of the ways it can be customized for special cases. Enjoy!

Video transcript & code


Welcome back to the world of rom-rb. We've spent the first and second episodes covering all the basics, so let's tackle one final, important aspect: how rom can help us build more complex queries and leverage our database to the fullest extent.

I want to share as much as possible here, so let's get right to it!


rom comes with an extensive SQL query API. We've seen a few bits and pieces of this in the previous episodes, but our queries so far have been relatively simple.

Let's see how we go with something more complex.


class ArticleRepo < ROM::Repository
  struct_namespace Entities

  def latest
    articles.to_a
  end
end

repo = ArticleRepo.new(rom)

Today we'll be playing with queries right here in our repository. This will help us explore the API and iterate more quickly.

In a real app, any complex or meaningful query logic is best pushed back into relation classes, which make that logic available for reuse, as well as keeping our repository tidy and easy to understand at a glance.


So let's start by looking at how we can select the columns we want returned from our queries.


For this we can use the select method and pass it a simple list of column names.


class ArticleRepo < ROM::Repository
  struct_namespace Entities

  def latest
    articles
      .select(:id, :title)
      .to_a
  end
end

repo = ArticleRepo.new(rom)

Now when we query for the latest articles, we'll see result structs with just the attributes corresponding to those columns.

rom builds these structs intelligently, generating different classes, with different sets of attributes, based on what we select in our queries.


class ArticleRepo < ROM::Repository
  struct_namespace Entities

  def latest
    articles
      .select(:id, :title)
      .to_a
  end
end

repo = ArticleRepo.new(rom)
repo.latest
# => [#<Entities::Article id=1 title="Together breakfast">,
#     #<Entities::Article id=2 title="Cat fingers">]

Apart from a list of literal names, we can also pass attribute objects to select.

We access these attribute objects off the relation, using their names. These attributes are all part of the relation's schema, and in this case they correspond to the columns in the database table.


class ArticleRepo < ROM::Repository
  struct_namespace Entities

  def latest
    articles
      .select(articles[:id], articles[:title])
      .to_a
  end
end

repo = ArticleRepo.new(rom)
repo.latest
# => [#<Entities::Article id=1 title="Together breakfast">,
#     #<Entities::Article id=2 title="Cat fingers">]

The select method also gives us a block form, along with a DSL for accessing those same attribute object directly by their names.


class ArticleRepo < ROM::Repository
  struct_namespace Entities

  def latest
    articles
      .select { [id, title] }
      .to_a
  end
end

repo = ArticleRepo.new(rom)
repo.latest
# => [#<Entities::Article id=1 title="Together breakfast">,
#     #<Entities::Article id=2 title="Cat fingers">]

Like everything else in rom, these attribute objects carry their own helpful API.

We can use it here if we want to select columns with name aliases:


class ArticleRepo < ROM::Repository
  struct_namespace Entities

  def latest
    articles
      .select { [id, title.as(:heading)] }
      .to_a
  end
end

repo = ArticleRepo.new(rom)
repo.latest
# => [#<Entities::Article id=1 heading="Together breakfast">,
#     #<Entities::Article id=2 heading="Cat fingers">]

Now when we look at our article structs, we'll see the title attributes have been renamed to heading, to match the column alias we specified.


The block form of select is useful because it also allows us to natively call SQL functions.

To do this, we chain the function call off its expected data type. We can even pass the attribute objects as function arguments.


class ArticleRepo < ROM::Repository
  struct_namespace Entities

  def latest
    articles
      .select { [id, string::concat(title, ': ',  subtitle).as(:full_title)] }
      .to_a
  end
end

repo = ArticleRepo.new(rom)
repo.latest
# => [#<Entities::Article id=1 full_title="Together breakfast: I made you to bring us together!">,
#     #<Entities::Article id=2 full_title="Cat fingers: Biorhythms, yo">]

The Ruby syntax here might seem a little unusual. That double colon is functionally equivalent to the regular dot we usually place before method calls.

We're following the rom convention here and intentionally using this colon syntax for the SQL function. It makes the SQL function stand out, and appear distinct from any other regular Ruby method calls we might use here.


Now let's move from selecting columns, to building restrictions, using the where method.

where gives us a similar block DSL that allows us to leverage attribute objects to build more advanced restrictions.


class ArticleRepo < ROM::Repository
  struct_namespace Entities

  def latest
    articles
      .select { [id, title] }
      .where { }
      .to_a
  end
end

To start with, we can use operators like SQL's "LIKE", if we wanted to do some simple matching:


class ArticleRepo < ROM::Repository
  struct_namespace Entities

  def latest
    articles
      .select { [id, title] }
      .where { title.like('%breakfast%') }
      .to_a
  end
end

repo = ArticleRepo.new(rom)
repo.latest
# => [#<Entities::Article id=1 title="Together breakfast">]

We can also use logical operators to build out more complex conditions:


class ArticleRepo < ROM::Repository
  struct_namespace Entities

  def latest
    articles
      .select { [id, title] }
      .where { title.like('%breakfast%') | (id > 1) }
      .to_a
  end
end

repo = ArticleRepo.new(rom)
repo.latest
# => [#<Entities::Article id=1 title="Together breakfast">,
#     #<Entities::Article id=2 title="Cat fingers">]

The pipe symbol here corresponds to a "WHERE" clause using the "OR" operator between these 2 conditions.

This block isn't immediately evaluating these conditions, either. When we work with rom we compose our queries over multiple steps, so this block just builds up a query expression that will ultimately be used when we materialize the relation and run the query.


OK. Let's move onto another aspect of common SQL queries: joins.


Keeping with the theme, rom offers a dedicated API for joins, and it lets us build joins by passing around native rom objects, this time whole relation objects.


Since our articles relation has a "belongs_to author" association defined, we can join articles to authors simply by calling join and passing the authors relation object:


class ArticleRepo < ROM::Repository
  struct_namespace Entities

  def latest
    articles
      .select { [id, title] }
      .join(authors)
      .to_a
  end
end

repo = ArticleRepo.new(rom)
repo.latest

We can then select extra columns using attributes on the authors relation. In this case we'll grab author name.


class ArticleRepo < ROM::Repository
  struct_namespace Entities

  def latest
    articles
      .select { [id, title, authors[:name].as(:author_name)] }
      .join(authors)
      .select_append(authors[:name].as(:author_name))
      .to_a
  end
end

repo = ArticleRepo.new(rom)
repo.latest

And there they are:


class ArticleRepo < ROM::Repository
  struct_namespace Entities

  def latest
    articles
      .select { [id, title, relations[:authors][:name].as(:author_name)] }
      .join(authors)
      .to_a
  end
end

repo = ArticleRepo.new(rom)
repo.latest
# => [#<Entities::Article id=1 title="Together breakfast" author_name="Rebecca Sugar">,
#     #<Entities::Article id=2 title="Cat fingers" author_name="Kat Morris">]

Using this join, we could fetch both article and author information in a single query, and make the author data part of our article struct.


In other cases, we may want to keep the associated records distinct. Let's say our articles have a "has_many" relationship to comments.

If we wanted to prepare a listing of articles along with their comments, a combine would make more sense:


class ArticleRepo < ROM::Repository
  struct_namespace Entities

  def latest
    articles
      .select { [id, title] }
      .combine(:comments)
      .to_a
  end
end

repo = ArticleRepo.new(rom)
repo.latest
# => [#<Entities::Article id=1 title="Together breakfast" comments=[
#       #<Entities::Comment id=1 article_id=1 name="Garnet" body="I have to burn this too." created_at=2018-10-12 00:00:00 +1100>,
#       #<Entities::Comment id=2 article_id=1 name="Steven" body="Noo, my apps!" created_at=2018-10-13 00:00:00 +1100>,
#       #<Entities::Comment id=3 article_id=1 name="Pearl" body="Care to explain this sword?" created_at=2018-10-14 00:00:00 +1100>,
#       #<Entities::Comment id=4 article_id=1 name="Amythest" body="Alright, snacks!" created_at=2018-10-15 00:00:00 +1100>
#     ]>,
#     #<Entities::Article id=2 title="Cat fingers" comments=[]>]

These results are actually built across 2 queries, first for the articles, then for the comments that belong to the articles, which are then combined in memory to form these aggregate structs.

In ActiveRecord, this technique is referred to as "eager loading".


But what if we wanted the comments to be fetched in a different order? For this rom gives us adjusted combines:


After we combine, we can now specify which combine we want to adjust...


class ArticleRepo < ROM::Repository
  struct_namespace Entities

  def latest
    articles
      .select { [id, title] }
      .combine(:comments)
      .node(:comments)
      .to_a
  end
end

repo = ArticleRepo.new(rom)
repo.latest

And then inside the block we have a full relation object that we can use to adjust that particular query. So here we can order our comments by creation date descending.


class ArticleRepo < ROM::Repository
  struct_namespace Entities

  def latest
    articles
      .select { [id, title] }
      .combine(:comments)
      .node(:comments) { |comments| comments.order { created_at.desc } }
      .to_a
  end
end

repo = ArticleRepo.new(rom)
repo.latest

And now our results are adjusted as we expect!


class ArticleRepo < ROM::Repository
  struct_namespace Entities

  def latest
    articles
      .select { [id, title] }
      .combine(:comments)
      .node(:comments) { |comments| comments.order { created_at.desc } }
      .to_a
  end
end

repo = ArticleRepo.new(rom)
repo.latest
# => [#<Entities::Article id=1 title="Together breakfast" comments=[
#       #<Entities::Comment id=4 article_id=1 name="Amythest" body="Alright, snacks!" created_at=created_at=2018-10-15 00:00:00 +1100>,
#       #<Entities::Comment id=3 article_id=1 name="Pearl" body="Care to explain this sword?" created_at=2018-10-14 00:00:00 +1100>>,
#       #<Entities::Comment id=2 article_id=1 name="Steven" body="Noo, my apps!" created_at=2018-10-13 00:00:00 +1100>,
#       #<Entities::Comment id=1 article_id=1 name="Garnet" body="I have to burn this too." created_at=2018-10-12 00:00:00 +1100>
#     ]>,
#     #<Entities::Article id=2 title="Cat fingers" comments=[]>]

Now what if we always want comments ordered like this, without having to worry about specifying it manually for each combine?


For this we can specify a custom view when we define the association.


Let's look at our articles relation now. Here we have our "has_many comments" association:


module Relations
  class Articles < ROM::Relation[:sql]
    schema :articles, infer: true do
      associations do
        belongs_to :author
        has_many :comments
      end
    end
  end
end

What we can do here is specify that the comments should use an "ordered" view


module Relations
  class Articles < ROM::Relation[:sql]
    schema :articles, infer: true do
      associations do
        belongs_to :author
        has_many :comments, view: :ordered
      end
    end
  end
end

When this association is loaded, it looks for a method on the comments relation matching the view name, and runs that.


So let's go over to the comments relation...


module Relations
  class Comments < ROM::Relation[:sql]
    schema :comments, infer: true
  end
end

And implement this view:


module Relations
  class Comments < ROM::Relation[:sql]
    schema :comments, infer: true

    def ordered
      order { created_at.desc }
    end
  end
end

Now we can go back to our latest articles method...


class ArticleRepo < ROM::Repository
  struct_namespace Entities

  def latest
    articles
      .select { [id, title] }
      .combine(:comments)
      .node(:comments) { |comments| comments.order { created_at.desc } }
      .to_a
  end
end

repo = ArticleRepo.new(rom)
repo.latest

Clear out the manual ordering...


class ArticleRepo < ROM::Repository
  struct_namespace Entities

  def latest
    articles
      .select { [id, title] }
      .combine(:comments)
      .to_a
  end
end

repo = ArticleRepo.new(rom)
repo.latest

And our comments remain ordered as we like, thanks to the association view.


class ArticleRepo < ROM::Repository
  struct_namespace Entities

  def latest
    articles
      .select { [id, title] }
      .combine(:comments)
      .node(:comments) { |comments| comments.limit(2) }
      .to_a
  end
end

repo = ArticleRepo.new(rom)
repo.latest
# => [#<Entities::Article id=1 title="Together breakfast" comments=[
#       #<Entities::Comment id=4 article_id=1 name="Amythest" body="Alright, snacks!" created_at=created_at=2018-10-15 00:00:00 +1100>,
#       #<Entities::Comment id=3 article_id=1 name="Pearl" body="Care to explain this sword?" created_at=2018-10-14 00:00:00 +1100>>,
#       #<Entities::Comment id=2 article_id=1 name="Steven" body="Noo, my apps!" created_at=2018-10-13 00:00:00 +1100>,
#       #<Entities::Comment id=1 article_id=1 name="Garnet" body="I have to burn this too." created_at=2018-10-12 00:00:00 +1100>
#     ]>,
#     #<Entities::Article id=2 title="Cat fingers" comments=[]>]

So that's a look at some of the things you might expect to be doing with rom in a larger, working application.


# Handling complexity with rom-rb

What we've seen here is that rom allows us to leverage the full power of the database. It gives us APIs and dedicated abstractions to devise any kind of query we could imagine.


# Handling complexity with rom-rb

- Leverage the full power of the database

This lets us push more processing work back into the database, where it can often do things much faster than we could in Ruby, while also improving overall data integrity.


# Handling complexity with rom-rb

- Leverage the full power of the database
- Improve performance and data integrity

What's more, rom allows us to build those more complex queries without breaking away from the approach we take for all our other queries, even the simplest ones!

rom keeps the simple things easy, and it makes the hard things possible, all while maintaining a consistent approach across the board.


# Handling complexity with rom-rb

- Leverage the full power of the database
- Improve performance and data integrity
- A consistent approach for both simple and complex persistence needs

And this approach is one that makes it easy to keep a clean separation between our application's domain layer and persistence layer.

With a clearly separated persistence layer, we have a dedicated place to throw all the nitty-gritty query details, like the ones we explored today.

Then, when we're working within the domain layer, we're freed up to focus on business logic first and foremost, and not have those persistence details muddy things up and bog us down.

And that, is the recipe for an app that's easy to maintain.


For me, personally, adopting rom-rb in my Ruby applications has been one of the best things I've done.

Not only have I learnt how to really put databases to work, I feel much more confident in the code I'm shipping.

I'd thoroughly recommend you give it a try too.


So that's it for this series. I hope you enjoyed it and learnt a thing or two.

Thanks so much for spending all this time with me, and as always, happy hacking!

Responses