Declarative Schemas for simpler database management • Hacker News Board

Re: Declarative Schemas for simpler database management

#51

Post by Guillaume86 » Fri, Apr 04, 2025, 8:24 AM UTC

evanelias wrote: post #49

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

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

Re: Declarative Schemas for simpler database management

#52

Post by mappu » Fri, Apr 04, 2025, 10:33 AM UTC

prmph wrote: post #16

bartvk wrote: post #4

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…

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?

I know it was just an example, but https://www.kalzumeus.com/2010/06/17/falsehoods-programmers-... definitely stopped me doing separate first and last name columns.

Re: Declarative Schemas for simpler database management

#53

Post by Onavo » Fri, Apr 04, 2025, 2:08 PM UTC

evanelias wrote: post #44

Onavo wrote: post #43

Earlier quoted context omitted.

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

Any plans to add Postgres and SQLite support?

Re: Declarative Schemas for simpler database management

#54

Post by evanelias » Fri, Apr 04, 2025, 3:24 PM UTC

Guillaume86 wrote: post #51

evanelias wrote: post #49

Earlier quoted context omitted.

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

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

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.

Re: Declarative Schemas for simpler database management

#55

Post by oulipo » Fri, Apr 04, 2025, 5:03 PM UTC

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

Re: Declarative Schemas for simpler database management

#56

Post by evanelias » Fri, Apr 04, 2025, 5:47 PM UTC

Onavo wrote: post #53

evanelias wrote: post #44

Earlier quoted context omitted.

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

Any plans to add Postgres and SQLite support?

Skeema will always be specific to MySQL/MariaDB, but I hope to eventually release a separate tool which is more generic and modular. That's still a long while away though.

There are some existing declarative tools for Postgres and SQLite though, see https://news.ycombinator.com/item?id=43576450

Re: Declarative Schemas for simpler database management

#57

Post by Guillaume86 » Fri, Apr 04, 2025, 8:28 PM UTC

evanelias wrote: post #54

Guillaume86 wrote: post #51

Earlier quoted context omitted.

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

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

Thanks for the additional information!

Re: Declarative Schemas for simpler database management

#58

Post by jackb4040 » Sat, Apr 05, 2025, 12:00 AM UTC

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

Re: Declarative Schemas for simpler database management

#59

Post by evanelias » Sat, Apr 05, 2025, 6:44 PM UTC

jackb4040 wrote: post #58

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

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