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.
Top comments (4)
Great post! I'm not using LuckyRecord in my app, but this might help with a few things I'm running in to.
Awesome, I'm glad it helped! What kind of issues are you running into? Maybe I can help.
I'm going to publish another post tomorrow about mapping materialized views which you might also find useful.
Issues I have are just slow load times. I just read your other post, and that also might help a ton! Thanks for putting these together.
No problem! I'm on the lookout for more post ideas so if you want me to help with anything just let me know :)