Mapping Database Queries to Crystal Classes With crystal-db and Lucky

Sometimes we need to get a couple columns from our database, or make complex queries and return many columns that don't fit into our models. In these cases we want the framework we use to be flexible enough to allow such queries and make it easy to use the results in our app. Crystal and Lucky let us do just that.

In this post we'll look at how to use crystal-db's DB.mapping macro to map database queries to generic Crystal classes. Then we'll quickly look at how Lucky uses DB.mapping internally.

In this article we'll be using Lucky to make the database queries, but remember that crystal-db can be used alone or with any framework.

Setup

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

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

The Query

For this example we'll map this fairly simple query which fetches posts, joins users on user_id and return the user's name and email as a JSON object. Since Lucky uses the crystal-pg Postgresql driver, we can use DB.mapping to easily parse json objects from our query into JSON::Any.

SELECT
  posts.id,
  posts.title,
  ('PREFIX: ' || posts.content) as custom_key, -- custom key for fun
  json_build_object(
    'name', users.name,
    'email', users.email
  ) as author
FROM posts
JOIN users
ON users.id = posts.user_id;

The Class

crystal-db returns the results of the query as DB::ResultSet which isn't directly useful for us. So lets create the class that the result will be mapped to, and we can use the DB.mapping to handle the dirty work.

class CustomPost
  DB.mapping({
    id: Int32,
    title: String,
    content: {
        type: String,
        nilable: false,
        key: "custom_key"
    },
    author: JSON::Any
  })
end

Essentially the mapping macro will create a constructor that accepts a DB::ResultSet and initializes this class for us, as well as a from_rs class method for intializing multiple results. It would expand to something like this.

class CustomPost
  def initialize(%rs : ::DB::ResultSet)
    # ...lots of stuff here
  end
  def self.from_rs(rs : ::DB::ResultSet)
    objs = Array(self).new
    rs.each do
      objs << self.new(rs)
    end
    objs
  ensure
    rs.close
  end
end

Hooking It All Up

Now let's write a spec to ensure everything is working as planned.

# spec/mapping_spec.cr
require "./spec_helper"
describe App do
  describe "CustomPost" do
    it "maps query to class" do
      user = UserBox.new.name("Mikias").create
      post = PostBox.new
                    .user_id(user.id)
                    .title("DB mapping")
                    .content("Post content")
                    .create
      sql = <<-SQL
        SELECT
          posts.id,
          posts.title,
          ('PREFIX: ' || posts.content) as custom_key,
          json_build_object(
            'name', users.name,
            'email', users.email
          ) as author
        FROM posts
        JOIN users
        ON users.id = posts.user_id;
      SQL
      posts = LuckyRecord::Repo.run do |db|
        db.query_all sql, as: CustomPost
      end
      posts.size.should eq 1
      posts.first.title.should eq post.title
      posts.first.content.should eq "PREFIX: " + post.content
      posts.first.author["name"].should eq user.name
    end
  end
end
class CustomPost
  DB.mapping({
    id: Int32,
    title: String,
    content: {
        type: String,
        nilable: false,
        key: "custom_key"
    },
    author: JSON::Any
  })
end

We can run the tests with lucky spec spec/mapping_spec and... green! Nice.

Lucky Models

This is actually very similar to how LuckyRecord sets up it's database mapping. For example if you have a User model like this.

class User < BaseModel
  table :users do
    column name : String
    column email : String
    column encrypted_password : String
  end
end

Calls to the column method will add the name and type of each column to a FIELDS constant.

macro column(type_declaration, autogenerated = false)
  ... # check type_declaration's data_type and if it is nilable
  {% FIELDS << {name: type_declaration.var, type: data_type, nilable: nilable.id, autogenerated: autogenerated} %}
end

The table macro will setup the model, including calling the setup_db_mapping macro which will call DB::mapping by iterating over the FIELDS.

macro setup_db_mapping
  DB.mapping({
    {% for field in FIELDS %}
      {{field[:name]}}: {
        {% if field[:type] == Float64.id %}
          type: PG::Numeric,
          convertor: Float64Convertor,
        {% else %}
          type: {{field[:type]}}::Lucky::ColumnType,
        {% end %}
        nilable: {{field[:nilable]}},
      },
    {% end %}
  })
end

Just like that each of your Lucky models can now be instantiated from DB::ResultSet and have a from_rs method that can be called by your queries. Pretty simple right?

Join Us

I hope you enjoyed this tutorial and found it useful. Join us on the Lucky gitter channel to stay up to date on the framework or checkout the docs for more information on how to bring your app idea to life with Lucky.