Sequel in conjunction with ActiveRecord any gotchas?

Question

I'm considering using Sequel for some of my hairier SQL that I find too hard to craft in Active Record.

Are there any things I need to be aware of when using Sequel and ActiveRecord on the same project? (Besides the obvious ones like no AR validations in sequel etc...)

Viccora

Answer 1

Disclaimer: I'm the Sequel maintainer.

Sequel is easy to use along side of or instead of ActiveRecord when using Rails. You do have to setup the database connection manually, but other than that, the usage is similar. Your Sequel model files go in app/models and work similarly to ActiveRecord models.

Setting up the database connections isn't tedious, it's generally one line in environment.rb to require sequel, and a line in each environment file (development.rb, test.rb, production.rb) to do something like:

DB = Sequel.connect(...)

So it's only tedious if you consider 4 lines of setup code tedious.

Using raw SQL generally isn't a problem unless you are targeting multiple databases. The main reason to avoid it is the increased verbosity. Sequel supports using raw SQL at least as easily as ActiveRecord, but the times where you need to use raw SQL are generally fairly rare in Sequel.

BTW, Sequel ships with multiple validation plugins. The validation_class_methods plugin is similar to ActiveRecord validations, using class methods. The validation_helpers plugin has a simpler implementation using instance level methods, but both can do roughly the same thing.

Finally, I'll say that if you already have working ActiveRecord code that does what you want, it's probably not worth the effort to port the code to Sequel unless you plan on adding features.

Selie

I hope I didn't come across as anti-Sequel, btw: it's actually my preferred option outside of Rails. I don't see a whole lot of benefit in using the two libs together, just to get a little extra platform independence even when the SQL needed gets complex. I think it's overkill, especially if the developer already knows what he wants to write. Reworking such custom SQL certainly wasn't the biggest database-related issue I faced when I had to port from MySQL to Oracle! – Could

Any hack-tip on how to get a sequel db object from an active record connection? – Tush

Answer 2

Personally, I wouldn't do it. Just managing connection more-or-less by hand would be tedious, for a start. I'd be more inclined, if I felt Sequel was the stronger option, to hold off for Rails 3.0 (or perhaps start developing against Edge Rails) where it should be fairly easy to switch ORMs, if Yehuda and co are doing their stuff right. A lot more Merb-like than now, at least.

This was DHH's take on the subject (I'm not saying it should be taken as gospel truth, mind, but it is, so to speak, from the horse's mouth):

But Isn’t Sql Dirty?

Ever since programmers started to layer object-oriented systems on top of relational databases, they’ve struggled with the question of how deep to run the abstraction. Some object-relational mappers seek to eradicate the use of SQL entirely, striving for object oriented purity by forcing all queries through another OO layer.

Active Record does not. It was built upon the notion that SQL is neither dirty nor bad, just verbose in the trivial cases. The focus is on removing the need to deal with the verbosity in those trivial cases but keeping the expressiveness around for hard queries – the type SQL was created to deal with elegantly.

Therefore, you shouldn’t feel guilty when you use find_by_sql() to handle either performance bottlenecks or hard queries. Start out using the object-oriented interface for productivity and pleasure, and the dip beneath the surface for a close-to-the-metal experience when you need to.

(Quote was found here, original text is on p334 of AWDRWR, the "hammock" book).

I think that's reasonable.

Are we talking about something that find_by_sql can't handle? Or are we talking about complex non-SELECT stuff that execute can't deal with?

Any examples we could look at?

Could

All of the stuff I am looking at execute and find_by_sql and sanitize_sql_array can deal with. I am just a little uncomfortable having hardcoded SQL, which is a really odd, since I am a pretty strong SQL developer. I like the LINQ like qualities that sequel seems to exhibit. But you may be right, perhaps I should just learn to accept getting dirty with sql in a rails app when performance is critical. – Viccora

This was an interesting answer. I've been looking for options and this conversation has been useful. – Annunciata

Well, with Sequel you're free to craft your own SQL statements, too. And it wasn't built on the notion that SQL is dirty or bad either. But it's useful in this case because it's more modular, and you can more easily use it to build queries that are partly built by hand and partly built with a powerful tool. – Simmie

The DHH answer is shortsighted. The real problem with SQL is with building up queries dynamically & incrementally (as we do with ActiveRecord scopes). Concatenated strings just aren't very good at that. – Polemics