I sometimes suspect a folder full of sql scripts would be better than black box migrations (which are a folder full of sql scripts or similar anyway).

Migrations are great, until they aren't.

Sometimes if you actually manually do the thing you know how it works better and can adjust. I suppose the drawback to that is everyone else doesn't.

Another option is a schema management tool that doesn't rely on migrations at all. I'm the author of one, Skeema [1], for MySQL/MariaDB. Others include migra [2] for Postgres, and sqldef [3] for MySQL or Postgres.

These all use a declarative approach [4], where the user just specifies the desired new state, by adding or changing the set of CREATE statements. The tool knows how to run the correct DDL to reach that new state.

[1] https://github.com/skeema/skeema

[2] https://github.com/djrobstep/migra

[3] https://github.com/k0kubun/sqldef

[4] https://www.skeema.io/blog/2019/01/18/declarative/

How Interesting. Thanks!

Yeah I've thought the exact same thing before and I've actually tried both.

The clear difference is that migrations are easily reversible. Good migration libraries can also reverse data migrations.

A folder of SQL scripts was very refreshing, but no way could I use that system in a production environment. There's just too many edge cases to keep in your head.

Manually migrating has the issue of testability and hit by the bus. I don't believe every migration needs a roll back, sometimes the better strategy is restore from a snapshot/backup.

I personally tend to prefer migrate[1] as it doesn't require java.

[1]: https://github.com/golang-migrate/migrate

Java seems ideal as it will be completely cross platform and you can add / update the database drivers after the fact.

Migrate has a different binary for each platform and from what I can see, the support for each database is compiled in, so just to update a driver you are going to be maintaining that binary all over the place.

Migrate is awesome! My only issue: Since it uses bare SQL for the migrations, you have to do some lifting if you need to support multiple RDBMS with different DDLs. I have some regexes in my project to translate Postgres DDL into SQLite DDL for my test suite.

Why would you not test your code with the same RDBMS you will have in production? Especially if it’s something as easy to install as Postgres? It seems like begging for trouble...

If you're using an ORM, it can be an easy way to ensure that no vendor specific stuff sneaks in. Also, until recently it was difficult to run, e.g., SQL Server in Docker, so Postgres might be easier to test with. Agreed SQLite is a weird choice, but not all integration tests are robust enough to test characteristics of the underlying db, i.e. focus on business logic not performance.

I have several dozen tests that run in completely separate DBs. With SQLite, the test suite runs through in 1 second:

  $ time go test github.com/sapcc/limes/... | grep -v 'no test files'
  ok      github.com/sapcc/limes  0.004s
  ok      github.com/sapcc/limes/pkg/api  0.515s
  ok      github.com/sapcc/limes/pkg/collector    0.449s
  ok      github.com/sapcc/limes/pkg/core 0.006s

I doubt that you can setup and tear down several dozen Postgreses in that time.

Flyway doesn't require Java. The CLI ships with a private JRE as well as drivers for most databases out of the box. Simply untar and run.