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

## 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)
```

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.