Ruby Sinatra with Postgres using Sequel

June 04, 2021

In Ruby on Rails we use ActiveRecord as our ORM (Object Relationship Mapper). Sinatra, being a minimalist framework is unopinionated about what Ruby ORM you use to work with to connect to the database of your choice. We will use the ORM, sequel, to connect our small application to a database.

Requirements

Have Ruby Installed
Have postgres installed and have a server running
Have Sinatra installed gem install sinatra
Have Sequel installed gem install sequel

Set One - Create a Database to Connect To

open up the postgres shell with psql
create a database CREATE DATABASE sequel_test;
create a super user to connect CREATE USER myuser WITH SUPERUSER PASSWORD 'myuser';
connect to the database \c sequel_test
create a table CREATE TABLE people (id SERIAL, name VARCHAR(20), age INTEGER);
quit psql \q
construct a connection postgres://myuser:myuser@localhost:5432/sequel_test (adjust the url as needed for your environment)

Write our file!

While Sequel has support for migrations, Model Mapping and more, we will keep it simple with raw sql queries just to demonstrate.

create a file called server.rb with the following, read the comments for explanation of the code.

# Import the needed libraries
require 'sinatra'
require 'sequel'

# Connect to our database using our database string
DB = Sequel.connect('postgres://test7:test7@localhost:5432/sequel_test')

#Create a route to see all records
get '/' do
   # create a dataset from the people table
   result = DB[:people]
   # return all the records from the people table as json
   return result.all.to_json
end

#Create a route to add a record
get '/add/:name/:age' do
   # Extract data from URL Params
   name = params["name"]
   age = params["age"].to_i
   # create query to add
   result = DB["INSERT INTO people (name, age) VALUES (?, ?)", name, age]
   # run the insert query
   result.insert(1)
   return "Record added"
end

Here we’re create a couple of basic enpoint to see and add data using sequel. I recommend going deeper into the sequel documentation to learn how to setup migrations and get more typical ORM patterns. But if you just need some quick database access, this is one way to do it very quickly.

Sequel Documentation