NAME

DBIx::Schema::Migration - An easy way to start using migrations.

VERSION

version 1.01

SYNOPSIS

use DBI; # it is assumed that you are using DBI to handle your database connections
use DBIx::Schema::Migration;

my $driver   = "SQLite"; # just example, you can use mysql, postgres e.t.c
my $database = "test.db";
my $dsn      = "DBI:$driver:dbname=$database";
my $userid   = "";
my $password = "";

my $dbh =
    DBI->connect($dsn, $userid, $password, { RaiseError => 1 }) 
    or die $DBI::errstr;

$migration = DBIx::Schema::Migration->new( {
    dbh => $dbh, # connection handler
    dir => 'db/migrations', # path to directory with migrations
} );

$migration->init(); # create applied_migrations table

$migration->up(1);   # run 1 migration
$migration->up();    # run all migrations

$migration->down(1); # rollback 1 migration
$migration->down();  # rollback all migrations

DESCRIPTION

Schema migration is a tool that helps to apply incremental and reversible changes to you relational database. You could build cli util or use this class whintin yor ci/cd script. This module relies on DBI and the database connection it creates. It only works with migrations written in sql.

Migration in terms of this module is a directory consisting of two files:

1 *_up.sql   - apply changes
2 *_down.sql - reverse changes

Example:

 db/migrations/
|
|-- 01_create_table_users/
|     \
|      \-- 01_create_table_users_up.sql
|      |
|      |-- 01_create_table_users_down.sql
|
|-- 02_add_users/
|     \
|      \-- 02_add_users_up.sql
|      |
|      |-- 02_add_users_down.sql

As you can see, there are some naming conventions:

1 Migrations starts with a number that determines the order 
2 Migrations ends with up.sql or down.sql 

Once a migration has been applied, it is stored in the database, and the migration will not be applied again, until your reverse it with down method.

DANCER2

The main idea behind this module is the creation of simple commands for the cli, as well as easy integration with large applications.

It can be used with dancer easily, just create migration.pl in your app root and put this code inside:

use Dancer2;
use Dancer2::Plugin::Database;
use DBIx::Schema::Migration;

my $dir = config->{migrations}->{directory};

# Instantinating Migration object.
my $migration = DBIx::Schema::Migration->new( {
    dbh => database,
    dir => $dir,
} );

# CLI logic.
my ($action, $num) = @ARGV;

SWITCH: for ($action) { 
    if (/up/)   { $migration->up($num);   last SWITCH; }
    if (/down/) { $migration->down($num); last SWITCH; }
    if (/init/) { $migration->init();     last SWITCH; }
}

Now you can run migrations simply:

perl migration.pl up
perl migration.pl down
perl migration.pl init

CONFIGURATION

dbh

Database handler.

dir

Path to migrations directory, full or relative.

METHODS

up($num)

Will apply $num migrations or all of them if $num is not specified.

down($num)

Will reverse $num migrations or all of them if $num is not specified.

BUGS AND LIMITATIONS

If you find one, please let me know.

SOURCE CODE REPOSITORY

https://github.com/AlexP007/DBIx-Schema-Migration - fork or add pr.

AUTHOR

Alexander Panteleev <alexpan at cpan dot org>.

LICENSE AND COPYRIGHT

This software is copyright (c) 2021 by Alexander Panteleev.

This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.