# SQL Drivers

`Bootgly\ADI\Databases\SQL\Drivers` registers the native wire drivers that execute the SQL
generated by the Query Builder and the Schema. Bootgly ships three built-in drivers —
**PostgreSQL**, **MySQL/MariaDB** and **SQLite** — implemented natively, with zero
third-party dependencies.

## Selecting a driver

The `driver` config key selects the driver, the Query Builder dialect and the Schema (DDL)
dialect at once:

```php
use Bootgly\ADI\Databases\SQL;

$PostgreSQL = new SQL(['driver' => 'pgsql']);
$MySQL = new SQL(['driver' => 'mysql']);
$SQLite = new SQL(['driver' => 'sqlite', 'database' => ':memory:']);
```

In a project, bind the driver through the `database` config scope — `DB_CONNECTION`
selects a connection block that must already be declared:

```bash :toolbar="true";
DB_CONNECTION=mysql DB_HOST=127.0.0.1 DB_PORT=3306 DB_USER=root php bootgly boot
```

The config adapter maps `pgsql`/`postgres`/`postgresql` to `Connections->PostgreSQL`,
`mysql`/`mariadb` to `Connections->MySQL`, and `sqlite`/`sqlite3` to
`Connections->SQLite`. It does not synthesize a missing block from ADI defaults. If the selected
canonical block is absent, it throws, for example,
`Database config is missing the selected connection scope: Connections->MySQL.`

## Capability matrix

| Capability | PostgreSQL | MySQL/MariaDB | SQLite |
|------------|------------|---------------|--------|
| Execution model | async (non-blocking) | async (non-blocking) | synchronous |
| Wire pipelining | yes | no (request-response FIFO) | — |
| TLS | yes (`secure` modes) | yes (`secure` modes) | — |
| Authentication | cleartext, MD5, SCRAM-SHA-256 | `mysql_native_password`, `caching_sha2_password` (full auth via TLS or pinned RSA key) | — |
| Prepared statements | extended protocol + LRU cache | binary protocol + LRU cache | `SQLite3Stmt` + LRU cache |
| `RETURNING` | yes | no — `Result->inserted` instead | no — the `sqlite3` extension would run it twice |
| Generated keys | `RETURNING` rows | `Result->inserted` (OK packet) | `Result->inserted` (`lastInsertRowID`) |
| Cancellation | `CancelRequest` side channel | `KILL QUERY` side channel | not supported |
| Transactional DDL | yes | no (implicit commits) | yes |
| Advisory locks | `pg_advisory_lock` | `GET_LOCK` | — (file lock only) |

Everything above the driver — connection Pool, Transactions, Query Builder, ORM
Repository, Migrations and Seeders — is driver-agnostic and works identically on the
three engines.

## Generated keys without RETURNING

The MySQL and SQLite dialects do not emit `RETURNING` (on SQLite the `sqlite3` extension
executes such statements twice, duplicating the write — the driver fails them fast).
Instead, the driver reports the generated id through `Result->inserted`, and the ORM
Repository backfills the entity key transparently at `hydrate()`:

```php
$Repository = $Database->map(User::class);

$User = new User;
$User->name = 'Ada';

$Operation = $Database->await($Repository->save($User));
$Saved = $Repository->hydrate($Operation)->entity;

$Saved->id; // backfilled from Result->inserted
```

### Keys wider than a PHP int

A MySQL `BIGINT UNSIGNED` key can go past 2^63, which no PHP `int` can hold. The driver
reports those ids as **exact decimal strings** rather than losing them, so declare the key
to accept one:

```php
#[Table('orders')]
class Order
{
   #[Key]
   public null|int|string $id = null;
}
```

`Result->inserted` is therefore `int|string`: an `int` for every id inside `PHP_INT_MAX`, a
string only past it. A key declared `null|int` that receives such an id raises a
`RuntimeException` naming the property instead of storing a narrowed value — a saturated key
would match no row, and the next `save()` would silently update nothing.

This only comes up when the table actually holds ids that large. A plain
`BIGINT UNSIGNED AUTO_INCREMENT` never reaches it on its own, but an import that preserves
one legacy id above 2^63 makes the server continue the sequence from there.

Reading has the same rule, and it is not limited to keys. Hydration narrows a decoded value to
the declared property type, so any column whose value is past `PHP_INT_MAX` — a key, a counter,
a `DECIMAL` — raises the same `RuntimeException` naming the property rather than saturating it.
Declare the property `null|int|string` and it hydrates exactly, as the driver decoded it.

## Pool notes

- Every pooled connection binds one driver instance — prepared-statement caches are
  per-connection.
- The prepared-statement cache size is the `statements` config key (default `256`);
  the least recently used statement is evicted when the cap is reached. The cap bounds the
  server side too: a statement the driver stops tracking is closed on the wire, and one
  statement is prepared per connection no matter how many operations ask for it at once.
- A statement is closed only once no pending command still carries its id. A co-located
  operation bakes the server id into its bytes when it is created, and those bytes can wait
  in the FIFO for several round-trips — evicting that statement in the meantime would send
  the close ahead of a command that still needs it.
- Operations created before the first one reaches the socket share that single prepare.
  A sibling binds the statement its owner is preparing instead of preparing it again —
  which on PostgreSQL would fail outright (`42P05`), and on MySQL would leave a server
  statement nothing could ever close.
- A batch too large for the socket buffer is written in parts, and the operation writing it
  holds the stream until it finishes. If its caller gives up, the next operation on that
  connection finds the holder past its deadline and finishes the flush itself: the answer is
  drained silently, the stale operation fails with its own timeout and is never retried —
  its work ran with an outcome nobody saw — and the connection stays up. A batch abandoned
  before any byte reached the wire is simply withdrawn, and a retry stays legal. A batch the
  caller **cancelled** is never completed: sending its remainder would run exactly what was
  withdrawn, so the session is dropped and the pool opens a fresh connection. And when the
  session dies under a half-written batch, that batch fails with the same cause as every other
  operation on it, rather than waiting out its own deadline.
- MySQL has no wire pipelining: co-located operations queue in a FIFO where only the
  head owns the socket. The Pool stays correct — siblings pump the shared read stream.
- SQLite is synchronous: operations resolve immediately and never suspend. A `:memory:`
  database is private to the handle that opens it, so its pool is confined to one
  connection — a `pool.max` above `1` is reduced to `1`. File databases keep their pool.

## Reference

- **[PostgreSQL driver](/manual/ADI/Databases/SQL/Drivers/PostgreSQL/overview/)** — wire
  protocol 3.0, SCRAM authentication, TLS and pipelining.
- **[MySQL driver](/manual/ADI/Databases/SQL/Drivers/MySQL/overview/)** — handshake,
  authentication plugins, binary protocol and `KILL QUERY`.
- **[SQLite driver](/manual/ADI/Databases/SQL/Drivers/SQLite/overview/)** — zero-setup
  file and `:memory:` databases.
- **[Query dialects](/manual/ADI/Databases/SQL/Builder/Dialects/overview/)** — SQL
  generation differences between the engines.
- **[Schema dialects](/manual/ADI/Databases/SQL/Schema/Dialects/overview/)** — DDL
  generation, transactional DDL and advisory locks.