Clearing RSpec test databases with Padrino and Sequel

Test-driven development is not just another stupid hype. It makes you confident that your code works and avoids situations where you have to explain your clients why that old bug suddenly appeared again. That is especially true when writing web applications. This little snippet makes sure your tests run on a clean database.

Many developers seem to take an easy route to testing their web applications. They rather use an SQLite in-memory database for testing by specifying their database as “sqlite:///”. Of course your database would be clean on every run of your tests. Oh, and it’s really fast. But in many non-trivial cases you don’t test well. SQLite has many limitations opposed to “real” DBMSs like PostgreSQL or even MySQL. So if you want to enforce date fields or foreign keys SQLite may lull you into a false sense of security. I recommend using the real DBMS to make the tests more realistic.

Bad:
when :test then Sequel.connect(“sqlite:///”, :loggers => [logger])

Good:
when :test then Sequel.connect(“mysql://testuser:testpassword@server/database_test”, :loggers => [logger])

One caveat is though that your tests will inevitably write data into the test database. Unless you delete that data your next run of tests will start with that data from the previous run. That will lead to funny results. So you need to make sure that before each test run the tables are emptied.

First I tried to use the database_cleaner gem. Unfortunately it does not support DELETE’ing rows using Sequel but can just TRUNCATE tables. Well, TRUNCATE is usually faster anyway. Unfortunately MySQL has a shortcoming: it does not support to TRUNCATE tables with foreign keys and an “ON DELETE CASCADE”. So you have to DELETE all rows to trigger the delete cascade. So the combination of MySQL, Sequel, database_cleaner and DELETE-CASCADE does not work. I believe that database_cleaner’s functionality is aimed at at ActiveRecord that does not care about referential integrity on a database level. But I am using Sequel because I do care about my database schema.

Surprisingly the functionality of database_cleaner was pretty simple in this case. So to finally provide you with the solution… just add this code into your Padrino applications’s spec/spec_helper.rb code within the “RSpec.configure do |conf|” block:

conf.before(:each) do
  existing_tables = Sequel::Model.db.tables
  tables_to_preserve = [:schema_info, :schema_migrations]
  tables_to_be_emptied = existing_tables - tables_to_preserve
  tables_to_be_emptied.each do |table|
    Sequel::Model.db[table].delete
  end
end

It will get a list of tables and run “DELETE FROM …” on them. However it will spare the “schema_info” and “schema_migration” tables because they contain valuable meta information needed for migrations.

Voila… now you can safely run your tests and be sure that you have clean database tables on every test.