AppUnite LogoBlog
< Go to homepage
Contact
Engineering

Patryk Bąk

Share:
Share on FacebookShare on TwitterShare on LinkedIn

Renaming column and table in database migrations with Elixir and PostgreSQL

| 2 min read

To guarantee zero downtime deployment, while deploying a new version of our application on more than one node, we can use rolling updates. What if this new version has a migration which renames either column or table? Will rolling updates protect our application against downtime?

Let's assume that we have a users table. As the name is not clear, we have decided to rename it to participants, as we keep there records of people who signed up for a triathlon race.

defmodule Migrations.RenameUsersTableName do
    use Ecto.Migration

    rename table(:users), to: table(:participants)
end

defmodule Participants do
    use Ecto.Schema

    schema "participants" do
        ...
    end
end

Not only did we write a migration which renames table, but also changed both the module and schema name.

Now, let's deploy this new code for both instances of our application using rolling update.

It means that its version will be updated incrementally. The first instance will be terminated and replaced with the new version of the application. Afterwards, if the first instance starts up properly, then the second instance will be terminated and also replaced with the new one.

As you can see, there is a moment, before the second instance is replaced with the new one, when we have two versions of the application.

Both of them can be used by users. The first new instance ran the migration which renamed a table from users to participants and started using a new schema name - participant. Both instances of the application use the same database. Due to that, until the second instance is replaced, every request handled by it will return error as this instance uses the old schema name - users. The one which does not exist anymore in our database structure.

How can we solve that?

One way to solve the above-mentioned problem is to use PostgreSQL views. In simple terms, a PostgreSQL view gives us a possibility to create a query and assign it a name. To make it clearer, I'll give an example in a moment.

Previously, while deploying the new version of our application, the change contained the migration which renames the table from users to participants. In this migration we can also create the following PostgreSQL view:

defmodule Migrations.RenameUsersTableName do
    use Ecto.Migration

    rename table(:users), to: table(:participants)

    execute("
        CREATE VIEW users AS SELECT * FROM participants;
    ")
end

The query which fetches all records from table participants, which we've just renamed, has been assigned users name - the old table name. It means that from now both instances of the application can fetch everything from this table using both users and participants name, so the new table name and the old one.

If our application starts up properly and all the instances will be replaced with the new one, we can create another migration which drops this PostgreSQL view, as it won't be required anymore.

defmodule Migrations.DropViewOnUsersTable do
    use Ecto.Migration

    execute("
        DROP VIEW users;
    ")
end

What about changing the column name?

Assuming that we deploy the application on two instances, we will face the same problem once we run a migration which renames the column on the first instance. The same problem as the one with changing a table name. The second instance, which hasn't been replaced yet with the new one containing new code, will use the old column name - the one which doesn't exist anymore in the database.

What can we do about it?

Do not rename the column - write a migration which creates a new one. Do all the steps which I described in my previous article entitled "How to migrate live production data" in order to implement these changes without any downtime.

Related articles

Engineering

How to introduce yourself to a new team

Jacek Marchwicki

When you join a new team, you usually want to show that you’re a valuable person to work with. Here you'll find some personal opinion based on our developer's experience.

Business & Strategy

What’s the best solution for your app: “No code” or traditional development?

Amelia Kaczmarek

Should you give “no code” a try or rather stick to traditional development methods in terms of developing your new app or a product?

Product Managment

5 tips for software engineers transitioning to the team leader role

Piotr Mądry

The demand for software is growing continuously. Newly created teams need strong leadership and leaders are often chosen among software engineers. What to expect and how to prepare?

This website stores cookies on your computer. The data is used to collect information about how you interact with our website and allow us to remember you. We use this information to improve and customize your browsing experience and for analytics and metrics about our visitors both on this website and other media. Cookie Policy Privacy Notice

We'd love to build something amazing together!

Make the first step for a great partnership! Share your idea with us and check what we can do for you and your company.

Start a project
ClutchFinancial TimesForbes