Having a variable available from anywhere through a function has two advantages over a global. First it gains some protection from being modified anywhere you want (though not enough in this case because anyone can close the connection). Secondly it allows you to put off the decision over whether to initialize the variable as long as possible. If you need it, it will be there, but if you never needed it you didn't want to do the work. The third win is that if the variable has a complex initialization then every place it is used you document where to look for its initialization. That can be nice in a complex system.

The second is the real win here.

Connecting to a database takes a lot of work. For many useful programs the database connection takes more time than the entire program does! This is not the kind of operation that you want to possibly wind up doing repeatedly in a loop.

Furthermore this is an operation that is common to want to do from many places for many reasons. Which is why the appropriate life of a database connection is often longer than the life of your script! In fact that is one of the main wins that make people go to mod_perl, perlex, and friends. That you can have database connections that be reused across multiple web pages.

Incidentally the above point is why I think it is important to know why things are good design decisions. Zigster's advice as abstract advice is along a line that is generally recognized as the right thing to do. Were it practically any other example I would (probably silently) be in agreement with him.

But the cost of this advice is that wind up creating and destroying things a lot. Usually the overhead of creation and destruction is justified from reduced conflicts and easier to follow code. But in this case the excess creations can quickly turn into an..interesting..stress test for your database...

I was talking about correctly scoping an object, this would involve the object being constructed whilst in scope and destroyed while out of scope. Now my point was that in this case as the scope is well defined so should be used. Now as database connections represent a scarce resource, keeping a connection open too long is not IMHO desirable.

If I were to solve a problem such as this I suppose I would take the middle ground. The point you make about complex initialisation is very valid, however I would disagree that this is something that the singleton pattern is best suited to resolve. I would suggest the factory pattern would be more appropriate here. I would wrap the database connection up in object that is scoped over the life of the query, and have this object constructed by the factory. I would probably make the query an object also, complete the query close the connection. If performance was really an issue and opening and closing connections was a concern I would NOT extend the scope of a connection DIRECTLY. I would implement connection pooling withing the database connection object via a faceted object (pattern not seen within DP but described in C++ for proffessional programmers, kinda like a cross between a factory and a singleton, an object that can doll out several of a suite of objects within it's control). In short my point was that the use of a singleton in this case covers up weaknesses in the design that could be resolved more elegently.

UPDATE I just want to say that I am not actually disagreing with tilly in this post. His comments are all valid (except the one where he disagrees with me *grins*) I think my views actually fit alongside his. This post is to expand upon my thinking not to create an argument.
--

Zigster

I fail to understand what you think a more complex approach buys.

I know the various things you are discussing, of course. But I fail to see what problem they are intended to solve above the simple approach.

For instance consider your factory object suggestion. Well a database handle is already an object wrapped around a connection. What do I need another for? Since I already have the intialization logic nicely wrapped, it would be trivial for me to implement that whenever I wanted if, for instance, I needed to add some logging facility. Or if I needed to prevent people from trying to inappropriately close the connection. (Which is something I would prefer to do by just never calling close.) So I don't have to do anything now to get that upon need. So what need do I have to do it now?

As you say, database handles are a limited resource. But suppose I am running my web-server. Well I can control how many database handles are available, and I can control how many Apache instances are active. As a first pass you solve that problem by having more possible database handles than Apache instances. In a non-forking environment it would take a lot of work to get any win from your idea of allocating connections out of a fixed pool of available connections. Oh it is possible. You could play with IPC::Shareable for dynamic allocation of connections to processes. But you can get from the simple idea to that whenever you need to.

Again, why do it now? What is the win?

In case it isn't obvious, my desire here is for the simplest API that solves the simplest problem that needs to be solved now, but doesn't limit my options later. A global variable may solve the problem, but limits the ways in which I can choose to extend it. Creating a function from which you can get something that works as an initialized database handle is something that solves the problem but doesn't limit what I can do later.

Now the first pass is to open a connection every time. I can tell you from both theory and experience, both for myself and others, the simple approach hits a major performance issue because opening database connections to a relational database is expensive. The first possible improvement - which can be done without changing the external API - is to memoize the connection. The second reasonable improvement is to share database connections across tasks. This is one of the big reasons that people build application servers, move to mod_perl, etc.

Beyond that there are a whole suite of things that you can do. They can be done without breaking the API. But while I can dream up cases in which I might want to do them, I have never felt the need to do it in real life.

So my question is and remains, what concrete win are you looking to get from moving to a more complex design up front? If there is something that I am missing then I want to know about it because I might need to do it. If the win is just the ability to use more complex buzzwords to describe what I am doing, then count me out...