dbmate

A regular code repo with the scripts (with pull/merge requests for review) and then a CI job that builds containers with something like dbmate https://github.com/amacneil/dbmate that can then be run against any staging/prod environment.

> This is why I prefer solutions like PostgREST, Postgraphile, and Hasura (for Postgres while other DBs have other similar solutions). Define it once in the DB and just let it bubble up. You may want to expose your storage API through views, but it still ensures optimal data structure at the foundation. > DB -> data-generated API service -> client API codegen -> client app such as web or mobile. This seems like a nice approach, albeit mine is even more basic. I pick a DB, create SQL migrations for the schema (sometimes even generate those from an ERD planning tool, with manual edits where needed), apply them to a DB with something like dbate: https://github.com/amacneil/dbmate After that, if I want to use an ORM, I generate entity mappings from the tables/views in a schema-first approach, for example, like https://blog.jetbrains.com/dotne

> For example having database migrations built in etc. I actually went the exact opposite route, at least when possible: https://github.com/amacneil/dbmate Pure SQL migrations, regardless of the back end technology that you use, completely decoupled from how each framework/library views things and therefore not dependent on them (you could even rewrite the back end in another technology later on, if needed; or swap ORMs; or avoid issues when there's a major ORM version update). It's really nice when you can generate entity mappings based on a live database, like with https://blog.jetbrains.com/dotnet/2022/01/31/entity-framewor... So in my case, I can have: * a DB that has migrations applied with dbmate, completely decoupled from any back end(s) that might use it * a back end that has entity mappings for ORM (if used) generated from th

We've used dbmate[1] outside of the Django/alembic ecosystem. [1] https://github.com/amacneil/dbmate

Yep, something like Flyway or Liquibase in the Java world has always made a lot of sense, though you also should be able to explicitly say when you do/don't want to run said migrations (e.g. run different profiles). Of course, recently i've also found Dbmate (https://github.com/amacneil/dbmate) which is a lightweight and language-agnostic tool that supports a variety of DBMSes (PostgreSQL, MySQL/MariaDB, SQLite and more recently even ClickHouse) and can be used for running SQL migration scripts regardless of what technologies that particular app uses (Java, .NET, Node, PHP and so on). Now, setting up the CI might be a little bit more difficult, but having one tool across all of the services in your architecture can be worth it!

If this interest you, both dbmate [https://github.com/amacneil/dbmate] and golang/migrate [https://github.com/golang-migrate/migrate] are in very similar spaces---and can be provided as a single executable.

I agree with most people here. Definitely keep it simple. With that said, I run a semi resource intensive operation, so I've invested a bit into dev ops to keep our costs down. My setup is currently on AWS, primarily using ECS, RDS, Elasticache. Infra is managed via Terraform. I felt ECS was a nice balance vs K8s, as it's much simpler to manage, while getting the benefit of maximizing resource utilization. For CI / deployment, I use Github Actions to build an image and push, then start a rolling refresh to update the containers to the new version. It was pretty easy to setup. On DBs, RDS handles all the backups and maintenance. For migrations, I use https://github.com/amacneil/dbmate. Happy to answer any other questions you have, as I've learned a lot through trial and error.

Dbmate: https://github.com/amacneil/dbmate I found most database migration tools tried to be too smart (I'm not a fan of automatic migrations), were too tightly integrated with specific languages or frameworks, or didn't support basic developer workflow pleasantries such as dropping and recreating the database. I created a simple single binary migration tool that runs migrations (sql files), and keeps track of applied migrations in a table (inspired by Active Record migrations). Dbmate works with any language or framework, and generally tries to be useful while staying out of your way. Comparison against other tools here: https://github.com/amacneil/dbmate#alternatives

My team recently evaluated 37 database schema migration tools and we ended up choosing Dbmate, which is written in Go but can be used with any framework or language, including Rust. https://github.com/amacneil/dbmate

Flyway works fine for us too and is super simple to use, simple sql files... Nothing wrong with flyway. Note: We ony implemented rolling forward. Rolling back is hard. Esepcially for DROPing collumns. Btw https://github.com/amacneil/dbmate also seems cool.

I recommend checking out Dbmate as an alternative to Liquibase. I've previously used Liquibase, Flyway, Django's built in migration tools, and others, but Dbmate is my favorite tool for migrations by far due to its simplicity. It's completely language agnostic, super easy to embed into any deployment pipelines, and works using regular SQL (so you don't need to learn a new syntax). https://github.com/amacneil/dbmate

I'd put sqitch in that list just before dbmate. https://github.com/flyway/flyway https://github.com/liquibase/liquibase https://github.com/sqitchers/sqitch https://github.com/amacneil/dbmate

We've been using it for about a year in production across four projects and it's been a major boon for productivity because it allows our frontend developers to do simple backend features on their own. I think there are a lot of use cases where it would be a great fit for many projects and there are I'm sure a lot of companies that could save a lot of time by using it as part of their toolkit. Also it doesn't have to be all or nothing. For example, there might be an API endpoint needed where there are multiple mutations needed inside a transaction but we just route between that custom backend and postgrest using nginx. Other things we use: - dbmate for migrations: https://github.com/amacneil/dbmate - postgrest-client, a library I developed for constructing type-safe queries to Postgrest in Elm: https://github.com/alex-tan/postgrest-client

I've been using Dbmate for a few years now across our entire stack. It does the job perfectly. https://github.com/amacneil/dbmate