10 ms·

Declarative Schemas for simpler database management

81 points by kiwicopple 2y ago

kiwicopple 2y ago
Combining this with the Postgres Language Server that was released this week, you can now execute statements directly within your IDE: https://x.com/psteinroe/status/1907803477939527728 https://x.com/psteinroe/status/1907803477939527728
xyzzy_plugh 2y ago
This is exactly backwards. You should have declarative schemas but inferring migrations is crazy. Only pain will follow. Instead, I am a fan of doing both: either committing the resulting schema of a migration, or hand writing it aside the migration. Then have tests to ensure the database schema matches the expected schema after a migration. Generating these artifacts is fine, but in TFA's case there is no chance I wouldn't inspect and possibly modify the generated "diff" migration. It's significantly easier to go the other way: write the migration and show me the resulting schema diff.
- skybrian 2y ago
  It seems like generating the diffs from the schema's version history is equivalent to doing it the opposite way, provided that each diff is tested to make sure the database upgrade works. Not all diffs will correspond to feasible database upgrades, so some patches would have to be rejected.
  - pounderstanding 2y ago
    Migrations give more control. Alter table foo add column bar; Update foo set bar=baz; Alter table foo modify column bar NOT NULL;
    - skybrian 2y ago
      True. Perhaps annotating the schema would help for simple things?
  - bootsmann 2y ago
    Not all migrations are as simple as adding and removing columns.
- williamdclt 2y ago
  Completely agree. When writing a migration, the resulting schema is usually much, much less important than the characteristics of the migration itself. When I review a migration, my first question isn’t “is this the right schema” but “is this migration going to bring downtime”. I’d much rather a smooth migration to an incorrect schema than having a critical table locked for minutes/hours. I think that updates of stateful components should be imperative (explicit migrations), not declarative (implicit migrations). For example I don’t think Terraform is great tool to manage RDS: it doesn’t tell you the consequences of changing an attribute (database restart or other downtime-inducing stuff), I’d much rather I had to explicitly say how to get from state A to state B. Similarly, I don’t think SQL migrations are perfect: they’re still declarative, you still need implicit knowledge to know if a migration will take a lock and what will be the consequences. I’d much rather have to code “take explicit lock; alter table xxx;”. This tool probably allows editing migrations, but I don’t think it’s a step in the right direction. Maybe it’s a step towards databases being much better at migrations (so that we can rely on never having downtime), but even then I think it’ll get worse before it gets better
  - sweatybridge 2y ago
    > it doesn’t tell you the consequences of changing an attribute (database restart or other downtime-inducing stuff) Modern diff tools are designed to provide better guardrails in these situations. For eg, pg-schema-diff [0] tries to generate zero downtime migrations by using lock-free migrations and warns you about potentially hazardous migrations. I think it's good direction to bake these best practices into the tooling itself, rather than relying purely on the experiences of engineers. [0] https://github.com/stripe/pg-schema-diff https://github.com/stripe/pg-schema-diff
    - evanelias 2y ago
      This is exactly the key point. Declarative schema management is typically better at catching hazardous operations, because declarative schema management tools inherently require the ability to introspect your desired CREATE definitions and also introspect the current database state. Once a tool has those abilities, adding linters and destructive-action guardrails is much easier. Especially when compared to a traditional migration tool, which often has no underlying understanding of the ALTER TABLE operations being requested.
- evanelias 2y ago
  As a concept, declarative schema management isn't crazy at all. Several thousand companies use this approach, including some huge names like Google, Meta, and GitHub, but many smaller companies too. When implemented well, with appropriate guardrails and linters, it's perfectly safe. And it has many benefits over imperative schema migrations, such as a substantially better Git versioning flow, and ability to synchronize environments / solve schema drift natively. The only major conceptual downsides are the inability to handle row data migrations, and inability to handle renames. These can be major hurdles for some smaller companies, but are more irrelevant at companies with large DB infrastructure, who require special-case handling for those operations anyway. In other words, if you have large tables, row data migrations are already substantially more complex than running a single UPDATE statement, and you can't do them using a traditional imperative migration tool anyway. > there is no chance I wouldn't inspect and possibly modify the generated "diff" migration Of course, you're generally supposed to do that with these tools. Database changes should always be reviewed carefully. Same with non-database-related infrastructure-as-code tools.
  - kiwicopple 2y ago
    > there is no chance I wouldn't inspect and possibly modify the generated "diff" migration > Of course, you're generally supposed to do that with these tools this seems to be the crux of the comments - we'll try to make it much clearer than declarative schemas don't skip any migrations/review process, they just provide another way of generating the migrations (note: you can also generate them directly from the database using the CLI)
    - nextaccountic 2y ago
      Hey, are you using some tool like the unmaintained migra https://github.com/djrobstep/migra https://github.com/djrobstep/migra (perhaps using this tool https://github.com/blainehansen/postgres_migrator https://github.com/blainehansen/postgres_migrator) or pg-schema-diff https://github.com/stripe/pg-schema-diff https://github.com/stripe/pg-schema-diff or maybe this other unrelated pg-schema-diff https://github.com/zombodb/pg-schema-diff https://github.com/zombodb/pg-schema-diff anything like it? Is it open source? I mean, I see you say > We then use a schema diff tool, like migra, to figure out the necessary updates to views and functions when generating the migration file. But "like migra" is very nonspecific. I guess you are not using migra itself
