Rails query caching with PlanetScale Boost

How to use PlanetScale Boost in a Rails application

To enable caching for our connection, you need to set the session variable boost_query_caching to true. This can be done in your database.yml

<<: *default
username: <%= Rails.application.credentials.planetscale.fetch(:username) %>
password: <%= Rails.application.credentials.planetscale.fetch(:password) %>
database: <%= Rails.application.credentials.planetscale.fetch(:database) %>
host: <%= Rails.application.credentials.planetscale.fetch(:host) %>
ssl_mode: verify_identity
sslca: "/etc/ssl/cert.pem"
variables:
  boost_cached_queries: true

This enables caching for all of your database connections.

Star.joins(repository: :tags).where(spammy: false).where(tags: {name: "trending"}).group(:repository).count

#  Star Count (2.1ms)  SELECT COUNT(*) AS `count_all`, `stars`.`repository_id` AS `stars_repository_id` FROM `stars` INNER JOIN `repositories` ON `repositories`.`id` = `stars`.`repository_id` INNER JOIN `repository_tags` ON `repository_tags`.`repository_id` = `repositories`.`id` INNER JOIN `tags` ON `tags`.`id` = `repository_tags`.`tag_id` WHERE `stars`.`spammy` = FALSE AND `tags`.`name` = 'trending' GROUP BY `stars`.`repository_id`

This is the simplest way to get started using PlanetScale Boost. We recommend going a step further and setting up separate connections for your cached and un-cached queries. Doing this will allow you to be confident you're getting up-to-date data when reading your own writes.

PlanetScale Boost with multiple connections

Setting up multiple connections is our recommended way of using PlanetScale Boost in Rails. To do this, we will be using Rails multiple databases support built into ActiveRecord.

First, you will need to update your database.yml to let Rails know about the new connection.

production:
  primary:
    <<: *default
    username: <%= Rails.application.credentials.planetscale.fetch(:username) %>
    password: <%= Rails.application.credentials.planetscale.fetch(:password) %>
    database: <%= Rails.application.credentials.planetscale.fetch(:database) %>
    host: <%= Rails.application.credentials.planetscale.fetch(:host) %>
    ssl_mode: verify_identity
    sslca: "/etc/ssl/cert.pem"
  primary_with_caching:
    <<: *default
    username: <%= Rails.application.credentials.planetscale.fetch(:username) %>
    password: <%= Rails.application.credentials.planetscale.fetch(:password) %>
    database: <%= Rails.application.credentials.planetscale.fetch(:database) %>
    host: <%= Rails.application.credentials.planetscale.fetch(:host) %>
    ssl_mode: verify_identity
    sslca: "/etc/ssl/cert.pem"
    variables:
      boost_cached_queries: true

You now have two connections: primary and primary_with_caching. You will want to replicate these for all of your environments.

Notice in primary_with_caching you have set boost_cached_queries: true. Otherwise the connection details for each are identical.

Update ApplicationRecord

Now that you have multiple connections, the next step is defining the new role for ActiveRecord to connect to.

class ApplicationRecord < ActiveRecord::Base
  primary_abstract_class

  connects_to database: { writing: :primary, with_caching: :primary_with_caching }
end

Running cached queries

By default, Rails will always connect using the writing role. All of your existing queries will work as normal.

The new with_caching role will allow you to specifically use caching in your application when needed.

ActiveRecord::Base.connected_to(role: :with_caching) do
  Star.joins(repository: :tags).where(spammy: false).where(tags: {name: "trending"}).group(:repository).count
end

#  Star Count (2.1ms)  SELECT COUNT(*) AS `count_all`, `stars`.`repository_id` AS `stars_repository_id` FROM `stars` INNER JOIN `repositories` ON `repositories`.`id` = `stars`.`repository_id` INNER JOIN `repository_tags` ON `repository_tags`.`repository_id` = `repositories`.`id` INNER JOIN `tags` ON `tags`.`id` = `repository_tags`.`tag_id` WHERE `stars`.`spammy` = FALSE AND `tags`.`name` = 'trending' GROUP BY `stars`.`repository_id`