« Back to Blog

Creating and Accessing an In-Memory Database with Ruby’s Sequel Gem

By Chris (Rain) Avila
Sep 7, 2018

When you’re working with a large set of data, you’ll likely need an easy way to store the data to later access and manipulate. I like to use the Sequel gem whenever I need to connect to a database in Ruby. Using the Sequel gem, you can easily either connect an existing database or create a new in-memory database to work with. I’m going to be using a few games to create a table in a database that has the title, price, platform and release date for some past and upcoming game releases.

Starting out, you’ll need to to install the ‘sequel’ gem. Also, install the ‘pry’ gem for debugging. To start, you’ll run the below:

gem install sequel pry

Once you’ve got the gem installed, you can initialize the database and create your first table.

DB = Sequel.sqlite

DB.create_table :games do primary_key :id String :title String :platform String :release_date Float :price End

games = DB[:games]

So, you’ll have a table with the columns title, platform, release_date and price. Now, you need to populate the table with some data. I’ve made an array of hashes with some games in it:

releases = [ {title: 'Dragon Quest XI', price: '59.99', platform: 'PC', release_date: Date.strptime('04-09-2018', '%d-%m-%Y')}, {title: 'Spider-Man', price: '59.99', platform: 'Playstation 4', release_date: Date.strptime('07-09-2018', '%d-%m-%Y') }, {title: 'Shadow of the Tomb Raider', price: '59.99', platform: 'Xbox One', release_date: Date.strptime('18-09-2018', '%d-%m-%Y')}, {title: 'Monster Hunter: World', price: '59.99', platform: 'PC', release_date: Date.strptime('09-08-2018', '%d-%m-%Y')}, {title: 'The Legend of Zelda: Breath of the Wild', price: '44.99', platform: 'Nintendo Switch', release_date: Date.strptime('03-03-2017', '%d-%m-%Y')}, {title: 'Resident Evil 2', price: '59.99', platform: 'PC', release_date: Date.strptime('25-01-2018', '%d-%m-%Y')}, {title: 'Kingdom Hearts 3', price: '59.99', platform: 'Playstation 4', release_date: Date.strptime('25-01-2019', '%d-%m-%Y')}, {title: 'Super Mario Odyssey', price: '44.99', platform: 'Nintendo Switch', release_date: Date.strptime('27-08-2017', '%d-%m-%Y')} ]

And to insert the data into the database:

releases.each { |game| games.insert(game) }

Now with Pry, you can see the list of games:

[2] pry(main)> games.all => [{:id=>1, :title=>"Dragon Quest XI", :platform=>"PC", :release_date=>"2018-09-04", :price=>59.99}, {:id=>2, :title=>"Spider-Man", :platform=>"Playstation 4", :release_date=>"2018-09-07", :price=>59.99}, {:id=>3, :title=>"Shadow of the Tomb Raider", :platform=>"Xbox One", :release_date=>"2018-09-18", :price=>59.99}, {:id=>4, :title=>"Monster Hunter: World", :platform=>"PC", :release_date=>"2018-08-09", :price=>59.99}, {:id=>5, :title=>"The Legend of Zelda: Breath of the Wild", :platform=>"Nintendo Switch", :release_date=>"2017-03-03", :price=>44.99}, {:id=>6, :title=>"Resident Evil 2", :platform=>"PC", :release_date=>"2018-01-25", :price=>59.99}, {:id=>7, :title=>"Kingdom Hearts 3", :platform=>"Playstation 4", :release_date=>"2019-01-25", :price=>59.99}, {:id=>8, :title=>"Super Mario Odyssey", :platform=>"Nintendo Switch", :release_date=>"2017-08-27", :price=>44.99}]

If you wanted to lookup a specific title, say Spider-Man, you can do so with:

[4] pry(main)> games.where(title: 'Spider-Man').first => {:id=>2, :title=>"Spider-Man", :platform=>"Playstation 4", :release_date=>"2018-09-07", :price=>59.99}

Games below a certain price (note here that we are passing a block):

[31] pry(main)> games.where { price < 59.99 }.all => [{:id=>5, :title=>"The Legend of Zelda: Breath of the Wild", :platform=>"Nintendo Switch", :release_date=>"2017-03-03", :price=>44.99}, {:id=>8, :title=>"Super Mario Odyssey", :platform=>"Nintendo Switch", :release_date=>"2017-08-27", :price=>44.99}]

Or if you don’t own a PC, you can explicitly exclude items from your results:

[43] pry(main)> games.where { price > 39.99 }.exclude(platform: 'PC').all => [{:id=>2, :title=>"Spider-Man", :platform=>"Playstation 4", :release_date=>"2018-09-07", :price=>59.99}, {:id=>3, :title=>"Shadow of the Tomb Raider", :platform=>"Xbox One", :release_date=>"2018-09-18", :price=>59.99}, {:id=>5, :title=>"The Legend of Zelda: Breath of the Wild", :platform=>"Nintendo Switch", :release_date=>"2017-03-03", :price=>44.99}, {:id=>7, :title=>"Kingdom Hearts 3", :platform=>"Playstation 4", :release_date=>"2019-01-25", :price=>59.99}, {:id=>8, :title=>"Super Mario Odyssey", :platform=>"Nintendo Switch", :release_date=>"2017-08-27", :price=>44.99}]

To learn more, full documentation for the sequel gem can be found here.