# 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 the connection block that will be read:

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

Driver aliases accepted by the config adapter: `pgsql`/`postgres`/`postgresql`,
`mysql`/`mariadb` and `sqlite`/`sqlite3`.

## 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.

## 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.
- 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. Keep
  `pool.max = 1` for `:memory:` databases (each handle would open an independent database).

## 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.