Setting up a Pakyow App with Sequel

A few weeks ago, I wrote a post about configuring ActiveRecord for Pakyow. ActiveRecord works great with Pakyow apps, but the Pakyow team recommend using Sequel. Sequel is great for deeper and more arbitrary access to the database, but it also includes an ORM for models. See the resources section at the bottom for more information on Sequel and its integration with Pakyow.

Setting up Sequel in a Pakyow app is relatively simple, and an including the pakyow-rake-db gem will include rake database tasks which make managing your database a breeze.

I will be using sqlite3 for this tutorial, but feel free to experiment with your favorite database. (The pakyow-rake-db gem works particularly well with postgres.) The configuration here is consistent with deployment to Heroku, which uses the DATABASE_URL environment variable to set up the database.

Creating a new app with the necessary dependencies

First things first, we need to create a new app and change to the directory.

$> pakyow new sequel-test
$> cd sequel-test

We will need to install 3 additional dependencies: sequel, sqlite3, and pakyow-rake-db by adding them to the Gemfile and installing with bundler. Note that we specify require: false for the pakyow-rake-db gem. This is because the gem loads rake tasks when it is required, and we aren’t interested in those being loaded with the application. We will require them specifically later.

# in Gemfile
gem 'sequel'
gem 'sqlite3'
gem 'pakyow-rake-db', github: 'bryanp/pakyow-rake-db', require: false
# on the command line
$> bundle install

Connecting to the database

Since we are using sqlite3 for our database, we can create a directory in our project to keep our database file.

$> mkdir db

In .env.development we will export the DATABASE_URL environment variable. Pakyow uses the dotenv gem to load environment variables from files in the project directory. The .env.development file will only be loaded when the application is running in the development environment. That will leave us free to specify a different database in production and other environments.

# in .env.development
DATABASE_URL=sqlite:///path/to/sequel-test/db/development.sqlite3

Note: The path after sqlite:// above should be the absolute path to your database file.

With the DATABASE_URL environment variable set, we can configure Sequel to use this as the database for the app. The app/setup.rb file is where your app configuration lives. We will add the Sequel configuration to the global configuration block, so that the database connection is setup for every environment, and we will trust that our environments correctly set the appropriate DATABASE_URL.

# in app/setup.rb
#...
Pakyow::App.define do
configure do
# ...
app.db = Sequel.connect(ENV['DATABASE_URL'])
end
# ...
end

We store the Sequel database configuration in the app.db configuration because the pakyow-rake-db gem uses this configuration variable to access the database and execute SQL.

Setting up models

Sequel::Model uses the active record pattern. This means that an instance of the model class represents a record of the table affiliated with that model in the database. Table names are assumed to be the plural, underscored name of the model class (e.g. model: ReportCard => table: report_cards).

To set up a model, therefore we need to do two things:

  1. Create the table in the database; and
  2. Write a class definition.

The best way to change the database is with migrations, which will generally allow you to easily and consistently apply updates and rollbacks.

The pakyow-rake-db gem expects migrations to live in the migrations directory, so we will create that and write a migration.

$> mkdir migrations
# in migrations/001_create_books.rb
# Create Migration for books table
Sequel.migration do
up do
create_table :books do
String :isbn, primary_key: true
String :title
Date :published_on
end
end

down do
drop_table :books
end
end

Now that we have the table, we can move onto building the model class. If you have read my previous post on the layout of a Pakyow app, you will know that we are relatively free to put our code anywhere in the project directory. I like to put my models in app/lib/models, so that they will be automatically loaded when the app starts.

$> mkdir app/lib/models
# in app/lib/models/book.rb
class Book < Sequel::Model
# enable mass assignment of primary key (isbn)
unrestrict_primary_key
  # ... validations, etc.
end

Creating some seed data

It might be nice to start developing our application with some existing data. Seeds are a good way to accomplish this.

Pakyow-rake-db expects be able to load seed data from config/seeds.rb, so we will need to create our seeds file there.

$> mkdir config
# in config/seeds.rb
Book.create(
isbn: '978-0545139700',
title: 'Harry Potter and the Deathly Hallows',
published_on: Date.new(2009, 7, 1)
)
Book.create(
isbn: '978-0439784542',
title: 'Harry Potter and the Half-Blood Prince',
published_on: Date.new(2005, 8, 1)
)
Book.create(
isbn: '978-0439358064',
title: 'Harry Potter and the Order of the Phoenix',
published_on: Date.new(2003, 7, 1)
)
# ...

Running database rake tasks

Before we can run our application, we need to create the database and run the migrations to create the table we defined. Thanks to the pakyow-rake-db gem, we have access to some handy rake tasks that will allow us to accomplish this.

We simply need to require the gem in our Rakefile:

# in Rakefile
require 'pakyow-rake'
require 'pakyow-rake-db'

Now if we run rake -T, we get a list of the tasks this added for us in the db namespace.

$> rake -T
rake db:create # Create the database
rake db:drop # Drop the database
rake db:migrate[version] # Migrate the database
rake db:reset # Drop and setup the database
rake db:seed # Seed the database
rake db:setup # Create, migrate, and seed the database
rake db:terminate # Fix 'database is being accessed by other...
# ...

From here, we can just run rake db:setup and start development on our application.

Sqlite3 gotchas with pakyow-rake-db

Unfortunately for sqlite3 databases, not all of these tasks work, but no need to worry, there are simple work arounds:

  • rake db:create: not necessary for sqlite3, rake db:migrate will create the database
  • rake db:drop: just delete the database file.
  • rake db:reset; fails because of the above: delete the database file and run rake db:migrate, rake db:seed
  • rake db:setup; fails because of rake db:create: run rake db:migrate, rake db:seed
  • rake db:terminate: not necessary for sqlite3 (postgres specific)

Finishing up

I hope this helps someone get started with Sequel and Pakyow. If you have never worked with Sequel before, I encourage you to give it a try and see how it enables you to work with your database in perhaps a different way than you are used to.

Resources: