New application X Legacy database — TDD

Quite often I see myself in a situation that I want to use Laravel, but the database that the application will use already exists for some time (maybe years). In order to write tests for my app, I add a sql file into the project and, if possible, start writing migration from that moment forward.

Dumping the database structure

All projects I have ever worked with relied on MySQL. I’m assuming this might help a lot of people even though it’s a MySQL-only solution

mysqldump --single-transaction=TRUE -v -d -u root database_name| gzip>database.tar.gz

-v is for verbose, it will show you what is happening.
-d is for structure only, no data will be dumped.
-u informs the command that the next word will be the username.
The database name is the last thing before piping it to gzip for easier download. You also may want to include -p if your database doesn’t allow root access without password from localhost.

Extract the gzip with gunzip database.tar.gz

[OPTIONAL] Cleaning Auto Increment

It annoys me that the records don’t start from 1 after creating a clean database with an old dump. I use PHPStorm regex search/replace to do this. The following regex will allow you to replace all the increment definitions with empty.


Setting up the CreatesApplication trait

Laravel 5.4 comes with CreatesApplication to be reused between TestCase and DuskTestCase. That’s the perfect place to build the database.



use Exception;
Use PDOException;
use Illuminate\Support\Facades\Schema;
use Illuminate\Support\Facades\Artisan;
use Illuminate\Contracts\Console\Kernel;

trait CreatesApplication {

* Creates the application.
@return \Illuminate\Foundation\Application
public function createApplication() {
$app = require __DIR__ . '/../bootstrap/app.php';



return $app;

* Remove cache and session files
protected function cleanUpStorage() {
// Reset the cache and sessions.
exec('git clean -fxd ' . storage_path('framework/cache'));
exec('git clean -fxd ' . storage_path('framework/sessions'));

* Drops and rebuilds the database
@throws Exception
public function prepareDatabase() {
$connection = getenv('DB_CONNECTION');
$database = getenv('DB_DATABASE');
$databaseStructure = getenv('DB_STRUCTURE') ?: 'database.sql';

// Ping the database. This will throw an exception in case the database does not exists.
try {
exec('mysql -e "DROP SCHEMA ' . $database . ';"');
} catch (PDOException $e) {
// 1049 means the database doesn't exist, which is precisely what we want.
if ($e->getCode() != 1049)
throw $e;

// Create the database
exec('mysql -e "CREATE DATABASE ' . $database . ';"');

// Start the database from database.sql
exec('mysql -D' . $database . ' < ' . database_path($databaseStructure));

// Make sure to grab recent migrations.

Done! All your tests now will create an application that consequently will build a new database from the sql file. I also take advantage of this trait for cleaning up session data because Dusk does not fully close the Browser for each test.


Although some people might think this is not quite an elegant solution for coupling too much with MySQL, remember that my goal is to make my life easier by starting a project with Laravel that will deal with a database that exists for years and is unlikely to change anytime soon. Even if it were to change in a couple of years, the benefit of using Laravel for rapid development will bring much more value than not using it or dealing with more complexity than legacy code.

A single golf clap? Or a long standing ovation?

By clapping more or less, you can signal to us which stories really stand out.