, (*1)
Fluid schema builder for Doctrine DBAL
Build and modify your database schema using DBAL and a fluid syntax., (*2)
, (*3)
Why?
Doctrine DBAL provides a powerful API to alter your database schema.
This library is a wrapper around the DBAL standard API to provide a shorter, quicker syntax for day-to-day use. It provides shortcuts and syntactic sugars to make you efficient., (*4)
IDE friendly
You can use the autocomplete of your preferred IDE (PHPStorm, Eclipse PDT, Netbeans...) to build your schema easily. No need to look at the docs anymore!, (*5)
Static code analysis
Your favorite static PHP code analyzer (Scrutinizer, PHPStan...) can catch errors for you!
For instance, each database type is a PHP method, so no typos anymore in the column type - ... was it 'INT' or 'INTEGER' already? :), (*6)
Why not?
The fluid schema builders aims at solving the cases you encounter in 99% of your schemas in a concise way.
It does not cover the whole possible use cases and there is no aim to target that goal., (*7)
For instance, if you have foreign keys on several columns, you cannot use FluidSchema. You should fallback to classic DBAL., (*8)
Comparison with DBAL "native" API
Instead of:, (*9)
$table = $schema->createTable('posts');
$table->addColumn('id', 'integer');
$table->addColumn('description', 'string', [
'length' => 50,
'notnull' => false,
]);
$table->addColumn('user_id', 'integer');
$table->setPrimaryKey(['id']);
$table->addForeignKeyConstraint('users', ['user_id'], ['id']);
you write:, (*10)
$db = new FluidSchema($schema);
$posts = $db->table('posts');
$posts->id() // Let's create a default autoincremented ID column
->column('description')->string(50)->null() // Let's create a 'description' column
->column('user_id')->references('users'); // Let's create a foreign key.
// We only specify the table name.
// FluidSchema infers the column type and the "remote" column.
Features
FluidSchema does its best to make your life easier., (*11)
Tables and column types, (*12)
$table = $db->table('foo');
// Supported types
$table->column('xxxx')->string(50) // VARCHAR(50)
->column('xxxx')->integer()
->column('xxxx')->float()
->column('xxxx')->text() // Long string
->column('xxxx')->boolean()
->column('xxxx')->smallInt()
->column('xxxx')->bigInt()
->column('xxxx')->decimal(10, 2) // DECIMAL(10, 2)
->column('xxxx')->guid()
->column('xxxx')->binary(255)
->column('xxxx')->blob() // Long binary
->column('xxxx')->date()
->column('xxxx')->datetime()
->column('xxxx')->datetimeTz()
->column('xxxx')->time()
->column('xxxx')->dateImmutable() // From Doctrine DBAL 2.6+
->column('xxxx')->datetimeImmutable() // From Doctrine DBAL 2.6+
->column('xxxx')->datetimeTzImmutable() // From Doctrine DBAL 2.6+
->column('xxxx')->timeImmutable() // From Doctrine DBAL 2.6+
->column('xxxx')->dateInterval() // From Doctrine DBAL 2.6+
->column('xxxx')->array()
->column('xxxx')->simpleArray()
->column('xxxx')->json() // From Doctrine DBAL 2.6+
->column('xxxx')->jsonArray() // Deprecated in Doctrine DBAL 2.6+
->column('xxxx')->object(); // Serialized PHP object
Shortcut methods:, (*13)
// Create an 'id' primary key that is an autoincremented integer
$table->id();
// Don't like autincrements? No problem!
// Create an 'uuid' primary key that is of the DBAL 'guid' type
$table->uuid();
// Create "created_at" and "updated_at" columns
$table->timestamps();
Creating indexes:, (*14)
// Directly on a column:
$table->column('login')->string(50)->index();
// Or on the table object (if there are several columns to add to an index):
$table->index(['category1', 'category2']);
Creating unique indexes:, (*15)
// Directly on a column:
$table->column('login')->string(50)->unique();
// Or on the table object (if there are several columns to add to the constraint):
$table->unique(['login', 'status']);
Make a column nullable:, (*16)
$table->column('description')->string(50)->null();
Set the default value of a column:, (*17)
$table->column('enabled')->bool()->default(true);
Create a foreign key, (*18)
$table->column('country_id')->references('countries');
Note: The foreign key will be automatically created on the primary table of the table "countries".
The type of the "country_id" column will be exactly the same as the type of the primary key of the "countries" table., (*19)
Create a jointure table (aka associative table) between 2 tables:, (*20)
$db->junctionTable('users', 'roles');
// This will create a 'users_roles' table with 2 foreign keys:
// - 'user_id' pointing on the PK of 'users'
// - 'role_id' pointing on the PK of 'roles'
Add a comment to a column:, (*21)
$table->column('description')->string(50)->comment('Lorem ipsum');
Declare a primary key:, (*22)
$table->column('uuid')->string(36)->primaryKey();
// or
$table->column('uuid')->then()
->primaryKey(['uuid']);
Declare an inheritance relationship between 2 tables:, (*23)
In SQL, there is no notion of "inheritance" like with PHP objects.
However, a common way to model inheritance is to write one table for the base class (containing the base columns/properties) and then one table per extended class containing the additional columns/properties.
Each extended table has a primary key that is also a foreign key pointing to the base table., (*24)
$db->table('contacts')
->id()
->column('email')->string(50);
$db->table('users')
->extends('contacts')
->column('password')->string(50);
The extends method will automatically create a primary key with the same name and same type as the extended table. It will also make sure this primary key is a foreign key pointing to the extended table., (*25)
Automatic 'quoting' of table and column names
By default, the fluid-schema-builder will not quote your identifiers (because it does not know what database you use)., (*26)
This means that you cannot create an item with a reserved keyword., (*27)
$db->table('contacts')
->id()
->column('date')->datetime(); // Will most likely fail, because "date" is a reserved keyword!
However, if you give to fluid-schema-builder your database platform at build time, then it will quote all identifiers by default. No more nasty surprises!, (*28)
use TheCodingMachine\FluidSchema\DefaultNamingStrategy;
// Assuming $connection is your DBAL connection
$db = new FluidSchema($schema, new DefaultNamingStrategy($connection->getDatabasePlatform()));
Wallogit.com
