DatabaseTruncation does not respect tables in other postgres schemas

[kolaente]

Laravel Version

11.34.2

PHP Version

8.3.14

Database Driver & Version

Postgres 17, using the postgres:17 docker image and psql extension from the 8.3 php docker image

Description

Using the DatabaseTruncation trait in a PostgreSQL database with multiple schemas will fail with the error relation "foo_in_other_schema" does not exist.

This is unexpected because the database connection is set to only use the public schema.

I was able to work around this issue with this code in the test which used DatabaseTruncation:

    public $exceptTables = [];

    protected function beforeTruncatingDatabase(): void
    {
        $tables = DB::select("SELECT table_name FROM information_schema.tables WHERE table_schema = 'other_schema'");
        $this->exceptTables = array_map(fn($t) => $t->table_name, $tables);
    }

However, that resulted in the test running after the first one to fail because the old data was not properly cleaned up. The test uses RefreshDatabase. That required another workaround:

   protected function beforeRefreshingDatabase()
    {
        $tables = DB::select("SELECT table_name, table_type FROM information_schema.tables WHERE table_schema = 'public'");
        $tableList = implode(', ', array_map(fn($v) => $v->table_name, array_filter($tables, fn($v) => $v->table_type === 'BASE TABLE')));
        DB::unprepared('TRUNCATE '.$tableList);
    }

Steps To Reproduce

Here's a repo with a minimal reproduction: https://github.com/kolaente/laravel-framework-issue-53745-reproduction

To see the error:

1. clone the repo
2. cd in the directory
3. run docker compose up -d
4. run docker compose exec web php artisan migrate so that the tables exist
5. run docker compose exec web php artisan test

[hafezdivandari]

Please try these 2 and let me know the result:

1. Fix your migration by adding connection and use schema.table:

// database/migrations/2024_12_03_160842_create_foo_in_other_schema_table.php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
+   /**
+    * The database connection that should be used by the migration.
+    *
+    * @var string
+    */
+   protected $connection = 'pgsql_other_schema';

    /**
     * Run the migrations.
     */
    public function up(): void
    {
        DB::unprepared('CREATE SCHEMA IF NOT EXISTS other_schema');

-       DB::unprepared('create table if not exists other_schema.foo_in_other_schema ("id" bigserial not null primary key, "bar" text not null)');
+       if (! Schema::hasTable('other_schema.foo_in_other_schema')) { 
+           Schema::create('other_schema.foo_in_other_schema', function (Blueprint $table) {
+               $table->id();
+               $table->text('bar');
+           });
+       }
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
+       DB::unprepared('DROP SCHEMA IF EXISTS other_schema');

-       Schema::dropIfExists('foo_in_other_schema');
+       Schema::dropIfExists('other_schema.foo_in_other_schema');
    }
};

2. Make sure the connectionsToTruncate is set correctly on your tests based on what you want to truncate:

/**
 * Indicates which connections should have their tables truncated.
 *
 * @var array
 */
protected $connectionsToTruncate = [null];                          // default
protected $connectionsToTruncate = ['pgsql'];                       // same as above on your environment
protected $connectionsToTruncate = ['pgsql', 'pgsql_other_schema']; // truncates both your connections

[kolaente]

I've added the changes as per your suggestions, but this does not seem to make a difference.

[hafezdivandari]

I can confirm this is a bug. You may refer to PR #53787.

[github-actions]

Thank you for reporting this issue!

As Laravel is an open source project, we rely on the community to help us diagnose and fix issues as it is not possible to research and fix every issue reported to us via GitHub.

If possible, please make a pull request fixing the issue you have described, along with corresponding tests. All pull requests are promptly reviewed by the Laravel team.

Thank you!