Ability to define a multi-schema search_path for PostgreSQL lost after changes in #8394

[dcro]

Up until the changes from #8394 were made, it was possible to define a multi-schema search path when defining a database configuration (in database.php):

'connections' => [
    'db1' => [
        'driver'   => 'pgsql',
        /* ... */
        'schema'   => 'schema1,schema2,schema3',
    ]
],

This translated in the following query that was executed immediately after the connection was established:

SET search_path TO schema1,schema2,schema3;

After the changes in #8394, this now translates to the following SQL query:

SET search_path TO "schema1,schema2,schema3";

which basically breaks the previous functionality.

Is it possible to get multi-schema support back?

A couple of potential solutions that I see:

• leave the current functionality unchanged, but allow the database config to include a list of SQL commands that would be executed immediately after the connection is established.
• allow a comma separated list of schemas (or possibly an array of schema names) and escape each schema name individually for connection adapters that do support multiple schemas.

[GrahamCampbell]

We're open to PRs.

[DrummerKH]

Hi, may we know about status of this issue please?

[elynnaie]

@dcro @DrummerKH

Since it doesn't seem like this will get official support, I came up with a fairly simple workaround. I'm using 5.3.

1. Set the config.database.connections.pgsql.schema config variable to your main schema (e.g. the one that holds the migrations table).

2. Add a new config value to the config.database.connections.pgsql array: 'search_path' => 'my,desired,search,path'

3. Create a service provider (e.g. php artisan make:provider SearchPathServiceProvider)

4. Edit the provider and add to the boot method:

   $searchPath = config('database.connections.pgsql.search_path');
   \DB::statement("SET search_path TO $searchPath");

5. Register your custom provider in config/app.php

The migrate command now works properly (because it relies on finding the migrations table from the schema defined by database.connections.pgsql.schema), but the rest of the app works as well, because we manually reset the search_path to what we want. I have not extensively tested this, and it may break in testing if you use a different database (e.g. sqlite), but that ought to be easy to work around.

[mattisdada]

@denaje Thanks, that helped a lot. Was using Heroku Connect and it requires multi-search path. This helped a lot!