Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Declarative Schemas for simpler database management

supabase.com

Post reply on HN
Print view View original

41–50 of 59 posts

Re: Declarative Schemas for simpler database management

#41

Post by carlmr »

webster451 wrote: post #29

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 dec…

>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.

Re: Declarative Schemas for simpler database management

#42

Post by evanelias »

sweatybridge wrote: post #36
williamdclt wrote: post #24

Earlier quoted context omitted.

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 stat…

> 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 too…

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.

Re: Declarative Schemas for simpler database management

#43

Post by Onavo »

webster451 wrote: post #29

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 dec…

I mean they just reinvented Prisma and Django

Re: Declarative Schemas for simpler database management

#44

Post by evanelias »

Onavo wrote: post #43
webster451 wrote: post #29

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 dec…

I mean they just reinvented Prisma and Django

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

Re: Declarative Schemas for simpler database management

#45

Post by efxhoy »

MrMcCall wrote: post #40
efxhoy wrote: post #22

Earlier quoted context omitted.

The best practice way to swap fullname for firstname, lastname would be to: 1. Migration that adds firstname and lastname columns will all nulls 2. Deploy application code change to start populating firstname and lastname alongside fullname, still reading fullname in the code. 3. backfill the firstname and lastname values with a script/command/migration 4. change app code to read firstname and lastname and stop writi…

6. ensure there are no nulls in firstname and lastname 7. alter the columns to be NOT NULL Because no non-statistician uses nullable columns, right? Of course, some dbs (SQLServer?) infer NULL from the empty string, or am I misremembering? Always having the columns be NOT NULL is a fundamental cheat, after always having a PK, or is that too old school for 2025?

There's nothing wrong with nullable fields when it's appropriate. When kids are born they don't have names. Not all users want to tell you their names. A null value is data too.

Re: Declarative Schemas for simpler database management

#46

Post by perrygeo »

xyzzy_plugh wrote: post #3

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 w…

> 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.

Re: Declarative Schemas for simpler database management

#47

Post by Guillaume86 »

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...

Re: Declarative Schemas for simpler database management

#48

Post by MrMcCall »

efxhoy wrote: post #45
MrMcCall wrote: post #40

Earlier quoted context omitted.

6. ensure there are no nulls in firstname and lastname 7. alter the columns to be NOT NULL Because no non-statistician uses nullable columns, right? Of course, some dbs (SQLServer?) infer NULL from the empty string, or am I misremembering? Always having the columns be NOT NULL is a fundamental cheat, after always having a PK, or is that too old school for 2025?

There's nothing wrong with nullable fields when it's appropriate. When kids are born they don't have names. Not all users want to tell you their names. A null value is data too.

> when it's appropriate

Yes, it just requires extra care when querying and handling the rows.

It's always just easier, if you can, to make it NOT NULL after prepopulating all rows' columns to the empty string (or real data).

Sometimes NULL is truly different than the empty string, but that, like you said, is just a kind of data.

Re: Declarative Schemas for simpler database management

#49

Post by evanelias »

Guillaume86 wrote: post #47

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 a…

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

[2] https://github.com/bikeshedder/tusker

[3] https://github.com/djrobstep/migra

[4] https://github.com/sqldef/sqldef/

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

[6] https://github.com/skeema/skeema

Re: Declarative Schemas for simpler database management

#50

Post by nextaccountic »

kiwicopple wrote: post #27
evanelias wrote: post #25

Earlier quoted context omitted.

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 synchroni…

> 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 databa…

Hey, are you using some tool like the unmaintained migra https://github.com/djrobstep/migra (perhaps using this tool https://github.com/blainehansen/postgres_migrator) or 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 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

Post reply on HN