Introduction
We always can create and run our migrations manually, but if we want to make it faster, safer, and easier to read and maintain, it's recommended to use a specialized tool for it.
To choose this tool, we need first to analyze what are the requirements for "handling migrations". They are:
- Generate
UP and DOWN migrations to apply and revert the changes
- Keep track of which migrations already were executed and be able to apply only the new migrations
- Have a clear, simple and fast way to know the current structure of your database
On the next sections, let's dig up a bit about each one of these features, and at the end let's see some tools that we can use for it.
Generate UP and DOWN migrations
Generate migrations is not a requirement, but is a great bonus. It saves a lot of time and rarely needs any adjust. To do it, the tool needs to get the current state of the database, the new state that you want to apply and create the necessary SQL queries to synchronize both, in the best way possible and without any bugs.
The current new state that you want to apply can be defined as code or a specific schema file, but I'll talk more about it on the Know the current structure of your database chapter.
Up and Down migrations can be written in 2 ways:
- Using raw SQL files
- Using
.<your-language> files (in this case, .go)
I personally prefer to have raw SQL files, because this way I fell safer that none of the behaviors of the language will affect my migration, and any of the updates in the language will make me update previous migrations.
If the tool doesn't generate the migrations for you, you will have to write them manually.
Keep track of migrations
This is without a doubt the most important part of every migration tool. It's very important to know which migrations already were executed, so the tool can avoid running the same migration twice and causing an error.
For our luck, all the most famous tools for handling migrations do it very well.
Know the current structure of your database
This is the part that improves dev experience and the velocity that you can modify your database to fit your new needs. Have a simple and fast way to know the current state of your database is essential for many things: Have the big picture of your database, be sure how a change would affect it, if it has the best performance that it can have (with all the right indexes and relations), if it has a column that you need and how can you add it if necessary.
Most tools don't have a way to do it, and once you start using this kind of documentation, your life changes, and you never want to work with an undocumented database again.
There are ORMs like gorm that supports some kind of documentation with files, but they mainly focus on converting the database to "things" in the code to be used by the ORM, and not as documentation only/documentation-first.
To solve this problem, DBML was created, but it lacks a lot of features required for migration tools.
A basic Golang specific tool to run migrations.
Pros:
- Is the most famous and loved one
- Has more than 14k stars in GitHub
- Is in development since 2014
- Very reliable
Cons:
- It can't generate migrations, can only run manually written migrations and ensure to not run already executed migrations.
- Has no way to know the current state of your database, depends on external tools like (DBeaver).
Another Golang specific tool to run migrations, with a bit more functionalities.
Pros:
- The second most famous and loved one
- Has more than 6k stars in GitHub
- Has better documentation than golang-migrate
- Can handle both SQL and Golang migrations
Cons:
- It can't generate migrations, can only run manually written migrations and ensure to not run already executed migrations.
- Has no way to know the current state of your database, depends on external tools like (DBeaver).
- Is more complex than golang-migrate
A language agnostic tool to run migrations.
Pros:
- Is language agnostic
- Is maintained by a (small) company
- Has more than 5k stars on GitHub
- Has an extensive and very good documentation
- Has a way to know the current state of your database
- Can generate migrations
- Has a Discord server for close contact with the maintainers
- Can convert your schema to other formats, like JSON, DBML, ERD and others
Cons:
- Is maintained by a SMALL company, and not really adopted, so if the company goes bankrupt, it may be the end of support
- Uses a version of HCL as its schema language
A wrapper to a JavaScript library that is both an ORM and a migration management tool.
Pros:
- Is language agnostic
- Has more than 2k stars on GitHub
- Has an extensive and very good documentation (on the original library, not the wrapper)
- Has a way to know the current state of your database
- Can generate migrations
- Both the original library and the wrapper have Discord servers for close contact with the maintainers
Cons:
- Is maintained by a small group of people
- It's a JavaScript library
- Is a wrapper for another library, what can cause conflicts
- It's a JavaScript library
- Not only a migration tool, but an ORM, what means a bunch of unnecessary things come with the library
- It's a JavaScript library
Both golang-migrate and goose can't generate migrations and don't have a way to document your database, so I can exclude them already.
Atlas has a lot of potential, has the core that we need: From a schema file it can generate things. Generate migrations, .dbml files or "things" specific for your code to be used by an ORM if you want to.
The only problem with Atlas is that I personally hate HCL for databases, I think that it's overcomplicating the problem, too many keywords words for something simple. I know that it decreases development complexity for the guys maintaining it, but it's a terrible experience for anyone using it.
If you like HCL, I recommend you to go with Atlas. It may be risk because it's maintained by a small company, but I think that it's the best option that we have in the market.
And last: Go prisma. I use it, not because I think it's a great tool, has the best performance or has extra unseen magical features. I use it because prisma.schema.
The Prisma syntax for writing database specifications is good, not the best, may be a little overdecorated, but works, it's simple, it's understandable and has its own formatter (HCL also has one btw). Go Prisma is a workaround to use Prisma without having to install it using node, and to be honest, at this moment, I'm not even sure if you can run it without having node installed.
I prefer to take the risk and have some other things installed to be able to use the prisma.schema file, since at the moment (and for the next years) it will not affect significantly my project.
Conclusion
Hope that you liked the article, and please feel free to share your options on the comments!
