Awesome
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 ofsqlite-jsonschema
. Currentlysqlite-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 CSVssqlite-http
, A SQLite extension for making HTTP requestssqlite-loadable-rs
, A framework for writing SQLite extensions in Rust