[mikekchar]

Just a disclaimer, I work with Rails every day, but I don't really like it all that much and I really don't like ActiveRecord. We have Rails legacy apps and I maintain one of them. I'd still use Rails for some things, but it I'm pretty picky about what I think it's good for.

Having said that, it makes me a bit sad that there are quite a few developers who actively avoid SQL. Granted the syntax is absolutely awful, but it is a very useful and powerful language. The concepts behind relational databases are embraced by SQL in ways that I can't imagine in any other language (I'm sure there must be other good relational query languages somewhere, but I've never been exposed to them). Honestly, if I have developers working with a relational DB doing anything even a little bit complex I want them to not not think twice or even flinch to use or read it (and I say that not really being all that accomplished with the language myself).

Rails has some good niches where it is very well suited. ActiveRecord is OK if you have a very particular data model. However, as they say, if a hammer is your only tool, every problem starts to look like a nail. You may find that if you approach the problem differently, you will find reasons why people choose not to use very simplified ORMs.

[vinceguidry]

As a counterpoint, I love ActiveRecord. It's a Swiss Army knife that's got pretty much everything you need, you just need to find the right tool and use it.

What ActiveRecord isn't is discoverable. Which is to an extent understandable. Its domain is literally anything you could express in SQL. But you really can do anything you want with it, you just have to find the right abstraction. A tool I use a lot is to .to_sql which will show you the compiled SQL fragment in a debugging session. You can compose with bare SQL fragments and ActiveRecord even includes an intermediate library so that you can work at the relational algebra level if you want.

It's not that ActiveRecord forces you to work a certain way. It just doesn't advertise all its features.

[Dangeranger]

Long ago I liked ActiveRecord(AR), then I worked at a company that did large aggregations in SQL and found it to be severely limited in working with complex queries which use many JOIN statement and sub-queries. During that time I came to enjoy working with the Sequel[0] gem, and its fantastic documentation. Now, after working with Ecto[1] in Elixir, I've found that the ROM[2] builder pattern is a better approach to abstracting SQL and mapping the results to Ruby objects. It's much cleaner and more maintainable than a long mess of AR queries.

Ryan Bigg wrote a great book about breaking away from AR in Rails called Exploding Rails[3]. It's a good read.

[0] http://sequel.jeremyevans.net/

[1] https://github.com/elixir-ecto/ecto

[2] https://rom-rb.org/5.0/learn/introduction/active-record/

[3] https://leanpub.com/explodingrails

[JohnBooty]

From what I've seen of Sequel is looks awesome and I've been dying for an excuse to use it.

    found [ActiveRecord] to be severely limited in 
    working with complex queries which use many 
    JOIN statement and sub-queries

I'd humbly suggest that this is not a flaw of ActiveRecord whatsoever! I think this is AR working exactly as designed.

I would certainly agree that SQL > AR once your queries grow past a certain (fairly low) complexity threshold.

But, AR (wisely) doesn't try to be a complete replacement for SQL. It very happily (and even elegantly, I might say?) lets you use raw SQL pretty much any time you, the developer, feel it's more convenient/productive. That principle is pretty explicitly baked into AR.

Many ORM frameworks don't make this easy at all, whereas with AR it's very painless.

I have a lot of beef with AR but I think this is one of its strong points.

[Dangeranger]

Yes, I agree. For simple to low complexity queries AR is hard to beat, especially if your SQL knowledge is limited, and it allows for arbitrary SQL statements as string arguments.

My biggest issue with AR is that the arbitrary SQL cannot be combined or reused in a way which leads to compositional queries. An example would be to break up a large SQL statement into several smaller statements and then recombine them in different ways, safely.

What I found with Ecto and ROM is that, while simple to low complexity SQL was about the same as AR, maybe 10% harder to understand, complex to very complex statements were about equivalent to their SQL counterparts and could be composed together. That was a major win over AR for non-trivial queries.

[vinceguidry]

> arbitrary SQL cannot be combined or reused in a way which leads to compositional queries

You can do this, look into arel. Here's a blog post:

https://blog.codeship.com/creating-advanced-active-record-db...

[Dangeranger]

Yes, I know about Arel, and I’ve used it a few times to do hairy queries, but honestly it felt like swimming against the current and I wouldn’t recommend anyone write Arel queries by hand.

[JohnBooty]

Yeah I think the easy composability of AR is probably its biggest asset.

As another commenter noted, yes you can do composable AR/Arel with raw SQL.... but like you said, it does get hairy pretty fast.

[yebyen]

Wow, this blew up! Thanks for all the information!

I've used sequel before and agree, it's closer to the SQL and does the job well. ROM looks excellent. I'm just reading down the page you linked, and I love every word. I can say I've had all of these problems with ActiveRecord, and ROM's answers to them all look great!

[mikekchar]

Hmm... I see your point. There is always the confusion between ActiveRecord the software and "Active Record" the pattern. It's really the latter that I dislike. I've used AR-the-software to build my own relational object mapping with scopes and enjoyed it quite a lot. But there was lots of SQL in that code :-)

[JohnBooty]

I agree! SQL-avoidant developers are something we actively try to screen out in our interviews. (Being too gung-ho about using raw SQL -- something I've been accused of --is bad too, but at least it's not a path of willful ignorance)

    Granted [SQL's] syntax is absolutely awful, but 
    it is a very useful and powerful language. The 
    concepts behind relational databases are embraced 
    by SQL in ways that I can't imagine in any other 
    language (I'm sure there must be other good relational 
    query languages somewhere, but I've never been exposed 
    to them)

Is SQL's syntax really that awful? I think any language that maps fairly closely to relational algebra will wind up looking similar, and a lot of frustration that we feel toward SQL's syntax is because sometimes we want to express procedural concepts in a language that is based on relational algebra.

It's somewhat significant, I think, that when Microsoft came up with LINQ (sort of a language- and backend- agnostic query language) they basically settled on something that looks like SQL but puts the FROM clause first, mainly so the editor can give you typing hints.

I'm not saying LINQ was a rousing success, but that was a pretty major greenfield MS devtools initiative and they have some pretty smart language/compiler people, so it's interesting that they settled on something not too different from good ol' SQL.

[mikekchar]

I probably shouldn't have said the syntax is awful because as you say, I've never seen anything better. But I just think that SQL is this nice composable structure but it seems needlessly awkward to write from my naive point of view. I keep thinking that something more lisp-like would be nicer, but perhaps it wouldn't work. Maybe it's something I can try as an experiment some day :-)

[JohnBooty]

You'll be a hero to millions!