Database migrations and how to use them

Migrations, the superhero your database deserves

Migrations ease the process of working together on projects, as well as deploying the database changes.

A newly released package for the DotKernel stack integrates migrations and seeders into the application; This is all done via the newly introduced “php dot” command that’s available in the DotKernel stack.

This article assumes that you’re familiar with the basic concepts of migrations, if you’re not, there’s an excellent article available here

To recap the article above, a database migration in a change in the database schema.

Benefits

By using migrations and seeders, you ensure a better workflow for teams, as changes to the database no longer require convoluted messages. It also ensures that all team members use the same types with the same limits on each column, and someone doesn’t have VARCHAR(50) while another has VARCHAR(150). It keeps the database synchronised throughout the team with minimal effort.

When pushing to production, the deployment script will automatically run the migrations and keep the database schema in sync with the codebase.

It requires no manual SSH’ing into production servers and no risky manual changes to production databases.

Getting started

To setup migrations, simply follow the installation instructions in the package provided for DotKernel.
After the package has been successfully setup, you’re ready to go.
To see the available commands, simply write “php dot” in a console that’s located in your project root.

How to use migrations in my project?

Migrations are very easily adapted to any project.

• Existing projects should start by creating a new migration, and mirror the current database design in that file.
  • Backup the database. in case anything unforeseen should happen.
  • After the design has been mirrored, the existing tables should be deleted, and the migrations can be run.

1. Whenever you would normally manually touch the database, make a migration for doing it instead. (php dot make:migration <name>)
2. Give the migrations reasonable names, so it’s easy to see their action, and the DB progression, in the project tree.
3. Run the migrate command, and watch the database automatically generate and run the SQL to update the tables and columns as configured.
4. Enjoy a safe and worry free database that won’t suffer from manually touching it.

Notice; Migrations are stored in version control, so whenever you pull an update, quickly run php dot migrate, as it will only ever migrate new files, and leave out the ones that have been run already.

Seeders

If you need to add some data to a table, e.g. adding a default admin profile on a fresh installation, this can be done via Seeders.

Migrations manage the database layouts, seeders add data to the database.

Seeders can be configured to run every time a certain migration is run, so no matter when you or how you reset your database, it’ll make sure that there’s always a default admin profile when the CreateAdminTableMigration is run.

Seeders are created the same way as migrations, and also benefits hugely from being given reasonable names that explain what they do.

1. Create the required seeders, one for each table is the best practice, so you can have a “UserTableSeeder” and a “CarTableSeeder” etc.
2. Setup when these seeders should run;
   • When running a migration
   • Via a console-command, this is used to seed x amount of rows of (random) data to the database for testing.