Integrating ActiveRecord Into a Sinatra Project

By @zachfeldman
Written on Jan 27, 2014

This article is just a tad bit more advanced then our last one on How the Internet Works.

Sinatra is an open-source and super lightweight Ruby web application development framework. It’s a great way to learn about web application development without having to dive into the crazy amount of files and default folder structure of a framework like Rails. You can literally get a web application up and running with about 4 lines of code:

1
2
3
4
5
require 'sinatra' get '/' do "Hello World" end

This will provision a web application that, when accessed at the URL localhost:4567, will return the words “Hello World”.

But what if we want some more advanced functionality for our Sinatra app, specifically the ability to store data in a database? We’ll have to bring in a DBMS (DataBase Management System) like Sqlite3 or PostgreSQL. Since we’re working in Ruby, we’ll also need an ORM, or “Object-relational mapper” like DataMapper or ActiveRecord.

Since our students have the option of learning Rails in the second part of Introduction to Web Design and Development 100 in our Web Application Development with Ruby on Rails 101 track, we’re going to stick to ActiveRecord as it is the default ORM for Rails apps.

Gem Installation

To use ActiveRecord in your Sinatra project, you’ll need the following gems:

1
2
3
4
gem 'activerecord' gem 'sinatra-activerecord' gem 'sqlite3' gem 'rake'

After you’ve added these to your Gemfile, you’ll need to run the bundle install command inside of your project directory.

The ActiveRecord gem is to be used as our ORM, giving us the ability to basically alias tables inside of our database to easily accessible Ruby objects. For example, the users table that stores email, first name, and last name for each user can be accessed through the User object once we’re done with this tutorial. So you can write code like this:

1
2
3
all_users = User.all user_with_the_id_five = User.find(5)

sinatra-activerecord gives us the necessary adapters to allow ActiveRecord to work with Sinatra. sqlite3 is our database of choice so we need the sqlite3 gem to allow us to interface with the SQLite3 DBMS. Finally, rake is a gem that allows us to run tasks from the command line. A task is any Ruby program defined in what is known as a Rakefile, simply a file filled with different scripts to be run from the command line. The reason that we need rake for our purposes is that sinatra-activerecord contains Rake tasks to setup our database.

Database Setup

Now that we have all of the necessary libraries, we need to setup an actual database to store all of our data in. Require sinatra/activerecord in your project like so, right after the line where you’ve required Sinatra itself:

1
2
require 'sinatra' require 'sinatra/activerecord'

The next line of code we’ll write will setup the actual database for this specific project and specify to connect to it in the future for all database calls:

1
set :database, "sqlite3:<databasename>.sqlite3"

Make sure to replace with a topical database name, like myblogdb or project_db.

Rakefile

Similar to the Gemfile, your Rakefile is saved with that exact name and no file extension. Remember that Rake is simply a command line task runner, meaning that it runs small scripts when prompted to do so from the command line. Our Rakefile for this project needs to include a couple of tasks necessary to prepare our database for use, which are luckily already defined by sinatra/activerecord. All we need to do is require them in our Rakefile along with our main app file:

1
2
require 'sinatra/activerecord/rake' require './app'

This assumes that your main Sinatra app file is called app.rb. The second line requires that while the first requires all of the tasks we’ll need to groom our project database into shape!

Migrations

Migration files are small Ruby scripts meant to make changes to your database. Changing a migration file will not make a change to your database until the file is run.

Some examples of cases where a migration is used:

  • To create a new table in your database and define the columns it will contain as well as their data types
  • To drop a table in your database (completely erase it and all of its data)
  • To add a column to an existing table in your database
  • To rename a column or table in your database
  • To add an index to a database column
Migrations are typically NOT used to add actual data to your database, unless it is boilerplate data meant to stand in place of actual data. They are mostly used to define the schema of your database, meaning the structure of your tables and the columns within those.

A migration is actually a Ruby class with boilerplate methods meant to be run when the migration is run. To generate your first migration, use the create_migration rake task from the command line. Don’t forget to make sure you’re in your project’s directory with pwd first!

1
rake db:create_migration NAME=create_users_table

In this case I named my migration “createuserstable”. Never name your migration after the table that you are using it to create, rather give it a more descriptive name.

