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.
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
Disclaimer: I'm involved with Atlas.
Good may be an alternative to Alembic, so we can get rid of the Python requirement =)
(Checks it out...)
Ahh, this is also Alembic.
I use goose[1] for db migrations.
[1]: https://github.com/pressly/goose
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
I've been looking at Atlas as an alternative to Alembic recently, it seems nice, but I'm wary of the non open source features.
https://github.com/ariga/atlas
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.
[1] https://github.com/dbcli/mycli
If it's a declarative tool, then "yes, thanks".
But if it's not, then "no thanks", I already have my native CLI tools bundled with my RDBMS.
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.
> This will create a config file for local and prod databases using sqlite for local and postgres for prod.
Hold on, people actually do that? I thought it's trivial to run your database in a container locally.
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.
You dont even need to look into advanced features; sqlite does not support ILIKE.
To be fair, most databases don't, since ILIKE is not in the SQL standard.
I mostly used ORM to manage db changes.
Yhhjkj