Skip site navigation (1)Skip section navigation (2)

FreeBSD Manual Pages


home | help

       DBIx::Class::Migration::Tutorial::FirstMigration	- Prepare your first

       In this section you will	use DBIx::Class::Migration to prepare
       migration files for version 1.  You will	also install a test database,
       create some 'seed' data and dump	some fixtures.

       In preparation for this section,	you might wish to revisit the
       documentation for DBIx::Class::DeploymentHandler	and in particular

Using the dbic-migration commandline tool
       From your project home directory	(that contains the "dist.ini" file)
       run the "dbic-migration"	command	line tool:

	   dbic-migration version --schema_class MusicBase::Schema -Ilib

       This should return (something similar to):

	   Application version is 0.025

       Let's do	a quick	review.	 "dbic-migration" is your main gateway to
       managing	your migrations.  When using the tool you will give it one
       command (such as	"version" above) and any number	of option flags
       (starting with "--" or "-").

       NOTE: The version reported might	be different from the one mentioned in
       the above documentation.	 If you	have a much older (or newer) version,
       please note this	tutorial was written against the one mentioned,	and
       although	I will strive for backward compatibility feature sets might

       When using the tool, you'll need	to specify the "schema_class" that you
       are creating and	using migrations for.  Typically this will be your
       subclass	of DBIx::Class::Schema,	and you'll need	to extend the Perl
       module search path with "I" as above.  This is so that "perl" will know
       about your custom application libraries.

       If you are going	to be working with one schema for a bit, you can
       export "DBIC_MIGRATION_SCHEMA_CLASS" into your current shell, that way
       you don't need to keep retyping it.  If your shell is "bash" you	can do
       this with the following command:

	   export DBIC_MIGRATION_SCHEMA_CLASS=MusicBase::Schema

       Then you	can simply do:

	   dbic-migration -Ilib	version

       For the remainder of the	tutorial, I will assume	your Schema Class has
       been exported.  Remember, you can always	specific with the
       "--schema_class"	option flag.

       Before we move on, let's	see the	status of your schema and database:

	   dbic-migration -Ilib	status

       This should return an error similar to the following:

	   Failed to find share	dir for	dist 'MusicBase-Schema'	at ....

       Why did this happen?  In	order to use DBIx::Class::Migration you	need
       to tell it where	to put the migration files.  You'd use the
       "--target_dir" option flag to do	this, but if you don't provide a
       value, it will automatically assume you have a "share" directory	in the
       home directory of you application and wish to put files there.  This is
       a good, accepted	community practice for storing non code	data for your
       project and I recommend you follow it.  Let's create the	share
       directory and try again:

	   mkdir share
	   dbic-migration -Ilib	status

       You should now get:

	   Schema is 1
	   Database is not currently installed

       Great!  Now you have the	basics of using	the commandline	tool!

