Over the past 10 years, there has been an explosion of new tools coming out of the community to solve the age old problem of interfacing with databases — from creation to migrations to seeding. It should be no surprise as databases are an essential part of any stateful service, whether you’re going with a NoSQL implementation such as Mongo, a traditional RDBMS like MySQL or a graphing database like Neo4j. Depending on your need and what type of data you store, you may find yourself putting the business logic in your database using foreign keys and constraints, or you can perform logic gates directly in your code, abstracting the database driving your software. Taking the abstract approach would make it easier to use temporary database engines like SQLite in dev and staging, but Postgres or MySQL in production. Personally, I see having a process to create, update and seed your database as a necessary capability to speed up your ability to develop and iterate. Managed migrations and seeding are two ways of doing that.
My requirements are simple: lean, lightweight and versatile. No solution should require me to be locked into a specific engine.
- Depending on the project, I will quickly pivot between Postgres to MySQL, and so on.
- Builds and deployments should not be dependent on a spider webs of modules, slowing down the process
- It should support multi-architectures, deployment strategies, support being run from within CI/CD pipelines as well as locally
The two tools I always find myself leveraging are SQLC and Goose, which work perfectly together.
SQLC allows us to organize our queries and create type definitions for our schema. Goose will use SQLC’s schemas as a migration. Piggybacking on the functionality, we repurpose Goose for seeding as well, using a different version table, but same approach.
Structuring My Projects
I’ll usually take the following approach to structuring my project. This keeps all your code and DDL separated, helping reduce the bloat of a pull request. Keeping sqlc.yaml at the root sets the context nicely, without needing to over inscribe the commands’ arguments.
We use the following sqlc.yaml configuration, which references the db/ directory for each of the migrations, seeds and queries, as well as an override for MySQL’s uuid.
version: "2"
overrides:
go:
overrides:
- column: "*.uuid"
go_type: "github.com/google/uuid.UUID"
sql:
- engine: "mysql"
queries: "db/queries"
schema: "db/migrations"
gen:
go:
json_tags_case_style: "snake"
emit_json_tags: true
package: "db"
out: "db"Lastly, our Makefile contains the following targets, simplifying the calls to each of the steps:
migrate:
@goose -dir db/migrations -table _db_version mysql '$(DSN)' up
seed:
@goose -dir db/seeds -table _db_seeds mysql '$(DSN)' up
sql:
sqlc generateFollowing a run of make sql, we now see the following directory structure — notice the now compiled db/users.sql.go and db/models.go which are compiled from our raw queries:
Why Not GORM?
This is a question I’ll get asked a lot. GORM is a great tool, but it depends on what you’re trying to accomplish. If I need to do something quick and lightweight that won’t have a lot of overhead or a long-term maintenance plan, GORM could be great. If it’s a quick To-do app or something with a limited view, it may be the right solution. However, where I always found it short was when it came to more complex queries requiring association tables and preloading.
Conclusion
I’ll be the first to tell you: there’s no right way to solve this problem.
You’ll find a million of opinionated approaches floating across blogs and Reddit, and you’ll need to see what approach works best for your specific scenario. It’s not one-size fits all.
Check out some of the projects we discussed in this article
Member discussion