how to manage DB connections

Hi If so how do  Team,
In my CI3 app I never worried about this because we autoloaded 'database' lib.
In CI4 it seems I now have to connect in my code - I have chosen the constructor of any Controller or Model where ever I need to access the database.
I am now aware that I could hit connection limits (currently 151 by default for mysql db)
A few (perhaps) obvious questions:
1) If I make a connection in a controller and the user session redirects to another controller will the $db persist for the session or do I open a second etc connection?
2) Should I check for an existing connection in each constructor and either proceed or open a new connection as appropriate? If so how please?
3) Another potential strategy would be to force a disconnect on completion of my db interation. If so how please?
4) I quite like the freeQuery() call. Is this recommended to optimise memory as lng as you don't need the cached result?
I have read the upgrade notes carefully but haven't found those nuggets yet.
Appreciate any thoughts and guidance.
TIA, Paul

---

Sorry typo in first line ...groan

Hey Paul, in CI4, each request is fresh—DB connections don’t persist between controllers or sessions. Just use db_connect() and CI4 will reuse the same connection for that request. No need to manually check or close unless you're doing something heavy—then $db->close() helps. And yeah, freeResult() is good for saving memory if you don’t need the result anymore.

Knowing if the data/state is ready is often not easy. Codeigniter 4 has an event system you could use, to trigger things when you want them: https://codeigniter4.github.io/userguide...vents.html
I reduced my db calls to the minimum using the resultArray to filter data usign lookup tables created with array_column or array_filter:

// Create a URI => ID mapping
$lookup = array_column($data, 'id', 'uri');
if (isset($lookup['/about'])) {
$id = $lookup['/about'];
echo $id;
}

or when you need all data:
$result = array_filter($data, function($item) use ($searchUri) {
return $item['uri'] === $searchUri;
});

if (!empty($result)) {
$id = reset($result)['id'];
echo $id; // Output: 2
}
good hunting!

Hi Paul, Great questions—moving from CI3 to CI4 can definitely bring a few surprises, especially around things like autoloading and database connection handling. Let me try to address your concerns one by one:

1) Does the $db connection persist between controllers during a session?

No, it doesn't persist across controllers or requests like a session variable might. When a user is redirected to another controller, a new request lifecycle begins, and so the $db variable from the previous controller isn’t carried over. CI4 does handle connection pooling behind the scenes, but your $db variable won’t “follow” the user across controllers—you’ll need to reconnect in each place you use it (e.g., in your constructor).

2) Should you check for an existing connection before creating one?

CI4’s database library handles connection reuse internally. When you do:

Code:

$db = \Config\Database::connect();

It doesn’t necessarily open a new connection every time—it reuses an existing one if possible (by default it uses the default group and keeps track). So generally, you don’t need to manually check for an existing connection. Just call connect() when you need it. That said, you can store the $db instance in a base controller or a shared service if you want to avoid repetitive code.

3) Should you disconnect after each DB interaction?

Generally, you don’t need to manually disconnect—CI4 closes connections at the end of the request lifecycle automatically. But if you’re doing something like long-running scripts or explicitly want to close it for resource reasons, you can do:

Code:

$db->close();

But use it cautiously. Manually closing connections mid-request can cause errors if you try to access the DB again after that.

---

4) Is freeResult() (or freeQuery()) useful in CI4?

CI4’s Result objects automatically free the result set once the object is destroyed, so you usually don’t need to call freeResult() manually. However, if you're running multiple heavy queries in a single request and want to be memory-efficient, you can do something like:

Code:

$query = $db->query("SELECT ...");

// Use $query->getResult() or whatever you need

$query->freeResult();

// Optional but helpful if you're done with it

Just make sure you don’t try to use the result object afterward.

Extra Tip: Make Use of Models Where Possible

In CI4, the Model layer is much more powerful—it can help encapsulate DB logic cleanly and automatically uses the DB connection when you extend CodeIgniter\Model. That way, you don't need to manually call connect() unless you have custom DB logic.

Hope this helps clear things up! You're definitely on the right track, and it's great that you're thinking about connection management early—many don’t until they hit those MySQL 151 connection errors! ?

Cheers!