Prepare	migration files
       Let's create some migrations for	Version	1 of your Schema.

	   dbic-migration -Ilib	prepare

       You should see:

	   There is no current database	deployed, so I can't prepare upgrades
	   or downgrades

       NOTE: If	you are	getting	some wild debugging messages, please see
       DBIx::Class::Migration::FAQ for details.

       Since this is the first version,	we won't create	any upgrade or
       downgrade migrations.  Okay, lets see what we now have:

       In your "share" directory you now have the following:

	       /_source	 (There's stuff	in here, but we	won't peek!)

       So let's	review.	 We created a default fixture configuration that just
       serializes all the database information.	 This is probably not great
       for the long term but until you get the hang of creating	custom fixture
       configurations (and for this you	need to	review DBIx::Class::Fixtures)
       it will serve.  In any case you can take	a quick	peek to	get the	idea:


	      "sets" : [
		    "quantity" : "all",
		    "class" : "Cd"
		    "quantity" : "all",
		    "class" : "Track"
		    "quantity" : "all",
		    "class" : "Artist"
	      "might_have" : {
		 "fetch" : 0
	      "belongs_to" : {
		 "fetch" : 0
	      "has_many" : {
		 "fetch" : 0

       DBIx::Class::Fixtures uses JSON for its configuration.  In this case
       you can note that we are	just dumping all the rows in all the tables.
       You will	see that each time you prepare a version, we always build a
       fresh "all_tables.json" for you to use as a default (in other words,
       don't change this one :)	).

       You should also note that the path to your fixtures and your migrations
       contain the schema version number you have prepared.  You'll see	later
       that as you add more schema versions this becomes your primary way of
       managing	all the	directories.

       Three other files of interest have been created.	 The first is
       "001-auto-_VERSION.sql" which is	the DDL	(data description language)
       for the default database	(SQLite) to create the meta table that
       DBIx::Class::DeploymentHandler uses to keep track of the	version
       history for your	deployments.  We also create a full DDL	for the	tables
       that make up your application.  In this case we have one	table for each
       of the Artist, CD and Track Result classes.


	   -- Table: artist
	   CREATE TABLE	artist (
	     name varchar(96) NOT NULL
	   -- Table: cd
	     artist_fk integer NOT NULL,
	     title varchar(96) NOT NULL,
	     FOREIGN KEY(artist_fk) REFERENCES artist(artist_id)
	   CREATE INDEX	cd_idx_artist_fk ON cd (artist_fk);
	   -- Table: track
	   CREATE TABLE	track (
	     cd_fk integer NOT NULL,
	     title varchar(96) NOT NULL,
	     FOREIGN KEY(cd_fk)	REFERENCES cd(cd_id)
	   CREATE INDEX	track_idx_cd_fk	ON track (cd_fk);

       You should review this DDL to make sure it properly reflects your

       DBIx::Class::DeploymentHandler will build full DDL for each of the
       databases you are creating migrations for.  We feel this	is the best
       approach	since it lets you take maximum advantage of your target
       database.  By default, if you don't specify a database (using the
       "database" option flag -	only needed if you aren't doing	the "default"
       thing; DBIx::Class::Migration will figure out the right thing to	do in
       nearly all circumstances) we build migrations for SQLite, since that is
       easy to use and test, but you can always	build any of the supported
       databases. For example if you ran the following:

	   dbic-migration -Ilib	prepare	--database MySQL --database SQLite

       We'd build migrations for both MySQL and	SQLite.	We'll try that later
       on, for now let's stick to SQLite, since	that is	very low effort	and
       you have	plenty to learn	already!

       There's one more	file we've created "musicbase-schema.db" which is an
       empty SQLite database you can use for testing your migrations or	for

       You've now completed creating your first	migration!

Using a	source control repository
       If you are using	a source control repository, like "git", you probably
       want to ignore checking in the Sqlite database file.  Ideally a new
       developer that checks out the project should just install the database
       to the current version rather than try to piggyback on your database.
       This allows for better developer	level isolation.

       In "git"	you can	add or modify ".gitignore" in your project directory:


       This will ignore	both SQLite databases and any Mysql or Postgresql
       sandboxes you might create (as we will in a later section of the

Customizing the	Migration
       You have	a database migration, but without any initial data it would be
       hard to use.  Let's add some code to prepopulate	the database with a
       few musicians and cds.  That way	when we	install	the database we	can
       run some	SQL on it and play with	it.  Generally when you	are developing
       you are going to	need to	setup the database with	some useful data in
       order to	be able	to do some work.  Lets do that now.  Perform the
       following commands in your shell:

	   mkdir share/migrations/_common
	   mkdir share/migrations/_common/deploy
	   mkdir share/migrations/_common/deploy/1
	   touch share/migrations/_common/deploy/1/

       Then open the file "share/migrations/_common/deploy/1/" in
       your editor of choice and add the following code:

	   use strict;
	   use warnings;
	   use DBIx::Class::Migration::RunScript;

	   migrate {

	     my	$artist_rs = shift

	       name =>'Michael Jackson',
	       cds => [
		 { title => 'Thriller',	tracks => [
		   { title => 'Beat It'	},
		   { title => 'Billie Jean' }],
		 { title => 'Bad', tracks => [
		   { title => 'Dirty Diana' },
		   { title => 'Smooth Criminal'},
		   { title => 'Leave Me	Alone' }],

	       name =>'Eminem',
	       cds => [
		 { title => 'The Marshall Mathers LP', tracks => [
		   { title => 'Stan' },
		   { title => 'The Way I Am' }],


       If you read the DBIx::Class::DeploymentHandler docs, you	know that in
       addition	to running SQL files natively against your database of choice,
       you can also create Perl	run scripts, which is a	Perl file that returns
       an anonymous subroutine (similar	to Plack).  That subroutine should
       expect to get one argument from the deployment handler, which is	a
       schema object upon which	you can	run DBIx::Class	commands.

       IMPORTANT: The schema that is passed to your subroutine reference is
       one that	we autogenerate	using DBIx::Class::Schema::Loader.  It is not
       the same	as your	application subclass of	DBIx::Class::Schema (for us
       that would be MusicBase::Schema).  Since	your schema is going to	be in
       flux, we	can't rely on it for creating Perl run files.  Because of
       this, the names of the relationships will reflect those that
       DBIx::Class::Schema::Loader generates as	part of	its introspection of
       the database.  If you get confused and can't figure out the generated
       schema, you can always dump it with the "make_schema" command.

       Why use Perl instead of SQL?  You should	use what makes sense to	you
       and what	you are	comfortable with.  I use Perl when I can since I can
       put that	in the "_common" directory and use it for all the database I
       create migrations for.  That saves me a bit of repeating	myself,	but
       adds a bit of complexity	to understanding.

       You should also notice that the file we created starts with '002'.
       This ensures that it will run after the '001' file (in this case	we run
       "001-auto.sql" first.

       Awesome,	you now	have customized	your first migration.  Now we can
       install it!

   Installing the migration
       Installing the migration	is straightforward:

	   dbic-migration -Ilib	install

       Lets take a peek	at the database	and make sure we got that demo data:

	   sqlite3 share/musicbase-schema.db

       This should give	us the SQLite shell, something like this:

	   SQLite version 3.7.5
	   Enter ".help" for instructions
	   Enter SQL statements	terminated with	a ";"

       Enter this command


       You should see that your	tables have been created:

	   artist		dbix_class_deploymenthandler_versions
	   cd			track

       Let's check the artist table.  We'd expect a few	because	of the
       script we wrote:

	   select * from artist;

       You should get:

	   1|Michael Jackson

       You've just verified your installation worked!  Exit the	SQLite shell
       with the	".q" command.

   Make	your first fixtures and	test them.
       Next, lets dump some fixtures, that way you can mess around with	the
       database	data as	much as	you'd like and then get	back to	a good,	known
       state at	any time:

	   dbic-migration -Ilib	dump_all_sets

       This will read each of the fixture configuration	you've prepared, and
       serialize them to "/share/fixtures/1".  Since we	just have the
       "all_tables" fixture configuration, we'd	only expect to see those.
       Look at the directory structure under share now:


       For each	fixture	configuration you've created, you will get a directory
       and serialized data.  Since this	is the "all_tables" set, this
       represents all the data in your database	at the time your ran

       Let's test the fixtures.	 Pretend you've	been developing	on this
       database	for a while and	you got	a bunch	of messy data around that you
       no longer need. Lets clear out all the tables:

	   dbic-migration -Ilib	delete_table_rows

       You should take care with this, and make	sure you are not pointing to a
       database	you care about (such as	Production) since this command loops
       through all your	tables and issues a "delete".  If you have a lot of
       data, this could	take a bit of time.

       Now you have tables but no data.	 Lets restore the "all_tables"
       fixtures	that you previously dumped:

	   dbic-migration -Ilib	populate

       Since we	only have one fixture configuration, we	can skip the step of
       specifying which	fixtures to load (you'd	use the	flag "--fixture_set"
       to do that and you can list as many as you wish).  You automatically
       restore the "all_tables"	set if you don't name one.  You'd expect to
       see some	output like this:

	   Reading configurations from .../share/fixtures/1/conf
	   Restored set	all_tables to database

       Let's peek into the database and	check:

	   sqlite3 share/musicbase-schema.db

       and see if we have some tracks:

	   sqlite> select * from track;
	   1|3|The Way I Am
	   3|1|Billie Jean
	   4|2|Leave Me	Alone
	   5|2|Smooth Criminal
	   6|1|Beat It
	   7|2|Dirty Diana

       Perfect,	you just restored your database	to a given fixture dump!

       You've just learned how to use the basics of the	commandline
       "dbic-migration"	to prepare and install migrations.  You	also learned
       some basic customizing of your migrations and you dumped	and restore
       some fixtures.

       At this point I'd say you have the minimum setup	for being able to do
       real database development.

       Proceed to DBIx::Class::Migration::Tutorial::SecondMigration.

       See DBIx::Class::Migration for author information

       See DBIx::Class::Migration for copyright	and license information

perl v5.32.1		   DBIx::Class::Migration::Tutorial::FirstMigration(3)

NAME | GOAL | Using the dbic-migration commandline tool | Prepare migration files | Using a source control repository | Customizing the Migration | SUMMARY | NEXT STEPS | AUTHOR | COPYRIGHT & LICENSE

Want to link to this manual page? Use this URL:

home | help