serde-sqlite-jsonb

This crate provides a custom Serde deserializer for SQLite JSONB columns.

It was initially developed for inclusion in the SQLPage website builder.

Why

Since version 3.45.0, SQLite supports JSONB columns, which can store JSON data in a binary format that is more efficient to manipulate than JSON.

The problem is that applications that use SQLite currently need to convert the data from JSONB to JSON, and then from JSON to their own data structures to use it. This prevents reading the blob data directly from the database in a streaming fashion using SQLite's blob streaming API, and requires making a SQL query to extract and convert the data to JSON.

This crate provides a custom Serde serializer and deserializer for JSONB directly, which allows skipping the JSON conversion step.

This can lead to significant performance improvements in certain scenarios, as demonstrated in this crate's benchmarks.

Benchmarks

These graphs show the time taken:

• deserializing a JSONB column directly to a struct using this crate
• making a SQL query to extract the JSONB column as JSON and then deserializing it to a struct using serde_json.

The data being deserialized contains a string, the length of which varies from 50 to 1000 characters, to demonstrate the evolution of the performance with the size of the data.

Disclaimer: These benchmarks should always be taken with a grain of salt. When performance matters, you should measure the performance inside your own application with your own data. serde_json is very well optimized and can be faster than this crate in some scenarios, especially when the JSON data is small.

Crate features

The binary format can contain raw json data, so this crate depends on the serde_json crate to parse the JSON data. Since SQLite also supports json5, the serde-json5 feature can be used if json5 support is needed.

By default, the (faster) serde_json feature is enabled and this crate returns an error when trying to parse json5 data. To enable json5 support, enable the serde-json5 feature (and optionally disable the default features to use the json5 parser even for json data):

[dependencies]
serde-sqlite-jsonb = { version = "0.1", features = ["serde-json5"], default-features = false }

Usage

This library does not handle the SQLite connection, so you need to use a crate like rusqlite or sqlx to interact with the database.

Once you have extracted the JSONB data from the database, either as a Vec<u8> or as a std::io::Read object that streams the BLOB data from the database, you can use the serde_sqlite_jsonb crate to deserialize the JSON data, either to your own data structures or to a serde_json::Value.

Deserialize JSONB from a query result

let conn = rusqlite::Connection::open_in_memory()?;
let blob: Vec<u8> = conn.query_row(
    r#"select jsonb('{"id": 1, "name": "John Doe"}')"#, [], |row| row.get(0),
)?;
let person: Person = serde_sqlite_jsonb::from_bytes(&blob).unwrap();

Streaming deserialization from a SQLite BLOB

let my_blob = conn.blob_open( // returns an object that implements std::io::Read
    DatabaseName::Main,
    "my_table", // table name
    "my_jsonb_column", // column name
    42, // primary key (rowid)
    true // read-only
)?;
let parsed: serde_json::Value = // or any other type that implements Deserialize
    serde_sqlite_jsonb::from_reader(my_blob).unwrap();

Format

The format of the JSONB column is described in the SQLite documentation: https://sqlite.org/draft/jsonb.html

The data format is a binary format with a header and payload. The header contains information about the type of the element and the size of the payload. The payload contains the actual data.

Here's a rough ASCII representation:

bits:  0  1  2  3  4  5  6  7  8
    +-------------+-------------+
    |  size(4)    | type(4)     | first header byte
    +-------------+-------------+
    |   payload size (0 - 64)   | header bytes number 2 to 9
    +---------------------------+
    |   payload data            | payload bytes (JSON strings or numbers in text format)
    +---------------------------+

Header

Payload size

If the payload data is between 0 and 11 bytes (inclusive), the size is encoded in the first 4 bits of the header. Otherwise, the size of the payload is encoded in the next bytes, and the first 4 bits indicate the number of bytes used to encode the payload size, using the following table:

Type

Example

The following JSON object:

{"a": false, "b":true}

is encoded as the following 7 bytes of binary data:

6c 17 61 02 17 62 01

MSRV

Requires rust >= 1.63 (debian stable)