- layer8 2y ago
  Yes. In particular, migrations expressed as SQL statements are strictly more powerful than a diff of two schemas, so there are situations where you can’t infer the former from the latter (but you can always infer the latter from the former). I also object to the use of “declarative” here. Either we are talking about the schema as present in the database, then it’s neither declarative nor non-declarative, it’s just whatever is in the database. Or we are talking about a schema definition, and then I really don’t know what a non-declarative schema definition would look like, in contrast to what is called “declarative” here. Thirdly, the traditional “declarative” SQL schema definition is really a series of imperative SQL statements, so arguably not declarative. What they seem to mean is a minimal sequence of statements that results in the desired schema, as opposed to a longer history of schema-altering statements. However, the minimal version is technically still a series of schema-altering statements, starting from (presumably) an empty schema.
  - evanelias 2y ago
    "Declarative" is the correct word, as the input to the tool is a desired-state set of CREATE statements, and the tool figures out what DDL is actually necessary to make the target DB reach that desired state. In other words, the engineer is declaring "make my schema look like this" and the tool makes that happen. That's the key definition of a declarative system, consistent with how this is described in CS classes. Meanwhile traditional migration tools are "imperative", as the engineer must tell the tool exactly what operations (e.g. ALTER) to be run.
- perrygeo 2y ago
  > You should have declarative schemas but inferring migrations is crazy. Only pain will follow. Inferring migrations isn't crazy. Automagically applying those migrations without review is crazy.
bartvk 2y ago
So to summarize. In the old situation, you write CREATE TABLE statement at the start of the project. And when you add a feature, you have to write an ALTER TABLE script. In this new situation, you just change the CREATE TABLE script. And Supabase uses migra to figure out the difference and it automatically alters the table. What's interesting is that in your SQL code, there's no longer any difference between creating a new database, and updating an existing database.
- infogulch 2y ago
  I've used this for Microsoft SQL Server and SQL Database Projects. It's basically as you say: write it as if creating a new database, then deploy it in CI where it does a diff on the live database to come up with the actual migration strategy on the fly. If you're clever you add a manual review stage in the pipeline and have the db engineers approve the generated migration script before deployment is completed automatically. https://learn.microsoft.com/en-us/sql/tools/sql-database-projects/tutorials/create-deploy-sql-project?view=sql-server-ver16&pivots=sq1-visual-studio https://learn.microsoft.com/en-us/sql/tools/sql-database-pro... I helped set this up in a fortune 500 company a few years ago. They were using a team of db engineers to execute manually written change scripts, with manual reviews, control processes, and deployment schedules. You'd be lucky if you got your schema change to prod in a month. When they started integrating this tool on some trial candidates they found SO many inconsistencies between environments: server settings differences, extraneous or missing indexes, vestigial "temp" tables created during previous migrations, enum tables that should be static with extra or missing rows, etc, etc. All the environment differences meant that deployments had to be manual in the past. Once they got through the initial pain of syncing up the environments the whole department got way more efficient.
  - rjbwork 2y ago
    >If you're clever you add a manual review stage in the pipeline and have the db engineers approve the generated migration script before deployment is completed automatically. This is how I've set it up at my current employer. It works well. We modeled it after the Terraform Plan/Apply steps, and double check that the script generated by the "apply" step matches the script generated by the "plan" step, since these can occur at significant temporal distances, and fail it if not, just so that we can be sure what we've read and approved matches what gets executed.
