Show HN: Pg_jsonschema – A Postgres extension for JSON validation (github.com)

... > Has a more ergonomic approach to validation-error collection been developed other than writing a large function to iterate the new record fieldwise against the schema?

Thats an interesting problem that makes sense in the context of form validation.

re: have the ergonomics improved -> no

but you could hack that behavior together like this:

```

create or replace function handle_errors(errors text[])

    returns bool

    language plpgsql

begin

if errors = array[]::text[] then

  return true;

 end if;

 raise exception using errcode='22000', message=(select string_agg(x, ', ') from unnest(errors) v(x));

$$;

create table account(

id int primary key,

email text,

first_name text

check (

  handle_errors(

   array[]::text[]

   || case when email is null then 'email must not be empty' else null end

   || case when first_name is null then 'first_name must not be empty' else null end

  )

 )

insert into account(id, email, first_name)

values (1, null, null);

-- ERROR: email must not be empty, first_name must not be empty

```

not that I'd recommend it :)

Adding a schema to it seems natural to me (as someone who prefers strong-typing), and the JSONSchema implementation seems pretty lightweight and sensible.

I'd go even further down the mining XML's legacy route and add better XPATH2.0 + XQuery - style support. XQuery was a great tool, so expressive. Though it did spawn XQueryX [1] which I'm still unsure whether it was a joke or not.

[1] https://www.oreilly.com/library/view/xquery/0596006349/ch22s...

I must admit that the whole extension ecosystem in Postgres is amazing, even without any additional language layer.

I don’t write C very often but writing Postgres extensions was so easy to approach, it blew my mind!

There are 9 drafts, at least six of which seem to have support in implementations. (Plus there is a different variation [an “extended subset” of Draft 5] defined and used within OpenAPI 3.0.)

pg_jsonschema is a (very) thin wrapper around https://docs.rs/jsonschema/latest/jsonschema/ it supports versions 4, 6, and 7

> appears to be a conformance test suite; does this carry the same, or was the focus more on speed?

it was mostly an excuse to play with pgx that went surprisingly well. The performance is very good but safety was our main interest.

If CHECKs are causing perf issues, then okay maybe put the problematic ones somewhere else.

By centralizing data validation, it removes many potential failure and inconsistency scenarios (w.r.t different services validating things differently).

Worrying about CPU, without seeing if it's a real problem for your use case, is a premature optimization. Similar to worrying about foreign key constraint checks being too expensive. This is rarely the case, but if it winds up being a problem, you can relax it later and move the check it elsewhere in your stack (or remove it entirely).

Toon Koppelaars shows that in a few experiments done with Oracle ( https://www.youtube.com/watch?v=8jiJDflpw4Y&ab_channel=Oracl... )

Also very often doing the work in the DB means a simpler solution, fewer race-condition issues, and less work overall.

When talking about this particular case, the CHECK constraint, it is probably matter of measuring the impact and understanding at which point this might be relevant, and considering the cost of the alternative implementation at the application layer

It's good to be aware of additional strain you put on your DB, but I've worked with lots of applications where scaling simply wasn't a problem, and there I'd prefer validations closer to the data.

Supported extensions on AWS RDS: https://docs.aws.amazon.com/AmazonRDS/latest/PostgreSQLRelea...

Supported extensions on Google Cloud SQL: https://cloud.google.com/sql/docs/postgres/extensions

Supported extensions on Azure Database: https://docs.microsoft.com/en-us/azure/postgresql/single-ser...

Developers sometimes really just want to dump the data as a JSON. For them this means not having to write a lot of boiler plate ORM or SQL templates, and shipping trivial features quickly.

Example, user UI preferences are a good candidate for something like this. You probably don't want to add a new column just to remember the status of checkbox, when the user last logged in.

As a DBA, you probably still want to define a schema for this data, so as to not cause unexpected web app crashes. It ensures the some level of data consistency without increasing the maintenance overhead.

Obviously you wouldn't use it for business critical data, in my opinion.

The schema on that end is pretty intricate but to prevent hitting two services for certain types of data, we just dump it to a json column.

Furthermore, for a personal project of mine to help me with productiving / daily schedules, i'm using a json column for a to-do list in the schema of

{[some_todo_item]: boolean,}

which can't traditionally be represented in pg columns as the to do items are variable.

Imagine you’re writing an editing interface of some sort with images, text blocks etc. Each is conceptually a “node” but have different attributes. A single node table with a column per attribute means redundant columns. A table per node type introduces other problems because you need to query all your nodes together. Basically however you model this you end up with tradeoffs and real apps can obviously get much more complex - next thing you have 50 tables and a complex query just to get the data out.

Contrast to the other extreme - storing the whole thing in a single hierarchical json document. There’s no restriction on the data shape, and you can just pass the whole thing around as json. Versioning becomes much simpler because you’re versioning a single document. Your export format is just the json.

There’s tradeoffs of course, and often a middle ground is the right approach - but json columns with validation definitely have their place.

good luck building, and make sure to share the critical feedback with us too. it really helps to improve the product

I didn't know about the pgx_json_schema (or is_jsonb_valid) and will add both of them to the README today