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!

1. Use a Connection Pool

Instead of opening and closing database connections for each operation, use a connection pool. A pool maintains a set of open connections and reuses them, which improves performance and resource management.

Examples of pooling libraries:

Node.js: pg-pool for PostgreSQL, mysql2 for MySQL

Java: HikariCP, Apache DBCP

Python: SQLAlchemy with pooling, or psycopg2 with pool management

PHP: Use persistent connections or libraries like Doctrine

Example in Node.js using PostgreSQL:

javascript
Copy
Edit
const { Pool } = require('pg');
const pool = new Pool({
user: 'user',
host: 'localhost',
database: 'mydb',
password: 'pass',
port: 5432,
max: 20
});

async function fetchData() {
const client = await pool.connect();
try {
const res = await client.query('SELECT * FROM users');
return res.rows;
} finally {
client.release();
}
}
2. Close Connections Properly

Always ensure database connections are closed or released back to the pool after use. Use try-finally blocks to guarantee cleanup even if errors occur.

3. Configure Timeouts and Limits

Set connection timeouts to avoid hanging requests. Configure the pool with a maximum number of connections and idle timeout to prevent resource exhaustion.

4. Monitor Connection Usage

Regularly monitor your database to check how many connections are in use, query performance, and idle connections. Use built-in tools like pg_stat_activity in PostgreSQL or SHOW PROCESSLIST in MySQL.

5. Avoid Connection Leaks

Connection leaks occur when a connection is opened but not released or closed. This can eventually exhaust all available connections.

To avoid leaks:

Always release or close connections in all execution paths

Use logging or monitoring tools to detect unusual patterns

Review error handling logic to ensure connections are cleaned up

6. Use Environment-Specific Configurations

Use different settings for development, testing, and production. Configuration options include host, database name, credentials, max pool size, and timeout durations.

7. Understand Your ORM Behavior

If you're using an ORM like Sequelize, SQLAlchemy, Hibernate, or Doctrine, understand how it manages connections and pooling. Tune its settings according to your application's load and usage patterns.