A connection string is only one part of a reliable database setup. A project also needs a consistent policy for secrets, permissions, character sets, errors, and connection lifetime. When each page invents its own connection options, small differences become hard-to-find bugs. This reference shows how to centralize those policies without hiding important database behavior. PDO remains the main course API, while MySQLi is an optional equivalent for MySQL-specific projects. You will design a connection factory, understand why persistent sessions are not an automatic speed improvement, and learn how deployment configuration can differ from your local machine.
Use a small explicit connection factory
A connection factory reads validated configuration and creates a PDO or MySQLi instance with the application's agreed options. Keep it focused: it should not create tables, run random business queries, or print HTML. The request handler or dependency container obtains a connection and passes it to repositories that need it. This makes error policy and character-set setup consistent. Avoid a global function that silently creates a new connection every time it is called. Document whether the factory returns a fresh connection or a request-scoped reused instance. Tests can then provide a controlled database fixture or a narrower interface where appropriate. An abstraction is useful when it makes policy visible, not when it erases transaction and driver distinctions.
Keep secrets in protected runtime configuration
Credentials belong in a deployment secret mechanism or protected environment, not committed source, public JavaScript, screenshots, or diagnostic output. A .env file is only safe when it is excluded from source control and inaccessible through the web root. Read required values and fail clearly if they are absent; empty default passwords can mask configuration mistakes. Separate development, test, and production credentials. Rotation should update runtime settings rather than require editing SQL files. Be careful with command arguments because they may appear in process listings or logs. A connection exception can include infrastructure details, so redact and log deliberately. Public error pages should contain a request identifier and a generic message rather than a credential dump.
Choose minimum privileges for each role
The web runtime usually needs row-level operations on a known schema, while migrations need broader DDL privileges. Keep those roles separate. Reporting jobs may need read-only access, and backups may require their own scoped permissions. Do not use an administrator account merely because it connects successfully. Database grants do not replace application authorization: a shared runtime account can still read many users' rows, so queries must enforce ownership or tenancy. Conversely, application checks do not remove the benefit of database privilege limits. Review grants whenever a feature changes. If a handler only reads products, it should not gain the ability to drop unrelated schemas through an unnecessarily broad account.
Configure text, errors and transport
For MySQL, choose utf8mb4 explicitly through the PDO DSN or MySQLi set_charset(). Agree on collation and schema text rules rather than expecting the connection charset to decide every comparison. Configure exception handling and predictable row-fetch modes. Remote database traffic may need verified TLS with certificates and options supported by your driver; encryption without verification can miss the intended server identity. Use a host and transport matching the deployment topology. Inside a container, localhost usually describes that container. Set reasonable connection and operation time budgets under your driver and infrastructure capabilities. Do not copy options from another driver and assume they are universally recognized. Every option should have a concrete purpose.
Manage request and worker lifetimes
A short web request commonly uses one connection for related work and releases it at the end. Long-running queue workers need additional care: stale connections, open transactions, unread results, and accumulated session settings can persist between jobs. End every transaction and clean statement resources. A persistent connection can reuse a session across requests in a process, but that brings state-reuse and server connection-count concerns. It is not automatically a connection pool suitable for every hosting model. Measure connection overhead and understand worker counts before enabling it. A large number of PHP workers can exhaust MySQL's connection limit even if each worker makes only one connection. Capacity planning should consider application concurrency and database limits together.
Observe failures without turning them into noise
Track connection latency, failures, query duration, and active-session pressure using safe identifiers. A service outage should be visible to operators, while ordinary missing rows should not flood error logs. Distinguish authentication errors, missing drivers, unreachable hosts, and denied query privileges. Bounded retries can help selected transient cases, but a wrong password or missing table is a configuration problem to fix. Avoid reconnecting halfway through a transaction and assuming its state survived. If the commit outcome is uncertain after a network failure, resolve it with an idempotency key or reconciliation. Health checks should verify useful readiness without disclosing secrets or repeatedly executing expensive business queries.
Practice task and worked solution
Refactor three product handlers that each create different connections. The solution introduces one factory with agreed charset, exception, and fetch options, then passes a request-scoped connection to each repository. Keep migration credentials in a separate controlled process. Review every log and error page for accidental DSN or password exposure. Test a missing setting and a query denied by the runtime account. For a queue worker, add job-boundary checks that no transaction remains open and unread results are handled. Explain why adding persistent connections should follow measurement of connection overhead and worker capacity rather than being the first optimization.
Continue learning
Combine PDO, security, and exceptions.
The PDO connection documentation discusses connection lifetime and persistent sessions.
PHP Database Connection Best Practices Examples
Each example shows the code, the output it produces and what each part does.
A minimal PDO factory
This function accepts already validated trusted configuration. It contains no request parameters and returns a connection rather than writing a response.
The function returns a PDO instance when the required driver, configuration, network access, and grants are correct. It emits no output by design. TLS and timeout options must be added according to the supported deployment and driver.
<?php
function connectDatabase(array $config): PDO {
foreach (['host', 'database', 'user', 'password'] as $key) {
if (!isset($config[$key]) || !is_string($config[$key]) || $config[$key] === '') {
throw new InvalidArgumentException('Incomplete database configuration');
}
}
$dsn = 'mysql:host=' . $config['host'] . ';dbname=' . $config['database']
. ';charset=utf8mb4';
return new PDO($dsn, $config['user'], $config['password'], [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,
]);
}No output: returns PDO on successful connection; throws on invalid configuration or connection failure.PHP Database Connection Best Practices FAQ
Does one connection factory prevent SQL injection?
No. A factory keeps connection settings consistent, such as the character set and error mode, but every query still needs prepared values. Injection comes from how each query is built, not from how the connection was created.
Can .env be public?
No. A .env file holds database passwords and API keys, so keep it outside served files, in a folder the web server never exposes, and out of source history by listing it in .gitignore. If one has ever been committed or served, rotate the secrets it contained.
Are persistent connections always faster overall?
Not always. They avoid the cost of reconnecting, but they also keep many idle connections open and can carry leftover state, such as open transactions or session settings, into the next request. Their benefits and costs depend on workload and hosting, so measure before enabling them.
Can a reconnect restore a transaction?
No. A transaction belongs to one database session, and when the connection drops the server rolls it back. A new session does not continue the old transaction, so restart the whole unit of work from the beginning, if it's safe to repeat.
PHP Database Connection Best Practices Exercises
Answer each one and check it instantly. If you get stuck, open the hint before the answer.
A worker starts its next job with an open transaction. What should change?
PHP Database Connection Best Practices Quiz
2 questions to check what you learned.
What belongs in a connection factory?
- Public page rendering
- Automatic table deletion
- Validated configuration and agreed connection options
- Visitor-selected credentials
Why separate runtime and migration accounts?
- PHP cannot hold two configuration sets
- Migrations need no credentials
- Every account must be root
- Their required privileges differ