Symfony 4 / Sonata: Manage a Multi-Server Admin Interface

sonata symfony

We will see how to manage an admin interface that connects to multiple servers.
In our example, we have set up a default MySQL server that manages our admin interface. Users, media, etc.
And we have set up a PostgreSQL server that contains a table we wish to manage.
We can add as many servers as we wish.

Our MySQL Server

We have traditionally set up a Symfony Sonata instance by default with a classic MySQL connection. So we can create our database, the schema, and the default user of our admin interface.

php bin/console doctrine:database:create
php bin/console doctrine:schema:update --force
php bin/console fos:user:create --super-admin
Sélection_162

Our PostgreSQL Server

On the other hand, we have a PostgreSQL server containing a "test" table.

Sélection_161-1

The Connections

In recent versions of Symfony, the credentials for connections are managed in the .env file at the root of your project.
We will create 1 connection for each server.

DATABASE_URL_PGSQL=pgsql://tbourdin:123456@127.0.0.1:5432/test?
DATABASE_URL_MYSQL=mysql://root:toor@127.0.0.1:3306/symfony-sonata-skeleton?serverVersion=5.7

Then we will configure our Doctrine settings by creating a default connection (MySQL) and an additional connection for our PostgreSQL server.
Be careful, to avoid confusion, we use an underscore naming "_" instead of dashes "–" because Symfony will then convert our "–" to "_" and the mapping will not be done correctly.

We specify that all our default entities will be managed in src/entity/ServerMysql and that the entities that need to connect to Pgsql will all be stored in the directory /src/Entity/ServerPgsql

doctrine:
    dbal:
        default_connection: db_mysql_local
        types:
            json: Sonata\Doctrine\Types\JsonType   
        connections:
            db_mysql_local:
                url: '%env(resolve:DATABASE_URL_MYSQL)%'
                driver: 'pdo_mysql'
                server_version: '5.7'
                charset: utf8

                default_table_options:
                    charset: utf8mb4
                    collate: utf8mb4_unicode_ci
                    
            db_pgsql_local:
                url: '%env(resolve:DATABASE_URL_PGSQL)%'
                driver: 'pdo_pgsql'
                server_version: '9.6.11'
                charset: utf8
                default_table_options:
                    charset: utf8mb4
                    collate: utf8mb4_unicode_ci
                                    

        
        # IMPORTANT: You MUST configure your server version,
        # either here or in the DATABASE_URL env var (see .env file)
        #server_version: '5.7'
    orm:
        auto_generate_proxy_classes: true
        
        default_entity_manager: App
        
        entity_managers:
        #default code, the sonata admin, that use the default connexion (no need to define it here)
            App:
                connection: db_mysql_local
                naming_strategy: doctrine.orm.naming_strategy.underscore
                auto_mapping: true
                mappings:
                    ApplicationSonataMediaBundle: ~
                    SonataMediaBundle: ~
                    App:
                        is_bundle: false
                        type: annotation
                        dir: '%kernel.project_dir%/src/Entity/ServerMysql'
                        prefix: 'App\Entity\ServerMysql'
                        alias: App       
        
        #our pgSql entities that use our pgsql connexion       
            app-pgsql:
                connection: db_pgsql_local
                naming_strategy: doctrine.orm.naming_strategy.underscore
                auto_mapping: false
                mappings:
                    AppPgsql:
                        is_bundle: false
                        type: annotation
                        dir: '%kernel.project_dir%/src/Entity/ServerPgsql'
                        prefix: 'App\Entity\ServerPgsql\'
                        alias: AppPgsql

And to verify our procedure we try to manage our test interface

image-4

And of course, it does not work. Because our entity is stored in our default directory.
So we move it to our directory "/src/Entity/ServerPgsql"
We just need to modify the service and the entity definition, and we're good!

#/config/services.yaml 
   admin.table1:
        class: App\Admin\Table1Admin
        arguments: [~, App\Entity\ServerPgsql\Table1, App\Controller\Table1AdminController]
        tags:
            - { name: sonata.admin, manager_type: orm, group: admin, label: Table1 }
        public: true


<?php

namespace App\Entity\ServerPgsql;

use Doctrine\ORM\Mapping as ORM;

/**
 * Table1
 *
 * @ORM\Table(name="table1")
 * @ORM\Entity
 */
class Table1
{
    /**
     * @var int
     *
     * @ORM\Column(name="id", type="integer", nullable=false)
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="SEQUENCE")
     * @ORM\SequenceGenerator(sequenceName="table1_id_seq", allocationSize=1, initialValue=1)
     */
    private $id;

    /**
     * @var string|null
     *
     * @ORM\Column(name="var1", type="string", length=250, nullable=true)
     */
    private $var1;

    /**
     * @var json|null
     *
     * @ORM\Column(name="var2", type="json", nullable=true)
     */
    private $var2;

    public function getId(): ?int
    {
        return $this->id;
    }

    public function getVar1(): ?string
    {
        return $this->var1;
    }

    public function setVar1(?string $var1): self
    {
        $this->var1 = $var1;

        return $this;
    }

    public function getVar2(): ?array
    {
        return $this->var2;
    }

    public function setVar2(?array $var2): self
    {
        $this->var2 = $var2;

        return $this;
    }


}
image-5