Rodrigo Rosenfeld Rosas

Using RSpec Nested Transactions to speed up tests touching the database

Fri, 05 Aug 2016 22:24:00 +0000 (Updated at Mon, 08 Aug 2016 13:05:00 +0000)

TLDR: This article proposes savepoints to implement nested transactions, which are supported by PostgreSQL, Oracle, Microsoft SQL Server, MySQL (with InnoDB but I think some statements would automatically cause an implicit commit, so I'm not sure it works well with MySQL) and other vendors, but not by some vendors or engines. So, if using savepoints or nested transactions are not possible with your database most likely this article won't be useful to you. Also, not all ORM provide support for savepoints in their API. I know Sequel and ActiveRecord do. It also provides a link on how to achieve the same goal with Minitest.

I've been feeling lonely about my take on tests for a long time. I've read many articles on tests in the past years and most of them, not only in the Ruby community, seem to give us the same advices. Good advices by the way. I understand the reasoning about them but I also understand they come with trade-offs and this is where I feel kind of lonely. All articles I've read and some people that have worked with me have tried to convince me that I'm just plain wrong.

I never cared much about this but I never wrote about it either as I thought no one would be interested in learning about some techniques I've been using for quite some years to speed up my tests. Because it seems everything would simply tell me I'd go to hell for writing tests this way.

A few weeks ago I read this article from Travis Hunter which reminded me of an old TO-DO. More importantly, it made me realize I wasn't that lonely in thinking the way I do about tests.

"Bullshit! I came here because the titles said my tests would be faster, I'm not interested in your long stories!". Sure, feel free to completely skip the next section and go straight to the fun section.


I graduated in Electrical Engineering after 5 years in the college. Then more two years working on my master thesis on hard real-time systems towards mobile robotics. I think there are two things which engineers in general get used to after a few years in the college. Almost everything involves trade-offs and one of the most important jobs of an engineering is to identify them and choose the one they consider to have the best cost benefit. The other one is related to the first one in knowing that some tools will better fit a set of goals. I mean, I know this is also understood by CS and similar graduated people, but I have this feeling it's not as strong in general in those areas as I observe in some (electrical/mechanical/civil) engineers.

When I started using RSpec and Object Daddy (many of you may only know Factory Girl these days), a popular factory tool by that time, I noticed my suite would take almost a minute for just a few examples touching the database. That would certainly slow me down as I would have to add many more tests.

But I felt really bad when I complained about that once in the RSpec mailing list and David Chemlinsky mentioned about taking 54s to run a couple of hundred examples when actually I had only 54 examples in my suite by that time.

And it felt even worse when I contributed once to Gitorious and noticed that over a thousand examples would finish in just a few seconds, even though lots of them didn't touch the database. Marius Mathiesen and Christian Johansen are very skilled developers and they were the main Gitorious maintainers by that time. Christian is the author of the popular Sinon.js, one of the authors of the great Buster.js and author of the Test-Driven JavaScript Development book.

For that particular application, I had to create a lot of records in order to create the record I needed to test. And I was recreating them on every single test requiring such record, through Object Daddy but I suspect the result would be about the same with FactoryGirl or any other factory tool.

When I realized that creating lots of records in the database was that expensive, I stopped following the traditional advises for writing tests and only worried about what I really cared for which remains basically the same to these days.

These are my test goals:

  • ensure my application works (the main goal by far);
  • avoid regressions (linked to the previous one);
  • the suite should run as fast as possible (just a few seconds if possible);
  • it should give me enough confidence to allow me to completely change the implementations during any refactoring without completely breaking the tests; To me that means avoid mocking or stubbing objects and performing HTTP requests against a real server for testing things like cookie-based sessions and a few other scenarios (rack_toolkit allows me to create such tests while still being fast).

These are not my test goals at all:

  • writing specs in such a way they would serve as a documentation. I really don't care how the output looks like when I run a single test file with RSpec. That's also the reason why I never used Cucumber. Worrying about this adds more complexity and I don't think they are useful for documentation purposes anyway;
  • each example should have a single expectation. I simply don't see much value on this and very often this has the potential of slowing down the test suite;
  • tests should be independent from each other and ideally we should run them in random order. I understand the reasoning behind this and I actually find it useful and see value in it. But if I see trade-offs I'd trade test-independence by speed. Fortunately this is not required by my tests touching the database using the technique I demonstrate in the next section, but it may speed up some request tests.

I even wrote my own JavaScript test runner because I needed one that allowed me to run my tests in the specified order, supported IE6 (by that time) and beforeAll and I couldn't find any by that time. My application used to register some live events on document and would never unregister them because it was not necessary, so my test suite would only be allowed to initialize it once. Also, recreating a tree on every test would take a lot of time, so I wanted to run a set of tests that would work on the same tree based on the result of previous tests.

I was okay with that trade as long my tests would run fast, but JavaScript test runners authors wouldn't agree, so I created OOJSpec for my needs. I never advertised it because I don't consider it to be feature complete yet, although it suites my current needs. It doesn't currently support running a single test because I need to think in some way to declare a test's dependencies (in other tests) so that those dependent tests would also be run before the requested one. Also, maintaining a test runner is not trivial and since it's currently hard for me to find time to review patches I preferred not to announce it. Since I can run individual test files, it's working fine for my needs, so I don't currently have much motivation to further improve it.

A fast approach to speed up tests touching the database

A common case while testing some scenarios is that one wants to write a set of tests that exercise about the same set of records. Most people nowadays are using either one of the two common approaches:

  • creating the records either manually (through the ORM usually) or through factories;
  • loading fixtures (which are usually faster than creating them using factories);

Loading specific fixtures before each context wouldn't be significantly faster than using a factory when using a competent factory and ORM implementations, so some will simply use DatabaseCleaner with the truncate strategy to delete all data before the suite starts and loading the fixtures to the database. After that usually each example would run inside a transaction that would be rolled back which is usually much faster than truncating and reloading the fixtures.

I don't particularly like fixtures because I find them to make tests more complicated to write and understand. But I would certainly consider them if they would make my tests significantly faster. Also, nothing prevents us from using the same fixtures approach with factories as we could also use the factories to populate the initial data before the suite starts, but the real problem is that writing tests would still be more complicated in my opinion.

So, I prefer to think about solutions that allows tests to remain fast even when using factories. Obviously that means that we should find some way to avoid recreating the same records for a given group since the only way to speed up a suite that takes a lot of time creating records in the database is to reduce the amount of time spent in the database creating those records.

There are other kind of optimizations that would be interesting to try but that it's probably complicated to implement as it would probably require a change in FactoryGirl API to allow such optimizations. For example, rather than sending one statement at a time to the database I guess it would be faster to send all of them at once. However I'm not sure it would be that much faster if you are using a connection pool (usually a single connection in the test environment) that keeps the connection open and you're using a local database.

So, let's talk about the low-hang fruits which are also the best ones in this case. How can we reuse a set of records among a set of examples while still allowing them to be independent from each other?

The idea is to use nested transactions to achieve that goal. You begin a transaction during the suite start (or some context involving database statements) and then the suite will create a savepoint before a set/group of examples (a context in RSpec language) and rollback to that savepoint after the context finished.

Managing such savepoint names can be complex to implement on your own but if you are going this route anyway because your ORM doesn't provide an easy API to handle nested transactions then you may not be interested in the rspec_nested_transactions gem I'll present in the next section.

However with Sequel this is as easy as:

# The :auto_savepoint option will automatically add the "savepoint: true" option to inner
# transaction calls.
DB.transaction(auto_savepoint: true, savepoint: true, rollback: :always){ run_example }

With ActiveRecord the API works like this (thanks Tiago Amaro, for showing me the API):

ActiveRecord::Base.transaction(requires_new: true) do
  raise ActiveRecord::Rollback

This will detect whether a transaction is already in place and use savepoints if it is or will issue a BEGIN to start the transaction. It will manage the savepoint names automatically for you and will even rollback it automatically when using the "rollback: :always" option. Very handy indeed. But in order to achieve this Sequel doesn't provide methods such as "start_transaction" and "end_transaction".

Why is this a problem? Sequel does the right thing by always requiring a block to be passed to the "transaction" method but RSpec does not support "around(:all)". However Myron Marston posted a few years ago how to implement it using fibers and Sean Walbran created a real gem based on that article. You'd probably be interested in combining this with the well known strategy of wrapping each example in a nested transaction themselves.

If you feel confident that you will always remember to use "around(:all)" with a "DB.transaction(savepoint: true, rollback: :always){}" block whenever you want to create such a common set of records to be used inside a group of examples then the rspec_around_all gem may be all you need to implement that strategy.

Not only I find this bug prone (I could forget about the transaction block) I also bother to repeat this pattern every time I want to create a set of shared records.

There's a caveat though. If your application creates transactions itself it should be aware of savepoints too (this is accomplished automatically when using Sequel provided you use the :auto_savepoint option in the outmost transaction) even if BEGIN-COMMIT is enough out of the tests, so that it works as expected in combination with this technique. If you are using ActiveRecord, that means using "requires_new: true".

If you are using Sequel or ActiveRecord and PostgreSQL, Oracle, MSSQL, MySQL (with InnoDB) or any other vendor supporting nested transactions and have full control over the transaction calls, implementing this technique can speed up your suite a lot with regards to the tests touching the database. And rspec_nested_transactions will make it even easier to implement.

Let the fun begin: introducing rspec_nested_transactions

I've released today rspec_nested_transactions which allows one to run all (inner) examples and contexts inside a transaction (usually a database transaction) with a single configuration:

require 'rspec_nested_transactions'

RSpec.configure do |c|
  c.nested_transaction do |example_or_group, run|
    (run[]; next) unless example_or_group.metadata[:db] # or delete this line if you don't care
    # with Sequel, assuming the database is stored in DB:
    DB.transaction(auto_savepoint: true, savepoint: true, rollback: :always, &run)

    # with ActiveRecord (Oracle, MSSQL, MySql[InnoDB], PostgreSQL):
    ActiveRecord::Base.transaction(requires_new: true) do
      raise ActiveRecord::Rollback

That's it. I've been using a fork of rspec_around_all (branch config_around) since 2013 and it has always served me great since then and I never had to change it since then, so I guess it's quite stable. However for a long time I considered moving it to a separate gem and remove the parts I didn't actually use (like "around(:all)"). I always post-poned it but Travis' article reminded me about it and I thought that maybe others might be interested on this approach as well.

So, I improved the specs, cleaned up the code using recent Ruby features (>= 2.0 [prepend]) and released the new gem. Since the specs use the "<<~" heredoc it will only run on Ruby >= 2.3 but I guess it should work with all Ruby >= 2.0 (or even 1.9 I guess if you implement Module.prepend).

What about Minitest?

Jeremy Evans, the Ruby Hero who happens to be the maintainer of Sequel and creator of Roda, was kind enough to provide a link on how to achieve the save with Minitest) in the comments below. No need for Fibers in that case. Go check that out if you're working with Minitest.