ucarion 2y ago
Sorry if asked and answered: can you hand-edit the generated migrations? Like, what if I want to do a create index concurrently or something?
- kiwicopple 2y ago
  yes, you can edit the migrations. The steps are for adding a new column, for example: 1/ Add a new column to the declarative file 2/ Generate a new migration: `supabase db diff -f my_new_migration` 3/ Review/edit the generated migration Docs are here: https://supabase.com/docs/guides/local-development/declarative-database-schemas#updating-your-schema https://supabase.com/docs/guides/local-development/declarati...
geocar 2y ago
You're going to regret this. The thing you need to be doing is testing your migrations, and some dumbass on your team is going to generate the migration during CI and load it into your database as a merge step, and you won't realise what a mistake this was until possibly years later. The good news, is you might be able to pay someone an enormous amount of money to unfuck things. Not good for you, I mean, obviously. Good for whoever you just bought a car.
- mylons 2y ago
  we at tahoe labs dot io are here for you in that case.
joshAg 2y ago
We built something similar for the managed DB we use, but i think it's a mistake to autogenerate the migration scripts instead of autogenerating the schema from the migration. Things like changing an enum, adding a nonnull column that shouldn't have a default to an existing table that already has data in it, and migrating data from one representation to another (eg, 'oh hey, we definitely shouldn't have made our users table have an fname and an lname field. let's change to full_name and preferred_name') are easily done in a migration script but hard, if not impossible, to infer from just schema changes.
neutralino1 2y ago
It seems to me Rails has been doing this but better for years. It definitely keeps atomic and historical migrations, but also maintains a schema.sql file that can be loaded as a one-off (e.g. for mock DBs in tests).
- kiwicopple 2y ago
  > doing this but better I'm curious what makes it better than the approach in the blog? If it's this: > also maintains a schema.sql file that can be loaded as a one-off That's exactly what the declarative files provide
xaas 2y ago
I use ZenStack. In future releases they are moving away from supabase…
wmanley 2y ago
See also: “Simple declarative schema migration for SQLite” https://david.rothlis.net/declarative-schema-migration-for-sqlite/ https://david.rothlis.net/declarative-schema-migration-for-s... Discussed previously: https://news.ycombinator.com/item?id=31249823 https://news.ycombinator.com/item?id=31249823 Disclosure: I am the co author of that article
mylons 2y ago
am i missing something? what does this offer over raw sql? it honestly looks very similar
- kiwicopple 2y ago
  It is raw sql. The update here is more of a "workflow" change. In the past we only offered tools to create a sql migration to make a change to your database. Now you can write the state of your database into files, then the migration is generated for you. This is very similar to something like Rails/Phoenix/whatever, where you write your models and then it generates a change. The difference is that here you write your models in raw SQL, rather than an ORM
webster451 2y ago
I think we are getting close to the peak of "declarative"—or rather, I hope we are near the peak. In my experience, declarative APIs are very powerful abstractions for specific cases where finding the path to the declared state is better left to a machine. This is seldom the case - in most cases, offering the programmer control over the changes leads to better behaviors. Kubernetes and IaC tools lead the way to a declarative state of infrastructure and these add a ton of value. But, they were also incredibly hard to build - it took many years before Kubernetes eventing and control loop abstracts were rock solid. Most CRD-backed implementations suffer from tons and tons of bugs, and most CRDs are not declarative - they abstract away an imperative operation! I guess this is nothing new - "anything in excess is bad". Anyways, I think an imperative approach offers much higher control and predictability at a lower cost. The world inherently is imperative.
- carlmr 2y ago
  >In my experience, declarative APIs are very powerful abstractions for specific cases where finding the path to the declared state is better left to a machine. This is seldom the case I've been waiting for this top comment for longer than you can imagine. The declarative madness has always bothered me. Sometimes it's easier to maintain when you see the process. And harder to declare the final state. It might look neat, but maintainability beats neatness every day.
