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.
- Link to the full series playlist on Youtube
- And its Github repository
Install golang-migrate
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
up
ordown
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. We can run migrate -help
to read its manual.
Create a new migration
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.
Start with migrate create
. Then the extension of the file will be sql
, and the directory to store it is db/migration
.
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 down
. Why?
Up/down migration
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.
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 init_schema.up.sql
file.
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';
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.
DROP TABLE IF EXISTS entries;
DROP TABLE IF EXISTS transfers;
DROP TABLE IF EXISTS accounts;
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.
Check postgres container status
But before that, we should check if our postgres
container is still running or not:
docker ps
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 exited
.
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.
Access postgres container shell
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.
Create/drop database inside postgres container
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
--username
option to say that we’re connecting asroot
user. - And the
--owner
option to say that the database we’re going to create will belong toroot
user as well. - The last argument is the database name:
simple_bank
.
OK, the database is created. We can access its console with the psql
command.
We can also delete the database using dropdb
command and pass in the name of the database.
dropdb simple_bank
We use exit
command to get out of the container shell.
Create/drop database outside postgres container
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
Write Makefile
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
make postgres
A new postgres container will be started. And we can run
make createdb
to create the simple_bank
database.
View database with TablePlus
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 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.
Run the migration
Start with migrate
. Then we use the -path
option to specify the folder contains our migration files, which is db/migration
.
migrate -path db/migration -database "postgresql://root:secret@localhost:5432/simple_bank" -verbose up
The -database
option is used 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
, port5432
. - And the database name is
simple_bank
.
We use -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: accounts
, entries
, transfers
, and schema_migrations
.
The 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.
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.
Add migrate up/down to the Makefile
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:
make migratedown
And 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. Excellent!
So that wraps up today’s lecture about database migration. Thank you for reading and see you in the next lecture!
If you like the article, please subscribe to our Youtube channel and follow us on Twitter for more tutorials in the future.
If you want to join me on my current amazing team at Voodoo, check out our job openings here. Remote or onsite in Paris/Amsterdam/London/Berlin/Barcelona with visa sponsorship.
Top comments (14)
Please use
go install github.com/golang-migrate/migrate/v4
and add go’s bin directory to your PATH. Using brew for a go package doesn’t sound like a good idea, considering version differencesI am on windows and did this, like ran this command on the terminal, it ran and installed it and said package is not a main package
then i ran the
migrate --version
commandand its not able to find it like not installed or something, confused with gobin and gopath.
I think your first problem is that you’re running Windows but further you might want to add your ~/go/bin directory to your PATH env in bashrc
cant stress this one enough
You can also try using
migrator
package to write migrations using Golarapulse / migrator
MySQL database migrator
MySQL database migrator
MySQL database migrator designed to run migrations to your features and manage database schema update with intuitive go code. It is compatible with the latest MySQL v8.
Installation
To install
migrator
package, you need to install Go and set your Go workspace first.migrator
.Quick start
Initialize migrator with migration entries:
Thanks for sharing, Sergey!
Getting this error while migratedown
"Dirty database version -1. Fix and force version."
can anyone help with a solution! it will be a great help if you can.
Hi Apoorv, try this cmd first
migrate -path db/migration -database "postgresql://root:secret@localhost:5432/simple_bank?sslmode=disable" force 1
and then run makefile cmd
make migrateup
it should solve this issue. Here's the docs for it
github.com/golang-migrate/migrate/...
try github.com/covrom/goerd
when i type run make migratedown in terminal i am getting following error:
xcrun: error: invalid active developer path (/Library/Developer/CommandLineTools), missing xcrun at: /Library/Developer/CommandLineTools/usr/bin/xcrun
I am not getting the issue!
How do I seed the DB in this example?
Hi Mori, please check out lecture 4 and 5 of this series. You will see how to generate codes to insert data to the database, and also how to generate random data for seeding or testing.
Thank you
There is probably error on the orange picture "MIGRATE DOWN" - files shown there should be ...down.sql not ...up.sql
Right?