>>davidk+kzc
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...

>>dewey+sCc
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...

>>Palmik+(OP)
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.

>>wener+nSc
Ariel from Atlas here. This setup is pretty common (declarative locally, versioned in real environments). Since migrations are auto-generated (by Atlas) on PRs, most developers never touch the versioned workflow directly.

See: https://atlasgo.io/concepts/declarative-vs-versioned#combini..., and https://github.com/ariga/atlas-action?tab=readme-ov-file#ari...

>>Palmik+(OP)
This post from 2022 is the one I keep going back to

https://david.rothlis.net/declarative-schema-migration-for-s...

>>Palmik+(OP)
If someone is looking for a more comprehensive coverage for Postgres, please check out https://github.com/pgschema/pgschema.

I built this last summer and thought it was fairly complete. And it turns out I was naive. 6 months later, I have resolved ~100 issues reported by the users.

>>stemch+9Xc
From the docs: https://github.com/sqldef/sqldef?tab=readme-ov-file#renaming...

You tell it what’s being renamed with a special comment.

>>netgho+Iad
For both data migrations and renames, there isn't really a one-size-fits-all solution. That's actually true when doing data changes or renames in imperative (incremental) migrations tools too; they just don't acknowledge it, but at scale these operations aren't really viable. They inherently involve careful coordination alongside application deploys, which cannot be timed to occur at the exact same moment as the migration completion, and you need to prevent risk of user-facing errors or data corruption from intermediate/inconsistent state.

With row data migrations on large tables, there's also risk of long/slow transactions destroying prod DB performance due to MVCC impact (pile-up of old row versions). So at minimum you need to break up a large data change into smaller chunked transactions, and have application logic to account for these migrations being ongoing in the background in a non-atomic fashion.

That all said, to answer from a mechanical standpoint of "how do companies using declarative schema management also handle data migrations or renames":

At large scale, companies tend to implement custom/in-house data migration frameworks. Or for renames, they're often just outright banned, at least for any table with user-facing impact.

At smaller scale, yeah you can just pair a declarative tool for schema changes with an imperative migration tool for non-schema changes. They aren't really mutually exclusive. Some larger schema management systems handle both / multiple paradigms.

For MySQL/MariaDB with Skeema in particular, a few smaller-scale data migration approaches are discussed in a separate post, https://www.skeema.io/blog/2024/07/23/data-migrations-impera...

>>Palmik+(OP)
This is great! It would be a cool feature to integrate with SQG. there you currently need to provide the sequence of migration steps to get the full schema.

https://github.com/sqg-dev/sqg/