- Onavo 2y ago
  I mean they just reinvented Prisma and Django
  - evanelias 2y ago
    Yes, although the article isn't claiming to have invented declarative schema management. They're just saying it is now available as a feature in Supabase. (Personally I think that's great!) Regarding prior art: Django migrations are indeed declarative, and were very early in this space. But they're tied to Python model definitions in the ORM, which is a bit more of a special-case than the native SQL CREATE based approach described here. As for Prisma Migrate, they directly copied several innovations from my tool Skeema [1] which has been available since 2016, so they can be equally accused of "reinventing" things :) Not that I invented pure-SQL declarative schema management either, by any stretch. I was largely inspired by the workflow at Facebook, who adopted declarative table management company-wide back in ~2012. FB found that having a declarative reconciliation loop is an operational necessity with a massively sharded system, given some hardware just dies every day. And centralized SQL-based schema management is advantageous when applications are written in many different programming languages. [1] https://github.com/skeema/skeema https://github.com/skeema/skeema
Guillaume86 2y ago
Could this be moved into a standalone CLI tool? Is there anything supabase specific about it? I've always wanted SSDT SQL projects for postgres (SSDT is MS declarative schema management solution for SQL Server). SSDT can also sync db projects (nicely organized DDL .sql files representing the schema) and databases (one way or the other), with the IDE support you can do stuff like "find all references" on a column or any other DB object, and build the project to check for errors. Linting the schema becomes possible, etc I have a hard time when I have to go back to imperative schema management...
- evanelias 2y ago
  There are a few stand-alone declarative Postgres tools available, including Stripe's pg-schema-diff [1], Tusker [2] which wraps Migra [3], and sqldef [4] which supports multiple DBMS. For sake of completeness for users of other databases: for SQLite check out stb-tester's migrator [5], and last but not least for MySQL/MariaDB there's my tool Skeema [6]. [1] https://github.com/stripe/pg-schema-diff https://github.com/stripe/pg-schema-diff [2] https://github.com/bikeshedder/tusker https://github.com/bikeshedder/tusker [3] https://github.com/djrobstep/migra https://github.com/djrobstep/migra [4] https://github.com/sqldef/sqldef/ https://github.com/sqldef/sqldef/ [5] https://david.rothlis.net/declarative-schema-migration-for-sqlite/ https://david.rothlis.net/declarative-schema-migration-for-s... [6] https://github.com/skeema/skeema https://github.com/skeema/skeema
  - Guillaume86 2y ago
    Thanks for the list, on the commercial side there's also Regate Flyway which looks like a full multi-DB SSDT alternative. I wasn't aware of sqldef I will need to take a look. Something nice about SSDT is that when we export a DB to a SQL project (files), each DB object is neatly organized is its own file, and the tooling solve ordering and dependency cycles on its own, I'm not sure any OS tool currently do it like that (skeema maybe? but no PG).
    - evanelias 2y ago
      Flyway is primarily an imperative migration tool, rather than a declarative-first system. Their paid editions do have support for doing diffs ("state-based") and auto-generating migrations, but it's bolted-on to a workflow that was designed to be imperative-first. From what I can find, their public docs are rather lacking on what types of database objects and edge cases it supports. Skeema does indeed separate each DB object to its own file, and each logical schema to its own subdirectory. It also figures out how to correctly apply changes in a valid order, but yeah it's MySQL/MariaDB only. I think some of the declarative FOSS Postgres tools do attempt to provide a correct ordering, but from memory I'm not certain.
oulipo 2y ago
So I guess this is a bit the same as using something like Drizzle to define your tables and handle your migrations?
jackb4040 2y ago
>declarative >create_table Commands are not declarative. You want a single file with the state of your schema in it? We call that a schema file. It is strictly a negative to declare your schema in SQL, an imperative language. There is no way to guarantee this create_table will run safely, because it's not meant to ever be run.
- evanelias 2y ago
  > Commands are not declarative. The tool is using a declarative approach; the language is irrelevant. The key point is the input to the tool describes a desired state, and the tool figures out how the necessary operations to actually achieve that. This contrasts with the imperative approach to schema management, in which the user must explicitly define the steps to take. Some declarative schema management tools have the desired state be expressed in YAML or a custom DSL, while others use SQL; either way, they're still using a declarative approach. > There is no way to guarantee this create_table will run safely, because it's not meant to ever be run. It is meant to be run, though: any time you spin up a new environment, such as every CI run, or every new dev environment set up by an engineer. Additionally, some declarative schema management tools run the desired-state CREATEs in a temporary location as a core part of their operation. And they can also verify their generated ALTERs are correct, by running the original live-state CREATE in a temporary location, then running the generated ALTER, and verifying that the post-alter CREATE matches the desired state. > SQL, an imperative language SQL is a mix of imperative and declarative. SELECT statements are the canonical example of a declarative approach: they describe the set of data you want, not how to physically fetch it.

