Global or Singleton For Database Connection

Global or Singleton for database connection?

I know this is old, but Dr8k's answer was almost there.

When you are considering writing a piece of code, assume it's going to change. That doesn't mean that you're assuming the kinds of changes it will have hoisted upon it at some point in the future, but rather that some form of change will be made.

Make it a goal mitigate the pain of making changes in the future: a global is dangerous because it's hard to manage in a single spot. What if I want to make that database connection context aware in the future? What if I want it to close and reopen itself every 5th time it was used. What if I decide that in the interest of scaling my app I want to use a pool of 10 connections? Or a configurable number of connections?

A singleton factory gives you that flexibility. I set it up with very little extra complexity and gain more than just access to the same connection; I gain the ability to change how that connection is passed to me later on in a simple manner.

Note that I say singleton factory as opposed to simply singleton. There's precious little difference between a singleton and a global, true. And because of that, there's no reason to have a singleton connection: why would you spend the time setting that up when you could create a regular global instead?

What a factory gets you is a why to get connections, and a separate spot to decide what connections (or connection) you're going to get.

Example

class ConnectionFactory
{
    private static $factory;
    private $db;

    public static function getFactory()
    {
        if (!self::$factory)
            self::$factory = new ConnectionFactory(...);
        return self::$factory;
    }

    public function getConnection() {
        if (!$this->db)
            $this->db = new PDO(...);
        return $this->db;
    }
}

function getSomething()
{
    $conn = ConnectionFactory::getFactory()->getConnection();
    .
    .
    .
}

Then, in 6 months when your app is super famous and getting dugg and slashdotted and you decide you need more than a single connection, all you have to do is implement some pooling in the getConnection() method. Or if you decide that you want a wrapper that implements SQL logging, you can pass a PDO subclass. Or if you decide you want a new connection on every invocation, you can do do that. It's flexible, instead of rigid.

16 lines of code, including braces, which will save you hours and hours and hours of refactoring to something eerily similar down the line.

Note that I don't consider this "Feature Creep" because I'm not doing any feature implementation in the first go round. It's border line "Future Creep", but at some point, the idea that "coding for tomorrow today" is always a bad thing doesn't jive for me.

should a db connection be a singleton?

A DB connection should not normally be a Singleton.

Two reasons:

1. many DB drivers are not thread safe. Using a singleton means that if you have many threads, they will all share the same connection. The singleton pattern does not give you thread saftey. It merely allows many threads to easily share a "global" instance.
2. Personally, I think Singleton often leads to bad design: See this post (by somebody else) http://tech.puredanger.com/2007/07/03/pattern-hate-singleton/

Instead of doing this consider a database pool. The pool is shared (and could be a singleton if you wanted). When you need to do database work your code does this:

getConnectioFromPool();

doWork()
closeConnection() // releases back to pool

Sample Pool Libraries:

• http://commons.apache.org/dbcp/
• http://jolbox.com/

Globally available database connection without using Global/Singleton

I personally thing a singleton (or a multiton, if you need several DB connections) is fine for such an usage.

But if you do not want to use it, you should then take a look at the Registry pattern.

This way, you can have your database class instance(s) available for all your application's classes, without having to pass an additional parameter each time (which is very ugly, IMHO).

Is singleton approach right for accessing/maintaining database and internet connection

Am I using the right approach or pattern ?

I don't think so. If you use a singleton (or a static) for the connection then you make it difficult if your code needs to be used / reused in a context where there may need to be more than one connection; e.g. if you make your application multi-threaded, or turn it into a library.

Singletons also have problems with respect to testability ...

Generally speaking, anything you can do with Singletons (or statics) you can also do with a context parameter of some kind. Dependency Injection (DI) frameworks provide a neat way to create the shared objects and "wire" them into the other objects that need them ... without using singletons or statics. Alternatively, you can do it by hand, in a variety of ways.

Does static and singleton work the same way ?

Singletons are a design pattern. Statics are Java language feature that can be used to implement the Singleton pattern. So they are not the same thing (or even the same kind of thing), but they are related.

Creating a global SQLite DB Connection with Singleton pattern in Swift

When I attempt to change the static conn to an instance variable I get the error Instance member 'conn' cannot be used on type 'DBConnection'

That makes sense considering how you're using open:

try DBConnection.open(dbPath: FileManager.default.urls(for: FileManager.SearchPathDirectory.documentDirectory, in: FileManager.SearchPathDomainMask.userDomainMask)[0])

