Software Engineer
Europe
Working with databases is complicated. I've spent a lot of time reading execution plans, understanding indexes, optimizing queries and rediscovering ways how to improve query performance. But, there is something else, I'm talking about managing database schema and migrations.
We started our product journey with Supabase. It's an amazing product, which we loved and enjoyed working with. However, I have quickly found myself struggling to implement business logic with SQL. It wasn't the SQL itself, but nightmare deploying the changes. After some time we have ended-up with hundreds of migration files, table alterations, little updates and it was impossible to figure out how the table or function supposed to look like. It was even harder to find what's the latest version. Long story short – too much mess.
We have spun up a backend to deal with business logic. And it is amazing. We have our code that is doing exactly what is supposed to do. Everything in git, easy to see what changed.
But then, DB schemas... we still have had a lot of migrations for schema related changes. It was still hard to figure out how the schema supposed to look like. Or, if we removed a field and forgot to update the query somewhere in the code. We have tried ORMs to manage schema migrations for us, but that means that we had to learn another DSL... and another DSL may often miss features. For example, how would you define a stored view in GORM or Peewee? Do we have to create a separate migration just for view? Yada yada.
While I have nothing against ORMs, personally me and my team find it easier to deal with old good SQL. It's just easier. You get exactly what you write. I have found myself many times in the situation where I'd write a complex SQL query in some other IDE, only to then spend time to port it to ORM DSL. Eventually, we have given up and just put SQL in the code.
Long story short, I was still missing a way how to sanely deal with schemas and queries.
After experimenting with several different tools I found myself found of sqlc and Atlas combination.
With sqlc you have a schema.sql file that contains table SQL code and a separate queries.sql file that contains SQL queries. And !boom! sqlc whatever in bash and it generates a code to query the database. It even checks that my queries do not try to reference something that does not exist.
Now I have my entire DB schema defined in plain old good stupid SQL. I still missed a link to deal with migrations. That is where Atlas comes in.
Atlas can take my schema.sql and figure out the diff with the database! No more writing migrations. Not a single line!
Both tools integrate nicely and provide with the workflow that I did not know is possible.
There still quirks with both tools, but they are more manageable. From my experience, quirks become an ops problem, they no longer relevant in dev.
Personally, I think that certain things do not need abstractions. SQL is fine on it's own and ORMs often are either hard to understand or generate subpar code. I'd love to see more tools that follow the same approach as sqlc and Atlas.