I’m looking for a ORM to use in a new project, looking at the current choices I have the impression that the community isn’t very well served in this area.
Clear - Seems to have a nice API but doesn’t compile, neither master branch or any recent tag I tested.
Avram - Seems well maintained, but the API with save operations, query objects, no “dirty” (in active record terms) models doesn’t match my taste .
crecto - Last released in 2021, it was even removed from Amber.
These days I find DB::Serializable - db 0.13.1 to be totally sufficient for most of what I needed to do. Tho I guess it really comes down to what features are “required.” I.e. if you want something fully featured like AR then you’re kinda out of luck I think. But if you just want a simple API to allow doing some queries, using DB shard directly with DB::Serializable works quite well.
Great article, I could imagine everything the guys suffered until come up with the current solution, that IMO looks interesting, I’m just afraid about a possible “explosion” of return types if the project start to have many queries, but this can be worked on and as good side the returned type will be more programmer friendly, just with the data that is really there for you to use.
Given the not so great options, I think Kemal + crystal-db will be the answer.
I was looking for an ORM because they give a fast start up, you create a model with few lines and done, a lot of basic queries is ready for you. However I see that over time the codebase gets messy and doing a lot of unneeded queries, I see that a lot at work in a rails application where the misuse of ActiveRecord translates to a lot of useless queries, or code that just run not too slow just because of ActiveRecord query cache.
I’ll also mention Interro as a useful alternative. It’s Postgres-only, but that’s mostly due to the differences in parameterized queries between Postgres and other RDBMSes. Unlike most ORMs, it doesn’t use the Active Record pattern for querying and actually encourages immutable models to avoid the false sense of security that often comes with ORMs performing dirty tracking.
For the most part, it’s really just a convenience layer on top of DB designed to help you with the most common things and get out of your way otherwise:
SQL generation via chainable methods like ActiveRecord
Your model’s instance variables are the only columns that are fetched from the DB by default
A type-safety layer — inside your query methods you have full control but you expose a type-safe interface so the rest of your application can’t pass arbitrary columns and values unless you’re into that sort of thing
If you’re using DB replication, SELECT queries are automatically sent to replicas for easy horizontal scalability
Explicit transactions are routed to the primary, so if you need to be able to read your own writes, you can do it inside a transaction
Models are just DB::Serializable
Interro::QueryBuilder#each doesn’t load the full result set so you never have to reach for hacks like ActiveRecord’s find_each (where you can’t use LIMIT or ORDER BY clauses) because you get that for free — the currently yielded row is always the only result row in memory.
Validations for creating/updating records
If you need to do nontrivial things and the SQL generator would just get in your way, you can use Interro::Query instead and get all the same benefits on a SQL query you wrote.
Example
require "interro"
Interro.config do |c|
c.db = DB.open("postgres:///")
end
abstract struct Query(T) < Interro::QueryBuilder(T)
# Your app-wide query methods go here
end
# Using structs for immutable models
struct User
include DB::Serializable
getter id : UUID
getter email : String
getter name : String
getter role : Role
getter created_at : Time
getter updated_at : Time
enum Role
Member
Admin
end
end
struct UserQuery < Query(User)
table "users"
def find(id : UUID) : User?
where(id: id).first?
end
def find(*, email : String) : User?
where(email: email).first?
end
def with_role(role : User::Role) : self
where role: role.value
end
def set_name(user : User, name : String) : User
Result(User).new
.validate_presence(name: name)
.validate_size("name", name, 2.., "characters") # names must be >= 2 characters
.valid { where(id: user.id).update(name: name).first }
end
def create(
*,
email : String,
name : String,
role : Role = :member,
) : User | Failure
# Inheriting from Interro::QueryBuilder gives us Interro::Validations, including
# `Result` and `Failure`
Result(User).new
.validate_presence(email: email, name: name)
.validate_format(email, /\w@\w/, failure_message: "email must be in the format `user@domain.tld`")
.validate_size("name", name, 2.., "characters") # names must be >= 2 characters
.validate_uniqueness("email") { find(email: email) }
.valid do
insert email: email, name: name, role: role.value
end
end
end
# INSERT query goes to the primary
case user = UserQuery.new.create(email: "user@example.com", name: "Test User")
in User
# success
in Failure
# do something with user.errors
end
# SELECT query goes to a replica
UserQuery.new.each do |user|
# do something with the `User` instance
end
# UPDATE goes to the primary
# Note also that we're returning a new instance of User
user = UserQuery.new.set_name(user, "New Name")
I used avram for my project, if don’t mind it only support pg, the new pattern is not bad anyway.
One more concern need to consider, because avram build SQL with strong typing support, it create many methods use macro based on column name, It can guarantee that many runtime errors will not occur, but this add many restrictions. unless avram support that pattern, othersize, you can’t write SQL yourself use raw format. e.g. you can’t build JOINS SQL more than one condition, like following:
universities left join foos on universities.id = foos.university_id AND foos.user_id = ?
I once wanted to contribute to this code, but I got delayed because it involves a lot of macros, making it difficult to understand, and I was delayed because of other matters.
This is legitimately a hard problem for ORMs. You can provide type safety in the query DSL (type safety can come in multiple forms here and can even apply to dynamic languages like Ruby) or you can allow arbitrary SQL columns/expressions to be used, but getting both is a huge challenge.
This is actually the exact reason Interro allows arbitrary SQL inside query objects, but disallows it outside of them. This way, if you change something about the schema, only the query objects need to be updated. The rest of your application doesn’t need to know about it.
That JOIN clause with Interro might look something like this:
struct UniversityQuery < Query(University)
table "universities"
def with_user(user : User)
self
# Avoid string concatenation without coupling to a table this
# object doesn't own by passing the join table name and giving
# it a unique alias that only this method knows about.
.left_join(FooQuery.new.sql_table_name, as: "university_with_user", on: "universities.id = university_with_user.university_id")
.where("university_with_user.user_id": user.id)
end
end
I keep meaning to add parameterized-query support to the *_join methods so it can just go in there, but the where clause is a decent tradeoff. When that’s added, that with_user method could just call left_join:
def with_user(user : User)
left_join FooQuery.new.sql_table_name,
as: "university_with_user",
on: <<-SQL,
universities.id = university_with_user.university_id
AND university_with_user.user_id = $1
SQL
args: [user.id]
end
.