RefreshDatabase trait only dropping tables for main connection

[StevePorter92]

• Laravel Version: 6|7
• PHP Version: 7.4
• Database Driver & Version: MySQL

Description:

\Illuminate\Foundation\Testing\RefreshDatabase runs the migrate:fresh command which will only wipe tables on the main database connection.

If running migrations on specific connections other than the main one, the tables for these connections will not be dropped so test runs after the first one will error with:

SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'tests' already exists (SQL: create table `tests` (`id` bigint unsigned not null auto_increment primary key, `created_at` timestamp null, `updated_at` timestamp null) default character set utf8mb4 collate 'utf8mb4_unicode_ci')

Given the RefreshDatabase trait can perform transactions on multiple connections with the connectionsToTransact property, should it also db:wipe those connections too?

Steps To Reproduce:

Fresh laravel project with steps to reproduce here https://github.com/StevePorter92/refresh-database-issue.

List of changed files: StevePorter92/refresh-database-issue@0d7e05c...master

git clone [email protected]:StevePorter92/refresh-database-issue.git
cd refresh-database-issue
composer install
docker-compose up -d
php artisan test
php artisan test

Notable files:

...
# app/Test.php
...
class Test extends Model
{
    protected $connection = 'mysql2';
}

...
# database/migrations/2020_05_27_150916_create_tests_table.php
...
class CreateTestsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::connection('mysql2')->create('tests', function (Blueprint $table) {
            $table->id();
            $table->timestamps();
        });
    }
...

...
# tests/feature/ExampleTest.php
...
class ExampleTest extends TestCase
{
    use \Illuminate\Foundation\Testing\RefreshDatabase;

    /**
     * Connections to transact
     *
     * @var array
     */
    protected $connectionsToTransact = ['mysql', 'mysql2'];
...

Happy to raise a PR for this if we think the trait should wipe all connections, or connections that it transacts on

[driesvints]

Sounds like a feature request. Moving this to discussions.

[StevePorter92]

Thanks @driesvints! It's worth noting that the RefreshDatabase trait doesn't work if migrating across multiple connections so is this potentially a bug?

I'm currently working around this by looping through the connectionsToTransact property and running db:wipe on each connection.

[jonnywilliamson]

@StevePorter92 I've been struggling with this for a while now. Would you mind showing me how you got this to work. I'm getting command not found errors when trying to call db:wipe using the Artsian call method and other attempts have also failed!

Thank you!

[maarten00]

I also ran into this bug. Would be great if I could optionally pass additional connections to refresh when calling refreshDatabase.

[inmanturbo]

Has anyone come up with a fairly succinct or elegant workaround for this problem, or perhaps a best practice? I have a client who runs many copies of the same laravel app, with business data on a mysql server connection, but each with their users, menus, auth data stored locally on sqlite, such as that they can bulk (drop and create) export/import all of their business data from one site to another, without disturbing the user settings. Alternatively they occasionally migrate the users from one app to another when moving teams around, etc. Not being able to write tests for requested feature additions is a severe handicap.

Nevermind. Using this for now

I'm currently working around this by looping through the connectionsToTransact property and running db:wipe on each connection.

[jonnywilliamson]

Just been hit with this problem too. Main connection gets refreshed ok, but any other connections do not and so the migrations fail on the next test to be run.

Will try getting the work around to work, but it would be nice for the RefreshDatabase to have this built in.

[jelib3an]

I had a chance to look at @StevePorter92's example. One thing that stood out to me is specifying the connection inside the migration file.

public function up()
{
    Schema::connection('mysql2')->create('tests', function (Blueprint $table) {
        $table->id();
        $table->timestamps();
    });
}

I'm curious is this standard practice? That means there is a single migrations table on the default db for all the connections.
Since every migrate command has a --database option, are you supposed to migrate separately?

$ php artisan migrate --database=mysql
$ php artisan migrate --database=mysql2

In this case, each database will have it's own migrations table, but it will also have empty tables that don't apply to each database.

I don't have much experience using multiple connections so I'm interested to know how people are managing all this because it does seem odd to me if you can specify one connection in the migration and another in the parameter.

Anyway, to address the original issue. if we change from migrate:fresh to migrate:refresh, it will work because that will do the rollback first based on the connection specified in the migration file. But we're not supposed to be implementing down() methods right? 😉

I also like @maarten00's suggestion. Something like $connectionsToRefresh?

[JohnnyWalkerDigital]

This seems to work fine in Laravel 8.

class MyTest extends TestCase {
    // Reset the DB between tests
    use DatabaseTransactions;

