Type constraints in 65 lines of SQL | Hacker News Reader

Type constraints in 65 lines of SQL

supabase.com

134 pointsoliverrice46 comments

Nice article. I've used composite types with success for similar problems. Some tips:

- Composite types are useful for data validation. Money types and unit-based types are good for composite types.

- Avoid over-using composite types. Most of the time, regular columns are better. Not many tools interact well with composite types, like reporting tools or database libraries.

- Like the article notes, avoid using composite types for data types that may change.

- A JSONB domain type is a good alternative for data types that change often. Note that if you put the validation into a helper function, Postgres will not revalidate the domain if the function definition changes.

- Using composite types is mildly annoying since you must wrap the composite column in parenthesis to access the field.

    -- Bad 
    SELECT package_semver.major FROM package_version;
    -- Errors with `missing FROM-clause entry for table "package_semver"`
    
    -- Good
    SELECT (package_semver).major FROM package_version;

- When defining a domain type, separate checks into named constraints with ALTER DOMAIN. The Postgres error message for check validation failures is lackluster and provides little beyond "it failed."

    CREATE DOMAIN item_delta_node AS jsonb NOT NULL;
    
    ALTER DOMAIN item_delta_node
      ADD CONSTRAINT item_delta_node_is_object
        CHECK (coalesce(jsonb_typeof(value), '') = 'object');
    
    ALTER DOMAIN item_delta_node
      ADD CONSTRAINT item_delta_node_item_delta_id_null_or_num
        CHECK (coalesce(jsonb_typeof(value['item_delta_id']), 'number') IN ('null', 'number'));

totally agree