81 points by kiwicopple 2y ago

Combining this with the Postgres Language Server that was released this week, you can now execute statements directly within your IDE: https://x.com/psteinroe/status/1907803477939527728 https://x.com/psteinroe/status/1907803477939527728

xyzzy_plugh 2y ago

This is exactly backwards. You should have declarative schemas but inferring migrations is crazy. Only pain will follow. Instead, I am a fan of doing both: either committing the resulting schema of a migration, or hand writing it aside the migration. Then have tests to ensure the database schema matches the expected schema after a migration. Generating these artifacts is fine, but in TFA's case there is no chance I wouldn't inspect and possibly modify the generated "diff" migration. It's significantly easier to go the other way: write the migration and show me the resulting schema diff.

skybrian 2y ago
It seems like generating the diffs from the schema's version history is equivalent to doing it the opposite way, provided that each diff is tested to make sure the database upgrade works. Not all diffs will correspond to feasible database upgrades, so some patches would have to be rejected.
- pounderstanding 2y ago
  Migrations give more control. Alter table foo add column bar; Update foo set bar=baz; Alter table foo modify column bar NOT NULL;
  - skybrian 2y ago
    True. Perhaps annotating the schema would help for simple things?
- bootsmann 2y ago
  Not all migrations are as simple as adding and removing columns.
williamdclt 2y ago
Completely agree. When writing a migration, the resulting schema is usually much, much less important than the characteristics of the migration itself. When I review a migration, my first question isn’t “is this the right schema” but “is this migration going to bring downtime”. I’d much rather a smooth migration to an incorrect schema than having a critical table locked for minutes/hours. I think that updates of stateful components should be imperative (explicit migrations), not declarative (implicit migrations). For example I don’t think Terraform is great tool to manage RDS: it doesn’t tell you the consequences of changing an attribute (database restart or other downtime-inducing stuff), I’d much rather I had to explicitly say how to get from state A to state B. Similarly, I don’t think SQL migrations are perfect: they’re still declarative, you still need implicit knowledge to know if a migration will take a lock and what will be the consequences. I’d much rather have to code “take explicit lock; alter table xxx;”. This tool probably allows editing migrations, but I don’t think it’s a step in the right direction. Maybe it’s a step towards databases being much better at migrations (so that we can rely on never having downtime), but even then I think it’ll get worse before it gets better
- sweatybridge 2y ago
  > it doesn’t tell you the consequences of changing an attribute (database restart or other downtime-inducing stuff) Modern diff tools are designed to provide better guardrails in these situations. For eg, pg-schema-diff [0] tries to generate zero downtime migrations by using lock-free migrations and warns you about potentially hazardous migrations. I think it's good direction to bake these best practices into the tooling itself, rather than relying purely on the experiences of engineers. [0] https://github.com/stripe/pg-schema-diff https://github.com/stripe/pg-schema-diff
  - evanelias 2y ago
    This is exactly the key point. Declarative schema management is typically better at catching hazardous operations, because declarative schema management tools inherently require the ability to introspect your desired CREATE definitions and also introspect the current database state. Once a tool has those abilities, adding linters and destructive-action guardrails is much easier. Especially when compared to a traditional migration tool, which often has no underlying understanding of the ALTER TABLE operations being requested.
evanelias 2y ago
As a concept, declarative schema management isn't crazy at all. Several thousand companies use this approach, including some huge names like Google, Meta, and GitHub, but many smaller companies too. When implemented well, with appropriate guardrails and linters, it's perfectly safe. And it has many benefits over imperative schema migrations, such as a substantially better Git versioning flow, and ability to synchronize environments / solve schema drift natively. The only major conceptual downsides are the inability to handle row data migrations, and inability to handle renames. These can be major hurdles for some smaller companies, but are more irrelevant at companies with large DB infrastructure, who require special-case handling for those operations anyway. In other words, if you have large tables, row data migrations are already substantially more complex than running a single UPDATE statement, and you can't do them using a traditional imperative migration tool anyway. > there is no chance I wouldn't inspect and possibly modify the generated "diff" migration Of course, you're generally supposed to do that with these tools. Database changes should always be reviewed carefully. Same with non-database-related infrastructure-as-code tools.
- kiwicopple 2y ago
  > there is no chance I wouldn't inspect and possibly modify the generated "diff" migration > Of course, you're generally supposed to do that with these tools this seems to be the crux of the comments - we'll try to make it much clearer than declarative schemas don't skip any migrations/review process, they just provide another way of generating the migrations (note: you can also generate them directly from the database using the CLI)
  - nextaccountic 2y ago
    Hey, are you using some tool like the unmaintained migra https://github.com/djrobstep/migra https://github.com/djrobstep/migra (perhaps using this tool https://github.com/blainehansen/postgres_migrator https://github.com/blainehansen/postgres_migrator) or pg-schema-diff https://github.com/stripe/pg-schema-diff https://github.com/stripe/pg-schema-diff or maybe this other unrelated pg-schema-diff https://github.com/zombodb/pg-schema-diff https://github.com/zombodb/pg-schema-diff anything like it? Is it open source? I mean, I see you say > We then use a schema diff tool, like migra, to figure out the necessary updates to views and functions when generating the migration file. But "like migra" is very nonspecific. I guess you are not using migra itself