Once the migration has been generated, you can edit the file created by navigating to the new /db/migrate folder inside of your project directory. Migration files are automatically named after being created in the format:

_.rb

Inside of your migration, you’ll find code similar to the following:

1
2
3
4
5
class CreateUsersTable < ActiveRecord::Migration def change end end

As mentioned, a migration is simply a class that extends the ActiveRecord::Migration class to give it the functionality necessary to make changes to your database. Inside of this class are several instance methods that you’ll define to tell the database what changes you’d like made. You can either use the change method, which is the default, or you can use both an up and down method.  If you define an up method, that will be run when you initially run the migration while the down method will be run if you undo your migration (“undo” is known as a “rollback” here in ActiveRecord land). Conversely, the change method should contain the changes you’d like to make to your database when this migration is run and then, if you need to undo your migration later on, it will try to infer what you’ve changed about your database. For instance, here’s what the code would look like to create a Users table with an email column:

Using change

1
2
3
4
5
6
7
class CreateUsersTable < ActiveRecord::Migration def change create_table :users do |t| t.string :email end end end

Using up/down

1
2
3
4
5
6
7
8
9
10
11
class CreateUsersTable < ActiveRecord::Migration def up create_table :users do |t| t.string :email end end def down drop_table :users end end

Notice how when we use the up/down method combination we explicitly define that to rollback this migration you’ll need to drop the users table. When we only use the change method, we expect ActiveRecord to guess that if we created a table, the reverse of that would be to drop that table. For a list of migrations that ActiveRecord knows how to reverse, check out the ActiveRecord::Migration::CommandRecorder documentation.

Now that we know the different ways of writing a migration, what do we actually put in the up/down methods? Check out the ActiveRecord::Migration documentation for some examples, but let’s start this project by creating a users table with a few columns.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
class CreateUsersTable < ActiveRecord::Migration def up create_table :users do |t| t.string :email t.string :fname t.string :lname t.datetime :birthday t.datetime :created_at t.datetime :updated_at end end def down drop_table :users end end

Remember once again that just because we’ve defined a migration file, it doesn’t mean anything has been done to our database. In order for our changes to take effect, we need to run the database migration command:

1
rake db:migrate

If your migration ran successfully, you should see something like this:

1
2
3
4
== CreateUsersTable: migrating =============================================== -- create_table(:users) -> 0.0009s == CreateUsersTable: migrated (0.0010s) ======================================

Great! But oh no - we forgot to add the user’s most important detail, their birthday! Remember once again that changing a migration file does not change the database until a migration command is run. First, let’s roll back the migration we just ran:

1
rake db:rollback

Now we can go back to our migration and add in the missing column:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
class CreateUsersTable < ActiveRecord::Migration def up create_table :users do |t| t.string :email t.string :fname t.string :lname t.datetime :birthday t.datetime :created_at t.datetime :updated_at end end def down drop_table :users end end

Now we can run our migration again and our table will be created once again, except with that extra column:

1
rake db:migrate

Important notes:

  • If you rollback a create_table migration for a table that has had data inserted into it since you initially ran the migration, you will lose all of your data. Instead, create an entirely new migration file to make the changes you’re looking to make in this case.
  • If a migration has already been run, it will not be run again unless it has been rolled back

Models: Using ActiveRecord and Ruby classes to represent your data

Now that you have a users table, it’s time to add some data to it. In order to access this table from Ruby, you need to have a class that models the database table. ActiveRecord makes this really easy! Create a new file called models.rb and add the following code inside of it:

1
2
3
class User < ActiveRecord::Base end

The fact that User is singular is very important. Table names are plural (“the users table”) and model names should be singular (“the User model”) Next, require this file in your main app file beneath the line where you set your database using the following code:

1
require './models'

Manipulating the users table: adding and looking up records

Awesome! Now let’s play around with our User model a bit in IRB, the Interactive RuBy terminal. Type irb in the terminal and require your main app file:

1
require './app'

Try typing in User and you should see a short description of the user object, which ActiveRecord has inferred based on your table’s schema, which you set when you created the table with your migration:

1
2
irb(main):002:0> User => User(id: integer, email: string, fname: string, lname: string, birthday: datetime, created_at: datetime, updated_at: datatime)

