A play on why you need database migrations


Written by:

Reading Time: 4 minutes

Currently, this is how you work.

You build your app. along the way to make a couple of changes to your database tables. We are going to illustrate this with a story of Kofi and Kwasi.  In this story, I was not specific on which database or framework I used. This is largely because all database frameworks behave in the same manner.

The story

Kofi is the senior dev whilst Kwasi is the junior dev. They have almost finished working on a feature for their client.

Kofi:  This app looks look really good.

Kwasi: Yes it does. You put some good hours into delivering it on time.

Kofi: Hmm. I only have to change this table. That would make this feature really easy enough to implement.

Kwasi: Did you document the prior changes you did.

Kofi: Nope. There was no need. It is all in my head. I remember them all.

Kwasi: Ok. So what happens when someone fetches your git changes? How will their development database will be in sync with yours.

Kofi: Simple. I will just head over to their computer and execute a bunch of SQL statements.

Kwasi: I am not so sure that sounds too good.

Kofi: You are over cautious. My solution works every time.

Kwasi: Maybe it works everything because you never have to work with more than one person. Me.

Kofi: And all this while, we have never ran into any issues.

Kwasi: True, but I going forward, do you not think we should change? I mean, where is the teamwork if only you knows all what is supposed to be done?

(Kofi is not listening and mocks him)

Kwasi: Ok. Just go ahead.

Kofi: Development on my local is done.

Kwasi: Sharp.

Kofi: Now onto the staging environment. Let me apply those change I made. Refresh that app to see how it plays out. What is this? An ERROR ?

Kwasi: Why is an error showing up?

Kofi: It beats me. The app worked beautifully on my development laptop. Just watch. Look at my laptop. See how well it works on my laptop.

Kwasi: It looks to me like the database went out of sync with your application. Maybe we should start considering that whale thing we heard about.

Kofi: Oh, you mean Docker. That looks like it is for wimps. Real devs do spaghetti code everything. We do not need virtual environments.

Kwasi: You sure? Speak for yourself. It only makes life easier. You do not have to always remember which tables were added. Or which columns were added or deleted for that matter.

Kofi: Oh yeah I am sure. (Kofi says sarcastically)

Kwasi: Whatever you say boss.

Kofi: This project is just complicated. That must be why. That is why this issue is popping right about now. I have to recall if this quick fix in production had been applied in test afterwards?

Kwasi: Looks like the database needed some other queries to have been ran, before we run this last alter table query.

Kofi: Here is an idea. I could get this problem to go away be dropping the database, and recreating the database from scratch with what I have on my local.

Kwasi:  And this time we could use some form of database migrations

Kofi: Stop with this database migration nonsense. For example, let me ask you. Does it upgrade or downgrade the database schema.

Kwasi: Yes it does.

Kofi: I do not believe you. When did you start lying? I might have to start googling all that you say.

Kwasi: Google away. However, it is the closest thing I can think of now to bring sanity into our database changes. We can, in a sense, version control our database.

Kofi: Everybody knows you cannot version control your database. You cannot put git into your database.

Kwasi: we are not putting git into the database. We are versioning control the files that keep track of the state the database is in. The files are version controlled. This is the closest you can get to versioning control your database.

Kofi: I still like the manually applied .sql scripts. We can start wiring up the sql scripts by HAND

(Kwasi looks unamused)

Kofi: And I can note down all the steps I did to just to this point of the database.

Kwasi: Which is exactly what database migrations do.

Kofi: Alright. I will bite. Can you demonstrate a little bit of this database migrations?

(Kwasi jumps up happily)

Kwasi: I have this side project I was working on.

Kofi: Great, let us see a demonstration

Kwasi: For the first illustration, I will revert to the first state of the database. We type in a command and viola.

Kofi: Before you start lying. Excuse me, I mean before you start anything. Let us see the current state of the database. Which tables are current created and so on.

(Kwasi shows the database, and types in the command to revert to the first state of the database.)

Kofi: Is it done?

Kwasi: Yes it is.

Kofi: Let’s see the database tables

(Kwasi opens up the database)

Kofi: Ok. (Kofi looks impressed)

Kwasi: As you can see, everything is in the first state of the database. It is just as if we recreated the database from scratch.

Kofi: Let us go on to the next state that is where you added a few more tables.

Kwasi: Well in my next state, I added just one table.

Kofi: Why just one?

Kwasi: That is because making small changes at a time, leads to less risky deployments

Kofi: Alright. Are you not going to script the changes you are doing?

(Kwasi looks at him funny.)

Kwasi:  You are not hearing me Kofi. The migration files are the changes. It makes it absolutely clear at all times what state a database is in.

Kofi: I think we should start using it more often.

Kwasi: I already have.

Kofi: Yep. Migration files can help sort out and prevent the mess of updates that can happen to a database schema.

Hope you enjoyed this short play on the importance of database migrations. I will keep sharing more blog posts in the near future on all things programming and productivity.

Until then take care.




Leave a Reply

Your email address will not be published. Required fields are marked *

Scroll Up