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.
- 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
- create a database
CREATE DATABASE sequel_test;
- create a super user to connect
CREATE USER myuser WITH SUPERUSER PASSWORD 'myuser';
- connect to the database
- create a table
CREATE TABLE people (id SERIAL, name VARCHAR(20), age INTEGER);
- quit psql
- 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.