Let’s create a new User record. Try the following code:

1
2
3
4
5
6
irb(main):001:0> User.create(email: "zach@nycda.com", fname: "Zach", lname: "Feldman", birthday: Time.parse("1990-06-15 01:00:01 -0500")) D, [2014-01-27T15:24:32.878900 #4714] DEBUG -- : (0.1ms) begin transaction D, [2014-01-27T15:24:32.888642 #4714] DEBUG -- : SQL (7.1ms) INSERT INTO "users" ("birthday", "created_at", "email", "fname", "lname", "updated_at") VALUES (?, ?, ?, ?, ?, ?) [["birthday", 1990-06-15 02:00:01 -0400], ["created_at", 2014-01-27 20:24:32 UTC], ["email", "zach@nycda.com"], ["fname", "Zach"], ["lname", "Feldman"], ["updated_at", 2014-01-27 20:24:32 UTC]] D, [2014-01-27T15:24:32.889684 #4714] DEBUG -- : (0.7ms) commit transaction => #<User id: 1, email: "zach@nycda.com", fname: "Zach", lname: "Feldman", birthday: "1990-06-15 02:00:01", created_at: "2014-01-27 20:24:32", updated_at: "2014-01-27 20:24:32">

You’ll notice that ActiveRecord converted your command into a SQL query (INSERT INTO…), which it runs against your specified database and table. Since we’re using the User object, that table would be the users table.

Now that we have a user record in the users table, let’s look it up by its primary key, the ID.

1
2
3
irb(main):003:0> User.find(1) D, [2014-01-27T15:26:21.990869 #4714] DEBUG -- : User Load (0.2ms) SELECT "users".* FROM "users" WHERE "users"."id" = ? LIMIT 1 [["id", 1]] => #<User id: 1, email: "zach@nycda.com", fname: "Zach", lname: "Feldman", birthday: "1990-06-15 06:00:01", created_at: "2014-01-27 20:24:32", updated_at: "2014-01-27 20:24:32">

This returns a user record. If we assign it to a variable, we can query it for specific data:

1
2
3
4
5
irb(main):004:0> my_user = User.find(1) D, [2014-01-27T15:27:58.000790 #4714] DEBUG -- : User Load (0.1ms) SELECT "users".* FROM "users" WHERE "users"."id" = ? LIMIT 1 [["id", 1]] => #<User id: 1, email: "zach@nycda.com", fname: "Zach", lname: "Feldman", birthday: "1990-06-15 06:00:01", created_at: "2014-01-27 20:24:32", updated_at: "2014-01-27 20:24:32"> irb(main):005:0> my_user.email => "zach@nycda.com"

We can also get an array of all existing users:

1
2
3
4
5
irb(main):006:0> all_users = User.all D, [2014-01-27T15:30:18.799857 #4714] DEBUG -- : User Load (0.2ms) SELECT "users".* FROM "users" => #<ActiveRecord::Relation [#<User id: 1, email: "zach@nycda.com", fname: "Zach", lname: "Feldman", birthday: "1990-06-15 06:00:01", created_at: "2014-01-27 20:24:32", updated_at: "2014-01-27 20:24:32">]> irb(main):007:0> all_users[0] => #<User id: 1, email: "zach@nycda.com", fname: "Zach", lname: "Feldman", birthday: "1990-06-15 06:00:01", created_at: "2014-01-27 20:24:32", updated_at: "2014-01-27 20:24:32">

These are some of the basics possible with ActiveRecord. For some more information, check out this Active Record Querying Guide from the Rails documentation.

Now that you have a users model and understand the basics of ActiveRecord, you can generate the schema necessary for whatever tables your database needs. You can also use the ActiveRecord create, find, all, and any other methods to retrieve and add data to the database. These calls can be made directly inside of your Sinatra routes:

1
2
3
4
5
get '/' do @user = User.find(4) erb :home end

Now, inside of your view (home.erb), you could write some code to display that user’s information:

1
<%= @user.fname %>

As you can imagine, the possibilities are endless! Enjoy them and have fun with ActiveRecord.

X-posted from the New York Code + Design Academy blog.





Sign up for our e-mail list to hear about new posts.