php – Connection to separate PDO Databases – (multi-tenancy)

Question:

I have the following scenario for my application: I have a PHP + MySql application, with PDO connection, I need the application to be shared with all registered companies, but each company will have its database separately.

APPLICATION STRUCTURE

Connection:

1 – Con.class.php => Responsible for connecting to the bank using PDO – SingleTon

2 – Config.php => Through define(); receives the values ​​to be passed to the connection – (Host, User, DB…)

Central Database:

3 – TABLE Companies => Receives the companies that will use the application, containing the connection information – (Host, User, DB, Password)

Customer Databases (db1, bd2, db3….):

4 – TABLE Users => Contains user information, including the "COMPANY_ID" to refer to which company it belongs in addition to the login information- (user_login, user_password, user_email…)

How can I make each company log in its base that will be created when registering companies, in addition to not having advanced knowledge in PHP, I didn't find any help about it by searching.

CODES

Con.class.php

<?php
class Conn {

    private static $Host = SIS_DB_HOST;
    private static $User = SIS_DB_USER;
    private static $Pass = SIS_DB_PASS;
    private static $Dbsa = SIS_DB_DBSA;

    private static $Connect = null;

    private static function Conectar() {
        try {
            if (self::$Connect == null):
                $dsn = 'mysql:host=' . self::$Host . ';dbname=' . self::$Dbsa;
                $options = [ PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES UTF8'];
                self::$Connect = new PDO($dsn, self::$User, self::$Pass, $options);
                self::$Connect->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            endif;
        } catch (PDOException $e) {
            PHPErro($e->getCode(), $e->getMessage(), $e->getFile(), $e->getLine());
            die;
        }

        return self::$Connect;
    }

    public static function getConn() {
        return self::Conectar();
    }

    private function __construct() {

    }

    private function __clone() {

    }

    private function __wakeup() {

    }

}

Config.php

if ($_SERVER['HTTP_HOST'] == $urlcentral):
    define('HOST', $linkBanco);
    define('USER', $userBanco);
    define('PASS', $senhaBanco);
    define('DBSA', $nomeBanco);
else:
    define('HOST_CLIENTE', $linkBancoCliente);
    define('USER_CLIENTE', $userBancoCliente);
    define('PASS_CLIENTE', $senhaBancoCliente);
    define('DBSA_CLIENTE', $nomeBancoCliente);
endif;

Is it possible to carry out this action? When the user enters the Login and Password, check which company he belongs to, and take the connection information from the database and he will be connected in the base of the company he belongs to. Thanks!

Answer:

In your case, young man, I would re-implement that connection. I think Singleton in this case (and most, in my humble opinion) is more structure than helpful.

I believe that a good option would be for you to have a list of connections saved in an array , each separated by an index, to indicate which application each one belongs to.

Then you can use the Multiton pattern combined with Factory, to be able to generate these unique connections…

I made a sketch just to illustrate, but you can create it based on a similar structure:

class Connection

{
    protected $dsn;

    protected $user;

    protected $password;

    protected $options;

    public function __construct(array $options)
    {
        // monta as variáveis de acordo com o option

        //$this->dsn = $this->buildDsn($options); 

        // ...
    }

    public function getPdo()
    {
        if ($this->pdo === null)
        {
            $this->pdo = new \PDO($this->dsn, $this->user, $this->password, $this->options);
        }

        return $this->pdo;
    }
}

class ConnectionFactory
{
    protected static $connections = [
        'app_1' => [
            'host' => '...',
            'password' => '...',
        ],

        'app_2' => [
            'host' => '...',
            'password' => '...'
        ]
    ];

    protected static $instances = [];

    // implementação multiton

    public static function getConnection($name)
    {
        if (! isset(static::$instances[$name])) {

            static::$instances[$name] = new Connection(static::$connections[$name]);
        }

        return static::$instances[$name];
    }
}

Note now that you could create the database connection instances normally according to the desired customer.

Example:

  $db1 = ConnectionFactory::getConnection('app_1');

  $db2 = ConnectionFactory::getConnection('app_2');

In the case above, the calls could occur anywhere in the application, as the instances' constructions are stored in an array and, if it exists, they are not recreated, just reused.

In the case above, I combined Factory with Multiton (a Singleton based on the parameters) in order to keep the structure similar to what you currently have (you must have a reason to be using singleton).

Scroll to Top