Right now, open is a static method of DBConnection, which means that you can call it without needing an instance of the class. It's similar to a "class method" in Objective-C. But in your broken code, you have:

var conn: Connection? = nil

so conn is an instance variable, i.e. a variable that only exists in the context of an instance of the class. A quick fix would be to make open non-static:

func open(dbPath: URL) throws {

and then call it using the shared instance:

try DBConnection.shared.open(dbPath: FileManager.default.urls(for: FileManager.SearchPathDirectory.documentDirectory, in: FileManager.SearchPathDomainMask.userDomainMask)[0])

(Note the shared added there.)

However, ideally I'd like for the open function to fire when the Singleton is first created without having to explicitly call it and assign it to some variable within the class.

You could call open from inside the initializer if you want the connection to open when the singleton is created.

Another direction you could go is to leave conn and open both static so that open can access conn, and let the class itself be the singleton. Then you could eliminate the shared variable.

However, I think the best option would be to do as described above, making conn and open non-static, and get rid of the shared instance and instead just keep track of the DBConnection object that you create.

Singleton or Dependency Injection pattern for database connection

A Singleton is a bad design in this case. Google would say it's a bad idea in all cases.

The right answer here is the connection pool.

You can avoid a max connections issue by configuring your pool to remain well below the limit and by making sure that your code rigorously closes all connections as soon as you're done with them. The cycle should be very short: check out of pool, execute SQL, close connection.

Is a global of my db connection a security risk?

Design-wise, there are better practices than having a global connection, but I can't see any security threats coming from this.

How to use singleton class for database connection in windows application

To use a singleton directly, you'd have to inherit from the connection object, which isn't possible with an OleDbConnection (as it is sealed). So you'd have to write a class, with a public property that exposes the connection itself, and make that wrapper class a singleton.

However, it the only goal is to have one instance of the connection, you could use a 'Lazy' construction, which is thread safe (For that matter, even in the singleton, I'd use Lazy or LazyInitializer to be prepared for threading)

public static class Data    
{
    public static readonly Lazy<OleDbConnection> Connection = new Lazy<OleDbConnection>(CreateConnection); //(using System.Threading)

    static OleDbConnection CreateConnection()
    {
        var conn = new OleDbConnection("YourConnectionString");
        //etc
        conn.Open();
        return conn;
    }
}

getting the connection would be something like:

var conn = Data.Connection.Value;

Then again, if you would incorporate a class that encapsulates all calls to the connection, that would be a perfect case for a singleton

* edit ** An example using a single connection, with reset*

    public static class Data   
{
    static OleDbConnection conn;
    public static OleDbConnection Connection
    {
        get
        {
            if (conn == null)
                LazyInitializer.EnsureInitialized(ref conn, CreateConnection);
            return conn;
        }
    }

    static OleDbConnection CreateConnection()
    {
        if (strDataFilePath == null)
            throw new Exception("Datafile paths is not set");
        //build connection, using strDataFilePath
        var conn = new OleDbConnection("YourConnectionStringWithDataFilePath"); 
        //other settings

        //open
        conn.Open();
        return conn;
    }

    static string strDataFilePath;

    public static string DataFilePath
    {
        get { return strDataFilePath; }
        set
        {
            if(strDataFilePath==value)return;
            strDataFilePath = value;
            if(conn!=null){
                conn.Close(); //NB, no checks were added if the connection is being used, but if the value is only set on startup or idle moments, this should be ok for the example.
                conn.Dispose();
                conn=null; //conn is reset, and (re)created the next time Connection is called
            }
        }
    }
}

And initializing:

Data.DataFilePath = ".....";

Using the connection:

var conn = Data.Connection; //Connection is created and opened on first call

Singleton design pattern is preferred to get a DB Connection

I can see nothing good coming from this class. If you want it that way (and I'm not in favor of it) you gained no benefit over a bunch of static methods. What would the singleton instance be good for? How would you put the SqlConnection variable inside a using block?

I think a singleton is a way overused pattern for people that want global variables, but read a book that mentioned the fact globals are bad. Now they found another book stating that "Singleton" is a pattern and patterns are "good" and they finally have an excuse to have a global.

Being a global variable is a side effect of the singleton. If you are using it for the side effect, it's not a good use of the pattern. I would say it's an abuse.

Singletons are almost impossible to unit test. This one is not even thread save and does not have any use over a non-singleton. So from my point of view... delete the class.

How many people use the application is irrelevant. Each will have it's own process and it's own singleton, as bad as that may be.

---

Related Topics