Is it good practice to use a single database connection?

Question

The PHP codebase I'm working on has an utility class that returns a database connection. The implementation looks like this:

class Database {
  private static $conn;
  private static $init = false;

  public static function connect() {
    if (!self::$init) {
      $cnf = new Configuration();
      self::$conn = new DatabaseConnection($cnf->get('dbname'), $cnf->get('username'), $cnf->get('password'));
      self::$init = true;
    }

    return self::$conn;
  }
}

Things have been done this way to apparently spend lesser time on opening connections, which occur over a network.

Is instantiating a single database connection like this and returning that instance to all callers a good software engineering practice?

Answer 1

No.

Unless you are on a really crappy O/S (or, to be precise, your ODBC library and/or database access drivers/library, which may or may not be part of the O/S proper), your code is not actually creating/using/closing database connections. It is creating/using/closing database connection objects, which are a wrapper for the underlying connections. The underlying connection, meanwhile, is being managed in a pool.

When your application code "opens" a connection, you are actually telling the system to go fetch a connection from the pool, check its status, and open new connections only when needed.

When you "close" a connection, you are actually telling the system to return a connection to the pool for re-use later, or until it times out.

You can manage the number of connections, life cycle, timeout, etc. via ODBC client settings, or by inserting parameters into the connection string. Each unique connection string results in a pool of connections with those settings.

If you really really want exactly one connection, you can configure it that way at the pool level-- however this would be a really bad idea in a web application, which sometimes has thousands of threads.

Let the underlying O/S manage these connections for you. It is going to be way better at it than any code that you write.

Answer 2

Yes.

Other answers are missing the fact that PHP is single-threaded and blocking. You generally process one HTTP request per process at a time. You can use more than one connection at a time if you really need to, but usually you won't. So it's perfectly fine to have a single connection per process.

Now if this was, say, Node.js, then yes, you should use pooling instead.

Answer 3

Is instantiating a single database connection like this and returning that instance to all callers a good software engineering practice?

That depends.

1. The code as it stands doesn't check if the connection is still alive, and reconnect if it isn't. Assuming this is a simplified version and that the real code performs such a check, then:
2. As long as the single instance of Database is injected into all callers, rather than being accessed via a service locator, then it's good practice.

The reason why it's good practice to inject a singleton class like this is that it can be referenced via an interface inside those callers, and thus a mock database connection can be provided to them when running unit tests.

Answer 4

What you have here is using the Singleton Pattern to ensure that all callers receive the same instance of the connection object. Sounds efficient to being with and, so long as your application is purely single-threaded and never tries to do more than one [database-related] thing at a time, it will probably work quite well. If this is a particularly long-running program (like, say, a Windows Service that runs 24x7), you might even consider adding some defensive code into this method to ensure that the connection is still "viable"; that the database hasn't been shut down for, say, nightly housekeeping since you last used the connection.

The problems start as soon as your program gains any kind of Parallelism, the sort of things that working with an event-driven User Interface does surprisingly well.

Now, your database connection might be in use, handling the processing of one event, while the user is clicking around the application, making more and more demands of your now-overloaded and blocked-up connection. Now your application is "seen" to be performing badly, because it can't do more than one thing at at time, which your users might expect.

Using more than database connection is these cases is "better".

Most operating systems will do connection pooling automatically for you, thereby making opening a new connection a lighter operation, but there's still a finite overhead involved. Pushing everything sequentially down the one connection is efficient; starting a new connection for each and every operation is not. You have to make a Judgement Call as to which way your application needs to go.

Not that you have to stick rigidly to that decision! The method that you have for providing your [Singleton] connection could be modified into a Factory; if you call it "every once in a while", it can return you the "one" connection object; if you call it in "rapid succession" or if it detects that the connection is "busy", then it might choose to return you a new one.

It all depends how much of a problem this causing you.