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

sql .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.

```sql 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.

sql 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 | PyPI | | Datasette | datasette install datasette-sqlite-jsonschema | Datasette | | Node.js | npm install sqlite-jsonschema | npm | | Deno | deno.land/x/sqlite_jsonschema | deno.land/x release | | Ruby | gem install sqlite-jsonschema | Gem | | Github Release | | GitHub tag (latest SemVer pre-release) | | Rust | cargo add sqlite-jsonschema | Crates.io |

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.

```python import sqlite3 import sqlite_jsonschema

db = sqlite3.connect(':memory:') sqlitejsonschema.load(db) db.execute('select jsonschemaversion(), 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.

```javascript 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.

```javascript import { Database } from "https://deno.land/x/sqlite3@0.8.0/mod.ts"; import * as sqlitejsonschema from "https://deno.land/x/sqlitejsonschema/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.

sql .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