Help language development. Donate to The Perl Foundation
Database migrations are an ordered, append-only list of database change operations that together bring the database up to a current schema. A table in the database is used to track which migrations have been applied so far, so that the database can be brought up to date by applying the latest migrations.
This module allows one to specify database migrations using a Raku DSL. The migrations are checked in various ways for correctness (for example, trying to drop a table that never existed, or adding duplicate columns), and are then translated into SQL and applied to the database.
If one is using a Raku ORM such as Red, it is probably worth looking into how it might assist with migrations. This module is more aimed at those writing their queries in SQL, perhaps using something like Badger to have those SQL queries neatly wrapped up in Raku subs and thus avoid inline SQL.
Warning: The module should currently be considered as a BETA-quality minimum viable product. Of note, only Postgres support is currently available, migrations can only be applied in the "up" direction, and various quite kinds of database change are not yet implemented.
Migrations can be written in a single file or spread over multiple files in a
single directory, where the filenames will be used as the ordering. For now
we'll assume there is a single file migrations.raku where the migrations
will be written one after the other.
A migration file with a single migration looks like this:
use DB::Migration::Declare;
migration 'Setup', {
create-table 'skyscrapers', {
add-column 'id', integer(), :increments, :primary;
add-column 'name', text(), :!null, :unique;
add-column 'height', integer(), :!null;
}
}
Future changes to the database are specified by writing another migration at the end of the file. For example, after adding another migration the file overall could look as follows:
use DB::Migration::Declare;
migration 'Setup', {
create-table 'skyscrapers', {
add-column 'id', integer(), :increments, :primary;
add-column 'name', text(), :!null, :unique;
add-column 'height', integer(), :!null;
}
}
migration 'Add countries', {
create-table 'countries', {
add-column 'id', integer(), :increments, :primary;
add-column 'name', varchar(255), :!null, :unique;
}
alter-table 'skyscrapers',{
add-column 'country', integer();
foreign-key table => 'countries', from => 'country', to => 'id';
}
}
When a project has migrations, it is wise to write a test case to check that
the list of migrations are well-formed. This following can be placed in a
t/migrations.rakutest:
use DB::Migration::Declare::Database::Postgres;
use DB::Migration::Declare::Test;
use Test;
check-migrations
source => $*PROGRAM.parent.parent.add('migrations.raku'),
database => DB::Migration::Declare::Database::Postgres.new;
done-testing;
Which will produce the output:
ok 1 - Setup ok 2 - Add countries 1..2
If we were to introduce an error into the migration:
alter-table 'skyskrapers',{
add-column 'country', integer();
foreign-key table => 'countries', from => 'country', to => 'id';
}
The test would fail:
ok 1 - Setup not ok 2 - Add countries # Failed test 'Add countries' # Migration at migrations.raku:11 has problems: # Cannot alter non-existent table 'skyskrapers' 1..2 # You failed 1 test of 2
With diagnostics indicating what is wrong. (If following this getting started guide like a tutorial, undo the change introducing an error before continuing!)
To migrate a database to the latest version, assuming we are placing this in
a service.raku script, do this:
use DB::Migration::Declare::Applicator;
use DB::Migration::Declare::Database::Postgres;
use DB::Pg;
my $conn = $pg.new(:conninfo('...write your connection string here...'));
my $applicator = DB::Migration::Declare::Applicator.new:
schema-id => 'my-project',
source => $*PROGRAM.parent.add('migrations.raku'),
database => DB::Migration::Declare::Database::Postgres.new,
connection => $conn;
my $status = $applicator.to-latest;
note "Applied $status.migrations.elems() migration(s)";
Depending on your situation, you might have this as a distinct script, or place it in the startup script for a Cro service to run the migrations upon startup.
Top-level operations supported within a migration are:
create-table(Str $name, &steps)alter-table(Str $name, &steps)drop-table(Str $name)execute(SQLLiteral :$up!, SQLLiteral :$down!)Within both create-table and alter-table one can use:
add-column(Str $name, $type, Bool :$increments, Bool :$null, Any :$default,
Bool :$primary, Bool :$unique)primary-key(*@column-names)unique-key(*@column-names)foreign-key(Str :$from!, Str :$table!, Str :$to = $from, Bool :$restrict = False,
Bool :$cascade = False)foreign-key(:@from!, Str :$table!, :@to = @from, Bool :$restrict = False,
Bool :$cascade = False)Only within alter-table one can use:
drop-column(Str $name)Column types are specified using any of the following functions:
char(Int $length)varchar(Int $length)text()boolean()integer(Int $bytes = 4) (only 2, 4, and 8 are reliably supported)date()timestamp(Bool :$timezone = False) (a date/time)arr($type, *@dimensions) (dimensions are integers for fixed size of *
for variable size; specifying no dimensions results in a variable-length
single dimensional array)type(Str $name, Bool :$checked = True) (any other type, checked by the
database backend against a known type list by default, but trusted and
passed along regardless if :!checked)SQL literals can be constructed either:
sql(Str $sql)sql(*%options) (where the named argument names are database
IDs, such as postgres, and the argument value is the SQL) now() (becomes the Right Thing depending on database and
column type when used as the default value of a date or timestamp column)