Mapping Database Queries to Crystal Classes With crystal-db and Lucky
Tags: lucky, crystal, database, query
Published: 2018-07-26 05:01:07

---
title: Mapping Database Queries to Crystal Classes With crystal-db and Lucky
published: true
description: Mapping Database Queries to Crystal Classes With crystal-db and Lucky
tags: lucky, crystal, database, query
---
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](http://crystal-lang.github.io/crystal-db/api/0.4.3/DB.html) [DB.mapping](https://github.com/crystal-lang/crystal-db/blob/master/src/db/mapping.cr) 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](http://crystal-lang.github.io/crystal-db/api/0.4.3/DB.html) 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](https://github.com/will/crystal-pg) Postgresql driver, we can use `DB.mapping` to easily parse json objects from our query into `JSON::Any`.

```sql
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](https://github.com/crystal-lang/crystal-db/blob/master/src/db/mapping.cr) to handle the dirty work.

```ruby
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.

```ruby
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.

```ruby
# 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](https://github.com/luckyframework/lucky_record/blob/189dd6418c3bd6375157ed585e0c099920f3975e/src/lucky_record/model.cr#L87-L101). For example if you have a User model like this.

```ruby
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. 

```ruby
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`.

```ruby
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](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.