Do you close database connection before script execution ends?

If you close please tell why and in which kind of situations you do that? Or is it standard to close always after you don’t need to fetch more data from db?

What I know if you use persistent connections then you should close the connection always when not needed anymore. Another scenario would be if the script execution takes a looooong time you probably should close the connection when not needed anymore. Most of the cases personally I haven’t explicitly closed the connection in normal PHP scripts. I am trying to figure out here if I should close them always? What is the best practise?

No. Don’t close the connection at all. Let PHP do that for you. What happens if you need to grab more data? Are you just going to open a new connection? That’s redundant and wastes too much time. Might as well keep the connection open and do everything you need or want. Let PHP close it by itself and be done with it. You waste time and resource every time you open and close connections just to do a simple generic query.

2 Likes

That is exactly what I have done over the years. I open a connection run queries and let the script execution end and close the connection. We had just a discussion today with my colleagues about this subject. They were saying that you should close the connection before script ends always when you don’t need to run queries anymore. Also PHP manual examples seems to suggest closing the connections.

And when I say “close the connection” I don’t mean that you open-close and re-open-close connection again for next query. What I would do if I closed the connections is that I run all queries first, close connection and then process the data returned from the queries. So there would be only one open-close connection cycle.

Usually script execution does not take much time. Lets imagine we have a scenario where we have to run multiple scripts in cron that take a long while. All these cron jobs will reserve a connection for a time period. Then in addition to that if we have a large volume of simultanous users using the same connection pool. This makes it possible that max connections exceed and some users aren’t able to use the service.

There’s a solution for that too, just raise the max connections limit, which on the other hand takes more resources. And my opinion is that the resources aren’t expensive today. It’s pretty easy and cheap to add more hardware processing on today’s VM’s.

I opened the topic to hear other community fellows opinions and habits how do you handle database connections when you code. Also I tried to google and the opinions were pretty divided. Some people like to close the connections strictly before script execution ends and some people are just saying to let the script do it in the end.

Couldn’t really find any web page stating if there is a commonly accepted best practise on this.

That sounds awesome in theory, but in practice I don’t think this is manageable. When do you know you’re done with all queries? What if you ever want to perform a query somewhere else and the connection is already closed?

As was already said, resources are dirt cheap these days. For the effort required for a developer to get this tuned to the max you can run a VM for several months. I know which one I’d pick :slight_smile:

2 Likes

There is no need to close the connection. PHP does it automatically when the script finishes running.

2 Likes

This. In a world of OOP rather than 10000 line sequential scripts, there’s no way to know when you’re done because you cannot possibly know what order functions are going to be chained together in.

Yes you could do something like this:


$pdo = new PDO();

require 'framework.php';
$framework = new FrameWork($pdo);
$framework->execute();

$pdo->close();

Which is the only way you’d be sure. But this requires connecting to the database regardless of whether that page needs a database connection.

3 Likes

Just what I was thinking.
I used to close my connections before I started using OOP and PDO. My procedural programming was fairly linear in nature, so it was clear at which point I no longer needed a connection.
But with OOP it’s different, a lot of queries happen within methods, then you have methods that call other methods which may or may not have queries and/or call yet more methods, which… etc, etc.
So now I just let it close itself when done.

2 Likes

Heading

I can think of only one time that I needed to explicitly close a connection. Many years ago I had a script that used a poorly designed database, innefficient queries and was polluted with global scope variables. By nuking everything as soon as I was done with them, the script could run with less problems.

BUT, when I revisited the script after having learned a bit more, closing the connection was not necessary.

The only situation that I can think of that it might be a good idea to close a connection is if the script is accessing different databases and the database identifier isn’t in queries and there is a possibility that the wrong database could be used. eg

$conn = database_one
SELECT field
$conn = database_two
SELECT field

Even then, I think it would be better to write the code so it couldn’t cause an error rather than depend on closing connections.

2 Likes

With a low traffic website and running MySQL you dont really need to close the connection manually, you can just let it be handled by the script destroy process.

Though, if your running a high traffic website, connect to multiple databases, connect to master/slave, do specific work in-between calls that takes time, etc. Having the ability to close and open connections at will is beneficial.

This is easiest done by making a database manager class instead of calling PDO/mysqli directly, or picking an available one on Composer.

On a side note: If a manager class is utilized, you can also add a destructor that automatically close the connection. Which in some cases as mentioned above can be useful, and in other cases are no different from waiting on the main destruction.

2 Likes

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.