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.
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.
- 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
You can add Ladder to your project using Composer:
$ composer require zerifas/ladder
$ edit ladder.jsonYour 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: OKYou can get Ladder to create a migration template file for you:
$ vendor/bin/ladder create 'Create user table'
src/Migration/Migration1455898526.phpThe 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: OKAdditionally, 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.
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.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();
}
}