Script

Welcome to the Configuring ActiveRecord in Sinatra screencast. In a previous episode, Using ActiveRecord with Sinatra, we showed you how to use ActiveRecord and Sinatra, and built a URL shortener. We showed how to get ActiveRecord up and running in your Sinatra application, and now in this screencast, we'll show you how to configure it.

Default Behaviour

By default the ActiveRecord Sinatra gem (sinatra-activerecord) looks for the environment variable DATABASE_URL. You wouldn't have this set on development machines. This means the default adapter is SQLite, and the database name is the name of the current environment.

Building DATABASE_URL

Here's how you would compose a DATABASE_URL.

<adapter>://<username>:<password>@<host>:<port>/<database_name>

The name of the database adapter would be where the scheme would normally be. So postgres, mysql, sqlite and so forth, would be in place of http or https.

postgres://<username>:<password>@<host>:<port>/<database_name>
mysql://<username>:<password>@<host>:<port>/<database_name>
sqlite://<host>

If you have a username and password, enter them in after the :// (colon double forward slash), separating the username and password with a colon, then following them with an @ symbol. If you don't have a password, you can leave it blank. SQLite doesn't require a username and password.

postgres://joe:12345@<host>:<port>/<database_name>
mysql://joe:@<host>:<port>/<database_name>
sqlite://<host>

After the @ symbol, add the host name. In development, this is usually localhost. If you're using MySQL or PostgreSQL on your local machine, and depending how your database is set up to listen, the hostname might be 127.0.0.1, 0.0.0.0 or localhost. If you're using SQLite, the hostname you provide will be used as the name of your database. Generally, this is the file name you'll want. So in the case of SQLite, you're done with writing your database URL. Beware of a gotcha here: since hostnames can't have underscores in them, your SQLite database name can only have letters, numbers, dashes and periods in it. So database_name.db isn't allowed, whereas database-name.db is.

postgres://user_name:sup3rs3cr3t@localhost:<port>/<database_name>
mysql://user_name:@127.0.0.1:<port>/<database_name>
sqlite://custom-database-name.db

Next, you can specify the port. If you have the default setup of MySQL or PostgreSQL, then you don't need specify the port. If your server is on a custom port, you specify it by adding a colon, followed by the port, after the host.

postgres://user_name:sup3rs3cr3t@localhost/<database_name>
mysql://user_name:@127.0.0.1:1234/<database_name>

Finally, the path after the forward slash is the name of the the database you want to connect to on the server.

postgres://user_name:sup3rs3cr3t@localhost/production
mysql://user_name:@127.0.0.1:1234/development

Setting DATABASE_URL in the Terminal

So, if you wanted to set a custom DATABASE_URL for development, all you would need to do is type into your terminal export DATABASE_URL= followed by your custom database URL.

export DATABASE_URL=<custom-url>

Then, run your migrations and Sinatra instance, and you're up and running.

One quick aside: depending on the permissions of the Postgres or MySQL user, you may not be able to create the database, even though you may have read/write access. You may need to create the database first before running your migrations.

Every time you open a new terminal window when running migrations or starting your application, you'll need to run the export command again. You could add this export to a .bashrc or .zshrc file, but if you're developing multiple sites, this may not be practical.

It may be more sensible to setup some configuration in the code that checks your environment per application. We'll go into this in just a second.

Deploying to Heroku

Let's talk about deploying to Heroku.

Most of the time you'll be developing on a local box with no DATABASE_URL set, and will be working with the default SQLite database. In other words, you'll be working with the defaults. If this is the case, then no further configuration is needed, as Heroku has the DATABASE_URL environment variable already set.

Once deployed your can run your migrations with zero configuration.

Deploying with Passenger

Similarly, if you don't want to write custom configuration code when deploying to Phusion Passenger, Apache allows you to set environment variables with the SetEnv directive. So, when deploying to Apache with Phusion Passenger, in your VirtualHost configuration file, you'd need to add SetEnv DATABASE_URL, followed by your custom database URL.

SetEnv DATABASE_URL  sqlite://custom.db

Note, the DATABASE_URL will then be exposed to applications running within your Apache server. However, this doesn't add it to your command line. So, if you want to run your rake tasks for database creation and migration, you'll need to set the DATABASE_URL in the terminal before running them.

Configuring with Code

There may be circumstances where you want to set different database URLs in the code, depending on which environment the application is running in. Doing the configuration in the code offers more flexibility than doing it via the methods we've covered so far.

To set the database URL in your application manually, all you need to do is set :database to the the URL you want it to be, after your includes in your Sinatra application.

...
set :database, 'sqlite://custom-in-code.db'
...

If you run rake db:migrate from the terminal, you'll now see that the custom-in-code.db has been created.

If we wanted to setup custom URLs for different environments, we could do this by setting up configuration blocks. We'd write configure, followed by the environment, followed by a block of code containing our variables.

...
configure :development do
  set :database, 'sqlite://custom-dev.db'
end

configure :production do
  set :database, 'sqlite://custom-production.db'
end

configure :test do
  set :database, 'sqlite://custom-test.db'
end
...

Once again, if we run our migrations, we see that the custom-dev.db is created. If we change our rack environment to production by setting RACK_ENV=prodcution when we run our migrations, we see that custom-production.db is created.

Alternatively you could setup a custom configuration hash, with symbols of environment names as keys pointing to their respective database URLs. You can access the current environment from the settings object by calling .environment on it.

database_urls = {
    :development => 'sqlite://custom-dev.db'
    :production     => 'sqlite://custom-production.db',
    :test       => 'sqlite://custom-test.db'
}
set :database, database_urls[settings.environment]

If we again run db:migrate in a new terminal window, the development database URL will be selected from the database_urls hash.

OK, so that's how you set up the different environments with database URLs.

Additional Database configuration

There may come a time when you want to configure additional options for your database connection. For example, you may want to set the connection pool and timeout values.

This can be done by setting a hash of options to :database_extras. You should set your options before you set your :database URL.

So you'd do something like this: set :database_extras, then {:pool => 5, :timeout => 3000 }.

configure :development do 
  set :database_extras, {:pool => 5, :timeout => 3000}
  set :database,  'sqlite://custom-dev.db'
end

And that's all you'll probably need to know about configuring ActiveRecord with Sinatra.

Thanks for watching! Subscribe to our RSS feed, follow us on Twitter, and please leave any questions, comments or suggestions for new screencasts in the comments below. If you like our videos, and think your friends, followers or colleagues would benefit from seeing them, please feel free share via any of the links below the video. We really appreciate your support.

See you next time!