---
title: Squirrel - Natural Looking Queries for Rails
teaser: |
  Announcing Squirrel, a new Ruby open source library we wrote to improve
  how we query SQL databases.
tags: web,rails,squirrel
author: Jon Yurek
published_on: 2006-09-29
---

I've never liked how you query the database in ActiveRecord. Sure it works, but
so does writing straight <abbr title="Structured Query Language">SQL</abbr>.
Neither seem very integrated into the framework. So after thinking about it, I
figured I'd do it one better and make something a little more Rubyish looking.
So I made Squirrel.

```ruby
posts = Post.find do
  user.email =~ "%thoughtbot%"
  tags.name === %w( ruby rails )
  created_on <=> [ 2.weeks.ago, 1.week.ago ]
end
```

I've seen various plugins for making queries nicer, but none looked like Ruby,
because even in the best of them, they still used what I consider to be
overly-awkward nested hashes to get relationships. Squirrel automatically gets
the relationships and builds them as you use them. It supports all the normal
associations, and it does it in a much friendlier way.

You reference associations by whatever name you gave to it in the `has\_many`,
`belongs\_to`, etc. You can then access all the columns and relationships on
that model. It is rather specific, though, and will raise errors if you misspell
your relationships or don't pluralize right.

And you simply reference columns by their normal names and use any of `==`,
`===`, `&lt;=&rt;`, `=~`, `&rt;=`, `&lt;=`, `&rt;`, and `&lt;` on them pretty
much like you'd expect to be able to (before you ask, yes, I cribbed the syntax
from [ez-where], since it makes sense). It handles `nil` values in `==` with a
quick trip to `IS NULL` and it handles negation of conditions through the unary
`-`.

[ez-where]: https://github.com/ezmobius/ez-where

It doesn't yet do all the fancy stuff I'd like it to, like adding aggregation
columns, limiting, or even `OR` joins and grouping, but it's still much better
looking than normal ActiveRecord::Base#find queries, I think. It does have some
fancy stuff like order_by and placeholders, though. Here's an example with both:

```ruby
query = User.find do
  company.name = cname?
  order_by created_on
end
```

When you do that, instead of an array of results, it hands you back Squirrel's
`Query` object, which is the base of all its querying (imagine that). From
there, you call `find` on it and pass a hash of the names of the placeholders
you specified. No surprises there. (Also, you can get a query object for
inspecting the <abbr title="Structured Query Language">SQL</abbr> by passing
`:query` to `find`, like so: `query = User.find(:query) {id == 2}`)

```ruby
users = query.find(:cname => "thoughtbot")
```

Now, what's tricky is that you could pass any syntax-changing value to that and
you'd get the right <abbr title="Structured Query Language">SQL</abbr> in the
executed query.

```ruby
query.find :cname => "thoughtbot"
# =>   company.name = "thoughtbot"
query.find :cname => nil
# =>   company.name IS NULL
query.find :cname => ["Google", "37 Signals"]
# =>   company.name IN ("Google", "37 Signals")
```

So there you have it. I think it makes queries **much** more readable and easier
to maintain.

You can obtain it with:

```sh
piston init https://svn.thoughtbot.com/plugins/squirrel/trunk vendor/plugins/squirrel
```

(Updated to reflect correct <abbr title="Uniform Resource Locator">URL</abbr>.)

Visit our [Open Source page](https://thoughtbot.com/open-source) to learn more about our team's contributions.