How to speed up unit tests using sqlite

Running unit tests might be dead slow if you're using mysql for it, cause for every test a new DB is set up and destroyed afterwards. One possible solution is using sqlite for unit tests, you'll need the sqlite3 database driver module for that. As you have surely installed all modules using composer you can put it to your dev requirements using

composer require --dev silverstripe/sqlite3

to install it in your development requirements, as you won't need it in production.

Next is configuring the switch to use sqlite instead of mysql for unit tests. You might use this db configuration in your _ss_environment.php:

// If we're requesting a different database for testing
if(isset($_REQUEST['db'])) {
		class_exists('PHPUnit_Runner_Version') // If the PHPUnit runner is loaded
		|| php_sapi_name() === "cli" // Or we're definitely in CLI mode
		|| (isset($_SERVER['REQUEST_URI']) && strpos($_SERVER['REQUEST_URI'], '/dev/tests') !== false) // Or we're running tests in-browser
	) {
		global $databaseConfig;
		switch ($_REQUEST['db']) {
			case 'sqlite':
			case 'sqlite3':
				define('SS_DATABASE_CLASS', 'SQLite3Database');
				define('SS_SQLITE_DATABASE_PATH', ':memory:');			
				$databaseConfig["path"] = ':memory:';
				user_error('Unknown database type: ' . $_REQUEST['db'], E_USER_WARNING);

This changes the DB to sqlite and - important - sets sqlite to run completely in memory, which is faster but not durable. But that's totally ok for running unit tests.

Now we need to run the unit tests, either from command line with

phpunit framework/tests '' db=sqlite3

or in your browser e.g. with

This concept has one little downside: you might encounter sql errors when you're not building your queries in a database agnostic way. E.g. the random function is different in mysql and sqlite. But don't worry, our beloved SilverStripe already has a way to get the right random string for each database. Instead of doing e.g.

$foo = Foo::get()->sort('rand()')

you can do

$randomString = DB::getConn()->random(); //e.g. rand() for mysql, random() for Sqlite3

$foo = Foo::get()->sort($randomString);

to get your speed up tests running again.

Rate this post

Post your comment


  • Gordon Anderson 02/12/2015 12:01pm (1 year ago)

    It should be noted that if xdebug is used whilst running the tests they are much slower, likes of 15 times for me. However it is required for creating coverage reports.

    On Ubuntu to dis/enable it, edit the file /etc/php5/cli/conf.d/20-xdebug.ini and prepend or remove a semi colon to edit out the line to enable xdebug.

    Note that this requires the package php5-xdebug to be installed.

  • Werner Krauß 23/09/2015 2:15pm (2 years ago)

    You can also call a specific test to run, e.g. in your webroot:

    phpunit --filter testNameOfMytest path/to/tests '' db=sqlite3

RSS feed for comments on this page | RSS feed for all comments