# Dialects

You write **one** table definition; Bootgly turns it into the right SQL for your database.
PostgreSQL is the default; MySQL and SQLite also work. You usually don't think about this —
this page is for when it matters.

## The short version

- **PostgreSQL** — everything works. Use this if you can.
- **MySQL** — works, with minor differences (no `USING` cast, no per-column nullability
  toggle in `alter`).
- **SQLite** — great for tests and local dev, but `ALTER` is limited: no type change, no
  default change, no drop-constraint, no multi-action alter. Rename/drop column do work.

If you write a migration that an engine can't perform, it **stops with a clear error** —
it never emits broken SQL.

## Same code, different SQL

```php
$Schema->create('users', function (Blueprint $Table): void {
   $Table->add('id', Types::BigInteger)->generate()->constrain(Keys::Primary);
   $Table->add('email', Types::String)->limit(190)->constrain(Keys::Unique);
}, exists: true);
```

PostgreSQL:
```sql
CREATE TABLE IF NOT EXISTS "users" ("id" BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL PRIMARY KEY, "email" VARCHAR(190) NOT NULL UNIQUE)
```
MySQL:
```sql
CREATE TABLE IF NOT EXISTS `users` (`id` BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, `email` VARCHAR(190) NOT NULL UNIQUE)
```
SQLite:
```sql
CREATE TABLE IF NOT EXISTS "users" ("id" INTEGER PRIMARY KEY AUTOINCREMENT, "email" TEXT NOT NULL UNIQUE)
```

Same migration, three correct results — that's the point. You only pick the driver:

```php
new SQL;                       // PostgreSQL
new SQL(['driver' => 'mysql']);
new SQL(['driver' => 'sqlite']);
```

## What each engine can do

| Action | PostgreSQL | MySQL | SQLite |
|--------|:---------:|:-----:|:------:|
| Change a column's type | ✅ | ✅ | ❌ |
| Type change with a cast (`USING`) | ✅ | ❌ | ❌ |
| Change a column's default | ✅ | ✅ | ❌ |
| Toggle nullable in `alter` | ✅ | ❌ | ✅ |
| Drop a constraint | ✅ | ✅ | ❌ |
| Several actions in one `alter` | ✅ | ✅ | ❌ |
| Rename a column | ✅ | ✅ | ✅ |
| Drop a column | ✅ | ✅ | ✅ |

Prefer this table over assumptions; if you target multiple engines, stick to the rows that
are ✅ everywhere.

## Explicit keys and identity sequences

| Engine | After an INSERT with explicit ids | `resync()` |
|--------|-----------------------------------|------------|
| PostgreSQL | the identity (or serial) sequence stays behind | `setval()`, only when the sequence is behind |
| MySQL/MariaDB | `AUTO_INCREMENT` moves past the highest id | `null` |
| SQLite | `AUTOINCREMENT` moves past the highest id | `null` |

On PostgreSQL the role that runs the statement needs `USAGE` (or `SELECT`) and `UPDATE` on the
sequence — the owner of the table has them. Without those privileges it fails with
`permission denied for sequence …`.

The statement never lowers a sequence that is already ahead when it runs. Its limits: a key
above an identity's own `MAXVALUE` fails with `setval: value … is out of bounds`; a key at the
column type's maximum leaves no ids for generated rows; and an insert into the same table while
it runs can slip between its read and its move — seed while nothing else writes.

## Reference

Check support in code with `Capabilities` (namespace
`Bootgly\ADI\Databases\SQL\Schema\Auxiliaries`):

```php
use Bootgly\ADI\Databases\SQL\Schema\Auxiliaries\Capabilities;

$Schema->Dialect->check(Capabilities::AlterColumnUsing); // bool
$Schema->Dialect->transactions;                          // bool — migrations run in a tx?
```

Capability names map to the table above: `AlterColumnType`, `AlterColumnUsing`,
`AlterColumnDefault`, `AlterColumnNullability`, `DropConstraint`, `MultiActionAlter`,
`RenameColumn`, `DropColumn`, `AddConstraint`.

Other engine-specific output:

```php
$pg->unindex('users', 'users_email_index'); // DROP INDEX IF EXISTS "users_email_index"
$my->unindex('users', 'users_email_index'); // DROP INDEX `users_email_index` ON `users`
$pg->rename('users', 'members');             // ALTER TABLE "users" RENAME TO "members"
$my->rename('users', 'members');             // RENAME TABLE `users` TO `members`
$pg->unindex('audit.users', 'users_email_index');
// DROP INDEX IF EXISTS "audit"."users_email_index"  (schema-qualified)
```

```php
resync (string $table, array $assignments): null|Query
```

Compile the statement that moves identity sequences past the explicit integer keys one INSERT
wrote. It takes what the Builder compiled for that INSERT — the quoted table and
`Builder::$assignments` — and the seed **[Runner](/manual/ADI/Databases/SQL/Seed/overview/)**
calls it after every `Builder` INSERT a seeder returns. PostgreSQL returns a forward-only
`setval()` statement: a sequence already past the keys when it runs is left untouched. MySQL and SQLite
return `null`, since their auto-increment counters already move past explicit values.

PostgreSQL keeps `Types::Json` and `Types::JsonB` distinct (`JSON` vs `JSONB`); other
engines map both to their JSON type. The migration
**[Runner](/manual/ADI/Databases/SQL/Schema/Migrations/overview/)** uses the dialect
advisory `lock()`/`unlock()` only on engines that support it.

## Transactional DDL and advisory locks

| Engine | `transactions` (DDL in a tx) | Advisory lock |
|--------|------------------------------|---------------|
| PostgreSQL | yes | `pg_try_advisory_lock` / `pg_advisory_unlock` |
| MySQL/MariaDB | no — DDL causes implicit commits | `GET_LOCK` / `RELEASE_LOCK` |
| SQLite | yes | — (local file lock only) |

On engines without an advisory lock, migration/seeder coordination falls back to the local
file lock. Run migrations with `pool.max = 1` so the advisory lock and its release land on
the same session.