Download the PHP package timefrontiers/php-sql-database without Composer

TimeFrontiers PHP SQL Database

A small SQL database manager supporting MySQLi and PDO through a compatible prepared-query and transaction API.

Features

• Backward-compatible MySQLi and PDO query APIs.
• Prepared statements with positional parameters.
• Manual and callback transactions.
• Nested transaction scopes implemented with generated savepoints.
• Rollback-only protection after ignored statement failures.
• Affected-row reporting for prepared INSERT, UPDATE, and DELETE statements.
• Driver error code and SQLSTATE accessors.
• Explicit transaction methods on the SQLDatabase facade.

Requirements

• PHP 8.5 or higher.
• ext-mysqli.
• ext-pdo and the appropriate PDO driver when using PDO.

Installation

Connections

MySQLi is the default facade backend:

Use the additive PDO factory when the database name must be supplied:

An existing concrete driver can also be wrapped without replacing it:

The existing constructor and class-name construction paths remain available for compatibility. The class-name PDO path has no database-name parameter, so new PDO callers should use SQLDatabase::pdo() or fromConnection().

Prepared queries

The legacy query(), multiQuery(), confirmQuery(), result helpers, and magic facade forwarding remain available.

Manual transactions

transactionDepth() reports the managed scope count. inTransaction() reports managed state and verifies it against PDO's native state when PDO can do so.

Callback transactions

The callback receives the concrete driver when called on a concrete driver, or the facade when called on SQLDatabase. Its original return value is returned unchanged; false, 0, null, and an empty array are normal values and are committed.

A thrown callback exception rolls back exactly the callback's scope and is rethrown unchanged when rollback succeeds. Begin, commit, and rollback infrastructure failures use the public exception hierarchy under TimeFrontiers\Exceptions.

Do not call beginTransaction(), commit(), or rollBack() inside a callback in a way that leaves the wrapper owning a different scope than it opened. The wrapper validates scope ownership by identity and throws TransactionException rather than guessing which scope to close. Two consequences matter:

• if the callback left an extra scope open, that exception arrives with the connection still inside a transaction at the deeper level, and neither the leaked scope nor the wrapper's own scope has been closed. The caller now owns cleanup — roll back or close the connection.
• if the callback already committed the wrapper's own scope, the native commit has succeeded and the work is durable. TransactionException here does not mean the operation had no effect.

Neither case is a rollback. Inspect transactionDepth() and reconcile state before deciding what to do.

Nested scopes and rollback-only behavior

Nested beginTransaction() or transaction() calls create library-generated savepoints. An inner rollback discards only the inner work and leaves a clean parent usable:

If a package-managed statement fails inside a transaction, its current scope becomes rollback-only. A later commit() rolls that scope back, records a transaction error, and returns false. The callback API throws TransactionException. Empty SELECT results and successful DML affecting zero rows do not mark a scope rollback-only.

A failure of the transaction machinery itself — begin, savepoint, commit, release, or rollback — marks every open scope rollback-only, not just the current one, because no remaining scope can be proven safe to commit. A failed nested SAVEPOINT will therefore also cause a clean parent scope to roll back.

Calling execute() lets the package observe failures and affected rows. If a caller obtains a native statement from prepare() and executes that native object directly, the package cannot observe a later native execution failure.

Locking and affected rows

Use database row locks inside a managed transaction:

affectedRows() stores the result from the most recently completed statement. Portable guarantees apply to INSERT, UPDATE, and DELETE; SELECT row counts vary by driver. Rolling back does not change what the statement itself reported as affected, although persisted state is restored.

Driver diagnostics

These accessors allow callers to classify duplicate keys, deadlocks, and lock timeouts without parsing localized messages. A later successful operation clears the latest driver identity. getErrors() retains its existing five-element entry format and historical collection behavior. Prepared bound values are not included in new failure messages.

Important transaction rules

• There is no automatic retry. Retry only at a higher layer after determining that the entire database-only operation is safe to repeat.
• A commit failure is an uncertain outcome. Do not rerun the business operation automatically; reconcile database state first.
• Keep HTTP, payment-provider, email, queue, and other external side effects outside transaction callbacks.
• Do not run DDL or database-specific implicit-commit statements inside managed business transactions. MySQL DDL cannot be made transactional by this API.
• changeDB() fails while a managed transaction is active.
• Closing a connection abandons an active transaction and clears managed state.
• Transaction state is local to one connection and cannot span connections.

Tests

Unit tests do not require a database:

Integration tests require a dedicated disposable MySQL or MariaDB database:

For safety, TF_SQL_TEST_DATABASE must contain test. The suite creates and drops uniquely named InnoDB tables but never creates or drops the database.

Focused suites are also available through composer test-nested, composer test-rollback-only, and composer test-concurrency.

License

MIT License. See LICENSE for details.