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

31–40 of 59 posts

Re: Declarative Schemas for simpler database management

#31

Post by layer8 »

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…

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.

Re: Declarative Schemas for simpler database management

#32

Post by kiwicopple »

pounderstanding wrote: post #30
kiwicopple wrote: post #14

Earlier quoted context omitted.

> db engineers approve the generated migration script yeah - this is definitely the intended flow here. We won't be recommending anyone blindly applying generated migrations. As you mention, it is expected that you generate & review on your local development machine, check into source control, push & merge. We've also been using this internally for ~2 years now and it works great

Do you keep the history of applied migrations? (Just in case subtle issue need to be investigated later)

yeah, migrations are generated from the Declarative files. For example, the steps are for adding a new column:

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, check it into git

4/ Apply migration to database

Re: Declarative Schemas for simpler database management

#33

Post by skybrian »

pounderstanding wrote: post #23
skybrian wrote: post #5

Earlier quoted context omitted.

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.

Migrations give more control. Alter table foo add column bar; Update foo set bar=baz; Alter table foo modify column bar NOT NULL;

True. Perhaps annotating the schema would help for simple things?

Re: Declarative Schemas for simpler database management

#34

Post by evanelias »

pounderstanding wrote: post #26
infogulch wrote: post #7

Earlier quoted context omitted.

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

> they found SO many inconsistencies between environments This implies somebody with admin rights makes alterations in ad-hoc way without first doing it in test env. If they continue with adhoc stuff, then it means auto-generated migrations will be different in test vs prod. (I prefer to test exactly same thing that will be used in prod)

> This implies somebody with admin rights makes alterations in ad-hoc way without first doing it in test env.

Not necessarily. With a large team/org using the same database schema, it can just mean multiple people were trying to make changes to an environment around the same time, e.g. the migrations were applied in a different order in staging vs prod.

Some migration tools provide extra checks for strict ordering, but many do not. There's often no guarantee that the migration file naming scheme ordering, Git commit ordering, and actual DB apply ordering line up -- that's 3 different possible sources of truth, or more since the DB state varies by environment (dev/stage/prod etc).

Late-night hot-fixes (to solve an emergency outage) can be another source of inconsistencies / drift.

> If they continue with adhoc stuff, then it means auto auto-generated migrations will be different in test vs prod

That depends on the declarative tool and whether it fully syncs the schema each time, or just generates migrations which are frozen into a plan which is executed as-is in all environments. Not that full-sync is bad, but yes in that case it will generate different things in each env. Although the end result is that it will solve the drift, and give you the same end state in all environments. And that's likely what you want to happen: after running the tool, the database state will match the desired state which was expressed by the CREATE statements in your schema repo.

That said, the declarative tooling should have sufficient safety checks to ensure it doesn't do anything destructive in prod without very loudly telling you and requiring manual confirmation. That way, you won't be harmed when trying to synchronize an environment that had unexpected out-of-band changes.

Re: Declarative Schemas for simpler database management

#35

Post by [deleted] »

pounderstanding wrote: post #26
infogulch wrote: post #7

Earlier quoted context omitted.

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

> they found SO many inconsistencies between environments This implies somebody with admin rights makes alterations in ad-hoc way without first doing it in test env. If they continue with adhoc stuff, then it means auto-generated migrations will be different in test vs prod. (I prefer to test exactly same thing that will be used in prod)

[deleted]

Re: Declarative Schemas for simpler database management

#36

Post by sweatybridge »

williamdclt wrote: post #24
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…

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 tooling itself, rather than relying purely on the experiences of engineers.

[0] https://github.com/stripe/pg-schema-diff

Re: Declarative Schemas for simpler database management

#37

Post by prmph »

evanelias wrote: post #28
prmph wrote: post #16

Earlier quoted context omitted.

But if you have data to migrate, it is not always possible to infer the diff, no? Say I have a users table with a name column. Then I alter the table and split the name column into two new columns: first name and last name. How is it possible to infer this change, just from seeing the new and old columns?

With large tables, you can't safely make data changes using migration tools either anyway. If you run a single UPDATE against a large table, you end up with a very long transaction with substantial MVCC implications (old row versions that the db needs to clean up) and can basically break production easily. Side note, but why do folks always bring up this "firstname lastname" example? It is not ever possible to implem…

Oh, I am aware of the problems with this scheme for names. I just used it as a simple example. I wouldn't design my users table this way.

If I can, I even avoid storing actual names of users at all. If I have to, I use a simple free form "display_name" column; what is put there is up to the user.

Re: Declarative Schemas for simpler database management

#38

Post by evanelias »

layer8 wrote: post #31
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…

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

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

Re: Declarative Schemas for simpler database management

#39

Post by bootsmann »

skybrian wrote: post #5
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…

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.

Not all migrations are as simple as adding and removing columns.

Re: Declarative Schemas for simpler database management

#40

Post by MrMcCall »

efxhoy wrote: post #22
prmph wrote: post #16

Earlier quoted context omitted.

But if you have data to migrate, it is not always possible to infer the diff, no? Say I have a users table with a name column. Then I alter the table and split the name column into two new columns: first name and last name. How is it possible to infer this change, just from seeing the new and old columns?

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?

Post reply on HN