php - "There is no active transaction" when refreshing database in Laravel 8.0 test

Solution:

The issue is that your test contains implicit commits and so ends the active transaction.

Re -

"As soon as I do a CREATE statement I get the transaction error." -

CREATE TABLE etc are statements that cause an implicit commit.

This in turn means the RefreshDatabase trait will not work because a rollback is not possible when the transaction is closed.

Hence PDOException: There is no active transaction

It seems to be a known issue/thrown error with php 8.0 - https://github.com/laravel/framework/issues/35380

Solution:

I figured it out for the case I was having. I am using a library wich uses the migration logic to execute data migrations (hence the $this->artisan('migrate-data'); in my code).
When using the RefreshDatabase trait, migrations are executed once and after that, a transaction is started. The migration logic does something with transactions, I think closing them afterwards, causing the error I was having.

The solution that worked for me, was to overwrite the RefreshDatabase trait to execute the data migrations before starting the transaction:

<?php

namespace Tests;

use Illuminate\Contracts\Console\Kernel;
use Illuminate\Foundation\Testing\RefreshDatabase;
use Illuminate\Foundation\Testing\RefreshDatabaseState;

trait RefreshDatabaseWithData
{
    use RefreshDatabase;

    protected function refreshTestDatabase(): void
    {
        if (! RefreshDatabaseState::$migrated) {
            $this->artisan('migrate:fresh', $this->migrateFreshUsing());
            $this->artisan('migrate-data'); // << I added this line

            $this->app[Kernel::class]->setArtisan(null);

            RefreshDatabaseState::$migrated = true;
        }

        $this->beginDatabaseTransaction();
     }
}

This is tested in Laravel 9 only, but I don't see why this wouldn't work on previous versions.

Solution:

This is how I solved it:

Step 1: Extend Connection class and override transaction method:

use Illuminate\Database\Connection as DBConnection;
use Closure;

class Connection extends DBConnection
{

    public function transaction(Closure $callback, $attempts = 1)
    {
        $callback($this);
    }
}

This override basically disables transactions internally, keeping your current code intact.

Step 2: Bind that concrete class to the abstract ConnectionInterface via a ServiceProvider (app/Providers/AppServiceProvider.php would be a good place):

if(config('app.env')==='testing') {
   $this->app->bind(ConnectionInterface::class, Connection::class);
}

Notice that the binding is inside a condition against the environment. We want to apply this binding only when running tests.

If you're not using a .env.testing file for Unit Testing, I really recommend you to do it. It keeps everything much more cleaner. You can just copy .env to .env.testing and only update the DB_CONNECTION and DB_DATABASE constants, pointing to your test database.

Then define the .env.testing environment in phpinit.xml:

<php>
   <env name="APP_ENV" value="testing"/>
   <!-- <env name="DB_CONNECTION" value="memory_testing"/> -->
   <!-- <env name="DB_DATABASE" value=":memory:"/> -->
</php>

Notice that I commented out DB_CONNECTION AND DB_DATABASE since those parameters will be fetched from .env.testing