# Schema Builder

The Schema Builder is **how you shape your database**. You call it from inside a migration
to create, change or drop tables — never raw SQL. It writes the correct SQL for your engine
(PostgreSQL, MySQL or SQLite) for you.

> New here? Follow the **[Database migrations](/guide/database-migrations/overview/)** guide
> first — it shows the whole flow. This page is what you do inside `Up`/`Down`.

## The five things you'll do

Inside a migration you get a `$Schema`. With it you almost always do one of these:

```php
// 1. Create a table
$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);
   $Table->add('active', Types::Boolean)->default = true;
   $Table->add('created_at', Types::Timestamp)->default = new Expression('CURRENT_TIMESTAMP');
});

// 2. Change an existing table
$Schema->alter('users', function (Blueprint $Table): void {
   $Table->add('bio', Types::Text)->nullable = true;  // new nullable column
   $Table->remove('legacy');                           // drop a column
});

// 3. Drop a table
$Schema->drop('users');

// 4. Rename a table
$Schema->rename('users', 'members');

// 5. Add / drop an index
$Schema->index('users', ['email', 'created_at'], unique: true);
$Schema->unindex('users', 'users_email_created_at_unique');
```

> `nullable` and `default` are **properties**, not methods: you assign them
> (`->default = true`, `->nullable = true`), you don't chain them. To drop an existing
> default in `alter`, assign `Defaults::None`. Details in
> **[Defining tables](/manual/ADI/Databases/SQL/Schema/Blueprint/overview/)**.

**What does it produce?** Each call returns a compiled query — it does not run until the
migration applies it. For the first `create` above, on PostgreSQL you get:

```sql
CREATE TABLE "users" ("id" BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL PRIMARY KEY, "email" VARCHAR(190) NOT NULL UNIQUE, "active" BOOLEAN NOT NULL DEFAULT TRUE, "created_at" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP)
```

How you describe columns (the `$Table->add(...)` part) lives in
**[Defining tables](/manual/ADI/Databases/SQL/Schema/Blueprint/overview/)**.

## Good to know

- **Safe by default.** `drop`, `unindex` and `unconstrain` use `IF EXISTS`, so re-running a
  migration won't blow up. Want it strict? `$Schema->drop('users', exists: false)`.
- **Idempotent create.** `$Schema->create('settings', $build, exists: true)` →
  `CREATE TABLE IF NOT EXISTS …`. Default column type is `Types::Text`.
- **Empty tables rejected.** A `create` with no columns throws `InvalidArgumentException`.
- **Index names are automatic.** `index()` names it `<table>_<columns>_<index|unique>` and
  truncates it to the engine's identifier limit (63 chars on PostgreSQL).

## Reference

Get a `$Schema` outside a migration via the database. The active engine is at
`$Schema->Dialect`.

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

$Schema = new SQL->structure();                         // PostgreSQL
$Schema = new SQL(['driver' => 'mysql'])->structure();  // MySQL
$Schema = new SQL(['driver' => 'sqlite'])->structure(); // SQLite
```

Names accept a `string`, any `Stringable`, or a **backed enum** (its `value` is used).

### create

```php
create (BackedEnum|Stringable|string $Table, Closure $Build, bool $exists = false): Query
```

Compile one `CREATE TABLE`. The closure receives a fresh `Blueprint`. `exists: true` emits
`CREATE TABLE IF NOT EXISTS`. An empty blueprint throws `InvalidArgumentException`.

### alter

```php
alter (BackedEnum|Stringable|string $Table, Closure $Build): Query
```

Compile one `ALTER TABLE`. The closure receives a fresh `Blueprint`; every add/drop/change/
rename/reference action is batched into a single statement when the engine supports it:

```php
$Schema->alter('users', function (Blueprint $Table): void {
   $Table->add('bio', Types::Text)->nullable = true;
   $Table->remove('legacy');
   $Table->reference('team_id', 'teams', 'id')
      ->delete(References::Cascade)
      ->update(References::Restrict);
});
// ALTER TABLE "users" ADD COLUMN "bio" TEXT, DROP COLUMN "legacy", ADD CONSTRAINT "users_team_id_teams_fk" FOREIGN KEY ("team_id") REFERENCES "teams" ("id") ON DELETE CASCADE ON UPDATE RESTRICT
```

### drop

```php
drop (BackedEnum|Stringable|string $Table, bool $exists = true): Query
```

Compile one `DROP TABLE`. `IF EXISTS` by default; pass `exists: false` for strict.
`$Schema->drop('users')` → `DROP TABLE IF EXISTS "users"`.

### rename

```php
rename (BackedEnum|Stringable|string $From, BackedEnum|Stringable|string $To): Query
```

Compile one table rename. `$Schema->rename('users', 'members')` →
`ALTER TABLE "users" RENAME TO "members"`.

### index

```php
index (BackedEnum|Stringable|string $Table, string|array $Columns, null|string $name = null, bool $unique = false): Query
```

Compile one `CREATE INDEX`. With no `name`, generates `<table>_<columns>_<index|unique>`,
truncated to the engine identifier limit.

```php
$Schema->index('users', ['email', 'created_at'], unique: true);
// CREATE UNIQUE INDEX "users_email_created_at_unique" ON "users" ("email", "created_at")
```

### unindex

```php
unindex (BackedEnum|Stringable|string $Table, BackedEnum|Stringable|string $Name, bool $exists = true): Query
```

Compile one `DROP INDEX` (`IF EXISTS` by default). PostgreSQL keeps the table schema
qualifier: `$Schema->unindex('audit.users', 'users_email_index')` →
`DROP INDEX IF EXISTS "audit"."users_email_index"`.

### unconstrain

```php
unconstrain (BackedEnum|Stringable|string $Table, BackedEnum|Stringable|string $Name, bool $exists = true): Query
```

Compile one `DROP CONSTRAINT` (`IF EXISTS` by default).
`$Schema->unconstrain('users', 'users_team_id_teams_fk')` →
`ALTER TABLE "users" DROP CONSTRAINT IF EXISTS "users_team_id_teams_fk"`.

Per-engine output differences: **[Dialects](/manual/ADI/Databases/SQL/Schema/Dialects/overview/)**.