When working with database, schema migration is one important task that we often have to do throughout the application
lifetime to adapt to new business requirements. In this lecture, we will learn how to write and run database schema migration in golang. For that, we’re gonna use golang migrate
library. This library works with many different database
engines like postgres, mysql, mongo, or cockroach Let’s open this CLI documentation to see
how to install it I’m on a mac, so I will use home brew Let’s open the terminal And run “brew install golang-migrate” While waiting for home brew to install, Let’s take a look at the usage guide. Migrate gives us several commands. The first one is “create”, which we can use to create new migration files The 2nd one is “goto”, Which will migrate the schema to a specific
version Then the “up” or “down” commands to apply all or N up or down migrations. There are several more commands, But in most of the time, we will work with create, up, and down. Alright, migrate is successfully installed. Let’s check its version with “migrate
-version” it's 4.11.0 We can run “migrate -help” to read its
manual. OK, now I’m gonna create a new folder for our simple bank project. And inside, I will create a new folder “db/migration” To store all of our migration files. Then let’s create the 1st migration file To initialize our simple bank’s database
schema. Start with “migrate create”. Then the extension of the file will be “sql” And the directory to store it is “db/migration” We use the -seq flag to generate a sequential version number for the migration
file And finally the name of the migration, which is “init_schema” in this case. 2 migration files have been generated for
us. They both have version 1 in the file name’s
prefix. But their suffixes are different. One file is up and the other is down. Why? Well, basically it’s a best practice when
writing database migration. The up-script is run to make a forward change
to the schema. And the down-script is run if we want to revert
the change made by the up-script. So when we run “migrate up” command, The up-script files inside “db/migration”
folder will be run sequentially by the order of their
prefix version. On the contrary, When we run “migrate down” command, The down-script files inside “db/migration”
folder Will be run sequentially by the reverse order
of their prefix version. Alright, Now let’s open the simple_bank.sql file that we’ve generated in previous lectures. I’m gonna copy all content of this file, And paste it to the init_schema.up.sql file. For the init_schema.down.sql file, We should revert the changes made by the up
script, In this case, the up script creates 3 tables:
accounts, transfers, and entries. So the down script should remove all of them. We use the DROP TABLE query for this purpose. And here we drop entries and transfers table
before dropping the accounts table because there’s a foreign key constraint
in entries and transfers that references accounts records. OK, so now our migration scripts are ready. Let’s try to run them. But before that, We should check if the postgres container
is still running or not with the “docker ps” command. OK, it’s still running. By the way, I’m gonna show you some more docker commands
to work with containers. If we want to stop a running container, We use “docker stop” with the container
name or ID. After this, if we run “docker ps”, we don’t see the postgres container anymore
because it’s not running. We can run “docker ps -a” to list all
containers, regardless of their running status. Now we see our postgres container with status
“exited”. To turn it back on, We just need to run “docker start” and pass in the container name or ID. Then here we go, The postgres12 container is now up and running. We can access its shell with the docker exec
command. As we’re using postgres alpine image, we don’t have /bin/bash shell as in ubuntu, so we use /bin/sh shell instead. Inside the shell, we have access to all standard
linux commands. And since this is a postgres container, It also gives us some CLI commands to interact with postgres server directly
from the shell. Let’s use the “createdb” command to
create a new database for our simple bank. We use the “--username” option to say
that we’re connecting as “root” user. And the “--owner” option to say that the database we’re going to create will
belong to “root” user as well. And the last argument is the database name:
simple_bank. OK, the database is created. Let’s try to access its console with the
psql command Cool, it works! We can also delete the database using “dropdb”
command We use “exit” command to get out of the
container shell. Now from outside of the container, We can run createdb directly with the “docker
exec” command. And access the database console without going through the container shell. OK, now I’m gonna create a Makefile in our
project Then add a createdb command to create the
simple bank database And also a dropdb command to delete it. When working in a team, these commands will be useful for your team
mates to easily setup the project on their local
machine for development. Let’s look for the command that we used to start postgres container in
the previous lecture And add it to the Makefile as well. Alright, Now let’s stop the current postgres container. The container is stopped. I’m gonna remove it completely using “docker
rm” command. OK it’s gone. Now when we run “make postgres” A new postgres container will be started. And we can run “make createdb” to create
the simple_bank database. So, the database is created. Let’s connect to it using TablePlus. The connection that we’ve setup in the previous
lecture will bring us to the root database. We can click on this icon To open our new simple_bank database. Alright, now you can see 2 databases here: root and simple_bank. For now the simple_bank database is empty. So let’s go back to the terminal and run
the first migration. Start with migrate, Then we use the -path option to specify the folder contains our migration
files, which is db/migration. Then the -database option to specify the URL to the database server. We’re using postgres, so the driver name is “postgresql”. Then the username is “root”, The password is “secret”, The address is localhost, port 5432 And the database name is “simple_bank” We use -verbose option to ask migrate to print
verbose logging Finally use the “up” argument to tell
migrate to run migrate up. Oh, we’ve got an error: SSL is not enabled
on the server. That’s because our postgres container doesn’t
enable SSL by default. So we should add “sslmode=disable” parameter
to the database URL. And the migration is successful. If we refresh the simple bank database in
TablePlus, We can now see 4 tables: accounts, entries, transfers, and schema_migrations. The schema_migrations table stores the latest
applied migration version, In our case, it is version 1 because we have run only 1 single migration
file. The dirty column tells us if the last migration
has failed or not. If it fails, we must manually fix the issues to make the database state clean before trying to run any other migration versions. OK, now I’m gonna add the migrate up command
to the Makefile Let’s add the migrate down command as well. Then add migrate up and migrate down to the
PHONY list Now let’s try them in the terminal First I will run “make migratedown”. Go back to TablePlus and refresh. All tables are gone, except for the schema_migrations
table. OK, now let’s run “make migrateup” Then refresh TablePlus. All tables are back again. So that wraps up today’s lecture about database
migration. Thank you for watching and see you in the
next lecture!
