LoginSign Up
← Back to all sparks
N

nodbi

ANALYTICS
Velocity0.0

Document NoSQL Database DBI Connector package for R

github.com/ropensci/nodbi ↗

One document API over six databases, and every release is spent absorbing their JSON engines' churn

document-databasesjsonduckdbsqliteropensciquery-performance
◆Current state
nodbi presents a single document-store interface — docdb_create, docdb_query, docdb_update — over SQLite, DuckDB, PostgreSQL, MongoDB, CouchDB and Elasticsearch. The engineering reality behind that abstraction is that each backend's JSON support keeps moving, and the releases show it: jsonb_tree adopted as RSQLite 2.4.4 exposes it, json_tree reworked for DuckDB 1.3.0, then avoided entirely for DuckDB listfields because it was too slow. The 0.11.0 release in late 2024 is the one that changed the contract, making docdb_query() return columns of a single consistent type.
◆Where it's heading
Two threads dominate. The first is performance, pursued backend by backend: fast direct NDJSON import moved from DuckDB-only to SQLite and PostgreSQL, query refactors chasing each DuckDB release, and the removal of expensive tree-walking where a cheaper path exists. The second is making results predictable — consistent column types, version checks on the database backend, clearer messages when a Postgres database does not exist yet or when column names contain the dots nodbi reserves for JSON paths.
◆Prediction
Given that most recent releases are triggered by DuckDB and RSQLite version changes, the next one likely follows the same pattern — adopting a new JSON function or working around a slow one. The duplicate-_id handling added in 0.14.0 suggests NDJSON ingestion edge cases are the current active area.

◆Recent moves

  1. 8mo ago

    jsonb_tree adopted; $in string queries and duplicate _id handling fixed

    Fixes $in queries against string values on SQLite, adopts jsonb_tree now that RSQLite 2.4.4 provides it, and drops the costly json_tree path for DuckDB when listing fields. It also handles docdb_update() against NDJSON files containing duplicate _id values — the ingestion edge case that direct file import made reachable.

    View source ↗
  2. 1y ago

    DuckDB version parsing and listfields fix

    Corrects DuckDB version-number parsing and fixes listfields = TRUE for the DuckDB source. A follow-up patch to the 0.13.0 DuckDB work.

    View source ↗
  3. 1y ago

    docdb_query reworked for DuckDB 1.3.0

    Reworks docdb_query() for DuckDB 1.3.0, including its new json_tree function, and moves the non-persistent-connection warning to where connections are actually opened. Keeping pace with DuckDB's JSON surface is the recurring cost of supporting it as a backend.

    View source ↗
  4. 1y ago

    NDJSON writing delegated to DuckDB's internal function

    Switches NDJSON file writing to DuckDB's internal function rather than doing it in R, continuing the pattern of pushing work down into the backend engine wherever it exposes a native path.

    View source ↗
  5. 1y ago

    Query results get consistent column types; fast NDJSON import reaches SQLite and Postgres

    ⚡ SPARK

    Changes what docdb_query() returns: each column now holds a single type across all rows instead of mixing atomic values and lists depending on how many items each document carried. It also brings direct NDJSON import to SQLite and PostgreSQL, matching what DuckDB already had. The type change is flagged as potentially breaking, and it is the release that makes query results safe to program against.

    View source ↗
  6. 1y ago

    Query and file-import speedups via newer DuckDB features

    Refactors docdb_query() onto newer DuckDB features to accelerate queries, and speeds up creating and updating from file. One of the incremental performance releases that the direct-import work in 0.11.0 builds on.

    View source ↗