Singleton with multiple databases

Question

example app, having employee information and being accessed by different applications like payroll and pos. i have employee data in one database, payroll data and pos in separate databases each.

i have a database connection class like below, so everytime i want to get a connection to a db i just do $conn = Database::getInstance(db1).

works great, but is super slow basically. makes the app run really slow. Any tips on why that is so or better yet alternative ideas to do this?

any help will be greatly appreciated

<?php    
class Database {
        private $db;
        static $db_type;
        static $_instance;

        private function __construct($db){
            switch($db) {
                case "db1":
                  try{
                      $this->db = new PDO("mysql:host=" . DB_HOST . ";dbname=" . DB_NAME, DB_USER, DB_PASSWORD);
                  }
                  catch(PDOException $e){
                      print "Error!: " . $e->getMessage() . "<br />";
                      die();
                  }
                break;
                case "db2":
                  try{
                      $this->db = new PDO("mysql:host=" . DB_HOST_2 . ";dbname=" . DB_NAME_2, DB_USER_2, DB_PASSWORD_2);
                  }
                  catch(PDOException $e){
                      print "Error!: " . $e->getMessage() . "<br />";
                      die();
                  }
                break;
            }
            self::$db_type = $db;

        }

        private function __clone(){}

        static function getInstance($db_type){
            if(!(self::$_instance) || $db != self::$db_type){
                self::$_instance = new self($db_type);
            }
            return self::$_instance;
        }
    }
?>

Answer 1

With this design. If you change databases then it destroys the connection to the previous database.

Make separate objects for each connection then switch between the connection objects.

Also, this is not thread safe for the same reason. If multiple functions are hitting this as the same time, one can disconnect the other before its done loading.

You really should just create a new connection object for each function and not share it between functions or other objects.