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:

Payload Data Size Range	Size Encoding	First 4 bits of Header
0 to 11 bytes	u4 (embedded in first 4 bits)	0 to 11 (0x0 to 0xB)
12 to 2^8 - 1 bytes	u8	12 (0xC)
2^8 to 2^16 - 1 bytes	u16	13 (0xD)
2^16 to 2^32 - 1 bytes	u32	14 (0xE)
2^32 to 2^64 - 1 bytes	u64	15 (0xF)

Type

Type	Hex Code	Description
`Null`	0x0	The element is a JSON "null".
`True`	0x1	The element is a JSON "true".
`False`	0x2	The element is a JSON "false".
`Int`	0x3	The element is a JSON integer value in the canonical RFC 8259 format.
`Int5`	0x4	The element is a JSON5 integer, such as `0xABC`.
`Float`	0x5	The element is a JSON floating-point value in the canonical RFC 8259 format.
`Float5`	0x6	The element is a JSON5 floating-point value that is not in the canonical JSON format.
`Text`	0x7	The element is a JSON string value that does not contain any escapes.
`TextJ`	0x8	The element is a JSON string value that contains RFC 8259 character escapes.
`Text5`	0x9	The element is a JSON5 string value that contains character escapes, including some from JSON5.
`TextRaw`	0xA	The element is a JSON string value that contains UTF8 characters that need escaping in JSON.
`Array`	0xB	The element is a JSON array. The header of the first array element starts immediately after the array header.
`Object`	0xC	The element is a JSON object. Object keys (strings) and values are alternated in the payload.
`Reserved13`	0xD	Reserved for future expansion.
`Reserved14`	0xE	Reserved for future expansion.
`Reserved15`	0xF	Reserved for future expansion.

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

byte	value	description
0	0x6c	header: payload size = 6, type = Object (0xC)
1	0x17	header: payload size = 1, type = Text (0x7)
2	0x61	payload: 'a'
3	0x02	header: payload size = 0, type = False (0x2)
4	0x17	header: payload size = 1, type = Text (0x7)
5	0x62	payload: 'b'
6	0x01	header: payload size = 0, type = True (0x1)

MSRV

Requires rust >= 1.63 (debian stable)

License

lovasoa/serde-sqlite-jsonb

ErrorLooks like something went wrong!

About

Resources

License

Uh oh!

Stars

Watchers

Forks

Releases

Packages 0

Languages

Error
Looks like something went wrong!

Packages