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 = :transactionor
config.use_transactional_fixtures = true)
Truncate 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):
|Transaction||87.14, 86.65||10.20, 10.11|
|Truncation||90.43, 90.69||26.82, 26.46|
|Deletion||86.20, 85.61||12.08, 12.12|
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 or when you have a lot of MyISAM tables.
- Avoid truncation. In fact you should probably grep your project for
:truncationand replace it with
:deletion. An exception to this rule is when you are using a lot of foreign key constraints (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 = falsewhen moving away from transactions. Using both transactions and another cleaning strategy will drag down your test performance by quite a bit.