Efficiently create and switch between multiple database checkpoints.

• Your acceptance test scenarios run the same setup over and over again
• You want to build test data through the application instead of relying on fixtures
• You need to debug a scenario in the application without the hassle of setting it up multiple times.
• You want a convenient way to roll back data changes during development or testing.

• Tracking persists with multiple connections and sessions. This means that unlike transaction-based checkpoints, you can create another connection to see the current state of the database.
• Works in the rails console with ActiveRecord or in the irb console with Mysql2.
• Checkpoints on a stack or named checkpoints.
• Restore to any checkpoint at any time.
• Only saves tables that have changed.

In your Gemfile:

gem 'checkpointer', :git => "git://github.com/bdwong/checkpointer.git"

require 'checkpointer'
#=> true
c = Checkpointer::Checkpointer.new(:database=>'MyApplication_development', :username=>'root', :password=>'mypassword')
#=> <Checkpointer::Checkpointer>
c.track
#=> nil

# Starting from a newly tracked database...

# Perform common setup for scenario

c.checkpoint "setup"
#=> "setup"

# Perform test case 1

c.restore       # restore to last checkpoint
#=> "setup"

# Perform test case 2

c.restore 0     # restore to clean database for next scenario
#=> 0

# Starting from a newly tracked database...

# Make changes to database

c.checkpoint  # incremental checkpoint

# Make more changes

c.checkpoint  # incremental checkpoint

# Make a mistake

c.restore # restore last checkpoint

# Make more changes...

=== Stop tracking the database

c.untrack

=== Other commands

    c.checkpoints           # List checkpoints
    #=> [1, 2, "special"]

    c.pop                   # Restore checkpoint 2 from the stack and remove it
    #=> 1

    c.drop                  # Delete checkpoint off the top of the stack
    #=> 0
    
    c.restore_all           # Restore all tables if you have problems.
    #=> nil

In order to use checkpointer, your database user must have access to a wildcard set of databases. Your user must have at least the following privilges: CREATE, INSERT, UPDATE, DELETE, DROP, TRIGGER, SHOW DATABASES. If you're not concerned about security, you can grant ALL, or you can use the Mysql root user. Example user setup:

GRANT ALL ON `database_%`.`*` TO 'dbuser' IDENTIFIED BY 'password';

• Runs with Mysql2 or ActiveRecord on Mysql2 only (pull requests welcome!)
• Uses triggers to detect database changes. Any database with existing triggers can't use Checkpointer (yet).
• Becase of triggers, initial setup time is slow.

Database Cleaner is good for transaction-based rollback and truncating tables. It also works on multiple ORMs and database engines.

Once you start dealing with external tests (e.g. Selenium or Sahi) and longish scenarios with branching test cases, you should consider Checkpointer.