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 using golang-migrate library.
Golang-migrate works with many different database engines like postgres, mysql, mongo, cockroach, etc.
Let’s open this CLI documentation to see how to install it. I’m on a mac, so I will use Homebrew.
brew install golang-migrate
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
downcommands to apply all or N up or down migrations.
There are several more commands, but in most of the time, we will work with
Alright, migrate is successfully installed. 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.
cd ~/Projects/techschool mkdir simple_bank cd simple_bank mkdir -p db/migration
Then let’s create the 1st migration file to initialise our Simple Bank’s database schema.
migrate create. Then the extension of the file will be
sql, and the directory to store it is
migrate create -ext sql -dir db/migration -seq init_schema
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.
As you can see, 2 migration files have been generated for us. They both have version 1 in the file name’s prefix, but their suffixes are different: 1 file is
up and the other is
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
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.
Aright, 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
CREATE TABLE "accounts" ( "id" bigserial PRIMARY KEY, "owner" varchar NOT NULL, "balance" bigint NOT NULL, "currency" varchar NOT NULL, "created_at" timestamptz NOT NULL DEFAULT (now()) ); CREATE TABLE "entries" ( "id" bigserial PRIMARY KEY, "account_id" bigint NOT NULL, "amount" bigint NOT NULL, "created_at" timestamptz NOT NULL DEFAULT (now()) ); CREATE TABLE "transfers" ( "id" bigserial PRIMARY KEY, "from_account_id" bigint NOT NULL, "to_account_id" bigint NOT NULL, "amount" bigint NOT NULL, "created_at" timestamptz NOT NULL DEFAULT (now()) ); ALTER TABLE "entries" ADD FOREIGN KEY ("account_id") REFERENCES "accounts" ("id"); ALTER TABLE "transfers" ADD FOREIGN KEY ("from_account_id") REFERENCES "accounts" ("id"); ALTER TABLE "transfers" ADD FOREIGN KEY ("to_account_id") REFERENCES "accounts" ("id"); CREATE INDEX ON "accounts" ("owner"); CREATE INDEX ON "entries" ("account_id"); CREATE INDEX ON "transfers" ("from_account_id"); CREATE INDEX ON "transfers" ("to_account_id"); CREATE INDEX ON "transfers" ("from_account_id", "to_account_id"); COMMENT ON COLUMN "entries"."amount" IS 'can be negative or positive'; COMMENT ON COLUMN "transfers"."amount" IS 'must be positive';
init_schema.down.sql file, we should revert the changes made by the up script. In this case, the up script creates 3 tables:
entries. So the down script should remove all of them. We use the
DROP TABLE query for this purpose.
DROP TABLE IF EXISTS entries; DROP TABLE IF EXISTS transfers; DROP TABLE IF EXISTS accounts;
Here we drop
transfers table before dropping the
accounts table because there’s a foreign key constraint in
transfers that references
OK, so now our migration scripts are ready. Let’s try to run them.
But before that, we should check if our
postgres container is still running or not:
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.
docker stop postgres12
After this, if we run
docker ps, we won't see the postgres container anymore because it’s not running. To list all containers, regardless of their running status, we can run:
docker ps -a
Now we see our
postgres container with status
To turn it back on, We just need to run:
docker start and pass in the container name or ID.
docker start postgres12
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:
docker exec -it postgres12 /bin/sh
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.
We have to create the database before we can run the 1st migration.
So let’s run the
createdb command inside the postgres container's shell to create a new database for our Simple Bank:
createdb --username=root --owner=root simple_bank
- We use the
--usernameoption to say that we’re connecting as
- And the
--owneroption to say that the database we’re going to create will belong to
rootuser as well.
- The last argument is the database name:
OK, the database is created. We can access its console with the
We can also delete the database using
dropdb command and pass in the name of the database.
exit command to get out of the container shell.
Now from outside of the container, we can also run
createdb directly with the
docker exec command.
docker exec -it postgres12 createdb --username=root --owner=root simple_bank
And access the database console without going through the container shell.
docker exec -it postgres12 psql -U root simple_bank
OK, now I’m gonna create a
Makefile in our project, then add a
createdb command to create the simple bank database, and 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 add the command that we used to start postgres container in the previous lecture to the Makefile as well.
postgres: docker run --name postgres12 -p 5432:5432 -e POSTGRES_USER=root -e POSTGRES_PASSWORD=secret -d postgres:12-alpine createdb: docker exec -it postgres12 createdb --username=root --owner=root simple_bank dropdb: docker exec -it postgres12 dropdb simple_bank .PHONY: postgres createdb dropdb
Alright, Now let’s stop the current postgres container.
docker stop postgres12
The container is stopped. I’m gonna remove it completely using
docker rm command.
docker rm postgres12
Now when we run
A new postgres container will be started. And we can run
to create the
OK, 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 the database icon to open our new
Alright, now you can see 2 databases here:
simple_bank. For now the
simple_bank database is empty. So let’s go back to the terminal and run the first migration.
migrate. Then we use the
-path option to specify the folder contains our migration files, which is
migrate -path db/migration -database "postgresql://root:secret@localhost:5432/simple_bank" -verbose up
-database option is used to specify the URL to the database server.
- We’re using postgres, so the driver name is
- Then the username is
- The password is
- The address is
- And the database name is
-verbose option to ask migrate to print verbose logging.
And finally we use the
up argument to tell migrate to run
migrate up command.
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. Now run this command:
migrate -path db/migration -database "postgresql://root:secret@localhost:5432/simple_bank?sslmode=disable" -verbose up
And the migration is successful!
If we refresh the simple bank database in TablePlus, we can now see 4 tables:
schema_migrations table stores the latest applied migration version, which in our case is version 1, because we have run only 1 single migration file.
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 and
migrate down command to the Makefile:
postgres: docker run --name postgres12 -p 5432:5432 -e POSTGRES_USER=root -e POSTGRES_PASSWORD=secret -d postgres:12-alpine createdb: docker exec -it postgres12 createdb --username=root --owner=root simple_bank dropdb: docker exec -it postgres12 dropdb simple_bank migrateup: migrate -path db/migration -database "postgresql://root:secret@localhost:5432/simple_bank?sslmode=disable" -verbose up migratedown: migrate -path db/migration -database "postgresql://root:secret@localhost:5432/simple_bank?sslmode=disable" -verbose down .PHONY: postgres createdb dropdb migrateup migratedown
Now let’s try them in the terminal! First I will run:
And go back to TablePlus and refresh.
All tables are gone, except for the
OK, now let’s run:
Then refresh TablePlus.
All tables are back again. Excellent!
So that wraps up today’s lecture about database migration. Thank you for reading and see you in the next lecture!