DBIx::C...igration(3)

✨ DBIx::Class::Migration::Tutorial::SecondMigration ✨

the one where we level up the database — straight outta the User Contributed Perl Documentation for DBIx::C...igration(3), bestie 🫶

---

💖 NAME 💖

DBIx::Class::Migration::Tutorial::SecondMigration — Upgrade the Database 🎀

---

🎯 GOAL 🎯

Okay so in this section you're gonna change your database and learn how to create upgrade files. You'll also be making some custom fixture configurations and learning all about creating seed data. It's giving "glow up arc" and honestly? Obsessed. 💅

---

🌈 Changing Requirements 🌈

So far your MusicBase app has been doing fine, no notes, thriving, living its truth. But then some new requirements come down from on high. 😭 In addition to storing the Artist's name, we now wanna store some geographical information — in this case a Country that can be associated with the Artist.

Right now we're literally only gonna care about Artists that can be located in one of these three Countries:

Canada
Mexico
USA

We'll need to seed that data to the database and also track it via some custom fixture configurations. That way, if you need to start a new database from go, you can get both the table structure and the essential running information. (In this case, the list of countries we care about could be considered system / domain data, not transactional data.) Let's do it! 🔥

---

🦋 Change the Schema 🦋

We're gonna need to add a new table and then link that table to Artist. From the project home directory — the one containing the dist.ini file — execute the following command in your terminal shell:

touch lib/MusicBase/Schema/Result/Country.pm

Then open this file in your text editor of choice and add the following code:

package MusicBase::Schema::Result::Country;

use strict;
use warnings;

use base 'DBIx::Class::Core';

__PACKAGE__->table('country');

__PACKAGE__->add_columns(
  'country_id' => {
    data_type => 'integer',
  },
  'name' => {
    data_type => 'varchar',
    size => '96',
  });

__PACKAGE__->set_primary_key('country_id');
__PACKAGE__->add_unique_constraint(['name']);
__PACKAGE__->has_many(
  'artist_rs' => "MusicBase::Schema::Result::Artist",
  {'foreign.country_fk'=>'self.country_id'});

1;

This will establish a new Result class with a one to many relationship to the Artist Result class. You probably noticed that we're referencing a column in the Artist Result class that doesn't exist yet 👀 — yeah. Let's add that as well.

Open lib/MusicBase/Schema/Result/Artist.pm in your editor and change it to look like this:

package MusicBase::Schema::Result::Artist;

use strict;
use warnings;

use base 'DBIx::Class::Core';

__PACKAGE__->table('artist');

__PACKAGE__->add_columns(
  artist_id => {
    data_type => 'integer',
  },
  country_fk => {
    data_type => 'integer',
  },
  name => {
    data_type => 'varchar',
    size => '96',
  });

__PACKAGE__->set_primary_key('artist_id');

__PACKAGE__->belongs_to(
  'has_country' => 'MusicBase::Schema::Result::Country',
  {'foreign.country_id'=>'self.country_fk'});

__PACKAGE__->has_many(
  'cd_rs' => 'MusicBase::Schema::Result::Cd',
  {'foreign.artist_fk'=>'self.artist_id'});

1;

So we added the foreign key field country_fk and we added the other end of our new relationship to Country — it's called has_country. 💫

Great, we've altered our schema to fit the new requirement. Let's up the $VERSION and commit if you're using a repository. 🎀

Alter file lib/MusicBase/Schema.pm:

package MusicBase::Schema;

use strict;
use warnings;

use base 'DBIx::Class::Schema';

our $VERSION = 2;

__PACKAGE__->load_namespaces;

1;

So we just change $VERSION from 1 to 2 — which is, like, literally how the default configuration of DBIx::Class::DeploymentHandler works. No cap. ✨

🎀 Prepare the new migration 🎀

Okay bestie, so now we've altered the schema and bumped the version, which means it's time to bust out the prepare command and make those version 2 files. But first — vibe check on our system status:

dbic-migration -Ilib status

and it should hit you back with

Schema is 2
Deployed database is 1

Iconic, that's exactly right. Now let's get the database caught up. First, prepare the files:

dbic-migration -Ilib prepare

That's gonna generate a whole lil pile of new files. Let's peek at the directory structure now:

