indiesquidge/gist:c2d133ccb17a5d845586

## gistfile1.md

      
    Raw
  

              gistfile1.md
            
          
    Changing Your Databases from YAML to SQLite

References to learning SQL and Sequel

Fundamental SQL
Getting Started with Sequel

So in case any of you were wondering how to get both your development and
testing databases migrated over to SQL, here's a quick way to do it.
Changing how to model interacts with the database

So first we have to change over our task_manager model to use SQLite rather than
YAML.
app/models/task_manager.rb
def self.database
  if ENV["TASK_MANAGER_ENV"] == 'test'
    @database ||= Sequel.sqlite('db/task_manager_test.sqlite3')
  else
    @database ||= Sequel.sqlite('db/task_manager_dev.sqlite3')
  end
end
Notice here that we changed both our local variable database for when we are
running in a test environment and when we are in development. Look closely at
the filepath for where we are saving these files and notice that we have both a
task_manager_test as well as a task_manager_dev file inside of our db
file. This allows our app to create two separate databases in Sequel for us to
use.
If this is your first change from YAML, it is most likely that all of your tests
are now broken. The first step to getting this fixed is to include sequel and
sqlite in your Gemfile:
Gemfile
source 'https://rubygems.org'

...

gem 'sequel'
gem 'sqlite3'
After which, save the file and run bundle from the command line.
Changing how the data is actually created and structured

Now we have the location of where our database files should be, as well as the
proper gems to use SQLite. Our next step would be to actually create the
databases to use.
In db, create a new folder migrations and inside of that, create a new file
called 001_create_tasks.rb. The contents of this file will contain the
structure for how we want our Task instances to look.
db/migrations/001_create_tasks.rb
require 'sequel'

test_database = Sequel.sqlite('db/task_manager_test.sqlite3')
dev_database = Sequel.sqlite('db/task_manager_dev.sqlite3')

[test_database, dev_database].each do |database|
  database.create_table :tasks do
    primary_key :id
    String      :title
    Text        :description
  end
end
We have to explicitly require 'sequel' here because we are outside the scope
of our environment.rb folder.
The next two lines here are not so different from the database method inside of
our task_manager.rb. You'll notice that the filepaths are set up exactly the
same in both files. This is so that once we create our database tables, our
task_manager model will have access to them.
The last part of this is fairly straightforward. We are using the local variable
names we gave to our two databases inside an array, and running the enumerable
each on this array to create two separate database schemas, one for testing
and the other for development. They are set up in exactly the same way. We
create a table called :tasks, and give it the column attributes :id,
:title, and :description. The starting primary_key,
String, and Text words are the data types of each of our attributes,
respectively. primary_key is given to us through Sequel and works as a wrapper
for SQL's "INTEGER PRIMARY KEY AUTOINCREMENT" instruction.
Save this file, and change directories back to your app's root directory, and
run ruby db/migrations/001_create_tasks.rb. Nothing will appear in your
terminal's output, but if you cd into your db folder, you will now see that
task_manager_test.sqlite3 and task_manager_dev.sqlite3 files have been
created. This means that your model now has access to those databases when it
runs it's #database method.
Your tests are probably still failing, but you have now successfully switched
from a YAML file to SQLite. Your next step to getting your tests to work
probably lies in your task_manager.rb methods needing refactoring to work with
SQLite. I would start by changing your #delete_all method, since that is called
at the end of every test run in the #teardown method. Good luck!