How do we make this library more maintainable?

[rzane]

History

When I first started this project, I really just wanted to provide a more human-friendly syntax for writing queries with Arel. Arel is fantastic, but using it directly can look really daunting to those who aren't familiar with it.

One of the sources of inspiration was this talk. There's also a really great library that accompanies that talk.

I was also aware of the excellent Squeel library, which at the time was no longer being maintained. I thought it would be cool to build a really simple version of Squeel, hence the "baby" in the name. I figured that if I avoided touching the guts of Active Record, it'd be easier to maintain much of the value that Squeel delivered.

Well, time went on, feature requests came in and I kept adding features. In the process, this library became more and more deeply integrated into Active Record. I figured it could still be maintainable because I avoided most of the monkey patching that Active Record had been doing. I was wrong.

Problem

Most of this library's complexity comes from the need to figure out what alias Active Record is going to use for a table that has been joined more than once.

Here's an example of what Active Record does:

User.joins(recipes: {user: {recipes: :user}})

SELECT "users".*
FROM "users"
INNER JOIN "recipes" ON "recipes"."user_id" = "users"."id"
INNER JOIN "users" "users_recipes" ON "users_recipes"."id" = "recipes"."user_id"
INNER JOIN "recipes" "recipes_users" ON "recipes_users"."user_id" = "users_recipes"."id"
INNER JOIN "users" "users_recipes_2" ON "users_recipes_2"."id" = "recipes_users"."user_id"

So, if you said:

User.joins(recipes: {user: {recipes: :user}).where.has { recipes.user.recipes.user.id == 1 }

You'd expect the following where condition:

WHERE "users_recipes_2"."id" = 1

How does this work?

It creates a new ActiveRecord::Associations::JoinDependency, then sniffs at the join dependency to figure out what the alias name is. However, that's a deeply private API, that seems in pretty drastic and sneaky ways with every Active Record version (even in tiny versions).

How could we make this library more maintainable?

We'd need to stop sniffing the JoinDependency to figure out generated alias names. My recommendation would be to stop sniffing alias names all together.

I really don't believe this is the thing that makes this library useful. I believe the value of this library is that it can be used to perform complex SQL operations. I'd actually like to devote more effort to this goal. I want to build a library that makes it less painful to leverage the powerful features that our databases have to offer.

I want:

• JSON operations
• Inline if
• Case expressions
• Common table expressions
• Window functions
• Upserts
• Bulk updates
• Bulk deletes

What might that library look like?

Well, I created a first pass here. My main source of inspiration is Ecto. Ecto allows you to get really close to the metal, but also provides some high-level abstractions.

Here's the sort of feedback that I'd like so see:

• If this library doesn't infer aliases (aliases would need to be explicit), is that really a deal breaker?
• The API can really look however we want. This is just a first pass. Please share examples of SQL queries and how you'd like to write them in Ruby.
• I'd like to see complex queries that you're performing in your applications. How might these be made more readable/expressive/composable by using a Ruby DSL?

[marnen]

Please share examples of SQL queries and how you'd like to write them in Ruby.

I think the existing baby_squeel interface is mostly fine, and I’d like to keep it to the extent possible. As I’ve said elsewhere, the benefit of baby_squeel for me is that it exposes complex DB operations while still feeling like ActiveRecord. I don’t want to lose that.

My main source of inspiration is Ecto.

My recollection of Ecto from when I tried Phoenix (a few years ago, so maybe something has changed) is that I found it really hard to understand and work with, and that dealing with it was one of the things that drove me away from Phoenix. I don’t particularly want to use an Ecto clone in Rails.

That’s a matter of taste, though. More to the point is the fact that Ecto is designed very differently from ActiveRecord. I don’t want to have to abandon AR’s API in order to use an extensions library to it; at that point, I might just as well start with another ORM entirely.

I like Squeel and baby_squeel pretty much as they are, from an API point of view. What makes them special is their AR integration, and my goal in updating baby_squeel for Rails 6 compatibility would be to keep that key feature. I do understand that that’s also the feature that tends to break compatibility, but I don’t think it can be discarded without losing the gem’s raison d’être.

[rzane]

Just to be clear, it's still chainable:

User.query { select id, name }.query { where id > 5 }.where(name: "Whatever")

You're not losing any part of Active Record.

[marnen]

Good to know, although as the syntax diverges from that of core ActiveRecord, it stops feeling like it’s meant to be chained with ActiveRecord. That is, while I’m glad chaining works, to me it would feel slightly unnatural to use both this and ActiveRecord methods in the same query (at least I think so). With baby_squeel it does not, which is why (if both this and baby_squeel were available and supported current Rails) I’d use baby_squeel rather than this approach for most projects.

I appreciate your work on this, and it’s interesting to look at alternative design choices, but I don’t think this is a solution I would favor. (Others may respond differently, and they should have that choice.)

[kbighorse]

I support @rzane's proposed changes if it means baby_squeel lives on.