Multiple database connections on Yii3

Yii 3.0 Ask a question
1

Hi everyone,

I’m currently working with Yii3 and I’m trying to understand the best way to handle multiple database connections.

In Yii2 this was quite straightforward and well-documented, but in Yii3 I’m honestly struggling to find a clear and practical approach. The documentation and examples I’ve found so far are a bit confusing, and I’m not sure what the recommended pattern is.

My use case is simple: I need to work with more than one database connection within the same application (e.g., different tenants or separate data sources).

So my questions are:

  • Is there an official or recommended way to configure and manage multiple DB connections in Yii3?
  • Are there any examples or best practices available?
  • How should this be integrated with repositories / Active Record (if applicable)?

Any guidance, examples, or pointers to documentation would be greatly appreciated.

Thanks in advance!

2

You can use class aliases.

3

Hi, thanks for your reply. I actually found the answer later in the DB documentation — my bad.

Now I was wondering how Active Record management with multiple DbConnection is supposed to work in Yii3. Coming from Yii2, each Active Record had its own connection defined, while here it seems a bit more complex. From what I understand, Yii3 relies on a ConnectionProvider (or DI), but then you need to instantiate Active Records through a factory, and you also lose access to their static methods — meaning, for example, you can’t directly use methods provided by RepositoryTrait.

One idea I had was to switch the connection in the provider right before using each Active Record. However, I’m not sure if that’s safe — especially if different Active Records are used sequentially, or if an instance created with a previous connection is reused after the provider has changed. That could potentially lead to inconsistent behavior.

I suspect I might still be reasoning with a Yii2 mindset. Would the correct approach be to rely on repositories that inject the proper connection into the Active Record?

Apologies if this is a naive question — I’ve only recently started exploring Yii3. Thanks in advance to anyone who can clarify this!

4

In my opinion, Active Record is not the most appropriate way to handle multiple database connections since it requires a ConnectionInterface to be injected into each model.
You may wish to consider alternative methods, such as Yii DB or Doctrine ORM.

5

Hi,

I did some reverse engineering and found out that ConnectionProvider actually accepts an array of connections. So it’s possible to configure multiple connections directly in the bootstrap definition, for example:

/**
 * @psalm-var list<callable(ContainerInterface): void>
 */
return [
    static function (ContainerInterface $container): void {
        ConnectionProvider::set($container->get(ConnectionInterface::class)); // default connection
        ConnectionProvider::set($container->get(ConnectionB::class), 'connectionB'); // other connection
    }
];

With this approach, you can override the db() method in your class that extends ActiveRecord and return the desired connection:

class Users extends ActiveRecord
{
    use MagicPropertiesTrait;
    use RepositoryTrait;

    public function db(): ConnectionInterface
    {
        return ConnectionProvider::get('connectionB');
    }

    public function tableName(): string
    {
        return 'users';
    }
}

This way, you can still use static methods like ::query() or those provided by RepositoryTrait. Internally, when static methods are called, they just instantiate an ActiveQuery passing the calling model class (e.g. Users::class). Then it simply creates an instance and calls the db() method to resolve the connection.

6

Example using two MySQL databases.


config/common/db.php
use Yiisoft\Db\Connection\ConnectionInterface;
use Yiisoft\Db\Mysql\Connection;
use Yiisoft\Db\Mysql\Driver;

return [

    // Default DB
    ConnectionInterface::class => static function () {
        return new Connection(
            new Driver(),
            'mysql:host=localhost;dbname=main_db',
            'root',
            'password'
        );
    },

    // Second DB
    'dbTenant' => static function () {
        return new Connection(
            new Driver(),
            'mysql:host=localhost;dbname=tenant_db',
            'root',
            'password'
        );
    },

];
7

How do you do migrations with the secondary db? In Yii2 you could just pass the --db= param but that seems to not be there now.

8

I suppose you could do something like this:

//config/common/di/application.php:
use Yiisoft\Db\Migration\MigrationBuilder;
use Yiisoft\Db\Migration\Informer\MigrationInformerInterface;
use Psr\Container\ContainerInterface;

class_alias(Yiisoft\Db\Pgsql\Connection::class, 'MyPgSql');
class_alias(MigrationBuilder::class, 'MyMigrationBuilder');

return [
    // ...
    MyPgSql::class => [ ... ],
    MyMigrationBuilder::class =>  static function (ContainerInterface $container) {
        return new MigrationBuilder(
            $container->get(MyPgSql::class),
            $container->get(MigrationInformerInterface::class)
        );
    }
 ]; 
 

//src/Migration/M251225221906CreateNewsTable.php:
use Yiisoft\Db\Migration\MigrationBuilder;
use Yiisoft\Db\Migration\RevertibleMigrationInterface;
use MyMigrationBuilder;

final class M251225221906CreateNewsTable implements RevertibleMigrationInterface
{
    public function __construct(private MyMigrationBuilder $myMigrationBuilder)
    {
        
    }
    
    public function up(MigrationBuilder $b): void
    {
        $cb = $this->myMigrationBuilder->columnBuilder();

        $this->myMigrationBuilder->createTable('news', [
            'id' => $cb::uuidPrimaryKey(),
            'title' => $cb::string()->notNull(),
            'content' => $cb::text(),
        ]);
    }

    public function down(MigrationBuilder $b): void
    {
        $this->myMigrationBuilder->dropTable('news');
    }
}
1 Like
9

Thank you, this almost works for what we want. It’s standard practice for us in yii/mysql to make an app_user that does not have table create, drop, alter permissions and migrate_user that has all permissions, so when we run the app we have extra security the app cannot change the tables. When the migration runs with this change it uses the migrate_user for the actual migration, but still uses the app_user to try and create and insert the migration table - is there a way to change the db connection it uses for those steps too?

10

The first parameter of the MigrationBuilder is the ConnectionInterface, which allows you to set up a connection with the appropriate permissions.

//config/common/di/application.php:
use Yiisoft\Db\Migration\MigrationBuilder;
use Yiisoft\Db\Migration\Informer\MigrationInformerInterface;
use Psr\Container\ContainerInterface;

class_alias(Yiisoft\Db\Mysql\Connection::class, 'MigrateMysqlConnection');
class_alias(MigrationBuilder::class, 'MyMigrationBuilder');

return [
    // ...
    MigrateMysqlConnection::class => [ ... ],
    MyMigrationBuilder::class =>  static function (ContainerInterface $container) {
        return new MigrationBuilder(
            $container->get(MigrateMysqlConnection::class),
            $container->get(MigrationInformerInterface::class)
        );
    }
 ];