""Using composite types is mildly annoying since you must wrap the composite column in parenthesis to access the field"""

^ yeah that is frustrating

if you create domains over jsonb frequently, check out pg_jsonschema[1] as a way to express the constraints more concisely

[1] https://github.com/supabase/pg_jsonschema

Any idea of how to implement tagged unions in Postgres. For example, Tree a = Leaf a | Branch (Tree a, Tree a)

This is one data type feature which would be great to have. I know you can create separate tables for each option in the type and use an id, but is a direct type implementation possible?

Dont need polymorphism(say a = String). Even a non recursive tagged union would be helpful.

One of the restrictions of composite types is that they can not contain an instance of themselves. So unfortunately, this is not currently possible.

I had this issue when trying to implement an AST type for pg_graphql[1] back when it was written in SQL [2]. In the end we used a JSON type which was much less constrained. That might be solvable using pg_jsonschema [3] if you really wanted to have a good time though

[1] https://github.com/supabase/pg_graphql

[2] https://github.com/supabase/pg_graphql/blob/34cc266da972d356...

[3] https://github.com/supabase/pg_jsonschema

What's your opinion between jsonb vs hstore columns? https://www.postgresql.org/docs/15/hstore.html

IIRC, hstore pre-dated jsonb. I'm not aware of any performance differences for the object type but it would be worth checking if you have a heavy workload. jsonb will tend to have better ORM support and it also isn't behind an extension which is a (small) perk

Actually, another thing. JSON works. But what about doing pattern matching on the tagged enum? Each variant should give access to its fields

Can https://www.postgresql.org/docs/15/functions-conditional.htm... be used to do it?

It depends on your tolerance for the exact feature set that you get. The JSON type can be pressed into service to get something that represents that, in the sense that what you are asking for is a subset of the valid JSON values that Postgres can store. If you just want to slam the values into some local tagged union in your language when querying them, then problem effectively solved. If you want to start operating on it in the database itself, you have a lot more quirky things to work through. If you want the semantics inside Postgres to be exactly what a tagged union/sum type looks like then I think you'd be looking at a custom data type at a minimum, and even then there are probably limits as to how close you could get inside.

While that might not fit your needs I map unions to Postgres with an enum encoding the type and then adding constraints on which additional columns must be set (and also must not be set) depending on the type. For your example the enum might be leaf or tree and then if it’s of type tree you have non nullable left_child and right_child columns and if it’s a leaf those must be null, and then you might have a value column that is non null in both cases or only for leaves.

This is my pet peeve with SQL. I hope some day PostgreSQL gain support for native tagged unions.

I have saved some links about this

https://github.com/solidsnack/pg-sql-variants

https://thelyfsoshort.io/variant-types-in-postgresql-c63725f...

https://www.parsonsmatt.org/2019/03/19/sum_types_in_sql.html

https://typeable.io/blog/2019-11-21-sql-sum-types.html

Right now I think the ideal is a jsonb column (with a field that stores the tag) plus https://github.com/supabase/pg_jsonschema. But this is only usable if your language translates to/from JSON and generates json schema for you.

Rust can do this with https://serde.rs/ and https://docs.rs/schemars/latest/schemars/

I often wonder about this kind of solution compared with a more traditional solution of using a table of columns and constraints. There seems like there should be an obvious heuristic of when to use one or the other but it eludes me.

Definitely. For the use-cases shown in the blog post, a table would be equally valid (and easier). The heuristic I use is to create custom types when the values will need to be used by SQL functions, or if support for things like ordering/aggregating are important to the application, and the rules to support those things require custom logic.

For example, SemVer requires that two version that differ only in metadata e.g. `1.0.0+metaA` and `1.0.0+metaB` should be considered equal. It would be error prone and tedious to push that logic onto every query that wanted to sort the table, but with a type we can define the logic once and have it work everywhere.

A good heuristic to consider when designing composite types is whether any part of the type would lead to redundant storage. For example, a currency composite type might consist of an amount field and a type field.

In most cases, a single invoice, order, deal, etc. is unlikely to be generated using multiple currencies. Therefore, a single currency_type field in the invoice table would be sufficient.

If we used a composite currency type, the currency_type field would be highly redundant.

> If we used a composite currency type, the currency_type field would be highly redundant.

It can help prevent the bug in which someone adds up multiple currencies without conversion.

“25 USD + 25 EUR” will either fail or work via some currency conversion routine.

“25 + 25” will produce a meaningless and useless wrong answer.

Yes, this is exactly what I’ve done previously. For example, I implemented a sum() aggregate function that took “monetary” (currency + value) composite types and raised an exception if the currencies were not all the same.

hi, author here happy to answer any questions

another approach that works great is to use `create type`[1] with an `input_function` and `output_function` to build on top of an existing scalar type. For example, using that method would allow semver to be persisted as a string natively. The only downside to that is you have to be superuser.

[1] https://www.postgresql.org/docs/current/sql-createtype.html

Why are these practices not common with web developers? What’s held them back in recent years using it?

Way easier handling this in the application code. If I had a developer join my team then commit this into the codebase rather than just using the ORM that everyone else is familiar with they would be fired UNLESS there was a very good reason for doing so

So in this case, you've let a new dev commit to a code base without review.

Then, instead of informing them of best practices and giving them time to re-work the code, you'd just fire them?

Interesting strategy.

Creating a pull request requires committing code too.

Agreed that there should be a data integrity or custom operation/filter/sorting requirement to motivate a user defined type

> using the ORM that everyone else is familiar with

A relational database isn't always fronted by the same tech. You have RESTful APIs, GraphQL APIs, Low Code/No Code Solutions (like Retool), reporting tools, ETL systems, etc.

You have company mergers where that one familiar ORM is now five to six unfamiliar ORMs.

Ensuring data integrity is key and easiest way to do that is to push constraints as close to the persistence layer as possible.

Easier, slower, more error-prone. Also, you should consider implementing code reviews rather than just firing people who commit code you don't like.

I love working directly in plpgsql, it makes it possible to perform all the database logic in the database, effectively presenting a domain-specific API to the world. This api is then available to any SQL user. It’s like creating a built in CLI for your data.

Putting logic directly in the database is much faster at runtime, doesn’t require redundant entity definitions, is substantially more likely to detect schema problems, and is way easier to test.

And it removes the dependency on a specific language and toolchain. Your logic is accessible by everyone.

Unfortunately, the tooling around managing this stuff is not great. The language is a bit boring; idiomatic upper case is the first thing I get rid of.

But it’s the best tool for the job. Far from being “way easier” in application code, plpgsql reduces LOC, improves performance and removes dependencies.

If someone on my team ignored all of these benefits, well, I’d explain it to them and show them examples until they “got it”.

Way easier than firing them.

Very few people know plpgsql. Like all stored proc languages (Oracle PL/SQL, etc.) it looks and feels like something out of the 80's. It's annoying to deploy, annoying to debug, annoying to test. Many developers today barely know SQL.

It’s easy to make it feel more modern (select ffs from stop_using_all_caps). But it’s an ideal DSL for data manipulation.

It’s not particularly annoying to debug - there are logging, and even single step debugging tools available - and it’s so much easier to test than writing unit tests to do the same thing.

I agree it’s painful to deploy, but as I’ve discovered this is a tooling problem; it’s not at all intrinsic to the medium.

It’s certainly possible to create tooling to make it easier, and who knows? Maybe I’ll have something to show HN one day.

> Many developers today barely know SQL.

If true - a very big if - it’s an inditement of the industry and the incentives we use, not a reason to avoid something that makes code faster, safer and smaller.

Objections to using these languages are a lot more about willingness to learn and try new (old) things, than they are about the capabilities of the platform.

I think there are a few things:

1) Its only been a few years since the trend of testing on sqlite locally and running postgres in production went away in favor of postgres everywhere (probably thanks to docker). That prevented using any feature that wasn't equally supported by both.

2) There's definitely a knowledge gap, and not just among developers. The features are most useful for building rich applications, so even DBAs didn't have much incentive to use them prior to tools like supabase using the database as the data model source-of-truth.

3) Companies are increasingly deciding that data is their competitive advantage and interest in data integrity is growing. Database constraints are unparalleled at that because they can't be sidestepped

I think most webdevs see databases as dumb datastores.

I blame the Apple II computer.

no to forget: Turing's seminal paper

Perhaps you're joking, but I'm not. The personal computer revolution began in earnest with the Apple II in 1977, joined by the Commodore 64 in 1982. Gen-X boys shoved Gen-X girls aside, shoved the computers into their bedrooms, and 15 years later were at the right place and the right time to shove an Algol-based tech stack onto the center stage of the dot com boom. SQL was a necessary evil held at arms length behind ORMs as fear, uncertainty, and doubt rapidly took root. It's taken decades, but finally, mercifully, the Gen-X priests of procedural programming are losing their grip and we're just starting to emerge from the Dark Ages of all this superstitious nonsense about not putting business logic in the database. It's been a long strange trip, but it's never too late to study the classics. After all, they never really go out of style.

IMO the lack of a Terraform-esque ecosystem of tools around declaratively managing database-objects-as-infrastructure, and understanding how a rollout of a change would be planned, has historically been a big issue. If I'm choosing where in the stack to declare some new business logic or type constraint, I can trivially check that into Git if it's running on an application server, and most frameworks' ORMs can handle column-level migrations. And if I make a mistake, I can push a change that declaratively says what my function signature or DDL should be; it's one of the first things taught to new developers. Terraform extended this mentality to infrastructure, and it was gamechanging.

But there are far fewer tutorials and best practices on how to, say, maintain a library of Postgres functions, types, and stored procedures that can be iterated on. I'd venture that most people have no idea how powerful their databases can be.

I think most web developers just do not understand SQL well enough to take advantage of these types of features (and if they do, they still may be sticking closer to "standard" ANSI SQL, or may just not be up to date on newer features). Unfortunately, lots of web developers don't get past their ORM abstraction into what is occurring behind the scenes and how to make use of the full range of their database's features.

The tooling ecosystem for databases is also terrible.

Bad support for testing, for version controlled schemas, language types, etc.

This is the reason for the spread of ORMs and similar abstractions to hide this lack of support.

Is there a way to (reasonably/somewhat ergonomically) describe why a thing failed? True/false without reasons is pretty painful sometimes.

sure, if you switch the sql functions for plpgsql functions you can use procedural logic to on each segment and `raise exception` with a custom error message that will get returned to the client

Why the {1,255} ? Pg's text type is not varchar, it can get a lot longer than that and still index just fine, and while I can see a restriction on the pre_release stuff -maybe- being useful, compilation/build metadata doesn't seem like something where an enforced maximum length that low is necessarily preferable. (I could see URLs, file paths, repo+path+sha etc. type stuff being useful in metadata and not something the code recording it can really control the length of - it may not do any harm in practice at least for a while, but I don't immediately see what -good- the restriction does, albeit I'm perfectly willing to be told "here's the good thing about it that you didn't think of" ;)

The 255 max length is something you could consider if untrusted people are able to insert data (through an API for example). A column in postgres can store up to 1 GB of data so its a possible DOS vector. Probably not an issue for most use-cases, just something that was considered in the application those code samples came from

I would still have gone with a higher length for the meta stuff, given the 2730 character limit on indexing I'd've thought 2048 would probably be fine and much less likely to result in a long path unexpectedly ventilating somebody's foot.

If space abuse is a vector under consideration, given there doesn't seem to be a restriction on the number of entries (if I somehow missed one my apologies) you could easily stuff it with a really big array of 255 character entries so I wonder if 2048 plus some sort of reasonably generous limit on the number of elements might not achieve the goal better and provide a better UX/DX as an added bonus.

(I'm currently working on a design in a somewhat related area so I've been doing a fair bit of thinking about this - I may of course still be completely wrong, but if so I can at least claim I put effort into ending up wrong ;)

I once heard that data validation should be implemented in back end server but not datsbase because the latter is more difficult to scale horizontally and usually the bottleneck of transaction performance.

Is that true?

The benefits of having a better type and form constraint is obvious. But you should remember that offloading computation to the DB, especially one that isn't fully distributed like Postgres. Might bite you later down the line.

while you always want to be careful with database load, that generally wouldn't be a concern for user defined types. In the example case, operating on the composite type would be more efficient than the string alternative for the most common operations (sorting, comparing, extracting sub-fields)

Please don't engineer Google scale if you're not Google. A modern server with NVME SSDs can handle a ridiculous amount of transactions (> 1000/s) without breaking the bank. Many LOB apps will run absolutely fine with everything on just one server and all logic inside the database.

I'd personally worry much more about the maintainability of this stuff. Testing & version control for in-Postgres things is just plain inferior as a development experience.

Is that true?

If data validation in the database is your bottleneck, you have a different problem. Horizontally scaling just to validate data is throwing money at the problem instead of solving it.

Reply on news.ycombinator.com