layer8 2y ago
Yes. In particular, migrations expressed as SQL statements are strictly more powerful than a diff of two schemas, so there are situations where you can’t infer the former from the latter (but you can always infer the latter from the former). I also object to the use of “declarative” here. Either we are talking about the schema as present in the database, then it’s neither declarative nor non-declarative, it’s just whatever is in the database. Or we are talking about a schema definition, and then I really don’t know what a non-declarative schema definition would look like, in contrast to what is called “declarative” here. Thirdly, the traditional “declarative” SQL schema definition is really a series of imperative SQL statements, so arguably not declarative. What they seem to mean is a minimal sequence of statements that results in the desired schema, as opposed to a longer history of schema-altering statements. However, the minimal version is technically still a series of schema-altering statements, starting from (presumably) an empty schema.
- evanelias 2y ago
  "Declarative" is the correct word, as the input to the tool is a desired-state set of CREATE statements, and the tool figures out what DDL is actually necessary to make the target DB reach that desired state. In other words, the engineer is declaring "make my schema look like this" and the tool makes that happen. That's the key definition of a declarative system, consistent with how this is described in CS classes. Meanwhile traditional migration tools are "imperative", as the engineer must tell the tool exactly what operations (e.g. ALTER) to be run.
perrygeo 2y ago
> You should have declarative schemas but inferring migrations is crazy. Only pain will follow. Inferring migrations isn't crazy. Automagically applying those migrations without review is crazy.

bartvk 2y ago

So to summarize. In the old situation, you write CREATE TABLE statement at the start of the project. And when you add a feature, you have to write an ALTER TABLE script. In this new situation, you just change the CREATE TABLE script. And Supabase uses migra to figure out the difference and it automatically alters the table. What's interesting is that in your SQL code, there's no longer any difference between creating a new database, and updating an existing database.

infogulch 2y ago
I've used this for Microsoft SQL Server and SQL Database Projects. It's basically as you say: write it as if creating a new database, then deploy it in CI where it does a diff on the live database to come up with the actual migration strategy on the fly. If you're clever you add a manual review stage in the pipeline and have the db engineers approve the generated migration script before deployment is completed automatically. https://learn.microsoft.com/en-us/sql/tools/sql-database-projects/tutorials/create-deploy-sql-project?view=sql-server-ver16&pivots=sq1-visual-studio https://learn.microsoft.com/en-us/sql/tools/sql-database-pro... I helped set this up in a fortune 500 company a few years ago. They were using a team of db engineers to execute manually written change scripts, with manual reviews, control processes, and deployment schedules. You'd be lucky if you got your schema change to prod in a month. When they started integrating this tool on some trial candidates they found SO many inconsistencies between environments: server settings differences, extraneous or missing indexes, vestigial "temp" tables created during previous migrations, enum tables that should be static with extra or missing rows, etc, etc. All the environment differences meant that deployments had to be manual in the past. Once they got through the initial pain of syncing up the environments the whole department got way more efficient.
- rjbwork 2y ago
  >If you're clever you add a manual review stage in the pipeline and have the db engineers approve the generated migration script before deployment is completed automatically. This is how I've set it up at my current employer. It works well. We modeled it after the Terraform Plan/Apply steps, and double check that the script generated by the "apply" step matches the script generated by the "plan" step, since these can occur at significant temporal distances, and fail it if not, just so that we can be sure what we've read and approved matches what gets executed.

