Now that we know how to fetch the information we want, we can insert it into a database for later use. In this series, we are going to use PostgreSQL, so first of all, we need to install the gem pg in our project:
bundle add pg
And in order to have a database to play with we can use docker-compose to set up our development (and test) Postgres instance. So in a new docker-compose.yml file:
services:
db:
image: postgres
volumes:
- ./.data/db:/var/lib/postgresql/data
environment:
POSTGRES_PASSWORD: password
ports:
- "5432:5432"
With that, we can create our Postgres container with docker compose up -d.
For connecting to the database in our Ruby applications we are going to use sequel so let’s add it with bundle add sequel
Once we have our instance up and running and sequel installed, we need to create the database itself. Sequel gives us all the tools to manage our database but doesn’t provide rake tasks, so we are going to create them for easier database management.
Add the rake gem with bundle add rake and create a new Rakefile file:
namespace :db do
task :create do |t, args|
require "sequel"
development_database = "free_game_development"
db = Sequel.connect(ENV["POSTGRES_URL"].to_s)
db.run("DROP DATABASE IF EXISTS #{development_database}")
db.run("CREATE DATABASE #{development_database}")
end
end
This task expects an environment variable called POSTGRES_URL that points to the default postgres database. For managing environment variables in your local environment you can use many different tools, I use direnv.
# In a .envrc file
export POSTGRES_URL="postgres://postgres:password@127.0.0.1:5432/postgres"
Then we can run the task with bundle exec rake "db:create"
For running Sequel migrations:
namespace :db do
task :create do |t, args|
require "sequel"
development_database = "free_game_development"
db = Sequel.connect(ENV["POSTGRES_URL"].to_s)
db.run("DROP DATABASE IF EXISTS #{development_database}")
db.run("CREATE DATABASE #{development_database}")
end
+
+ task :migrate do
+ require "sequel"
+ require "sequel/extensions/migration"
+
+ Sequel::Migrator.run(Sequel.connect(ENV["DATABASE_URL"].to_s), "db/migrations")
+ end
end
This task will connect to a database using the DATABASE_URL environment variable, for example:
export POSTGRES_URL="postgres://postgres:password@127.0.0.1:5432/postgres"
+ export DATABASE_URL="postgres://postgres:password@127.0.0.1:5432/free_game_development"
The rake task expects migrations to be placed in a folder db/migrations, create it and lets move to creating models and their migrations.
We want to store games and their promotions, either active or expired. The migrations for games need to include data like the game’s title and the URL slug for crafting the Store URL we will send in the notification.
# In a new file 'db/migrations/001_create_games.rb'
Sequel.migration do
up do
create_table(:games) do
primary_key(:id)
String(:title, null: false)
String(:url_slug, null: true)
end
end
down do
drop_table(:games)
end
end
Each game may have many promotions so we are going to store promotion data in a different table. For each promotion we are going to store the reference to the game itself, start/end dates and the discount itself.
# In a new file 'db/migrations/002_create_promotions.rb'
Sequel.migration do
up do
create_table(:promotions) do
primary_key(:id)
foreign_key(:game_id, :games, null: false)
DateTime(:start_date, null: false)
DateTime(:end_date, null: false)
String(:discount_type, null: false)
Integer(:discount_percentage, null: false)
end
end
down do
drop_table(:promotions)
end
end
Before running migrations lets modify the rake task to create a schema.rb file with our database structure after running migrations. Having this file is useful for when we want to quickly check the structure of the database without reading all migrations.
require "sequel"
require "sequel/extensions/migration"
+ ENV["RACK_ENV"] ||= "development"
Sequel::Migrator.run(Sequel.connect(ENV["DATABASE_URL"].to_s), "db/migrations")
+ if ENV["RACK_ENV"] == "development"
+ system("sequel -d #{ENV["DATABASE_URL"]} > db/schema.rb")
+ end
And finally run bundle exec rake db:migrate, this should have created a schema.rb with:
Sequel.migration do
change do
create_table(:games) do
primary_key :id
String :title, :text=>true, :null=>false
String :url_slug, :text=>true
end
create_table(:schema_info) do
Integer :version, :default=>0, :null=>false
end
create_table(:promotions) do
primary_key :id
foreign_key :game_id, :games, :null=>false, :key=>[:id]
DateTime :start_date, :null=>false
DateTime :end_date, :null=>false
String :discount_type, :text=>true, :null=>false
Integer :discount_percentage, :null=>false
end
end
end
To make use of these tables we need two Sequel models, Game and Promotion. In our app directory create a new models directory to store them.
# app/models/game.rb
class Game < Sequel::Model
one_to_many :promotions
end
# app/models/promotion.rb
class Promotion < Sequel::Model
many_to_one :game
end
In the previous post we created a class to fetch all games and return them as Data structures. We need to create a new class that make use of that and insert the data into the database.
Create a new file app/update_games.rb with our new class:
class UpdateGames
def initialize(adapter)
@adapter = adapter
end
def call
end
end
As you can see this class receives the adapter to use and contains a method to do all the logic. The call method will fetch all free games and insert them (and their promotions) into the database:
class UpdateGames
def initialize(adapter)
@adapter = adapter
end
def call
+ @adapter.get_free_games.each do |game_data|
+ game = Game.update_or_create(title: game_data.title) do |new_game|
+ new_game.url_slug = game_data.url_slug
+ end
+
+ game_data.promotions.each do |promotion_data|
+ Promotion.update_or_create(
+ game_id: game.id,
+ start_date: promotion_data.start_date,
+ end_date: promotion_data.end_date,
+ discount_type: promotion_data.discount_type,
+ discount_percentage: promotion_data.discount_percentage
+ )
+ end
+ end
+ end
end
We use update_or_create because we are going to run this everyday and we will receive multiple times the same games.
Now lets create a simple test that checks that we have insert the correct number of games and promotions. In a new file spec/app/epic_store_adapter_spec.rb
require_relative "../../app/update_games"
RSpec.describe UpdateGames do
subject { described_class.new(adapter) }
let(:adapter) { EpicStoreAdapter.new("https://store-site-backend-static.ak.epicgames.com") }
it "inserts game data into the database" do
VCR.use_cassette("free_games") do
expect { subject.call }.to change { Game.count }.from(0).to(6)
end
end
it "inserts promotion data into the database" do
VCR.use_cassette("free_games") do
expect { subject.call }.to change { Promotion.count }.from(0).to(5)
end
end
end
Now before we run the test we have to update our testing configuration to connect to the database. First of all we need to create a different database for testing.
Update the .envrc file to add a new environment variable TEST_DATABASE_URL:
export POSTGRES_URL="postgres://postgres:password@127.0.0.1:5432/postgres"
export DATABASE_URL="postgres://postgres:password@127.0.0.1:5432/free_game_development"
export TEST_DATABASE_URL="postgres://postgres:password@127.0.0.1:5432/free_game_test"
And create a new rake tast to prepare the test environment:
namespace :db do
...
+ namespace :test do
+ task :prepare do
+ require "sequel"
+ require "sequel/extensions/migration"
+
+ test_database = "free_game_test"
+
+ db = Sequel.connect(ENV["POSTGRES_URL"].to_s)
+ db.run("DROP DATABASE IF EXISTS #{test_database}")
+ db.run("CREATE DATABASE #{test_database}")
+
+ Sequel::Migrator.run(Sequel.connect(ENV["TEST_DATABASE_URL"].to_s), "db/migrations")
+ end
+ end
end
Now in our spec_helper we have to connect to the new database using Sequel.
- require "vcr"
+ Bundler.require(:default, :test)
+ Sequel.connect(ENV["DATABASE_URL"].to_s)
+ Sequel::Model.plugin(:update_or_create)
We have removed the vcr require and updated it to use the bundler require method that will require every gem we have defined in our Gemfile, either outside any groups or in the testing one.
Is mandatory to enable the plugin update_or_create because we use in our UpdateGames class.
To allow our test to access our models we need to require them too in spec_helper:
Bundler.require(:default, :test)
Sequel.connect(ENV["DATABASE_URL"].to_s)
Sequel::Model.plugin(:update_or_create)
+ require_relative "../app/models/game"
+ require_relative "../app/models/promotion"
VCR.configure do |config|
config.cassette_library_dir = "spec/fixtures/vcr_cassettes"
config.hook_into(:faraday)
end
And that’s it, let’s run the test
bundle exec rspec
..F
Failures:
1) UpdateGames inserts promotion data into the database
Failure/Error: expect { subject.call }.to change { Promotion.count }.from(0).to(5)
expected `Promotion.count` to have initially been 0, but was 5
# ./spec/app/update_games_spec.rb:17:in `block (3 levels) in <top (required)>'
# ./spec/app/update_games_spec.rb:16:in `block (2 levels) in <top (required)>'
Finished in 0.07583 seconds (files took 0.30525 seconds to load)
3 examples, 1 failure
Failed examples:
rspec ./spec/app/update_games_spec.rb:15 # UpdateGames inserts promotion data into the database
Woops. The first one succeeded but the second one failed. It says that initially it should have had 0 promotions in the database which is correct but it found 5 already. The issue is that we already inserted promotions in the previous spec so we have to clean up the database between test cases.
For that we can add the gem database_cleaner-sequel which will truncate all data created in each test. Add it as usual with bundle add database_cleaner-sequel and configure it in our spec_helper.rb file:
RSpec.configure do |config|
+ config.before(:suite) do
+ DatabaseCleaner.strategy = :transaction
+ DatabaseCleaner.clean_with(:truncation)
+ end
+
+ config.around(:each) do |example|
+ DatabaseCleaner.cleaning do
+ example.run
+ end
+ end
...
end
And if we run our tests again:
bundle exec rspec
...
Finished in 0.06822 seconds (files took 0.30429 seconds to load)
3 examples, 0 failures
Nice!
We’ve reached the end of the second part. We have created the logic needed to save games and promotions into our database. The next step is to notify about new promotions to a telegram channel that users can join.