zerifas / ladder
A database migration tool, written in PHP.
Requires
- php: >=7.0
- pimple/pimple: ^3.0
- symfony/console: ^2.8
- symfony/event-dispatcher: ^2.8
- zerifas/collections: ^1.1
- zerifas/json: ^2.0
Requires (Dev)
- php-coveralls/php-coveralls: ^1.0
- phpunit/phpunit: ^6.5
- squizlabs/php_codesniffer: ^2.6
README
What is it?
Ladder started life many years ago as an extremely simple database migration system. It's grown over time to include a host of features. It's written for PHP >= 8.0, and supports the popular MySQL database server via PDO.
What would I use this for?
It's used in conjunction with source control (Git, Mercurial, SVN, et al) in order to track the changes made to the database alongside application source code.
This allows multiple developers to work on a project and know whether or not they have the correct database schema for their local development environments, and enables them to bring their schema up-to-date with a single command.
Migrations have at least two methods: apply()
and rollback()
. The apply()
method
is run when the migration is applied, and rollback()
when it is un-applied.
Logically, a rollback()
method should do the opposite to its counterpart apply()
method; Dropping a column instead of adding it, etc.
What is supported?
- Creating and dropping tables
- Adding, dropping, and altering columns
- Adding and dropping indexes/constraints
- Data operations: insert/update/delete
- Storing metadata when applying a migration, and using it during rollback
How do I use it?
You can add Ladder to your project using Composer:
$ composer require zerifas/ladder $ edit ladder.json
Your ladder.json
file should look something like the included ladder.json.sample
:
{ "db": { "host": "localhost", "dbname": "YOUR_DATABASE", "charset": "utf8", "username": "privileged_user", "password": "G00D_P@ssw0rd%" }, "migrations": [ { "namespace": "YourAppNamespace\\Migration", "path": "src/Migration" } ] }
If your database does not exist, and your user has access to create it, init
will do so:
$ vendor/bin/ladder init
Created database 'test2'.
Now, you should be able to run the status
command to see what's not applied, and migrate
to apply changes.
$ vendor/bin/ladder status Status Missing Migrations 1 - Ladder internal tables $ vendor/bin/ladder migrate Migrate from 0 to 1 Applying 1 - Ladder internal tables: OK
You can get Ladder to create a migration template file for you:
$ vendor/bin/ladder create 'Create user table'
src/Migration/Migration1455898526.php
The template shows an example of creating and dropping a table, edit and save the file, and then:
$ vendor/bin/ladder migrate Migrate from 1 to 1455898526 Applying 1455898526 - Create user table: OK
Additionally, you can provide a migration id to the migrate
command in order to migrate to that specific point.
The migrate
command can be used to migrate to an older migration only when given the --rollback
option. This is to avoid accidentally dropping tables or columns.
Commands
Ladder aims to be self-documenting in use (though we need documentation for the Migration methods):
$ vendor/bin/ladder Ladder version 2.1.0 Usage: [options] command [arguments] … Available commands: create Create a new Migration file. help Displays help for a command init Attempt to create the configured database. list Lists commands migrate Migrate to the latest, or specified migration number. reapply Rollback the given migration (if applied), then apply it. remove Rollback a single migration. status Show the current status of the database.
Example Migration
Here's an example that does way too much for a single Migration, but should cover all the use cases.
<?php namespace YourAppNamespace\Migration; use Zerifas\Ladder\Migration\AbstractMigration; class Migration1455898557 extends AbstractMigration { public function getName() { return 'Demo lots of features'; } public function apply() { // We are assuming that the `users` table already exists for this example, // and we are creating the `posts` table, and creating a user. $posts = $this->table('posts'); // addColumn($name, $type, array $options = []) // Possible keys and values for `$options`: // null - boolean used to allow a column's value to be null, default: true // limit - integer for `varchar` column width, or string '10, 3' for `float`/`double` column precision // options - array of strings for `enum` column options // unsigned - boolean, used to mark `integer` columns as unsigned, default: false // default - mixed value to use as default column value // after - column name to add this new column after $posts->addColumn('id', 'autoincrement', ['null' => false, 'unsigned' => true]) ->addColumn('userId', 'integer', ['null' => false, 'unsigned' => true]) ->addColumn('urlSlug', 'varchar', ['null' => false, 'limit' => 10]) ->addColumn('createdAt', 'datetime', ['null' => false]) ->addColumn('publishedAt', 'datetime') ->addColumn('title', 'varchar', ['null' => false, 'limit' => 128]) ->addColumn('body', 'text', ['null' => false]) ->addIndex('PRIMARY', ['id']) // Create index named PRIMARY, containing the `id` column ->addIndex('userId') // If no columns given, Ladder assumes the name is a column ->addIndex('userId:createdAt', ['userId', 'createdAt'], ['unique' => true]) // Custom name, specified columns, and unique ->create() ; // Create a user, and get its unique id. $users = $this->table('users'); $users ->addColumn('userGroup', 'integer', ['null' => false, 'default' => 0, 'first' => true]) ->addColumn('notes', 'text', ['after' => 'userGroup']) ->alter() ; $users->insert([ 'username' => '<username here>', 'password' => '<some valid password hash>', ]); // If we return an array from this `apply` method, the same data will be supplied // as the `$data` parameter when `rollback` is called. return [ 'userId' => $users->getLastInsertId(), ]; } public function rollback(array $data = null) { $users = $this->table('users'); if (($id = $data['userId'] ?? null)) { $users->delete([ 'id' => $id, ]); } $users ->dropColumn('notes') ->dropColumn('userGroup') ->alter() ; $this->table('posts')->drop(); } }