getting started with sinatra and postgres web service

I’m in the process of resurrecting some of my past projects. One of them is a web service that provided details for a given zip code. For example, you could lookup the zip code ‘15213’ and the web service would spit out the geographical center for the zip code (i.e., the latitude and longitude coordinates), as well as the city and state for that zip. I originally programmed the web service using Ruby on Rails, but using RoR for a relatively simple API is a bit overkill. So I decided to use this project as a chance to learn and try out Sinatra, a lightweight Ruby-based web framework.

Sinatra works with various backends, including MySQL, sqlite, and PostgreSQL. Originally the backend I was using with the RoR version of my web service was a MySQL, but this time I wanted to host the web service on Heroku. Knowing that Heroku natively uses Postgres, I decided to try my hand at using that, along with a Sinatra setup.

To get things started on a Mac OS X machine, I did an install of the basics, which included upgrading Ruby to the latest version (2.1) and installing Sinatra, Datamapper, and Postgres. I’ve found that it’s easiest to use RVM to upgrade Ruby:

$ rvm get head
$ rvm reload
$ rvm install 2.1
$ rvm use 2.1 --default

To use Postgres locally, I used Homebrew to install it. Sinatra supports ActiveRecord as well as DataMapper. I decided to use DataMapper, which meant installing some extra gems:

$ gem install sinatra
$ brew install postgres
$ gem install data_mapper
$ gem install dm-postgres-adapter

The heart of the zip code web service is really the database. Two freely available versions are here and here. I got some parsing errors using the former (there are commas within fields so it’s not a purely comma-delimited file), so I decided to go with the latter file. I did some quick sanity checks on the file and the data seems reasonably kosher.

I found it easier to have an app to help me manage Postgres, so I went with Postgres.app which makes it easy to start/stop the Postgres process (and makes sure to override the older version of Postgres that comes with OS X). I also installed PG Commander which is a simple GUI Postgres client. I ran the following commands to get the zip code set up as a local Postgres db. The columns are taken directly from the CSV file,

$ CREATE DATABASE geocode;
/* after logging into geocode database, create the table */
$ CREATE TABLE zipcodes (
  zip INTEGER PRIMARY KEY,
  TYPE VARCHAR(20),
  city VARCHAR(45),
  state VARCHAR(45),
  locationtype VARCHAR(20),
  lat NUMERIC,
  lng NUMERIC,
  locationstring VARCHAR(100),
  decommissioned BOOLEAN,
  taxreturns INTEGER,
  estimatedpopulation INTEGER,
  totalwages INTEGER
);
/* now import CSV file into table */
$ COPY zipcodes FROM 'free-zipcode-database-Primary.csv' DELIMITER ',' CSV HEADER;

Now to get to the Sinatra part. To make sure the Postgres connection was working, I created these two file stubs

# app.rb
require 'sinatra'
require 'data_mapper'
require	'./models/zipcode.rb'
DataMapper.setup(:default, 'postgres://localhost/geocode')
DataMapper.finalize.auto_upgrade!
 
# ./models/zipcode.rb
# only specifying the columns that the web service will support
class Zipcode
  include DataMapper::Resource
 
  property :zip, Integer, :key => true  # zip codes are unique, so we'll use them as a key
  property :city, String
  property :state, String
  property :lat, Float
  property :lng, Float
end

Then running irb, we can verify that Sinatra is indeed talking to the local database:

$ require './app.rb'
$ Zipcode.all
=> [#<Zipcode @zip=501 @city="HOLTSVILLE" @state="NY" @lat=40.81 @lng=-73.04>, #<Zipcode @zip=544 @city="HOLTSVILLE" @state="NY" @lat=40.81 @lng=-73.04>, #<Zipcode @zip=601 @city="ADJUNTAS" @state="PR" @lat=18.16 @lng=-66.72>, #<Zipcode @zip=602 @city="AGUADA" @state="PR" @lat=18.38 @lng=-67.18>, ...
$ Zipcode.all.count
=>42522
$ z = Zipcode.first(:zip => '15213')
=> #<Zipcode @zip=15213 @city="PITTSBURGH" @state="PA" @lat=40.43 @lng=-79.97>
$ z.zip
=> 15213

Now the next step is to add a view so that we can pull these results via the web, instead of through the console. To do this, we edit our app.rb file:

# app.rb
require 'sinatra'
require 'data_mapper'
require	'./models/zipcode.rb'
 
DataMapper.setup(:default, 'postgres://localhost/geocode')
get '/zipcode/:id' do
  @zip = Zipcode.get(params[:id])
  erb :zip
end
DataMapper.finalize.auto_upgrade!

and add a view:

# ./views/zip.erb
<h2><%= @zip.zip %></h2>
<h2><%= @zip.city %></h2>
<h2><%= @zip.state %></h2>
<h2>(<%= @zip.lat %>,<%= @zip.lng %>)</h2>

Now when you run ruby app.rb, you’ll see the WEBrick server start and if you visit http://localhost:4567/zipcode/15213, you should see this output:

15213
PITTSBURGH
PA
(40.43,-79.97)

But this still isn’t quite a web service. Instead of having an HTML view, we need to support JSON output. To support JSON, we add in the json library (via require 'json') and change the routing portion in app.rb:

# app.rb
require 'sinatra'
require 'data_mapper'
require	'./models/zipcode.rb'
require 'json'
 
DataMapper.setup(:default, 'postgres://localhost/geocode')
DataMapper.finalize.auto_upgrade!
 
get '/zipcode/:id' do
	content_type :json
  zip = Zipcode.get(params[:id])
  { zip: zip.zip, city: zip.city, state: zip.state, lat: zip.lat, lng: zip.lng }.to_json
end

Now when you run ruby app.rb and ping http://localhost:4567/zipcode/15213, you should see this output:

{"zip":15213,"city":"PITTSBURGH","state":"PA","lat":40.43,"lng":-79.97}

H/T to some helpful references:
http://ididitmyway.herokuapp.com/past/2010/3/30/superdo_a_sinatra_and_datamapper_to_do_list
http://www.sitepoint.com/build-sinatra-api-using-tdd-heroku-continuous-integration-travis