Simple library in PHP for database version control. Supports Sqlite, MySql, Sql Server and Postgres.
Database Migration is a set of commands for upgrade or downgrade a database. This library uses only SQL commands.
The basic usage is
- Create a connection a ConnectionManagement object. For more information see the "byjg/anydataset" component
- Create a Migration object with this connection and the folder where the scripts sql are located.
- Use the proper command for "reset", "up" or "down" the migrations scripts.
See an example:
$connectionUri = new \ByJG\Util\Uri('mysql://migrateuser:migratepwd@localhost/migratedatabase');
$migration = new Migration($connectionUri, '.');
// Restore the database using the "base.sql" script and run ALL existing scripts for up the database version
// and run the up() method to maintain the database updated;
$migration->reset();
// Run ALL existing scripts for up the database version from the current version to the last version;
$migration->up();The Migration object controls the database version.
The scripts are divided in three set of scripts:
- The BASE script contains ALL sql commands for create a fresh database;
- The UP scripts contain all sql migration commands for "up" the database version;
- The DOWN scripts contain all sql migration commands for "down" or revert the database version;
The directory scripts is :
<root dir>
|
+-- base.sql
|
+-- /migrations
|
+-- /up
|
+-- 00001.sql
+-- 00002.sql
+-- /down
|
+-- 00000.sql
+-- 00001.sql
- "base.sql" is the base script
- "up" folder contains the scripts for migrate up the version. For example: 00002.sql is the script for move the database from version '1' to '2'.
- "down" folder contains the scripts for migrate down the version. For example: 00001.sql is the script for move the database from version '2' to '1'. The "down" folder is optional.
If you work with multiple developers and multiple branches it is to difficult to determine what is the next number.
In that case you have the suffix "-dev" after the version number.
See the scenario:
- Developer 1 create a branch and the most recent version in e.g. 42.
- Developer 2 create a branch at the same time and have the same database version number.
In both case the developers will create a file called 43-dev.sql. Both developers will migrate UP and DOWN with no problem and your local version will be 43.
But developer 1 merged your changes and created a final version 43.sql (git mv 43-dev.sql 43.sql). If the developer 2
update your local branch he will have a file 43.sql (from dev 1) and your file 43-dev.sql.
If he is try to migrate UP or DOWN
the migration script will down and alert him there a TWO versions 43. In that case, developer 2 will have to update your
file do 44-dev.sql and continue to work until merge your changes and generate a final version.
Migration library creates the 'migrate' script. It has the follow syntax:
Usage:
command [options] [arguments]
Options:
-h, --help Display this help message
-q, --quiet Do not output any message
-V, --version Display this application version
--ansi Force ANSI output
--no-ansi Disable ANSI output
-n, --no-interaction Do not ask any interactive question
-v|vv|vvv, --verbose Increase the verbosity of messages: 1 for normal output, 2 for more verbose output and 3 for debug
Available commands:
create Create the directory structure FROM a pre-existing database
down Migrate down the database version.
help Displays help for a command
install Install or upgrade the migrate version in a existing database
list Lists commands
reset Create a fresh new database
up Migrate Up the database version
update Migrate Up or Down the database version based on the current database version and the migration scripts available
version Get the current database version
The basic usage is:
migrate <COMMAND> --path=<scripts> uri://connection
The --path specify where the base.sql and migrate scripts are located.
If you omitted the --path it will assume the current directory. You can also
set the MIGRATE_PATH environment variable with the base path
The uri://connection is the uri that represents the connection to the database. You can see here to know more about the connection string.
You can omit the uri parameter if you define it in the
MIGRATE_CONNECTION environment variable
export MIGRATE_CONNECTION=sqlite:///path/to/my.dbCreate a empty directory structure with base.sql and migrations/up and migrations/down for migrations. This is useful for create from scratch a migration scheme.
Ex.
migrate create /path/to/sql If you already have a database but it is not controlled by the migration system you can use this method for install the required tables for migration.
migrate install mysql://server/databaseWill apply all necessary migrations to keep your database updated.
migrate update mysql://server/databaseUpdate command can choose if up or down your database depending on your current database version. You can also specify a version:
migrate update --up-to=34Creates/replace a database with the "base.sql" and apply ALL migrations
migrate reset # reset the database and apply all migrations scripts.
migrate reset --up-to=5 # reset the database and apply the migration from the
# start up to the version 5.
migrate reset --yes # reset the database without ask anything. Be careful!!Note on reset: You can disable the reset command by setting the environment variable
MIGRATE_DISABLE_RESET to true:
export MIGRATE_DISABLE_RESET=true- Sqlite
- Mysql / MariaDB
- Postgres
- SqlServer
composer require 'byjg/migration=2.0.*'
composer global require 'byjg/migration=2.0.*'
sudo ln -s $HOME/.composer/vendor/bin/migrate /usr/local/binThis library has integrated tests and need to be setup for each database you want to test.
Basiclly you have the follow tests:
phpunit tests/SqliteDatabaseTest.php
phpunit tests/MysqlDatabaseTest.php
phpunit tests/PostgresDatabaseTest.php
phpunit tests/SqlServerDatabaseTest.php