    // Setting this allows both DB connections to be reset between tests
    protected $connectionsToTransact = ['mysql', 'otherConnection'];
}

[cxj]

This is still broken in Laravel 9.42.2.

If one is unaware of this problem in advance of running DB tests using the RefreshDatabase trait, it absolutely will trash your development or test databases in a manner that is not recoverable using Laravel tools. You'll have to do it all by hand.

[cxj]

Here is one way in which I think the code takes the wrong path.

In Trait RefreshDatabase:

  protected function refreshTestDatabase()
    {
        if (! RefreshDatabaseState::$migrated) {
            $this->artisan('migrate:fresh', $this->migrateFreshUsing());

The above call to artisan does not pass a --database argument to migrate:fresh among those it does pass (for seeders, views and types).

Hence, in the command FreshCommand we call db:wipe without specifying the database:

 public function handle()
    {
        if (! $this->confirmToProceed()) {
            return 1;
        }

        $database = $this->input->getOption('database');

        $this->newLine();

        $this->components->task('Dropping all tables', fn () => $this->callSilent('db:wipe', array_filter([
            '--database' => $database,
            '--drop-views' => $this->option('drop-views'),
            '--drop-types' => $this->option('drop-types'),
            '--force' => true,
        ])) == 0);

But I think the problem is larger than a simple fix to the above. Here's why.

Consider a single Laravel Application (LA). It may need to access multiple databases in a variety of situations:

1. In some cases, the schema (and migrations, if any) for an entire database or perhaps only for the tables used by it are dictated by an external factor or separate application. In this case, the LA will usually only need Models which accurately match the tables they use.
2. The LA may dictate the schema of the tables it uses, but they must be in a database it does not control. This often occurs in enterprises which have multiple applications and databases, e.g. Laravel applications for communication with 3rd-party APIs and support for websites, while having to interface with the databases maintained by SaaS ERPs and CRMs.
3. The LA may need to share some tables in a database with another Laravel application. Which application gets to own the migrations table in that database? Both will need to know that their individual migrations are complete before they can safely run.

Both number 2 and 3 are incompletely supported by the current Laravel migration tooling. I believe the 2 following general cases of Laravel migration implementations should be supported:

1. All migrations are stored in the default database's migrations table, for multiple databases. The RefreshDatabase trait needs to support this use case and does not.
2. The migrations for each database are stored in that target database's migrations table. This is poorly supported right now in that it requires splitting migrations into separate directories and always specifying the --path and the --database parameters on artisan migrate. It also appears there are problems with RefreshDatabase interacting with $connection = or ::connection() usage in migrations in this situation.

I apologize for any errors in the above examples and use cases. I will try to make the time to more carefully delineate the categories of use case and the problems found in each. At present, I'm only confident in my current use case, which is the second of the 3 above. In my situation, using RefreshDatabase is not possible; doing so corrupts the database as it hits a fatal error before completing the process.

[eafarooqi]

still no fix for this ?

[Sophist-UK]

I would suggest that someone creates a PR which does the following:

1. Create tests that show this is a problem by failing;
2. Fixes the problem identified by Chris @cxj (i.e. by adding --database to the parameters for the migrate:fresh call). Now the tests in commit 1 are successful.
3. If you can identify any, add further tests to show that the fix in 2. doesn't disrupt any existing functionality that currently works.

Obviously I cannot guarantee that this will get merged by Taylor, but given that 2. is likely to be a 1 line fix, I think it is highly likely it would be merged.

[fnematov]

I'm using laravel with Postgres and I have multiple DB schemas. migrate:fresh works only with public scheme only

[jimits]

I am also facing the same problem. What was your solution/work around?

[ClaraLeigh]

My solution for those finding this via google.

Create or modify an existing seeder that runs with the migrate command and call this lil dude

Note: I do not need migrations for this connection after the schema import, so you might have issues running tests if you need to change things on this connection later (you'd have to check). In my use case my second connection is unlikely to ever change.

    public function run()
    {
        // Seeders for the main db
        $this->call(UserSeeder::class);
        
        // Migrate the schema for the other connection
        $this->migrateSchema('example');
        $this->call(ExampleTableSeeder::class, [
            '--database' => 'example',
        ]);
    }
    /**
     * Migrate the schema for a connection.
     *
     * @param  string  $database
     * @return void
     */
    protected function migrateSchema(string $database): void
    {
        $connection = \DB::connection($database);
        $connection->getSchemaState()->load(
            database_path('schema/'.$connection->getName().'-schema.sql')
        );
    }