/share
  /fixtures
    /1
      (Same as before)
    /2
      /conf
        all_tables.json
  /migrations
    /_common
      (Same as before)
    /_source
      (system files for DBIC::DeploymentHandler.  Leave alone!)
    /SQLite
      /deploy
        /1
          (Same as before)
        /2
          001-auto-_VERSION.sql
          001-auto.sql
      /downgrade
        /2-1
          001-auto.sql
      /upgrade
        /1-2
          001-auto.sql
  musicbase-schema.db

Okay so yeah, we got a bunch more files, no cap. You can see we've created a new all_tables.json fixture configuration, and if you peek inside it, it's been updated to include your shiny new Country Result class. ✨

Also you'll spot a brand new version 2 directory under deploy containing the full DDL for your new schema, plus the special metadata table that DBIx::Class::DeploymentHandler uses to manage deployment history. Take a quick look inside those too — you'll see the new country table has been added. 🫶

So far this is all giving very much version 1 energy. What's different is you've now got the new downgrade and upgrade directories. For simplicity I will not deal with downgrades for this section of the tutorial — we're focusing on the upgrade path, periodt.

Let's take a closer look at /upgrade:

/upgrade
  /1-2
    001-auto.sql

Since our schema was at version 2 and the database was at version 1, we created a 1-to-2 upgrade path, which will let us get the database in sync with our schema. DBIx::Class::DeploymentHandler will introspect your schema and database using SQL::Translator and try to suggest some DDL for this. You should treat this initial 001-auto.sql file as a suggestion and as a guide. You will need to make changes to it based on your data change needs (SQL::Translator knows about your table structure, but not your data) as well as your performance and uptime needs. For example, when changing a table that has 1 million rows you might need to take an alternative approach than what is suggested. 😭

This is the part of the job where you need to exercise the most planning and good judgment, and you might need to involve your DBA to help you craft a change set that can work for your particular needs. In this case we have a very small database using sqlite, so our needs are similarly small — however that should not excuse us from planning! 💅

Let's look at the proposed DDL change. In the editor of your choice, open share/migrations/SQLite/upgrade/1-2/001-auto.sql

BEGIN;

CREATE TABLE country (
  country_id INTEGER PRIMARY KEY NOT NULL,
  name varchar(96) NOT NULL
);

CREATE UNIQUE INDEX country_name ON country (name);

ALTER TABLE artist ADD COLUMN country_fk integer NOT NULL;

CREATE INDEX artist_idx_country_fk ON artist (country_fk);

COMMIT;

So the first part of this that adds the country table is pretty straightforward. Let's break that part out into its own upgrade step. Generally, even if the DDL change proposed is perfect, I prefer to move the code to a file name other than the default 001-auto.sql — because if I need to prepare the upgrade several times (as you might if you're building a new version and realize you made a mistake and need to re-prepare it, as we'll see in a later step), each time you do it will overwrite that file, blowing away any customization you made. 💀 So let's bust out the first part:

touch share/migrations/SQLite/upgrade/1-2/001-add_country.sql

And then open it in your text editor and add the following (copied from the 001-auto.sql file):

BEGIN;

;
CREATE TABLE country (
  country_id INTEGER PRIMARY KEY NOT NULL,
  name varchar(96) NOT NULL
);

CREATE UNIQUE INDEX country_name ON country (name);

;
COMMIT;

Great, now we have a step that makes a table. 🎀

Next we need to add the list of default countries. For this we'll use a Perl script similar to the one we did for version 1. You could do this in SQL if you wanted — it's just that if I can use a Perl script I'd prefer that, since it's more portable across other databases (and eventually you will need something other than SQLite for production). Let's add that now:

mkdir share/migrations/_common/upgrade
mkdir share/migrations/_common/upgrade/1-2
touch share/migrations/_common/upgrade/1-2/002-insert_countries.pl

We're putting this in the _common migrations directory so that later on, if we add another database (such as MySQL), we'd be able to have it run for both. Since Perl migration run files are going to be database agnostic, it makes sense to do this. You could have just as easily created a file:

## example, you don't need to do this!
touch share/migrations/SQLite/upgrade/1-2/002-insert_countries.pl

And that would have made a script that would only run on SQLite installs.

Let's edit our Perl run file:

share/migrations/_common/upgrade/1-2/002-insert_countries.pl

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

migrate {
  shift->schema
    ->resultset('Country')
    ->populate([
      ['name'],
      ['Canada'],
      ['Mexico'],
      ['USA'],
    ]);
};

