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.