Connecting a Pakyow app to Postgres with Sequel.

Pakyow doesn't ship with an ORM (object-relational mapper) layer. This reflects a design decision we made early on to be unopinionated about such things. Instead, we let the user choose the ORM that they're most comfortable with. There are some good options out there, including Sequel, ROM, and ActiveRecord.

The ORM we use most often at Metabahn is Sequel. It's stable, performant, and stays out of our way. Today I'd like to show how we've integrated Sequel into the dozens of Pakyow apps we've shipped to production over the last couple of years. Postgres is our database of choice in most cases, so we'll be talking specifically about that here.

For reference, you can find the complete app here.

Setting up dependencies

Make sure you have Postgres installed and running locally. If you're on a Mac, Postgres.app is the easiest way to get going. Once Postgres is running, open up your Gemfile and add three new gems:

group :development do
  gem "dotenv"
end

gem "sequel"
gem "pg"

Note that we only want to use dotenv in development as there are better configuration strategies for production environments.

Run bundle install to make sure your dependencies are up to date. Next, open app.rb and require sequel (after require 'bundler/setup'):

require 'sequel'

It's also helpful to have Sequel automatically add the created at and updated at timestamps to our data. To enable this behavior, add the following code underneath the call to require:

Sequel::Model.plugin :timestamps, update_on_create: true

Inside the development configuration block add the following code to load your environment:

configure :development do
  require 'dotenv'
  Dotenv.load
end

You're good to go!

Environment-specific configuration

We'll use the Dotenv library to keep configuration details out of app.rb. Create a .env file in the root application directory (it's also a good idea to ignore in your .gitignore file). Define a variable that contains the database connection string:

DATABASE_URL=postgres://{user}@localhost/{database}

Replace {user} and {database} with your own username and database you'd like to use.

Creating the database connection

Back in app.rb add the following bit of code to the development configuration block to create a database connection:

$db = Sequel.connect(ENV['DATABASE_URL'])

This tells Sequel to connect to the database configured for your environment. It stores this connection in a global variable for use throughout the application. Note that you'll need to setup the connection in your production environment as well based on your production configuration strategy. If you're deploying to Heroku you can use the exact same code.

Setting up models + migrations

Now that we have a connection, let's create a model. For our purposes, let's create a model that represents a blog post. Create a new app/lib/models directory with a new file named post_model.rb. Add the following code:

class Post < Sequel::Model; end

Next, we need to create a migration that defines the schema for our new model. Create a migrations directory in the root application directory with a new file named 001_create_posts.rb. Add the following code:

Sequel.migration do
  up do
    create_table :posts do
      primary_key :id
      String :title
      Text :body
      DateTime :created_at
      DateTime :updated_at
    end
  end

  down do
    drop_table :posts
  end
end

Now Sequel will automatically create our tables when running the migrations. Note that the migration file names are important. You can read more about migrations the naming strategy itself in the Sequel docs.

Database maintenance tools

We perform database maintenance via Rake tasks. I've published an example rakefile that contains common tasks for creating, deleting, and migrating your database. Copy these tasks into your Rakefile and run rake db:setup to create and migrate your database.

Presenting data in a Pakyow view

Before we can present data we should create some for testing. To keep it easy for this tutorial, we'll use console. Run pakyow console and enter the following commands:

irb(main):001:0> Post.create(title: 'One', body: 'This is the first post!')
=> #<Post @values={:id=>1, :title=>"One", :body=>"This is the first post!", :created_at=>2015-04-10 16:12:40 -0500, :updated_at=>2015-04-10 16:12:40 -0500}>
irb(main):002:0> Post.create(title: 'Two', body: 'This is the second post!')
=> #<Post @values={:id=>2, :title=>"Two", :body=>"This is the second post!", :created_at=>2015-04-10 16:12:49 -0500, :updated_at=>2015-04-10 16:12:49 -0500}>
irb(main):003:0> Post.create(title: 'Three', body: 'This is the third post!')
=> #<Post @values={:id=>3, :title=>"Three", :body=>"This is the third post!", :created_at=>2015-04-10 16:12:55 -0500, :updated_at=>2015-04-10 16:12:55 -0500}>

Now we have three posts in our database. Type exit and hit enter to exit console.

Let's create a view that we'll use to present our posts. Create an index.html file in app/views. Add the following HTML:

<div data-scope="post">
  <h1 data-prop="title">
    This is the post title
  </h1>

  <p data-prop="body">
    Post body goes here.
  </p>
</div>

Run pakyow server to start the server, then navigate to localhost:3000 to see the new view. Now let's bind our post data to it. Open app/lib/routes.rb and define a default route. Here's what it should look like:

Pakyow::App.routes do
  default do
    view.scope(:post).apply(Post.all)
  end
end

Reload your browser and you'll see three three posts we created earlier.

Conclusion

That's all there is to it! I hope this gives you some valuable knowledge in building your next Pakyow app. Hit problems or have questions? Post on Stack Overflow or ask us for help on Gitter. Thanks for reading!