Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

tomturton's avatar

Multiple databases and testing

I'm trying to work out a practical way of writing tests when multiple databases are needed.

We can't use Database Migrations in tests, as the phpunit DB_CONNECTION environment variable is overruled by a model's $connection property.

We can't use Database Transactions either, as there seems to be a flaw, which stops the transactions from reverting on non-default database connections.

What might the best practices be here, to make working with multiple databases practical?

Some Context…

In my situation, I am building a set of Laravel sites that all use the same user accounts. I.e. a user can sign up for an account, and access many of these sites with it.

As all the sites are very different, it makes sense to use a separate database for each. However, the users table needs to be in a separate database again.

0 likes
7 replies
tomturton's avatar

Okay, I figured out a reasonable workaround:

  1. Create test versions of each database. They have to use the same database driver (eg. MySQL) as your real databases.
  2. In your phpunit.xml file, add an tag for each database, overriding the environment variables for each. It may be that you will need to change username and password for test databases too, but I left them the same so didn't need to override any more environment variables.
  3. Use the DatabaseMigrations trait in your tests.

Example phpunit.xml

    …
        <env name="DB_ACCOUNT_DATABASE" value="accounts_test"/>
        <env name="DB_SITE_DATABASE" value="site_test"/>
    …

Now you can run normal local databases that can change over time, and test databases that are only used by phpunit with Faker entries (for example)

1 like
agentphoenix's avatar

I have a very similar setup for a series of web applications I've built. I came across the same solution, but the problem is that it requires me to have set up those databases ahead of time, so when moving to another machine, I have to go through the setup for all of the applications before I can run tests. This setup has also caused problems when setting up Travis CI as the database tables required don't exist.

I don't really want to re-create the migrations from the users app and wrap them in logic to make sure they only run in testing. Has anyone come up with some more elegant solutions to help test with multiple database connections in better isolation?

shawnyv's avatar

Thanks for sharing this - I was just about to post a thread asking about this very topic.

Will try this method first - it looks like you've saved me a lot of time! :)

shawnyv's avatar

Just one point I wanted to bring up that I didn't see elsewhere - there is a fabulous property that we can include in tests where we're using DatabaseTransactions called $connectionsToTransact.

So we can do

    use DatabaseTransactions;
    protected $connectionsToTransact = ['database1','database2'];

In order to ensure our testing is able to hit both databases without leaving anything behind. The names come from the database.php config file.

3 likes
StepToMe's avatar

@shawnyv Maybe you could help me with a similar problem :)

I tried adding the $connectionsToTransact property with my two databases but it doesn't seem to work. However, if I only add my secondary (tenant) database, all tests are running, but data on my main database is not being cleared...so transactions are not working there. Have you experienced anything similar?

I also opened a separate thread detailing my issue: https://laracasts.com/discuss/channels/laravel/phpunit-test-not-running-with-multiple-database-setup

digitalhuman's avatar

Hi all,

So, we are really running out of options here. We use different database and some tests rely on these databases. We run the tests with RefreshDatabase trait and at first we expect it to refresh all 'databases' that are in use. We found out that this is not the case. So, we now 'refresh' the additional databases using the Artisan facade in the test where we actually use the additional database.

    public function setUp(): void
    {
        parent::setUp();

        Artisan::call("migrate:fresh --env=tests --path=database/migrations/db2 --database=db2");
    }

Now this does not work. We also tried moving this logic to the TestCase but nothing seem to work properly either. We are looking for a more controlled way to refresh databases but without loosing the ability to refresh the database only once for all tests.

I hope anyone here can give us some new leads or solutions.

1 like
devincross's avatar

@digitalhuman Not sure if you are still having this issue - I also ran into it - what I did to fix it is to tie the migration to the correct connection. I then just drop if it exists.

Schema::connection('test')->dropIfExists('requests');
Schema::connection('test')->create('requests', function (Blueprint $table) {
      $table->uuid('id')->primary();
      $table->timestamps();
});
2 likes

Please or to participate in this conversation.