Since the populate method uses bulk insertion, it's generally my favored way to insert rows for migrations. It's going to be much faster than doing separate inserts. In this case we don't have a lot of data, so it didn't make a big difference — it's just that later on, if your data needs are larger, it would have an impact. 🔥

Okay, so let's look at the remaining part of the 001-auto.sql file that we haven't examined yet:

ALTER TABLE artist ADD COLUMN country_fk integer NOT NULL;

CREATE INDEX artist_idx_country_fk ON artist (country_fk);

So that only does part of the job. First of all, we can't really add a NOT NULL column without default values, since that's going to be a constraint. Also the foreign key is missing. That's because it seems SQLite doesn't let you alter a table to add that kind of constraint. Let's look at the DDL for the new artist table from the full DDL in share/migrations/SQLite/deploy/2/001-auto.sql:

CREATE TABLE artist (
  artist_id INTEGER PRIMARY KEY NOT NULL,
  country_fk integer NOT NULL,
  name varchar(96) NOT NULL,
  FOREIGN KEY(country_fk) REFERENCES country(country_id)
);
CREATE INDEX artist_idx_country_fk ON artist (country_fk);

Quite often when pondering a DDL change, it's valuable to glance at the full DDL that we give you in the deploy directory, so you can understand better what the target is. In this case it's really clear the diff is only getting you partway there. 🙈

Alright, let's try to fix it. Let's create a file to hold our code:

touch share/migrations/SQLite/upgrade/1-2/003-change_artist.sql

So this is going to be the third step for this upgrade (the second one was to add the country rows we need). Open the file in your text editor and enter:

BEGIN;

CREATE TEMPORARY TABLE artist_temp_alter (
  artist_id INTEGER PRIMARY KEY NOT NULL,
  name varchar(96) NOT NULL
);

INSERT INTO artist_temp_alter SELECT artist_id, name FROM artist;

DROP TABLE artist;

CREATE TABLE artist (
  artist_id INTEGER PRIMARY KEY NOT NULL,
  country_fk integer NOT NULL DEFAULT 1,
  name varchar(96) NOT NULL,
  FOREIGN KEY(country_fk) REFERENCES country(country_id)
);

CREATE INDEX artist_idx_country_fk ON artist (country_fk);

INSERT INTO artist SELECT artist_id, 1, name FROM artist_temp_alter;

DROP TABLE artist_temp_alter;

COMMIT;

So this was my personal best shot, but of course there's going to be more than one way to do it. I decided it would be best to create a temporary table to hold the current artist info, build a new artist table with the correct FK constraint and new column, and then copy back from the temp table. Now, if I had a million rows this wouldn't work out very well — but with a database that big you'd probably have moved on to something that would actually allow you to add an FK in an alter statement. So I'll go with this since it does the job. 💁

Notice that when I copy back to the new artist table from the temporary table, I make all the artists live in the country matching country_id = 1 (Canada). I'll leave it this way for the section of the tutorial, just to keep it simple — but in reality you would probably need to copy things more carefully, or maybe add an "UNKNOWN" country option to the list of countries. We'll do an example of something like that in a later section. I just want to point out that when you craft your migration code you need to interpret the suggested DDL change and keep in mind how your data is put together. That way you can craft a good change set that keeps your database well constrained and organized. 🦋

Now that you're done converting the suggested DDL change, you should delete the generated auto file — otherwise when later you run the migration, you'll end up doing more changes and get some errors.

rm share/migrations/SQLite/upgrade/1-2/001-auto.sql

Okay, so now you have a good set of migrations to move from version 1 to 2. Let's try that out now. ⭐

🚀 Upgrade the database

Okay so once you have good migrations, it’s time to upgrade, bestie. 💅 Typically I first make sure the database is in a good known state:

dbic-migration -Ilib drop_tables

⚠️ DANGER, BESTIE: that will blow away your tables, so be careful when using this command! No cap. 😭 It’s giving “iconic but destructive.”

You should see:

Dropping table cd
Dropping table track
Dropping table artist
Dropping table dbix_class_deploymenthandler_versions

Great, now the database is clear. ✨ Let’s install version 1:

dbic-migration -Ilib install --to_version 1

Since the schema is version 2 we need to explicitly mention the target version we are deploying. Otherwise DBIx::Class::Migration will want to deploy a version that matches the current schema. We want to do an upgrade, not an install, since we need to modify both the tables AND our data, then we can build some good new fixtures. 👑

Let’s do the upgrade:

