62 ms·

Type constraints in 65 lines of SQL

134 points by oliverrice 4y ago

sa46 4y ago
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'));
- oliverrice 4y ago
  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 https://github.com/supabase/pg_jsonschema
margorczynski 4y ago
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.
- oliverrice 4y ago
  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)
- RedShift1 4y ago
  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.
- yencabulator 4y ago
  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.
maxloh 4y ago
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?
- RedShift1 4y ago
  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.
oliverrice 4y ago
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 https://www.postgresql.org/docs/current/sql-createtype.html
- Groxx 4y ago
  Is there a way to (reasonably/somewhat ergonomically) describe why a thing failed? True/false without reasons is pretty painful sometimes.
  - oliverrice 4y ago
    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
- mst 4y ago
  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" ;)
  - oliverrice 4y ago
    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
nickpeterson 4y ago
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.
- oliverrice 4y ago
  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.
- ericHosick 4y ago
  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.
  - skissane 4y ago
    > 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.
    - doctor_eval 4y ago
enugu 4y ago
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.
- csnweb 4y ago
  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.
- nextaccountic 4y ago
  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://github.com/solidsnack/pg-sql-variants https://thelyfsoshort.io/variant-types-in-postgresql-c63725f8678b https://thelyfsoshort.io/variant-types-in-postgresql-c63725f... https://www.parsonsmatt.org/2019/03/19/sum_types_in_sql.html https://www.parsonsmatt.org/2019/03/19/sum_types_in_sql.html https://typeable.io/blog/2019-11-21-sql-sum-types.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 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/ https://serde.rs/ and https://docs.rs/schemars/latest/schemars/ https://docs.rs/schemars/latest/schemars/
- oliverrice 4y ago
  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 https://github.com/supabase/pg_graphql [2] https://github.com/supabase/pg_graphql/blob/34cc266da972d356365f953503deb7a39960da0a/pg_graphql--0.4.0.sql https://github.com/supabase/pg_graphql/blob/34cc266da972d356... [3] https://github.com/supabase/pg_jsonschema https://github.com/supabase/pg_jsonschema

134 points by oliverrice 4y ago

sa46 4y ago

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'));

oliverrice 4y ago
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 https://github.com/supabase/pg_jsonschema

margorczynski 4y ago

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.

oliverrice 4y ago
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)
RedShift1 4y ago
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.
yencabulator 4y ago
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.

maxloh 4y ago

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?

RedShift1 4y ago
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.

oliverrice 4y ago

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 https://www.postgresql.org/docs/current/sql-createtype.html

Groxx 4y ago
Is there a way to (reasonably/somewhat ergonomically) describe why a thing failed? True/false without reasons is pretty painful sometimes.
- oliverrice 4y ago
  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
mst 4y ago
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" ;)
- oliverrice 4y ago
  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

nickpeterson 4y ago

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.

oliverrice 4y ago
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.
ericHosick 4y ago
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.
- skissane 4y ago
  > 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.
  - doctor_eval 4y ago

enugu 4y ago

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.

csnweb 4y ago
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.
nextaccountic 4y ago
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://github.com/solidsnack/pg-sql-variants https://thelyfsoshort.io/variant-types-in-postgresql-c63725f8678b https://thelyfsoshort.io/variant-types-in-postgresql-c63725f... https://www.parsonsmatt.org/2019/03/19/sum_types_in_sql.html https://www.parsonsmatt.org/2019/03/19/sum_types_in_sql.html https://typeable.io/blog/2019-11-21-sql-sum-types.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 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/ https://serde.rs/ and https://docs.rs/schemars/latest/schemars/ https://docs.rs/schemars/latest/schemars/
oliverrice 4y ago
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 https://github.com/supabase/pg_graphql [2] https://github.com/supabase/pg_graphql/blob/34cc266da972d356365f953503deb7a39960da0a/pg_graphql--0.4.0.sql https://github.com/supabase/pg_graphql/blob/34cc266da972d356... [3] https://github.com/supabase/pg_jsonschema https://github.com/supabase/pg_jsonschema