I use https://github.com/ariga/atlas for this, migration based and schema base both has there good and bad side, I prefer both even in one project, schema based can make dev faster, eaiser, migration based make feel reliable.
Also means I can stop with my hobby project that was supposed to do the same. Wasn't far along and haven't worked on it in months anyway.
So I'll spend my time on another hobby project then that also solves something that is already solved 100 times over, but I don't like the other solutions (simple log monitoring including systemd and sending error emails if something is found).
Looks like this is only useful for empty databases. Which severely limits possible use cases.
Schema management is only a small part of the problem, and I don’t think this tool handles data migrations. E.g. if I reshape a JSONB column into something more structured, I don’t think it would be able to handle that. Or if I drop a column the backwards migration it generates ADD COLUMN … NOT NULL, which is obviously unusable if the table has any data in it already.
I'm always in the market for new sql tooling, but I'm wondering what's the use case there?
Isn't it much quicker to write a one line migration vs copying the DDL, then adapting it to the desired state and then getting getting the migrations from that tool? Or am I misunderstanding something?
Be sure to look at the actual sqldef command-line tool, not the trivial copy-and-paste demo on their website. Declarative schema management is best used combined with a Git repo.
In the big picture, declarative schema management has lots of advantages around avoiding/solving schema drift, either between environments (staging vs prod) or between shards in a sharded setup (among thousands of shards, one had a master failure at an inopportune time).
It's also much more readable to have the "end state" in your repo at all times, rather than a sequence of ALTERs.
Yes, we've used skeema for this for many years, and it is just plain lovely. Putting into source control your desired end state is so much more intuitive and understandable than accumulating migrations. In a way it's like the difference between jQuery and React -- you just say how you want it to look like in the end, and the computer does the work to figure out how to make it so.
Personally I've always called this style "declarative schema management" since the input declares the desired state, and the tool figures out how to transition the database to that state.
sqldef is really cool for supporting many database dialects. I'm the author of Skeema [1] which includes a lot of functionality that sqldef lacks, but at the cost of being 100% MySQL/MariaDB-specific. Some other DB-specific options in this space include Stripe's pg-schema-diff [2], results [3], stb-tester's migrator for sqlite [4], among many others over the years.
The more comprehensive solutions from ByteBase, Atlas, Liquibase, etc tend to support multiple databases and multiple paradigms.
And then over in Typescript ORM world, the migrators in Prisma and Drizzle support a "db push" declarative concept. (fwiw, I originated that paradigm; Prisma directly copied several aspects of `skeema push`, and then Drizzle copied Prisma. But ironically, if I ever complete my early-stage next-gen tool, it uses a different deployment paradigm.)
> Personally I've always called this style "declarative schema management" since the input declares the desired state, and the tool figures out how to transition the database to that state.
Personally I've called it a mistake, since there's no way a tool can infer what happened based on that information.
MS' Sql Server Data Tools is such an abominable garbage fire that I have no interest in these kind of tools. Besides being a buggy mess, it's very often insufficient - you end up having to maintain migrations anyways because often you have to inject data into new tables or columns, or rename columns, etc.
Anyone with real-world usage of this one vs atlas. I've used atlas but it seems to be moving away from FOSS (paywalled-fearures). Its also struggled with some migrations that were a bit more than not trivial (like trying to play changes in the wrong order)
Looks pretty cool. Lately I've been using ChatGPT to generate SQLite schema migrations and it works shockingly well. I give it the original schema and new schema and it generates the statements including caveats and gotchas to watch out for.
I use https://github.com/ariga/atlas for this, migration based and schema base both has there good and bad side, I prefer both even in one project, schema based can make dev faster, eaiser, migration based make feel reliable.
This is very cool!
Also means I can stop with my hobby project that was supposed to do the same. Wasn't far along and haven't worked on it in months anyway.
So I'll spend my time on another hobby project then that also solves something that is already solved 100 times over, but I don't like the other solutions (simple log monitoring including systemd and sending error emails if something is found).
Looks like this is only useful for empty databases. Which severely limits possible use cases.
Schema management is only a small part of the problem, and I don’t think this tool handles data migrations. E.g. if I reshape a JSONB column into something more structured, I don’t think it would be able to handle that. Or if I drop a column the backwards migration it generates ADD COLUMN … NOT NULL, which is obviously unusable if the table has any data in it already.
I renamed a column and it added a new one.
I'm always in the market for new sql tooling, but I'm wondering what's the use case there?
Isn't it much quicker to write a one line migration vs copying the DDL, then adapting it to the desired state and then getting getting the migrations from that tool? Or am I misunderstanding something?
Be sure to look at the actual sqldef command-line tool, not the trivial copy-and-paste demo on their website. Declarative schema management is best used combined with a Git repo.
In the big picture, declarative schema management has lots of advantages around avoiding/solving schema drift, either between environments (staging vs prod) or between shards in a sharded setup (among thousands of shards, one had a master failure at an inopportune time).
It's also much more readable to have the "end state" in your repo at all times, rather than a sequence of ALTERs.
There are a bunch of other advantages; I have an old post about this topic here: https://www.skeema.io/blog/2019/01/18/declarative/
It's also quite essential when maintaining nontrivial stored procedures. Doing that with imperative migrations is a gateway to hell. https://www.skeema.io/blog/2023/10/24/stored-proc-deployment...
Yes, we've used skeema for this for many years, and it is just plain lovely. Putting into source control your desired end state is so much more intuitive and understandable than accumulating migrations. In a way it's like the difference between jQuery and React -- you just say how you want it to look like in the end, and the computer does the work to figure out how to make it so.
Wow. I saved the link for emergencies. And I sent it to all my team. It is like a diff tool but to advance the schema.
Personally I've always called this style "declarative schema management" since the input declares the desired state, and the tool figures out how to transition the database to that state.
sqldef is really cool for supporting many database dialects. I'm the author of Skeema [1] which includes a lot of functionality that sqldef lacks, but at the cost of being 100% MySQL/MariaDB-specific. Some other DB-specific options in this space include Stripe's pg-schema-diff [2], results [3], stb-tester's migrator for sqlite [4], among many others over the years.
The more comprehensive solutions from ByteBase, Atlas, Liquibase, etc tend to support multiple databases and multiple paradigms.
And then over in Typescript ORM world, the migrators in Prisma and Drizzle support a "db push" declarative concept. (fwiw, I originated that paradigm; Prisma directly copied several aspects of `skeema push`, and then Drizzle copied Prisma. But ironically, if I ever complete my early-stage next-gen tool, it uses a different deployment paradigm.)
[1] https://github.com/skeema/skeema/
[2] https://github.com/stripe/pg-schema-diff
[3] https://github.com/djrobstep/results
[4] https://david.rothlis.net/declarative-schema-migration-for-s...
> Personally I've always called this style "declarative schema management" since the input declares the desired state, and the tool figures out how to transition the database to that state.
Personally I've called it a mistake, since there's no way a tool can infer what happened based on that information.
I like how Drizzle provides several options for the migrations.
1. DB is source of truth, generate TS from DB 2. TS to DB direct sync, no migration files 3. TS source, Drizzle generates and applies SQL 4. TS source, Drizzle generates SQL, runtime application 5. TS source, Drizzle generates SQL, manual application 6. TS source, Drizzle outputs SQL, Atlas application
MS' Sql Server Data Tools is such an abominable garbage fire that I have no interest in these kind of tools. Besides being a buggy mess, it's very often insufficient - you end up having to maintain migrations anyways because often you have to inject data into new tables or columns, or rename columns, etc.
Looks great! Could this work with duckdb?
Anyone with real-world usage of this one vs atlas. I've used atlas but it seems to be moving away from FOSS (paywalled-fearures). Its also struggled with some migrations that were a bit more than not trivial (like trying to play changes in the wrong order)
Looks pretty cool. Lately I've been using ChatGPT to generate SQLite schema migrations and it works shockingly well. I give it the original schema and new schema and it generates the statements including caveats and gotchas to watch out for.