We’ve put off implementing the database layer for as long as we could. But with the rest of the surrounding layers defined, it’s all that’s left.
For this exercise, you’re going to use a Ruby database library called Sequel. Sequel allows you to create tables, add data, and so on, without tying your code to any specific database product. You’ll still need to choose a database, though, and for this project the low-maintenance SQLite library will do fine.[40]
Go ahead and add the following two lines to your Gemfile:
| | gem 'sequel', '4.48.0' |
| | gem 'sqlite3', '1.3.13' |
Now, rerun Bundler to install the new libraries:
| | $ bundle install |
| | Fetching gem metadata from https://rubygems.org/......... |
| | Fetching version metadata from https://rubygems.org/.. |
| | Resolving dependencies... |
| | Using bundler 1.15.3 |
| | Using coderay 1.1.1 |
| | Using diff-lcs 1.3 |
| | Using mustermann 1.0.0 |
| | Using rack 2.0.3 |
| | Using rspec-support 3.6.0 |
| | Fetching sequel 4.48.0 |
| | Installing sequel 4.48.0 |
| | Using tilt 2.0.7 |
| | Fetching sqlite3 1.3.13 |
| | Installing sqlite3 1.3.13 with native extensions |
| | Using rack-protection 2.0.0 |
| | Using rack-test 0.7.0 |
| | Using rspec-core 3.6.0 |
| | Using rspec-expectations 3.6.0 |
| | Using rspec-mocks 3.6.0 |
| | Using sinatra 2.0.0 |
| | Using rspec 3.6.0 |
| | Bundle complete! 6 Gemfile dependencies, 16 gems now installed. |
| | Use ‘bundle info [gemname]‘ to see where a bundled gem is installed. |
Once Sequel and SQLite are installed, you’re ready to use them. In our application, you’ll eventually do the following:
That’s quite a task list, but the APIs to perform these steps are simple. In fact, the first example from the Sequel homepage provides a short code snippet that shows how to do all of these![41] If you’re following along, we encourage you to launch an IRB session and try all these steps based on the example:
| | >> require 'sequel' |
| | => true |
| | >> DB = Sequel.sqlite |
| | => #<Sequel::SQLite::Database: {:adapter=>:sqlite}> |
| | >> DB.create_table(:gems) { String :name } |
| | => nil |
| | >> DB[:gems].insert(name: 'rspec') |
| | => 1 |
| | >> DB[:gems].insert(name: 'sinatra') |
| | => 2 |
| | >> DB[:gems].all |
| | => [{:name=>"rspec"}, {:name=>"sinatra"}] |
Now that you’re familiar with the APIs, you’re ready to create some databases.
You’ll want to create separate SQLite databases for testing, development, and production (so that you don’t clobber your real data during testing).
An environment variable is the easiest way to configure separate databases for this app. In Getting Started, you used the RACK_ENV variable to indicate which environment the code was running in. Let’s piggyback off that work. Create a new file called config/sequel.rb with the following code:
| | require 'sequel' |
| | DB = Sequel.sqlite("./db/#{ENV.fetch('RACK_ENV', 'development')}.db") |
This configuration will create a database file such as db/test.db or db/production.db depending on the RACK_ENV environment variable. Note that you’re assigning the database connection to a top-level DB constant; this is the Sequel convention when there’s just one global database.[42]
With this configuration in place, you don’t have to worry about accidentally overwriting your production data during testing.
Now, it’s time to think about the structure of your data. Each expense item will need several pieces of information:
You’ll use a Sequel migration to create the table structure that holds this information.[43] You can put migration files anywhere, but a common convention is to keep them in db/migrations. Add the following code to db/migrations/0001_create_expenses.rb:
| | Sequel.migration do |
| | change do |
| | create_table :expenses do |
| | primary_key :id |
| | String :payee |
| | Float :amount |
| | Date :date |
| | end |
| | end |
| | end |
To apply this migration to your database, you’ll need to tell Sequel to run it. In a minute, you’ll configure RSpec to do so automatically every time you run your integration tests. For now, try running this migration against the development database. To do so, you can use the sequel command that ships with the Sequel library:
| | $ bundle exec sequel -m ./db/migrations sqlite://db/development.db --echo |
| | truncated |
| | |
| | I, [2017-06-13T13:34:25.536511 #14630] INFO -- : Finished applying ↩ |
| | migration version 1, direction: up, took 0.001514 seconds |
Now that you’ve configured Sequel, it’s time to write some specs.