Final notes

Currently our application runs 364 examples (RSpec doesn't report the expectations count, but I suspect it could be around a thousand) in 7.8s while many of them will touch the database. Also, when I started this Rails application I decided to give ActiveRecord another try since it had also included support for a lazy API when Arel was introduced, which I was already used to with Sequel. A week or two later I decided to move to Sequel after finding AR API quite limiting for the application's needs. At that time I noticed that the tests finished considerably faster after switching from ActiveRecord to Sequel, so I guess Sequel has a lower overhead when compared to ActiveRecord and switching to Sequel could possibly help speeding up your test suite as well.

That's it, I hope some of you would see value in this approach. If you have other suggestions (besides running the examples in parallel) to speed up a test suite, I'm always interested in speeding up our suite. We have a ton of code both in server-side and client-side and only part of them is currently tested and I'm always looking towards improving the test coverage which means potentially we could implement over 500 more tests (for both server-side and client-side) while I still want the test suite to complete in just a few seconds. I think the most hard/critical parts are currently covered in the server-side and it will be easier to test other parts once I'm moving the application to Roda (the client-side needs much more work to make it easier to test some critical parts). I would be really happy if both server and client-side suites would finish in within a second ;) (currently the client-side suite takes about 11s to complete - 204 tests / 438 assertions).

comments powered byDisqus