Schema Builder

The schema builder is the query builder's counterpart for DDL: the same fluent object, rendering CREATE TABLE and ALTER TABLE in whichever dialect the connected adapter speaks. Write the table once and it stands up on MySQL, PostgreSQL and SQLite alike.

Creating a Table#

createSchema() on the adapter returns a builder bound to it, the same way createSql() does. create() names the table, the type methods add columns to it, and execute() sends the result:

use Pop\Db\Record;

$db = Record::getDb();

$schema = $db->createSchema();
$schema->create('widgets')
    ->int('id', 16)->increment()
    ->varchar('name', 255)->notNullable()
    ->decimal('price', 10, 2)
    ->datetime('created_at')
    ->primary('id');

$schema->execute();

Each type method takes the column name first, then whatever that type needs.

The dialect differences are the point of the builder. That statement renders as `id` INT(16) AUTO_INCREMENT with an ENGINE=InnoDB suffix on MySQL, a CREATE SEQUENCE alongside the table on PostgreSQL, and "id" INTEGER PRIMARY KEY AUTOINCREMENT on SQLite.

Column Types#

The type methods cover the families every adapter has:

The integer and string methods take a size second; decimal(), numeric(), float(), real() and double() take a size and a precision. For a type outside the list, addColumn($name, $type, $size, $precision, $attributes) writes it through verbatim.

Where an adapter spells a type differently, the builder renders the one it has. SQLite renders decimal('price', 10, 2) as NUMERIC, float() as REAL and varchar('name', 255) as a plain VARCHAR; PostgreSQL renders blob() as TEXT and datetime() as TIMESTAMP. Render the schema and read it once when you are targeting more than one adapter.

Modifiers, Keys and Indexes#

increment(), notNullable(), nullable(), defaultIs() and unsigned() modify the column most recently added, which is what makes the chain read the way it does. increment() takes the starting value, 1 by default.

primary(), index() and unique() name their column again, since a key can span several — pass an array for a composite one, or no argument to fall back to the last column added:

use Pop\Db\Record;

$db = Record::getDb();

$schema = $db->createSchema();
$schema->create('widget_tags')
    ->int('widget_id', 16)
    ->varchar('tag', 64)
    ->bigInt('hits')->unsigned()->defaultIs(0)
    ->primary(['widget_id', 'tag'])
    ->index('tag', 'idx_tag');

$schema->execute();

An index is named for the columns it covers — index_tag, or index_widget_id_tag for a composite — and the second argument names it yourself, as above.

unsigned() renders on MySQL; PostgreSQL and SQLite take the column without it. On SQLite a primary() renders as a UNIQUE constraint, and an increment() column additionally carries PRIMARY KEY AUTOINCREMENT inline; MySQL and PostgreSQL emit PRIMARY KEY in both cases.

Foreign Keys#

Foreign keys are declared on the referencing table, and references(), on() and onDelete() build one constraint between them:

use Pop\Db\Record;

$db = Record::getDb();

$schema = $db->createSchema();
$schema->create('widget_notes')
    ->int('id', 16)->increment()
    ->int('widget_id', 16)
    ->text('note')
    ->primary('id')
    ->foreignKey('widget_id')->references('widgets')->on('id')->onDelete('CASCADE');

$schema->execute();

MySQL and PostgreSQL take the constraint as a trailing ALTER TABLE ... ADD CONSTRAINT that the same execute() applies against the table it just created. SQLite takes constraints at creation time, so the builder writes it inline in the CREATE TABLE instead — the same PHP produces whichever form the adapter wants. Declare foreign keys inside create() for a schema SQLite will run: alter() names the reason if you reach for it there, since changing a constraint on SQLite means recreating the table.

SQLite checks foreign keys per connection, and starts with the check off. Run PRAGMA foreign_keys=ON on a connection that should reject a row pointing at a parent that is not there.

Altering, Renaming and Dropping#

The other four verbs return their own objects from the same builder, and all four execute the same way:

use Pop\Db\Record;

$db = Record::getDb();

$schema = $db->createSchema();
$schema->alter('widgets')->varchar('sku', 64)->after('name');
$schema->execute();

$schema = $db->createSchema();
$schema->alter('widgets')->dropColumn('created_at');
$schema->execute();

after() positions a newly added column and is MySQL syntax — PostgreSQL and SQLite take the column without it. cascade() on drop() or truncate() is MySQL and PostgreSQL.

modifyColumn($old, $new, $type, $size) renders MySQL's CHANGE COLUMN, which renames and retypes in one statement. On PostgreSQL and SQLite it renders RENAME COLUMN, so for a migration that runs on more than MySQL, add the new column, copy the values across and drop the old one.

Rendering and Executing#

Reading the SQL does not spend it. echo $schema; prints the statement and leaves the object intact, so the same object renders identically again and still executes afterward — which is what makes logging a migration before running it a one-liner:

use Pop\Db\Record;

$db = Record::getDb();

$schema = $db->createSchema();
$schema->dropIfExists('widgets');
$schema->create('widgets')->int('id', 16)->increment()->primary('id');

echo $schema;

$schema->execute();

One schema object holds as many statements as you give it, and it renders them grouped by verb rather than in call order: every drop() first, then create(), alter(), rename() and truncate(). The sample above prints the DROP before the CREATE for that reason, and would do so whichever order the two calls came in. Use a separate schema object per statement when the sequence matters.

execute() runs the statements and clears the object; execute(false) runs them and keeps them, and reset() clears without running. disableForeignKeyCheck() wraps the whole render in SET foreign_key_checks = 0 on MySQL and PRAGMA foreign_keys=off on SQLite, which is what lets a batch drop tables in any order.

setEngine() and setCharset() on a create() set the MySQL table options, InnoDB and utf8 by default:

use Pop\Db\Record;

$db = Record::getDb();

$schema = $db->createSchema();

$create = $schema->create('widgets');
$create->int('id', 16)->increment()->primary('id');
$create->setEngine('MyISAM')->setCharset('utf8mb4');

$schema->execute();

See Also#

• Migrations — wrapping a pair of these in up() and down(), and running them with Kettle
• Query Builder — the same builder pattern applied to SELECT, INSERT, UPDATE and DELETE
• Querying — the adapter createSchema() hangs off
• Records & the ORM — the table classes a schema is written for
• pop-db README — the full column-type and schema API surface