This is part 2 of my Sinatra blog tutorial. Today I’m talking about the database layer. Don’t forget to check out the full source of my blog on github.

 

 

I used sequel as the ORM for this blog. I had originally wanted to use DataMapper, but I ran into some problems. They’re pretty similar anyway.

First, we need to give the connection information to Sequel. I put my database info in a yaml file because I wanted to be able to pass the command-line migration tool the same info. I require this file from my “controller”. It’s important to remember that the following files aren’t a sinatra standard or anything. You can organize your files however you like.

data/init.rb

require 'rubygems'
require 'sequel'
require 'yaml'

content = File.new("data/database.yml").read
settings = YAML::load content
DB = Sequel.connect "#{settings['adapter']}://#{settings['username']}:#{settings['password']}@#{settings['host']}/#{settings['database']}"

require 'data/models'

data/database.yml

adapter: mysql
host: localhost
username: root
password: root
database: custom_blog

Here are my models. The syntax of these models will look similar to active record. There aren’t much to them. A few things to notice are that I used simple many_to_many and one_to_many associations.

data/models.rb

require 'rubygems'
require 'sequel'

class Post < Sequel::Model
  Page = "page"
  Post = "post"

  many_to_many :tags
  one_to_many :comments

  def date
    created.strftime "%B %d, %Y"
  end

  def summary(length=300)
    body.gsub(/(<[^>]*>)|\n|\t/s," ")[0..length]
  end

  def update_title(value)

    raise "[ ! ] Could not find title for post" if value.nil?

    self.title = value
    self.name = value.downcase.gsub(/[^\w]/,"_").gsub(/__/,"")
  end
end

class Tag < Sequel::Model
  many_to_many :posts, :order => :created.desc
end

class Comment < Sequel::Model
  many_to_one :posts

  def post
    Post[:id => post_id]
  end
end

These behave as expected. The syntax is only slightly different from active record. Sequel allows you to chain commands to a dataset together, which makes it easy to create complex queries.

Post.all
Post.first.comments
Post.filter(:kind => Post::Page).reverse_order(:created).all
Post.first.tags

I’ll give you more concrete examples when we cover the controller. The next thing to look at is the migration. Now, I don’t really like migrations, but until these ORMs add support to dynamically update the database schema, I’m stuck with them. Here’s where I’m at currently.

data/001_BaseSchema.rb

require 'data/models'

class BaseSchema < Sequel::Migration
  def up
    create_table! :posts do
      primary_key :id
      String :title
      Text :body
      Time :mtime
      Time :created
      String :kind, :default => Post::Post
      String :file
      String :name
    end

    create_table! :tags do 
      primary_key :id
      String :name
    end

    create_table! :posts_tags do
      primary_key :id
      foreign_key :tag_id, :tags
      foreign_key :post_id, :posts
    end      
  end

  def down
    drop_table :posts
    drop_table :tags
    drop_table :posts_tags
  end
end

data/002_Comments.rb

require 'data/models'

class Comments < Sequel::Migration
  def up
    create_table! :comments do
      primary_key :id
      foreign_key :post_id, :posts

      String :name
      String :email
      String :url
      Text :body
    end
  end

  def down
    drop_table :comments
  end
end

To run the migrations, you use the sequel command-line tool. Here, I’m telling it to run migrations from my data folder, and to use data/database.yml for connection info.

sequel -m data data/database.yml
sequel -m data data/database.yml -M 1 # migrates to 001

That’s it for now!

 

 

 


Please more more more Sinatra usage examples!
I'm working with another person on Sinatra, Sequel, Shotgun and Mysql application. database.yml has db users settings. How db users suppose to be setup? Symphony framework had a system for setting up the initial things like db users and etc.

Can this be done here?
How to build mode with rake?