We can execute raw sql using Avram::Database
which gives us direct access to our app’s
database instance. The result
of the query will be a DB::ResultSet
which we will later map to classes that can be easily used in our app.
posts_result_set = AppDatabase.run do |db|
db.query_all "SELECT * FROM posts;"
end
Since we have direct access to the database instance, we can run whatever query we want as long as it’s valid sql for the postgresql driver that Lucky uses.
Let’s create a query with a non-trivial SELECT
statement that we can map to a Crystal class.
Note: This assumes we have post and user tables already created
sql = <<-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;
SQL
crystal-db comes with a powerful DB.mapping macro that makes it simple to map a database query to a class by defining the keys and types of each column.
Let’s create a ComplexPost
class in our models folder and define the database mapping.
# src/models/complex_post.cr
class ComplexPost
DB.mapping({
id: Int32,
title: String,
content: {
type: String,
nilable: false,
key: "custom_key"
},
author: JSON::Any
})
end
Now we can make our query and instantiate ComplexPosts
from the result easily using the as
method.
complex_posts = AppDatabase.run do |db|
db.query_all sql, as: ComplexPost
end
Believe it or not, that’s all it takes! complex_posts
is now of the type Array(ComplexPost)
and ready to be used in your templates or returned by your JSON api.