JSON Schema assist for Postgres

2023-03-22 03:32:50

pg_jsonschema: JSON Schema support for Postgres

Launched on the Supabase platform right this moment, pg_jsonschema is a Postgres extension
which provides JSON Schema validation assist for json and jsonb information varieties.

The use-case for JSON validation

Regardless of Supabase being an SQL store, even our most zealous relational information mannequin advocates (begrudgingly) acknowledge some benefits to the doc information mannequin.
Primarily, if some complicated information will at all times be consumed collectively, a doc information kind could be a good match.

For Instance:

If our software receives information through a webhook:

{
  "status_code": 200,
  "checksum": "89b623f6332d2b9b42b4e17eaf1bcc60"
  "headers": {
    "Content material-Sort": "software/json",
    "Final-Modified": "Tue, 09 Aug 2022 09:14:10 GMT"
  },
  "payload": {
    {
      "success": true,
      "content material": {
        "account_id": "d928b484-16bd-4f10-a827-3eb959b4cc14",
        "occasion": "SUBSCRIBED",
        "subscriptions": [
          {"subscription_id": 481, "exp": 1660050940},
          {"subscription_id": 121, "exp": 1660041852},
        ]
      }
    }
  }
}

An affordable swing at normalizing that information into tables would possibly appear like this:

That is a whole lot of architecting! Furthermore, the question to get well the unique enter requires 5 joins!

An answer that aligns higher with our intent could be to persist no matter we obtain from the exterior service as long as it meets a minimal set of necessities.
With Postgres’ json information kind we are able to obtain half of that purpose.

Treating the webhook contents as a json doc simplifies our information mannequin. It’s also sturdy to altering payloads and extra environment friendly to question, replace, and delete.

Now what about this half?

as long as it meets a minimal set of necessities

Challenges

The flexibleness of doc varieties additionally comes with some downsides.

The schema of the json payload from the earlier instance is just a little intense for a weblog put up, so let’s as a substitute say we intend for a desk’s json column to carry objects with a string attribute named foo and no extra attributes.

With out constraints, the setup could be:

create desk some_table (
  id serial main key,
  metadata json not null
);

insert into some_table (metadata)
values (<SQL Enter>);

However the ensuing schema is far more permissive than our intent. When inserting a mixture of right and incorrect values:

Solely 2 of our 8 check circumstances have been dealt with appropriately by our information mannequin.

A core power of SQL databases is their potential to constrain information’s types, nullability,
referential integrity, uniqueness,
and even arbitrary developer-defined rules.
These constraints are loads to sacrifice to realize the comfort of doc varieties.

Thankfully, the problem of validating json paperwork is not particular to SQL databases. NoSQL/Doc databases, like MongoDB,
optionally enforce data constraints so there’s loads of prior artwork for us to attract from.

JSON Schema

JSON Schema is a specification for validating the form and contents of json paperwork.
It will probably describe constraints for paperwork just like these utilized by relational databases.

Translating our constraints from the earlier instance right into a JSON Schema we get:

// objects with a string attribute
// named foo and no extra attributes
{
  "kind": "object",
  "properties": {
    "foo": {
      "kind": "string"
    }
  },
  "required": ["foo"],
  "additionalProperties": false
}

Which is a proper and human-readable description of our intent. A tutorial on the JSON Schema language is out-of-scope
for this text however you could find a full introduction in their guide.

So now we’ve:

✅ versatile doc information kind → json

✅ a language to explain constraints on json paperwork → JSON Schema

❌ a technique to implement JSON Schema constraints on json paperwork in Postgres

pg_jsonschema

pg_jsonschema is a Postgres extension that may validate json and jsonb information varieties
towards a JSON Schema. The extension provides two capabilities:

-- Validates a json *occasion* towards a JSON Schema *schema*
json_matches_schema(schema json, occasion json) returns bool

-- Validates a jsonb *occasion* towards a JSON Schema *schema*
jsonb_matches_schema(schema json, occasion jsonb) returns bool

We will use these capabilities together with a check constraint to extra utterly describe our information mannequin.

create desk some_table(
  id serial main key,
  metadata json not null,

  verify (
    json_matches_schema(
      schema :='{
         "kind": "object",
         "properties": {
          "foo": {
           "kind": "string"
          }
         },
         "required": ["foo"],
         "additionalProperties": false
      }',
      occasion := metadata
    )
  )
);

insert into some_table(metadata)
values
  (<SQL enter>);

With that verify constraint in place, we re-run the identical check circumstances:

Now all 8 exams are dealt with appropriately. In circumstances the place data did not insert, Postgres throws an error referencing the failing constraint.

ERROR: new row for relation “some_table” violates verify constraint “some_table_metadata_check”

DETAIL: Failing row incorporates (1, null).

SQL state: 23514

With these instruments you may wield the pliability of json/jsonb information varieties with out sacrificing the ensures of a nicely specified information mannequin!

To get began with pg_jsonschema, hearth up a brand new supabase challenge and allow the extension with