ucarion 2y ago

Sorry if asked and answered: can you hand-edit the generated migrations? Like, what if I want to do a create index concurrently or something?

kiwicopple 2y ago
yes, you can edit the migrations. The steps are for adding a new column, for example: 1/ Add a new column to the declarative file 2/ Generate a new migration: `supabase db diff -f my_new_migration` 3/ Review/edit the generated migration Docs are here: https://supabase.com/docs/guides/local-development/declarative-database-schemas#updating-your-schema https://supabase.com/docs/guides/local-development/declarati...

geocar 2y ago

You're going to regret this. The thing you need to be doing is testing your migrations, and some dumbass on your team is going to generate the migration during CI and load it into your database as a merge step, and you won't realise what a mistake this was until possibly years later. The good news, is you might be able to pay someone an enormous amount of money to unfuck things. Not good for you, I mean, obviously. Good for whoever you just bought a car.

mylons 2y ago
we at tahoe labs dot io are here for you in that case.

joshAg 2y ago

We built something similar for the managed DB we use, but i think it's a mistake to autogenerate the migration scripts instead of autogenerating the schema from the migration. Things like changing an enum, adding a nonnull column that shouldn't have a default to an existing table that already has data in it, and migrating data from one representation to another (eg, 'oh hey, we definitely shouldn't have made our users table have an fname and an lname field. let's change to full_name and preferred_name') are easily done in a migration script but hard, if not impossible, to infer from just schema changes.

neutralino1 2y ago

It seems to me Rails has been doing this but better for years. It definitely keeps atomic and historical migrations, but also maintains a schema.sql file that can be loaded as a one-off (e.g. for mock DBs in tests).

kiwicopple 2y ago
> doing this but better I'm curious what makes it better than the approach in the blog? If it's this: > also maintains a schema.sql file that can be loaded as a one-off That's exactly what the declarative files provide

xaas 2y ago

I use ZenStack. In future releases they are moving away from supabase…

wmanley 2y ago

See also: “Simple declarative schema migration for SQLite” https://david.rothlis.net/declarative-schema-migration-for-sqlite/ https://david.rothlis.net/declarative-schema-migration-for-s... Discussed previously: https://news.ycombinator.com/item?id=31249823 https://news.ycombinator.com/item?id=31249823 Disclosure: I am the co author of that article

mylons 2y ago

am i missing something? what does this offer over raw sql? it honestly looks very similar

kiwicopple 2y ago
It is raw sql. The update here is more of a "workflow" change. In the past we only offered tools to create a sql migration to make a change to your database. Now you can write the state of your database into files, then the migration is generated for you. This is very similar to something like Rails/Phoenix/whatever, where you write your models and then it generates a change. The difference is that here you write your models in raw SQL, rather than an ORM

webster451 2y ago

I think we are getting close to the peak of "declarative"—or rather, I hope we are near the peak. In my experience, declarative APIs are very powerful abstractions for specific cases where finding the path to the declared state is better left to a machine. This is seldom the case - in most cases, offering the programmer control over the changes leads to better behaviors. Kubernetes and IaC tools lead the way to a declarative state of infrastructure and these add a ton of value. But, they were also incredibly hard to build - it took many years before Kubernetes eventing and control loop abstracts were rock solid. Most CRD-backed implementations suffer from tons and tons of bugs, and most CRDs are not declarative - they abstract away an imperative operation! I guess this is nothing new - "anything in excess is bad". Anyways, I think an imperative approach offers much higher control and predictability at a lower cost. The world inherently is imperative.

carlmr 2y ago
>In my experience, declarative APIs are very powerful abstractions for specific cases where finding the path to the declared state is better left to a machine. This is seldom the case I've been waiting for this top comment for longer than you can imagine. The declarative madness has always bothered me. Sometimes it's easier to maintain when you see the process. And harder to declare the final state. It might look neat, but maintainability beats neatness every day.
Onavo 2y ago
I mean they just reinvented Prisma and Django
- evanelias 2y ago
  Yes, although the article isn't claiming to have invented declarative schema management. They're just saying it is now available as a feature in Supabase. (Personally I think that's great!) Regarding prior art: Django migrations are indeed declarative, and were very early in this space. But they're tied to Python model definitions in the ORM, which is a bit more of a special-case than the native SQL CREATE based approach described here. As for Prisma Migrate, they directly copied several innovations from my tool Skeema [1] which has been available since 2016, so they can be equally accused of "reinventing" things :) Not that I invented pure-SQL declarative schema management either, by any stretch. I was largely inspired by the workflow at Facebook, who adopted declarative table management company-wide back in ~2012. FB found that having a declarative reconciliation loop is an operational necessity with a massively sharded system, given some hardware just dies every day. And centralized SQL-based schema management is advantageous when applications are written in many different programming languages. [1] https://github.com/skeema/skeema https://github.com/skeema/skeema

