sqlite-jsonschema

A SQLite extension for validating JSON objects with JSON Schema. Based on sqlite-loadable-rs and the jsonschema crate.

If your company or organization finds this library useful, consider supporting my work!

Usage

.load ./jsonschema0
select jsonschema_matches('{"maxLength": 5}', json_quote('alex'));

Use with SQLite's CHECK constraints to validate JSON columns before inserting into a table.

create table students(
  -- ensure that JSON objects stored in the data column have "firstName" strings,
  -- "lastName" strings, and "age" integers that are greater than 0.
  data json check (
    jsonschema_matches(
      json('
        {
          "type": "object",
          "properties": {
            "firstName": {
              "type": "string"
            },
            "lastName": {
              "type": "string"
            },
            "age": {
              "type": "integer",
              "minimum": 0
            }
          }
        }
        '),
      data
    )
  )
);

insert into students(data)
  values ('{"firstName": "Alex", "lastName": "Garcia", "age": 100}');
-- ✓

insert into students(data)
  values ('{"firstName": "Alex", "lastName": "Garcia", "age": -1}');
-- Runtime error: CHECK constraint failed: jsonschema_matches

Find all the values in a column that don't match a JSON Schema.

select
  rowid,
  jsonschema_matches(
    '{
      "type": "array",
      "items": {
        "type": "number"
      }
    }',
    foo
  ) as valid
from bar
where not valid;

Installing

Language	Install
Python	pip install sqlite-jsonschema
Datasette	datasette install datasette-sqlite-jsonschema
Node.js	npm install sqlite-jsonschema
Deno	deno.land/x/sqlite_jsonschema
Ruby	gem install sqlite-jsonschema
Github Release
Rust	cargo add sqlite-jsonschema

sqlite-jsonschema is distributed on pip, npm, and https://deno.land/x for Python, Node.js, and Deno programmers. There are also pre-built extensions available for use in other environments.

Python

For Python developers, use the sqlite-jsonschema Python package:

pip install sqlite-jsonschema

The sqlite-jsonschema extension can then be loaded into a sqlite3 Connection object.

import sqlite3
import sqlite_jsonschema

db = sqlite3.connect(':memory:')
sqlite_jsonschema.load(db)
db.execute('select jsonschema_version(), jsonschema()').fetchone()

See Using sqlite-jsonschema with Python for details.

Node.js

For Node.js developers, use the sqlite-jsonschema NPM package:

npm install sqlite-jsonschema

The sqlite-jsonschema extension can then be loaded into a better-sqlite3 or node-sqlite3 connection.

import Database from "better-sqlite3";
import * as sqlite_jsonschema from "sqlite-jsonschema";

const db = new Database(":memory:");

db.loadExtension(sqlite_jsonschema.getLoadablePath());

const version = db.prepare("select jsonschema_version()").pluck().get();
console.log(version); // "v0.2.0"

See Using sqlite-jsonschema with Node.js for details.

Deno

For Deno developers, use the x/sqlite_jsonschema Deno module with x/sqlite3.

import { Database } from "https://deno.land/x/sqlite3@0.8.0/mod.ts";
import * as sqlite_jsonschema from "https://deno.land/x/sqlite_jsonschema/mod.ts";

const db = new Database(":memory:");

db.enableLoadExtension = true;
db.loadExtension(sqlite_jsonschema.getLoadablePath());

const [version] = db
  .prepare("select jsonschema_version()")
  .value<[string]>()!;

console.log(version);

See Using sqlite-jsonschema with Deno for details.

Datasette

For Datasette, use the datasette-sqlite-jsonschema plugin to include sqlite-jsonschema functions to your Datasette instances.

datasette install datasette-sqlite-jsonschema

See Using sqlite-jsonschema with Datasette for details.

sqlite3 CLI

For the sqlite3 CLI, either download a pre-compiled extension from the Releases page or build it yourself. Then use the .load dot command.

.load ./jsonschema0
select jsonschema_version();
'v0.2.1'

As a loadable extension

If you're using sqlite-jsonschema in a different way from those listed above, then download a pre-compiled extension from the Releases page and load it into your environment. Download the jsonschema0.dylib (for MacOS), jsonschema0.so (Linux), or jsonschema0.dll (Windows) file from a release and load it into your SQLite environment.

Note: The 0 in the filename (jsonschema0.dylib/ jsonschema0.so/jsonschema0.dll) denotes the major version of sqlite-jsonschema. Currently sqlite-jsonschema is pre v1, so expect breaking changes in future versions.

Chances are there is some method called "loadExtension" or "load_extension" in the SQLite client library you are using. Alternatively, as a last resort, use the load_extension() SQL function.

Building from source

Make sure you have Rust, make, and a C compiler installed. Then git clone this repository and run make loadable-release.

git clone https://github.com/asg017/sqlite-jsonschema.git
cd sqlite-jsonschema
make loadable-release

Once complete, your compiled extension will appear under dist/release/, either as jsonschema0.so, jsonschema0.dylib, or jsonschema0.dll, depending on your operating system.

Documentation

See the full API Reference for every sqlite-jsonschema SQL function.

Supporting

I (Alex 👋🏼) spent a lot of time and energy on this project and many other open source projects. If your company or organization uses this library (or you're feeling generous), then please consider supporting my work, or share this project with a friend!

See also

• sqlite-xsv, A SQLite extension for working with CSVs
• sqlite-http, A SQLite extension for making HTTP requests
• sqlite-loadable-rs, A framework for writing SQLite extensions in Rust