Data Migration & Seeding In Yuk Recycle
Hello! My name is William Rumanta. In this blog, I will demonstrate on how to do database migration and data seeding in our project Yuk-Recycle. First of all, we use Go as our server-side framework and PostgreSQL as our database service.
PS: Credits goes to Yusuf Sholeh and Endrawan Andika for creating this database migration and data seeding for our team.
Why Do We Need Database Migration?
Database migration is one of a few feature that we need to implement in our app, especially a large-scaled app. It helps maintaining our database versions as there are changes overtime in a long run. To achieve that we have migration files which related to specific timestamp to act as the record for the version’s number.
In Yuk-Recycle, our first attempt at the moment in using database migration is to create tables, indexes and constraints.
How To Do Database Migration
First of all, we need a library tool for operating the migrations. In Yuk-Recycle, we use sql-migrate — https://github.com/rubenv/sql-migrate.
Here’s what we need:
- dbconfig.yaml file
- sql migration files
dbconfig.yaml file consists of our configuration for the targeted database in our specific distinct environment — development and production.
To create a sql migration files, we can use command:
sql-migrate new <file_name>After doing this, then there will be an auto-generated timestamp in the file name. With that timestamp, it will provide the version of the migrations.
This is our list of migrations files which attached to this picture shown below.
Let’s take a look on what is inside of an migration file. For this example, we will be looking at 2019040607105-Table_Mitras.sql
Briefly this file consists of two parts — +migrate Up and +migrate Down.
This is one of the features on tools that we are using here, sql-migrate.
sql-migrate up will update our database version to next recent timestamp
sql-migrate down will downgrade our database version to previous timestamp
In this Mitras_Table, there is attached a timestamp text on the file name which demonstrates the version of that migration file.
Now what we want to do in this file is when we run sql-migrate up, we will create mitras sequence, table and index. On the other hand, if we run sq-migrate down, we will undo the operation by dropping the index, table, and sequence we created before.
Operating The Migrations
We will use sql-migrate up/down to update/downgrade our database versions.
Let’s take a look at this migration table’s status created by sql-migrate tools
As you can see we have applied half of the migrations now. What if we want to downgrade the database versions to none? Simply run command sql-migrate down. It will undo the migration file each.
Now what if we want to update the database to the recent version? Simply run command sql-migrate up. It will update all the migrations files immediately.
Now we can control on what database version we want to have in our local, by simply using this tool.
Why Do We Need Data Seeding?
In related to Database migration which handle any changes on the structure and models, data seeding provides the data needed for our developers. The seeded data proved to be useful when our developers testing different features while maintaining the same cases and states.
The data which will be using for data seeding is called as master data. It is basically certain data that is required to run the app, for example user accounts, user roles, locations. Remember that master data must be using realistic data in production environment.
How to Do Data Seeding
Again, we will be using our handy tool — sql-migrate to operate this seeding. What we will need for seeding is only sql script to insert the datas we needed for certain tables.
In Yuk-Recycle, we use data seeding for customer and mitra accounts for testing in our development environment. But we also have real data that we have to specified in our app, garbage types and order statuses.
Let’s take a look on what is inside of our seeding sql script.
Similar to the one we have in migration, the script consists of two section — migrate up & migrate down.
What we want in migrate up is to insert data we needed for the tables. On the other hand, in migrate down we want to delete all the data that we inserted before.
[UPDATE] Database Migration Automation in Docker
We have included an automation script to run the database migration in docker.
The command is:
sql-migrate up -env="production" && run main.goConclusion
Database migration and data seeding proved to be useful for our team during development. It was tiring when we need to create table and insert data manually. Not to mention when there is some cases we want to test the app with the same data on two or more local computers.
Thank you for reading this article!
