`migrate:fresh` doesn't drop tables with Postgres

[binaryfire]

PHP Version

8.3

Swoole Extension Version

6

Database Driver & Version

Postgres

Description

php artisan migrate:fresh doesn't drop tables when using Postgres:

When I check the db after running migrate:fresh the existing data is still there

Steps To Reproduce

1. Install hyperf/database-pgsql

2. Use the following config for the pgsql connection:

'pgsql' => [
    'driver' => env('DB_DRIVER', 'pgsql'),
    'host' => env('DB_HOST', 'localhost'),
    'database' => env('DB_DATABASE', 'hypervel'),
    'port' => env('DB_PORT', 5432),
    'username' => env('DB_USERNAME', 'root'),
    'password' => env('DB_PASSWORD', ''),
    'charset' => env('DB_CHARSET', 'utf8'),
    'pool' => [
        'min_connections' => 1,
        'max_connections' => env('DB_MAX_CONNECTIONS', 10),
        'connect_timeout' => 10.0,
        'wait_timeout' => 3.0,
        'heartbeat' => -1,
        'max_idle_time' => (float) env('DB_MAX_IDLE_TIME', 60),
    ],
],

3. Migrate the db, add some data, then run migrate:fresh. The old data will still be there.

PS. migrate:fresh works properly with Postgres in Hyperf

[albertcht]

Hi @binaryfire , it looks like a bug from hyperf/database-pgsql package. While executing migrate:fresh, the command will call dropAllTables in the schema builder (https://github.com/hyperf/hyperf/blob/master/src/database-pgsql/src/Schema/PostgresBuilder.php#L76-L99). However, there's a whitelist for postgres to delete tables from (https://github.com/hyperf/hyperf/blob/master/src/database-pgsql/src/Schema/PostgresBuilder.php#L148-L153). That means your tables won't be deleted unless you set schema config manually.

This behavior is totally different from what Laravel did: https://github.com/laravel/framework/blob/12.x/src/Illuminate/Database/Schema/PostgresBuilder.php#L16-L35

That's why deleting tables doesn't work in hyperf/database-pgsql

[binaryfire]

@albertcht Thanks. I just checked and you’re right - the same problem exists in Hyperf. I have my own scripts for refreshing the db in Hyperf projects so I mustn’t have noticed.

That whitelist doesn’t make sense to me. There’s already a way to exclude tables here: https://github.com/hyperf/hyperf/blob/f0f69745f3714e36f86f27a8e5e6bd3b5f6744ca/src/database-pgsql/src/Schema/PostgresBuilder.php#L80

And the MySQL builder doesn’t do it: https://github.com/hyperf/hyperf/blob/f0f69745f3714e36f86f27a8e5e6bd3b5f6744ca/src/database/src/Schema/MySqlBuilder.php#L194.

So I’m not sure why they did it just for Postgres. I’ll submit a PR today - hopefully they’ll let us remove it.

[binaryfire]

@albertcht hyperf/hyperf#7447

[binaryfire]

@albertcht I've added schema to the config so migrate:fresh works now. Even once the Hyperf PR gets merged, I still having a config entry for schema (that's set to public by default) is better anyway.

Closing