Keeping Track of Database Schema Changes | by Hernan Reyes | Jun, 2022

Ideas that can assist you with database adjustments

Migrations help you build your database schema
Migrations enable you to construct your database schema

Have you ever ever puzzled how builders deal with adjustments within the database schema of a challenge? What do they do when a brand new desk/column/index is added, or when a desk, column, or index is dropped.

How do they guarantee everybody concerned within the challenge has all of the adjustments utilized to their database? Effectively, for this, they maintain monitor of a historical past of each change made to the database schema.

SQL migrations ad GIT agreement
SQL migrations settlement with a model management system

This was one thing I didn’t know till I first labored with different individuals and I learn the README of the challenge and mentioned that I’ve to run the “migrations” to have my database schema up-to-date.

That made me ask myself: how didn’t it cross my thoughts to deal with one thing that apparent earlier than? Effectively, it was as a result of I by no means change off the pc or reformatted my PC, so it made me reinstall the entire challenge from zero to understand that I didn’t have a strategy to restore my database with out a backup.

My aim on this article is to indicate you ways this database migration works so you realize what to do when new adjustments are utilized to the database, whether or not you’re working solo or with different individuals.

The way in which it really works is straightforward, you must do the identical factor you do whenever you maintain a historical past of the challenge’s code. So, we’ll want a model management system like GIT. There, we will guarantee everybody concerned within the challenge can have the database’s adjustments historical past updated.

Right here, I’ll present you two methods to deal with the database migrations, however first, let’s see what they’ve in widespread:

You’ll write information that comprise the SQL with a reputation format like: version_description.up/down.ext

The outline could be a quick clarification of what the migration file does, for instance:

  1. create_users_table
  2. adds_birthdate_column_to_users_table
  3. drops_country_column_from_users_table

The SQL to use adjustments to the schema (referred to as up migration) and a SQL to reverse these adjustments (referred to as down migration)

That’s why within the earlier level, the identify format signifies whether or not is an up or down migration

Observe: Some libraries handle one file and with feedback point out the up SQL and down SQL

A strategy to run the migrations so as

For this, we make use of the model of the filename format, you may put a sequence quantity or the timestamp of whenever you created the file, so after we run the migrations we execute them in ascending order. That is wanted as a result of you might add migrations that rely upon different migrations to be executed first.

Guarantee we don’t run a migration twice

Libraries could create a desk to maintain a log of the executed migrations, so that they don’t do it twice.

The extension of the file will depend on the tactic you select to deal with the migrations

It may be a .sql file or the extension of your programming language

Now that we all know what they’ve in widespread, let’s see every technique in motion.

Right here you’ll must create a listing to retailer the .sql information, which can comprise the adjustments made to the database schema and a strategy to reverse them. Following the format described within the earlier part, you’ll have information like these:

SQL migrations directory
SQL migrations listing tree

Then in your migration information, simply write the adjustments you wish to make. For instance, the 000001_create_users_table.up.sql migration within the above picture would have one thing like this:

and for the migration 000001_create_users_table.down.sql, the wanted SQL to reverse the adjustments made by the up migration file:

DROP TABLE IF EXISTS customers;

Observe: Some libraries can learn the SQL information from sources like GitHub, GitLab, AWS s3, and so on.

For this technique, you need to use the following libraries:

  1. https://github.com/golang-migrate/migrate
  2. https://github.com/pressly/goose
  3. https://www.prisma.io/migrate
  4. https://pypi.org/project/yoyo-migrations/
  5. https://github.com/alexyslozada/migrations

Right here utilized the identical conventions as within the Pure SQL’s migrations, however as a substitute of getting .sql migrations, you’ll have migrations with the extension of your programming language, in my case, I’ll be utilizing Golang, so the earlier construction of information I confirmed you earlier will appear to be this with a timestamp versioning as a substitute of a sequence:

Go SQL migrations listing

As you may see, in contrast to the earlier technique, right here we solely have three information. Why is that? It’s due to the library I’m utilizing. We point out the up and down migration in the identical file. Right here is the content material for the customers desk:

Right here you could have an up and down operate which is handed to goose.AddMigration(). It will clearly be totally different relying in your programming language and the library you’re utilizing.

For this technique, you need to use the following libraries:

  1. https://github.com/pressly/goose
  2. https://laravel.com/docs/9.x/migrations
  3. https://pypi.org/project/yoyo-migrations/

For this, you may create a backup of the database schema, so you may add it as an preliminary migration, however you must make it possible for the library you employ doesn’t execute it in your native database (as a result of you have already got these adjustments), so you must dig into the library to see how they maintain monitor of the migrations which have been already run, those I do know creates a desk in your database, so you can begin there.

Then, you can begin conserving monitor of the database adjustments. If you would like a brand new desk, add a migration. If you wish to drop a column? Add a migration. If you would like an index? Add a migration. Keep in mind, each change to your database schema should have a migration.

Right here I gave you an outline of database migrations, with two widespread approaches on deal with them. Personally, I like to make use of the primary technique as a result of I simply have to fret about writing the SQL and never writing code to execute the migration with my programming language. I’ve been utilizing this technique for over three years now and by no means had the necessity to use the second.

I hope you’ve realized one thing new at this time and began utilizing these migrations when you’re not already. Three years in the past I didn’t know use them, however with observe, you may be ready to make use of them or train them to different builders.

If you wish to know create and run the file migrations robotically, discuss with the libraries I discussed. There they clarify use them.

Thanks for studying and see you within the subsequent article.

More Posts