DEV Community

loading...
Cover image for Database migrations: the beauty

Database migrations: the beauty

Julien Maury
Practice what you preach. Don't, don't believe the hype!
Originally published at blog.julien-maury.dev ・4 min read

Databases are everywhere. Most programming languages have specific modules to access them.

Databases are handy to store data and run queries. Even if there are great alternative approaches now, such as NoSQL, most of the databases are relational.

It can be challenging to modify the structure (~schema) without your colleagues or co-maintainers dropping their local version and re-downloading the new version.

Fortunately, database migrations allow for doing that relatively quickly and, above all, in a predictable and organized way.

You can see migrations as database versioning.

The PHP universe loves Doctrine

Doctrine is one of the best PHP libraries. It's a database abstraction layer that allows for Object Relational Mapping (ORM).

Major PHP frameworks such as Symfony recommend using this library to handle database operations safely. In Symfony, the installation is simple as that:

 composer require symfony/orm-pack
 composer require --dev symfony/maker-bundle
Enter fullscreen mode Exit fullscreen mode

N.B.: They recommend the maker bundle along with Doctrine because it provides excellent commands to generate code, which speeds up dev.

Source: Symfony - Doctrine

In Doctrine, migrations are complete lists of instructions you usually generate and save in the same git repository. They are helpful to update the structure safely by making sure all your fields are okay and no one gets an error because of an old schema.

With Symfony, after installing the Doctrine Migrations Bundle, you can run something like:

php bin/console doctrine:migrations:generate
Enter fullscreen mode Exit fullscreen mode

It generates a blank migration class with empty methods to customize the code to execute.

Besides, you can use a diff command to generate a migration by comparing your current database to your mapping information:

php bin/console doctrine:migrations:diff
Enter fullscreen mode Exit fullscreen mode

RoR migrations

Ruby on Rails (RoR) has a migration feature. It adds some magic called Ruby DSL to avoid manual SQL statements.

You don't have to write migrations from scratch. There's a generate command. For example, to add a new table:

rails g migration CreateComments content:text
Enter fullscreen mode Exit fullscreen mode

Of course, there would be more fields here, but let's keep it simple for this demo. The rails g command is a shorthand for rails generate.

I'm creating a new comment table with a field called content that has a text type (and not a string limited to 255 chars).

Note that Rails automatically handles the auto-increment part (a unique id as primary key), as it considers you will need it.

It will generate a new file in the db/ folder, for example, db/migrate/20210427082623_create_comments.rb. The filename will have the timestamp and CreateComments (snake case).

N.B.: Timestamps are helpful to execute things in order.

Anyone in the team that needs to grab my new code will only have to run the migration:

rails db:migrate
Enter fullscreen mode Exit fullscreen mode

Source: RoR - active record migrations

Note that if things go wrong for any reason, you can still rollback:

rails db:rollback
Enter fullscreen mode Exit fullscreen mode

Not only are migrations astonishing to create new tables and fields, but they are also way easier and painless to rename existing fields.

Migrations in Node

If you work with Node.js, knex is one of the best libraries. It is database agnostic, which means it works with various systems such as Postgres, MSSQL, MySQL, MariaDB, SQLite3, Oracle, or Amazon Redshift.

To install, you add knex and the appropriate driver, for example:

yarn add knex pg sqlite3
Enter fullscreen mode Exit fullscreen mode

Then you can generate your knex config file by running:

npx knex init
Enter fullscreen mode Exit fullscreen mode

You get a file called knexfile.js at the root of your project. You can add your credentials to this file.

The library can handle different drivers for different environments so that you can use SQLite to dev and deploy with Postgres on production.

To generate a migration, just run:

npx knex migrate:make CreateComments
Enter fullscreen mode Exit fullscreen mode

You'll get a file like migrations/20210427085457_CreateComments.js with up and down ready-to-use wrappers:

exports.up = function(knex) {

};

exports.down = function(knex) {

};
Enter fullscreen mode Exit fullscreen mode

You could use the up function to create the table and the down function to drop the table:

exports.up = function(knex) {
  return knex.schema.createTable("comments", table => {
    table.increments("id").primary();
    table.string("text");
  })
};

exports.down = function(knex) {
  return knex.schema.dropTable("comments")
};
Enter fullscreen mode Exit fullscreen mode

After that, run the following:

npx knex migrate:latest
Enter fullscreen mode Exit fullscreen mode

If everything is correct, you'll get the following:

Batch 1 run: 1 migrations

Of course, you have a rollback command too.

Awesome!

Wrap up

Migrations are a very robust and handy feature to work with databases. It removes the hassle of manual sequel statements and makes updates safer.

You don't have to use any database manager and run manual exports/imports to grab the latest schema changes. It also prevents losing time on annoying errors due to a lousy diff between your local version and the newest version of the database.

Photo by Stephen Leonardi on Unsplash

Discussion (0)

Forem Open with the Forem app