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
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);
construct a connection
postgres://myuser:myuser@localhost:5432/sequel_test(adjust the url as needed for your environment)
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.