Hello dear readers, lately I've been struggling to run database migrations in Golang with sqlite3
but I have found a solution and I want to share it with you guys.
Let's do this
Start by creating a simple project Golang project in your computer
mkdir migration-cli
cd migration-cli
go mod init migration-cli
Then get the cobra package
go get -u github.com/spf13/cobra
Open this in your favourite code editor and create and add a main.go
file
Adding Cobra
We are going to start by creating a package cmd with a root.go
file.
There we are going to setup a root command for our cli using cobra of course. The name of this cli will be mcli for example purposes.
SubCommands
Let's create a subcommand named migrate that will have a another up
and down
subcommand.
migrate.go
will be a very simple file, it is like a subroot command that is added to the root command 🙂
Now we can create migrateUp.go
and migrateDown.go
that are subcommands for migrate. The difference is that up and down will have an extra Run cobra property where we are going to write the actual logic to run our migrations.
To finish setting this up, in our main.go
we need to call Execute() from our cmd/root.go
file.
To test if the commands are called you can either build your program with a custom name and then call it
go build -o mcli
./mcli migrate up
or for now just use go run
go run main.go migrate up
Setup Database
I'll be using sqlite3 for the database, I'm gonna start by creating a database package with a config
file.
In that config file I'll write a Open method that will return an instance of my database.
For that I'll need the sqlite3 driver package
go get -u github.com/mattn/go-sqlite3
Now we can call database.Open() in our migrate subcommands to test the database connection.
go build -o mcli
./mcli migrate up
It's working and as you can see it created a myDB file.
I will open this file in DB Browser for Sqlite that is a GUI for SQLITE.
Okay we know how to open our database let's write our migrations.
Migrations
In a new directory migrations I am going to create a 1_install.up.sql
file and a 1_install.down.sql
, this syntax will allow golang migrate to know the version of our migration file as well as the action.
Now let's add golang migrate
go get -u github.com/golang-migrate/migrate/v4
Back to migrateUp.go
we are going to write the logic to run our up migration using golang migrate package.
Let's build the program and run ./mcli migrate up
again to test this 🙂
Seems to be working, if we open the myDB file in DB Browser for Sqlite again we should see our tables.
Perfect! Now let's do the same thing for migrate down
the logic is basically the same but instead of call the golang migrate built in func Up() we call Down().
Let's test migrate down
👍🏼
Now if you open your myDB
file again our tables should be gone.
Conclusion
Okay, that's it! I hope this article will help 😀
The code source is here.
Youtube video here.
See you soon 👋🏼
Top comments (0)