Although I’m still going to work on using my “in-memory” objects for testing and development purposes, yesterday I decided to go ahead and set up the database for my app. Since I’m going to be deploying to Heroku I have to use PostgreSQL so it’s not like deferring that decision really defers anything. My database decision is already made. And although I could wire up ActiveRecord through Sinatra, I wanted to use something simpler and most of the Sinatra examples out there use DataMapper, an object relational mapper that provides all of the crud actions and associations on top of the database.

I had quite a few hiccups getting this set-up so hopefully these steps will help somebody out there.

PostgreSQL and Mac OS X Lion are mortal enemies

Sort of… OS X Lion ships with a version of PostgreSQL, but it’s really difficult for a user to do anything with it. As many people have discovered, if you try to work with this for development there are lots of permission errors. What I ended up doing was using Homebrew to install the latest version of PostgreSQL and then archiving the default version.

Install PostgreSQL with Homebrew

Steps 1 and 2 (but only 1 and 2, for now) from Greg Cardoni’s installation guide (cached copy available here) will get you through these steps. You might have to uninstall MacPorts (which I did), but that was pretty painless.

“Hide” the default Lion PostgreSQL installation

The next step is to hide the PostgreSQL that comes with OS X Lion. This conflict is what had prevented me from getting PostgreSQL up and running during the Code Academy internal project, I just never figured it out back then. I finally stumbled across Paul Denya’s little script that will archive those default OS X PostgreSQL binaries and link up the newly installed homebrew ones. I highly recommend reading through the post to see exactly what happens.

Firing up the “good” PostgreSQL

Now that the install is out of the way you can start up PostgreSQL in Terminal and create a database, which is step 3 in Cardoni’s guide. This line will initialize the db and start it up.

initdb /usr/local/var/postgres

You can confirm that it’s up and running with the following:

ps auxwww | grep postgres

which will give you some output about the PostgreSQL processes. Alternatively, you can also fire up Activity Monitor in your Applications>Utilities folder and select “All Processes” in the main window to see that the processes are running. They are named “postgres”.

Cardoni also has three lines you can type into terminal to automatically fire up PostgreSQL every time you start your computer, and since it takes a minimal amount of resources it’s something I recommend doing.

Create Your App DB

If everything is looking good at this point then you can create a database specific to your app with the following line in terminal (where yourappname is the name of your app):

createdb yourappname

You can check that it’s set up with this command:

psql yourappname

…at which point you should be prompted with the following:

psql (9.1.3) #*or whatever version of PostgreSQL you're running*
Type "help" for help.

yourappname=#

And you’re good! Type “help” (no quotes) to get a dive into all the commands you have at your disposal.

DataMapper and Cucumber are Mortal Enemies… at first

Installing the DataMapper and PostgreSQL Gems

Here’s my Gemfile. I originally had it broken up into :test, :development, :production groups, but then cleared all that out when things stopped working.

source "http://rubygems.org"

gem "sinatra", "1.3.2"
gem "shotgun", "0.9"
gem "cucumber", "1.2.1"
gem "capybara", "1.1.2"
gem "rspec", "2.10.0"

gem "data_mapper", "1.2.0"
gem "dm-postgres-adapter", "1.2.0"
gem "pg", "0.13.2"

Just installing the DataMapper gem broke Cucumber, which was decidedly not awesome. Although my Gemfile only has eight gems, DataMapper and Cucumber have dependencies back to json, one installing json version 1.6.7 and the other installing 1.7.3. The solution? gem uninstall json -v 1.7.3. After that the Cucumber tests and everything else ran fine.

Hooking DataMapper up to Your PostgreSQL database

Ok, time for the home stretch. Now that DataMapper and PostgreSQL are installed, we just need to tell DataMapper what database to use. Here are the lines you need at the top of your main Sinatra app file (and again, yourappname is the name of the database we created above):

    require 'sinatra'
    require 'data_mapper'

    DataMapper.setup(:default, 'postgres://localhost/yourappname')
    DataMapper.finalize.auto_upgrade!

The last line tells DataMapper to update the database with the tables and fields that we will eventually create in our models, and then automatically update it if we change anything.

And there you have it, you’re wired up! You can jump down to the “Define Your Models” section of the DataMapper Getting Started guide and learn about setting up your models with the properties you want. I just set up a User table with a name and email field so in the post “/users” method I can use the familiar crud actions:

    post "/users" do
      @user = User.create(params)
      erb :thankyou
    end

And if I want to check that it’s actually created I can just kick back to terminal and enter the following commands:

psql yourappname
select * from users;

and here we are:

  id |    name     |     email     
    ----+-------------+---------------
   1 | mike         | mike@test.com

Of course, the next step is configuring my environments so that I use the in-memory objects for testing and development and DataMapper and PostgreSQL for production, but it’s good to know that I have it up and running when I need it.

If anyone has any feedback or their own hiccups please hit my up on Twitter.