# 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
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 | yes (libsqlite ≥ 3.35) |
| Generated keys | `RETURNING` rows | `Result->inserted` (OK packet) | `Result->inserted` + `RETURNING` |
| 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 dialect does not emit `RETURNING`. 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
```

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