The Sinatra Songbook Project

Database ORMs

I’m currently working on the next project, which is a mini To Do list app with a database back end that uses the Datamapper ORM.

When I started using Rails and Sinatra, I didn’t really get what an ORM was at first, so thought I’d take a bit of time to explain what Datamapper actually is.

One of the main differences between building a static website and a dynamic web app is the database back end. A database is a place where all your data is stored. You can create, read, update and delete the data in your database (these are called the CRUD operations). When I first got into web development, I was excited about being able to store data in a database, but wasn’t ready for the overwhelming number of database options. I was aware of simple databases like (cough) Access, but all of a sudden I was hearing about MySQL, SQL Server, Postgresql, SQLite and Oracle - to name a few. One thing that they had in common was they all used the Structured Query Language (SQL) which is a standard way of performing the CRUD operations on the data in a database, although it is implemented in a slightly different way in each different database.

MySQL seemed to be the way to go for open source development. It was free to use and there was lots of documentation online. When I started using Ruby on Rails, this was the default database that was recommended. It was relatively easy to set up, but it did require running on a server that had to be started every time I worked on a site. Then Rails switched to SQLite by default. This is a database that just runs from a simple file, so is much easier to set up and run. Last time I looked, Heroku used Postgresql for its database, but I don’t know anything about using or installing this.

The reason I was able to switch easily from one database to another was that Rails doesn’t connect directly with the database. It uses something called Active Record to do all the talking to the database. You basically write your CRUD operations using Active Record and it takes care of things at the database level, regardless of which database you are using.

Basically, SQL databases store data in tables and rows, whereas your app has objects with properties. The two don’t map exactly, and this is where ORMs come in. Object Relational Mappers (to give them their full title), map your Ruby objects onto the database tables. This has many advantages - normally you get to write less code as all the common requirements are taken care of. It also means that a large number of databases are taken care of. Active Record is an ORM, and that’s why I never had to change any of my Rails code whenever I changed databases - because it took care of all the details.

When I first started reading about Sinatra, I kept hearing that one of the good things about it was that you were able to choose which ORM you used. I didn’t really get or understand what all this fuss was about. At the time Rails was tied quite tightly to Active Record (not any more, now Rails 3 has been released!), but I hadn’t even even thought much about this (The Rails mantra of ‘Convention over configuration’ meant that I actually thought it was just part of Rails). In fact, a lot of the code that I thought was Rails code was actually just Active Record code.

When I started using Sinatra, I discovered that there were a number of different ORM options that could be used. The more popular ones were Active Record (just like Rails), Sequel (Scanty, the blogging engine that powers this site, uses this) and Datamapper, which, after trying them all out, has become my personal favourite and will be the ORM used in the next project.

Datamapper is a relatively new entrant into the ORM stable, but is already a popular option and fits well with Sinatra. It is written in Ruby and has a really simple syntax that is easy to get to grips with and allows you to manipulate the data in your database with ease. Take a look at these code examples to see what I mean:

Task.get(1) # get the task with primary key of 1.
Task.first(:name => 'Get Milk') # first matching record with the name 'Get Milk'
Task.all # get all the tasks
Task.all(:completed => true) # get all the tasks that have been completed
Task.all(:completed_on => (start..finish)) # get all the tasks completed in the date-range

It also has a brilliant auto-migrate feature, which updates your database as you make any changes while you’re going along. It is very stripped down to the basics, but there are loads of plugins available that add all sorts of functionality.

Recently there has been lots of noise coming from databases that don’t use SQL - object-orientated databases, document databases and key-value stores. These include Redis, Couchdb, MongoDB and Tokyo Cabinet. And guess what - Sinatra supports these too. These all sound really exciting, and I’m planning on trying them out (particularly MongoDB an Redis) on some of the future projects, so stay tuned.

I hope this has helped made things a bit clearer about all the different database options and how ORMs fit into the equation. I’d like to hear what other people think and if you have any preferences - please leave any comments or questions below.