Database Migrations: Part I

In this blog we are going to explore about database migrations, and cover following things:

• What are database Migrations ?
• What is the use of this Migrations ?
• How and where do we maintain this Migration’s Version ?
• Advantages of Migrations
• How to write Migrations ?

What are database Migrations ?

Migrations are way to modify database schema using programming language DSL, so that we don’t have to write raw SQL for altering our databases.

Different programming languages have their respective libraries, to write these migrations.

For example, In Ruby(ROR) we have ActiveRecord library to write migrations, whereas in Elixir(Phoenix) we have Ecto library to write migrations.

Below are the examples showing migrations in Ruby on Rails(Ruby) and Phoenix(Elixir) frameworks:

What is the use of this Migrations ?

We know that migrations are used so that we don’t have to write raw SQL to modify database schema. We can think of each and every migrations as being a new version of database because in each migration we are doing some changes to our database schema, which in result alter our database’s version.

What are Migration’s Version ?

It is nothing, just a timestamp prefixed before the name of migration file. This timestamp is used as unique identifier for identifying migrations individually. This is how a migration file name looks:

20200825114400_create_users.rb

Above file name, can be divided into following parts:

• 20200825114400 : It is timestamp(auto-generated)
• create_users : It is name which you provide when we generate migration file
• .rb : file extension

How and where do we maintain this Migration’s Version ?

Library like ActiveRecord maintains a schema file, which keeps the history of all the database changes, which are done using migrations. It also keeps track of the most recent database change(using migration’s recent version).

We can say that schema file is the blueprint of our database.

This is how schema file looks, in case of Rails’s ActiveRecord:

Initially this schema file does not have anything, but with each and every migrations, its content gets modified, depicting the changes done to database till now.

What Advantages does Migration Offers ?

• Migrations are database -type independent, as they are written in respective programming language DSL. So, we don’t have to care about underlying database type whether it is PostgreSQL or MySQL
• Using migrations, we can create or setup our database from scratch. This is specially very helpful, when we are setting/moving up our existing project to a new system
• Migrations always make it crystal clear about the current state of our database
• Using this, we can easily migrate from current version of database to newer one

How do we write Migrations ?

In order to understand how to write migrations, let’s take an example of adding users table to database.
So, migration file will look like this in Rails Active Record:

class CreateUsers < ActiveRecord::Migration[6.0]
  def change
    create_table :users do |t|
      t.string :first_name, :null => false
      t.string :last_name
      t.string :email, :null => false
      t.timestamps
    end
  end
end

Users Table Creation Migration in Rails using ActiveRecord

This above migration, adds a table called users with following columns:

• first_name, of string column type
• last_name, of string column type
• email, of string column type
• id, which is primary key will be implicitly added by ActiveRecord
• created_at(inserted_at in case of Phoenix’s Ecto) and updated_at, of datetime column type will be added because of timestamps method

On database which supports transactions, each and every migrations are wrapped inside this database transactions, because of this when we run this migration, the transaction will begin and the changes(users table creation in our case) will be committed. We define this change in our change method.
We can also rollback this transaction, and make our database move to its previous state(without users table in our case).

Alternatively we can also use up and down method, instead of change method. Using this approach of writing up and down methods explicitly is specially useful in scenarios , where our written database migrations are not rollback safe. So in those cases, it is always good to explicitly write up and down methods instead of change one.

In the next blog, we’ll be going to learn about what are reversible and irreversible migrations, as well as how to write safe migrations and precautions which we should take while writing migrations.

❤️ Like, Share or Leave A Comment!

Well, thats all for this blog, Hope you find it informative. If you liked it, please click the 👏🏼 button and share it with others! Feel free to leave any comments.
For more such blogs … stay tuned.

Happy Coding …

Thanks