Setting up sequel sqlite3

Using Sequel (http://sequel.rubyforge.org/) connected to a sqlite3 (http://www.sqlite.org/) file database.

Overview

One of the major advantages of Ramaze is that it does not include database support. Yes, you read that right! We do not have to figure out how to disable or replace a magical database that works by convention. Whoooop! Instead we get to tell it exactly what we want. So let's do it!

Sequel uses the external migration file paradigm for defining the schema, then the models retrieve their schema from the database. So we will need rake tasks for creating, migrating, rolling back the database. These rake tasks will need a URL string for the sequel command line command.

Our models will need a connection to the database. The connection can be established given the URL string for the database.

Ramaze supports two runtime modes by default, :live and :dev. Lets add a third, :test. So our database configuration will need to support the runtime modes.

So given a runtime mode, we need a method that returns the URL connection string. This method needs to be usable by both our ramaze app and our Rakefile.

For the migrations we will need a directory to store them in.

That should be enough to get started.

Directories

∴ mkdir -p db/migrations
∴ mkdir -p db/data

Configuration

Then our arbitrarily chosen db/config.rb file:

require 'sequel'
require 'fileutils'
APP_NAME='the-greatest-app-ever'
module Database
  DB_MODES = %w{dev live test}
  DB_DIR = File.expand_path('data', File.dirname(__FILE__))
  FileUtils.mkdir_p DB_DIR
  def self.url(mode)
    raise "Unsupported runtime mode (Ramaze.options.mode): #{mode.inspect}" unless DB_MODES.include? mode.to_s
    "sqlite://#{DB_DIR}/#{APP_NAME}-#{mode.to_s}.db"
  end
end

Rake Tasks

Our new rake tasks the the Rakefile:

require_relative 'db/config'
namespace 'db' do
  desc "Run database migrations where mode is: #{Database::DB_MODES.join(', ')}"
  task :migrate, :mode do |t, args|
    cmd = "sequel -m db/migrations #{Database.url(mode(args[:mode]))}"
    puts cmd
    puts `#{cmd}`
  end

  desc 'Zap the database my running all the down migrations'
  task :zap, [:mode] do |t, args|
    cmd = "sequel -m db/migrations -M 0 #{Database.url(mode(args[:mode]))}"
    puts cmd
    puts `#{cmd}`
  end

  desc 'Reset the database then run the migrations'
  task :reset, [:mode] => [:zap, :migrate]
end

def mode(arg)
  mode = arg
  if mode.nil? || mode.strip.empty?
    mode = 'dev'
  end
  mode.to_sym
end

App Initialization

And our current app.rb with the new db hook:

# This file contains your application, it requires dependencies and necessary parts of 
# the application.
#
# It will be required from either `config.ru` or `start.rb`
require 'rubygems'
require 'bundler/setup'
require 'ramaze'

# Make sure that Ramaze knows where you are
Ramaze.options.roots = [__DIR__]

Ramaze::Log.loggers = [Logger.new($stdout)]

# The mode defaults to :dev
mode = ENV['Ramaze.options.mode'] || 'dev'
Ramaze.options.mode = mode.to_sym
puts "Ramaze.options.mode => #{Ramaze.options.mode.inspect}"

require_relative 'db/config'
DB = Sequel.connect(Database.url(Ramaze.options.mode))

# Initialize controllers and models
require __DIR__('model/init')
require __DIR__('controller/init')

Test Environment

A small digression, we need to go back to our features/support/env.rb and spec/spec_helper.rb files and replace:

require_relative('../app')

with

require 'ramaze'
ENV['Ramaze.options.mode'] = :test.to_s
require_relative('../app')

First migration

Our first migration will simply create the :users table with :id, :name, and :email columns.

∴ cat db/migrations/001_create_user.rb
Sequel.migration do
  up do
    create_table(:users) do
      primary_key :id
      String :name, :null=>false
      String :email, :null=>false
    end
  end

  down do
    drop_table(:users)
  end
end

And running the migration for :test yields:

∴ rake db:migrate[test]
sequel -m db/migrations sqlite:///Volumes/MacHD/Users/royw/projects/the-greatest-app-ever/db/data/the-greatest-app-ever-test.db

Verify:

∴ sqlite3 db/data/the-greatest-app-ever-test.db
SQLite version 3.7.10 2012-01-16 13:28:40
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .schema
CREATE TABLE `schema_info` (`version` integer DEFAULT (0) NOT NULL);
CREATE TABLE `users` (`id` integer PRIMARY KEY AUTOINCREMENT, `name` varchar(255) NOT NULL, `email` varchar(255) NOT NULL);
sqlite> .quit

User model

Add the User model:

∴ cat model/user.rb

class User < Sequel::Model(:users)

end

Hook into the model/init.rb:

∴ cat model/init.rb
# This file is used for loading all your models. Note that you don't have to actually use
# this file. The great thing about Ramaze is that you're free to change it the way you see
# fit.

# Here go your requires for models:
# require __DIR__('user')
Dir["#{__DIR__}/**/*.rb"].reject{|fn| ['init.rb'].include? File.basename(fn)}.each {|fn| require fn}