How to set default schema in Yii2

Question

My Yii2 is setup with PostgreSQL. Instead of using separate database per project, I like to use schema for each project. Problem with later setup is that I can't figure out how to select default schema "defaultSchema" through configuration.

I am having a problem with migrations table because it defaults to "public" schema when I run migration command. Default "public" schema also prevents using database user's search_path. Although I set up my db user with "search_path=myschema, public" I still cannot use migrations without additional configuration, because during runtime Yii looks for the schema in the table name and if it is not provided falls back to the defaultSchema so no matter what you have in the database user's search_path it will still use "public.migrations".

What is the best way of setting default schema in Yii2? Is there any configuration parameter designated for schema selection? After all each connection will use one schema and it would be nice to set it through the connection configuration.

Answer 1

try this variant of db.php to specify defaultSchema

return [
    'class' => 'yii\db\Connection',
    'dsn' => 'pgsql:host=localhost;dbname=db_name', 
    'username' => 'db_username',
    'password' => 'db_password',
    'charset' => 'utf8',
    'schemaMap' => [
      'pgsql'=> [
        'class'=>'yii\db\pgsql\Schema',
        'defaultSchema' => 'public' //specify your schema here
      ]
    ], // PostgreSQL
];