dbic-migration -Ilib upgrade
dbic-migration -Ilib status

That should give you:

Schema is 2
Deployed database is 2

Vibe check: passed. 💖 Let’s peek in the database and see if everything is good:

sqlite3 share/musicbase-schema.db
sqlite> .tables

Should give you:

artist                  dbix_class_deploymenthandler_versions
cd                      track
country

So there’s our new table. Main character energy! 👑 Let’s make sure our data is correct:

sqlite> select * from country;
1|Canada
2|Mexico
3|USA

sqlite> select * from artist;
1|1|Michael Jackson
2|1|Eminem

So looks like our migration worked out. 😌 Later on we’ll learn how to write some test cases for our database, and I would highly suggest that you write some tests that make sure your migration worked as expected, rather than doing the manual inspect as above. fr fr, iykyk.

You are done with the upgrade, lets move on to make new fixtures. Exit the sqlite shell:

sqlite> .q

---

🧚 Customize Fixtures and Serializing Fixtures

When you “prepared” the new version, in addition to creating the database structure migrations, we also got a new version 2 directory for the fixtures. 🎀

/share
  /fixtures
    /1
      (Same as before)
    /2
      /conf
        all_tables.json

By default we build you a fresh all_tables.json that should reflect any new or removed tables. Additionally, if there were any custom fixture configurations, we would have copied those from the version 1. We didn’t have any custom files so nothing was copied. Lowkey clean. ✨

Lets make a custom fixture config now:

touch share/fixtures/2/conf/countries.json

We will make a fixture configuration to dump all the countries. 🌍 Since the list of countries is part of your domain data (and not transaction data) you should create fixtures for it so that when someone is setting up a new version 2 database they can install a version that has both the structure and the data needed to have a functioning database. Data that is part of the domain we call ‘seed’ data. You’ll need to create fixtures for all types of seed data, such as country lists, roles, product names, etc. 🌱

So you can use fixtures for a variety of jobs, from demo databases you create to show clients, to databases for developers, to fixtures for running tests, and fixtures to preserve necessary domain data. You can then install a database and setup whatever list of fixtures is needed for the job at hand. Additionally since our migrations perform updates on the database data, we can just dump new fixtures after each clean update, and expect to have properly updated fixtures. 🫶

Here’s our fixture configuration for share/fixtures/2/conf/countries.json:

{
   "sets" : [
      {
         "quantity" : "all",
         "class" : "Country"
      }
   ],
   "might_have" : {
      "fetch" : 0
   },
   "belongs_to" : {
      "fetch" : 0
   },
   "has_many" : {
      "fetch" : 0
   }
}

This will dump all the countries, and you will be able to load them later. You could review DBIx::Class::Fixtures for a better understanding of how fixture rule sets are created, but this is basically saying: “Dump everything in the Country source, and don’t follow any relationships from Country or to Country.” 💅

Now that you have the new, custom fixture, lets dump all the sets:

dbic-migration -Ilib dump_all_sets

Please note that if you ‘played’ with the database after doing the update, any new additional data will now become part of your fixtures. This may or may not be desirable. 🙈

Let’s see the list of new fixtures:

/share
  /fixtures
    /1
      (Same as before)
    /2
      /all_tables
        _dumper_version
        /artist
          1.fix
          2.fix
        /cd
          (1-3.fix)
        /country
          (1-3.fix)
        /track
          (1-7.fix)
      /countries
        _dumper_version
        /country
          (1-3.fix)
      /conf
        all_tables.json
        countries.json

So now you have two sets of fixtures, the all_tables and the countries sets! periodt. 🎉

---

✨ SUMMARY ✨

dbic-migration -Ilib status
Schema is 2
Deployed database is 2

You’ve successfully crafted a migration to move your database structure and your data from version 1 to version 2. You’ve also updated your fixtures and created a custom fixture configuration for managing your seed data. You now have a good system where a new developer can walk in and run one or two commands to get a solid working database! Congrats. 👑💖 This migration ate and left no crumbs.

🌈 NEXT STEPS

Proceed to DBIx::Class::Migration::Tutorial::Testing. 🚀

👑 AUTHOR

See DBIx::Class::Migration for author information.

💖 COPYRIGHT & LICENSE

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

💖 And that’s a wrap! This glossy little page is brought to you by perl v5.42.0, dated 2020-06-03, and it’s literally called DBIx::C...igration(3) — stan. 💅✨