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:

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,

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: