If you are using SQL Server, then SQL Server Database Projects are an amazing tool to work with. I found them to generate high-quality migration scripts and it makes it easy to diff against an existing database.
ORMs are good up until the point you need to include SQL Views, Stored Procedures, Functions, User-defined Types… which is usually the point the ORM abstractions begin to crack (and every SQL Server database I use include them).
For PostgreSQL I usually hand-write the scripts, because it is easier, than fighting against an ORM.
I heard the Redgate tooling is also great to work with, but I’ve never used it personally.
Good point regarding ORMs - that was one of the main problems I wanted to tackle when we built Atlas (https://atlasgo.io). We added support for reading ORM definitions directly, then let you extend the "base schema" defined in them. For example, you can define your models in SQLAlchemy, EF Core, Ent, or others as a partial schema, and then extend it with functions, views, and additional objects.
From there, Atlas handles diffing, planning, and execution. This is similar to importing modules in TF, but for database schemas in Atlas. See this example: https://atlasgo.io/guides/orms/sqlalchemy
Goose is great, been using it for many years and is my goto db schema manager.
Love how you can write you migrations in go using goose and mix in raw sql migrations as well. Allows for great flexibility when doing complicated migrations and enables writing unit tests for migrations with regular go test
If you happen to be using MSSQL or Postgres, the Redgate tools are a game changer for schema management. I was a big fan of using things like EF and custom code to handle schema migrations until I tried SQL Compare. These tools make normalizing a large number of instances significantly easier. If you've got a multi tenant setup where everything should have the same schema, you could fix the whole fleet in an afternoon.
For SQLite, I still vastly prefer using custom code to run migrations. Something about the ownership model makes manual external tooling feel inappropriate.
Tangential, but anyone can suggest their favorite SQL client? Many years ago on Windows I enjoyed HeidiSQL, and while you can kind of use it with wine, it doesn't make a stable impression to me.
Recently I found mycli[1], which seems slightly better than the official mariadb cli client, but still a bit cumbersome.
I like when projects like this mention other projects. "Phinx" (PHP) has been a breeze to work with for database migrations for years now, and handles more than my team needs. Meanwhile, some time ago a colleague in other job was raging they didn't have migrations but a chaos of DBs in their environments.
This one company I worked for created like 5 databases for every client they had.
So we had hundreds of databases. And no migrations or way to keep them in sync.
One day I got fed up and ran some statistical analysis on all the databases to find inconsistencies and figure out what the most popular schemas were, because sometimes even when they had the same table and column names, the types were slightly different.
I don't recall if I managed to get them all in sync before I quit.
Especially if you use any of the features that make Postgres nice to work with (For example good jsonb handling) these are immediately different than on sqlite and then won't work for development. Don't think there's a good reason for not running the same DB in both environments.
ORMs are good up until the point you need to include SQL Views, Stored Procedures, Functions, User-defined Types… which is usually the point the ORM abstractions begin to crack (and every SQL Server database I use include them).
For PostgreSQL I usually hand-write the scripts, because it is easier, than fighting against an ORM.
I heard the Redgate tooling is also great to work with, but I’ve never used it personally.
From there, Atlas handles diffing, planning, and execution. This is similar to importing modules in TF, but for database schemas in Atlas. See this example: https://atlasgo.io/guides/orms/sqlalchemy
Disclaimer: I'm involved with Atlas.
(Checks it out...)
Ahh, this is also Alembic.
[1]: https://github.com/pressly/goose
Love how you can write you migrations in go using goose and mix in raw sql migrations as well. Allows for great flexibility when doing complicated migrations and enables writing unit tests for migrations with regular go test
https://github.com/ariga/atlas
For SQLite, I still vastly prefer using custom code to run migrations. Something about the ownership model makes manual external tooling feel inappropriate.
Recently I found mycli[1], which seems slightly better than the official mariadb cli client, but still a bit cumbersome.
[1] https://github.com/dbcli/mycli
But if it's not, then "no thanks", I already have my native CLI tools bundled with my RDBMS.
So we had hundreds of databases. And no migrations or way to keep them in sync.
One day I got fed up and ran some statistical analysis on all the databases to find inconsistencies and figure out what the most popular schemas were, because sometimes even when they had the same table and column names, the types were slightly different.
I don't recall if I managed to get them all in sync before I quit.
Hold on, people actually do that? I thought it's trivial to run your database in a container locally.