Guillaume86 2y ago

Could this be moved into a standalone CLI tool? Is there anything supabase specific about it? I've always wanted SSDT SQL projects for postgres (SSDT is MS declarative schema management solution for SQL Server). SSDT can also sync db projects (nicely organized DDL .sql files representing the schema) and databases (one way or the other), with the IDE support you can do stuff like "find all references" on a column or any other DB object, and build the project to check for errors. Linting the schema becomes possible, etc I have a hard time when I have to go back to imperative schema management...

evanelias 2y ago
There are a few stand-alone declarative Postgres tools available, including Stripe's pg-schema-diff [1], Tusker [2] which wraps Migra [3], and sqldef [4] which supports multiple DBMS. For sake of completeness for users of other databases: for SQLite check out stb-tester's migrator [5], and last but not least for MySQL/MariaDB there's my tool Skeema [6]. [1] https://github.com/stripe/pg-schema-diff https://github.com/stripe/pg-schema-diff [2] https://github.com/bikeshedder/tusker https://github.com/bikeshedder/tusker [3] https://github.com/djrobstep/migra https://github.com/djrobstep/migra [4] https://github.com/sqldef/sqldef/ https://github.com/sqldef/sqldef/ [5] https://david.rothlis.net/declarative-schema-migration-for-sqlite/ https://david.rothlis.net/declarative-schema-migration-for-s... [6] https://github.com/skeema/skeema https://github.com/skeema/skeema
- Guillaume86 2y ago
  Thanks for the list, on the commercial side there's also Regate Flyway which looks like a full multi-DB SSDT alternative. I wasn't aware of sqldef I will need to take a look. Something nice about SSDT is that when we export a DB to a SQL project (files), each DB object is neatly organized is its own file, and the tooling solve ordering and dependency cycles on its own, I'm not sure any OS tool currently do it like that (skeema maybe? but no PG).
  - evanelias 2y ago
    Flyway is primarily an imperative migration tool, rather than a declarative-first system. Their paid editions do have support for doing diffs ("state-based") and auto-generating migrations, but it's bolted-on to a workflow that was designed to be imperative-first. From what I can find, their public docs are rather lacking on what types of database objects and edge cases it supports. Skeema does indeed separate each DB object to its own file, and each logical schema to its own subdirectory. It also figures out how to correctly apply changes in a valid order, but yeah it's MySQL/MariaDB only. I think some of the declarative FOSS Postgres tools do attempt to provide a correct ordering, but from memory I'm not certain.

oulipo 2y ago

So I guess this is a bit the same as using something like Drizzle to define your tables and handle your migrations?

jackb4040 2y ago

>declarative >create_table Commands are not declarative. You want a single file with the state of your schema in it? We call that a schema file. It is strictly a negative to declare your schema in SQL, an imperative language. There is no way to guarantee this create_table will run safely, because it's not meant to ever be run.

evanelias 2y ago
> Commands are not declarative. The tool is using a declarative approach; the language is irrelevant. The key point is the input to the tool describes a desired state, and the tool figures out how the necessary operations to actually achieve that. This contrasts with the imperative approach to schema management, in which the user must explicitly define the steps to take. Some declarative schema management tools have the desired state be expressed in YAML or a custom DSL, while others use SQL; either way, they're still using a declarative approach. > There is no way to guarantee this create_table will run safely, because it's not meant to ever be run. It is meant to be run, though: any time you spin up a new environment, such as every CI run, or every new dev environment set up by an engineer. Additionally, some declarative schema management tools run the desired-state CREATEs in a temporary location as a core part of their operation. And they can also verify their generated ALTERs are correct, by running the original live-state CREATE in a temporary location, then running the generated ALTER, and verifying that the post-alter CREATE matches the desired state. > SQL, an imperative language SQL is a mix of imperative and declarative. SELECT statements are the canonical example of a declarative approach: they describe the set of data you want, not how to physically fetch it.