Understanding database cleaning strategies in tests

TLDR: In tests you need to clean out the database before each example. Use :transaction where possible. Use :deletion for Selenium features or when you have a lot of MyISAM tables.

Understanding database cleaning

You want to clean out your test database after each test, so the next test can start from a blank database. To do so you have three options:

• Wrap each test in a transaction which is rolled back when you're done (through DatabaseCleaner.strategy = :transaction or config.use_transactional_fixtures = true)
• Truncate Show archive.org snapshot all tables after each test (through DatabaseCleaner.strategy = :truncation)
• Delete all rows after each test (through DatabaseCleaner.strategy = :deletion)

Additionally there are some constraints for when you can use transactions:

• Transactions cannot be used for Selenium features, where Rails and test run in different processes and thus don't see changes within the transaction of another process.
• Transactions are not available for MyISAM tables (which you might use for FULLTEXT indexes).

Performance of different cleaning strategies

I measured the runtime of different strategies using an average-sized Rails project (with MySQL):

What we can take away from this:

• Use transactions where possible. It is the faster option all around, and will scale better than deletion as your number of records grow.
• Use deletion for Selenium features ( here's why Show archive.org snapshot ) or when you have a lot of MyISAM tables. You can also use truncation, but it's maybe a bit slower.
• Avoid truncation. In fact you should probably grep your project for :truncation and replace it with :deletion. An exception to this rule is when you are using a lot of foreign key constraints Show archive.org snapshot (you probably don't). In that case truncation might outperform deletion (but I didn't measure that).
• Take care to set config.use_transactional_fixtures = false when moving away from transactions. Using both transactions and another cleaning strategy will drag down your test performance by quite a bit.