Duckdb: Nodejs API - Support for outputting data as binary columns.

[jpkli]

Is it possible to get binary columns from NodeJS API? Currently, the NodeJS API only allows getting data or results as rows (in JSON arrays). It would be great if it can return the data as binary columns in TypedArrays. In addition, it would be awesome if it can have support for chunking or streaming the output.

[hannesmuehleisen]

The focus for the first iteration was to more or less support the sqlite nodejs API. Streaming access to typed arrays is certainly a nice addition, but I would prefer if I did not have to implement that myself. Happy to review a PR.

[rostislavdeepcrawl]

You can try node-duckdb
It supports returning binary data as nodejs Buffers, which are instances of Uint8Array
Example:

  it("supports BLOB", async () => {
    const result = await connection.executeIterator<Buffer[]>(`SELECT 'AB'::BLOB;`, {
      rowResultFormat: RowResultFormat.Array,
    });
    const resultBuffer = result.fetchRow()[0];
    const view = new Int8Array(resultBuffer);
    // ASCII "a"
    expect(view[0]).toBe(65);
    // ASCII "b"
    expect(view[1]).toBe(66);
  });

Does this address your use case? Or do you want the whole row to be returned as a TypedArray?

It also supports streaming using regular Nodejs Readable streams, see here

If you have any questions, let us know

[jpkli]

Thank you @rostislavdeepcrawl for the node-duckdb work! I will try it.

I actually want the whole __column__ to be returned as a TypedArray based on the DuckDB field type. I think node-duckdb can provide a work-around, but it would be nice if the API can support fetching columns as TypeArrays (for performance as well).

[willium]

@rostislavdeepcrawl

You can try node-duckdb
It supports returning binary data as nodejs Buffers, which are instances of Uint8Array
Example:

  it("supports BLOB", async () => {
    const result = await connection.executeIterator<Buffer[]>(`SELECT 'AB'::BLOB;`, {
      rowResultFormat: RowResultFormat.Array,
    });
    const resultBuffer = result.fetchRow()[0];
    const view = new Int8Array(resultBuffer);
    // ASCII "a"
    expect(view[0]).toBe(65);
    // ASCII "b"
    expect(view[1]).toBe(66);
  });

Does this address your use case? Or do you want the whole row to be returned as a TypedArray?

It also supports streaming using regular Nodejs Readable streams, see here

If you have any questions, let us know

I'm curious what the difference in objective is between node-duckdb and the one bundled in this repo. It seems like this early in the development, it'd be great to rally around one client. It seems node-duckdb is inherently consumed in row-oriented manner (with row iterators)? I'm not sure I understand what the benefit of creating binary arrays across a row of different types—it seems like streaming would be better done as chunks of columnar-oriented results.

@hannesmuehleisen really appreciate your work kicking this off—once us JavaScript kids align, we can get a PR in. What _would_ be great from the DuckDB team: any guidance or examples you have of columnar oriented output, chunking, and/or stream as they relate to the internals

[hannesmuehleisen]

I think it would be most straightforward to emulate the C++ API in JS-land, where you can call Fetch() on a streaming query result and it will yield a DataChunk that contains a bunch of Vectors that contain c-style arrays of primitive values of length STANDARD_VECTOR_SIZE, which is currently 1024. This is what we also do internally in the JDBC API. Mapping those DataChunks to JavaScript more or less directly would be the most efficient I think.

[willium]

Reference to relevant part of JDBC source

[hannesmuehleisen]

Isn't JNI code beautiful? :D

[rostislavdeepcrawl]

@jpkli

I actually want the whole column to be returned as a TypedArray based on the DuckDB field type.

Would you provide more info on your use case? I'm curious what you're trying to do

@willium

I'm curious what the difference in objective is between node-duckdb and the one bundled in this repo.

In terms of objective in the grand scheme of things there isn't a difference. We developed node-duckdb for our use case at DeepCrawl and released it in prod 3 days after the DuckDb team announced the release of their bindings. In terms of smaller differences, it's written in TS which is important to us as we're a TS company.

It seems like this early in the development, it'd be great to rally around one client.

We're definitely open for collaboration 👍

[willium]

@jpkli

I actually want the whole column to be returned as a TypedArray based on the DuckDB field type.

Would you provide more info on your use case? I'm curious what you're trying to do

@willium

I'm curious what the difference in objective is between node-duckdb and the one bundled in this repo.

In terms of objective in the grand scheme of things there isn't a difference. We developed node-duckdb for our use case at DeepCrawl and released it in prod 3 days after the DuckDb team announced the release of their bindings. In terms of smaller differences, it's written in TS which is important to us as we're a TS company.

It seems like this early in the development, it'd be great to rally around one client.

We're definitely open for collaboration 👍

TypeScript is great, we also use it. But it would be nice to adapt the first-party tool to typescript and better typings rather than have a parallel tool :)

I took a look at your implementation of fetchRow--it looks great!--can I ask why you don't parse all the rows in the current chunk rather than just one at a time?

[rostislavdeepcrawl]

@willium

TypeScript is great, we also use it. But it would be nice to adapt the first-party tool to typescript and better typings rather than have a parallel tool :)

I don't think it would make sense for us, since we have already invested in creating our own tool which works well for us (it's been thoroughly tested, it's working in production and it is in typescript already). But we'd definitely be very happy to have the duckdb team contributing to node-duckdb :)

I took a look at your implementation of fetchRow--it looks great!--can I ask why you don't parse all the rows in the current chunk rather than just one at a time?

Thanks!
There isn't a reason. Do you suppose that parsing all rows in a data chunk at once will make a statistically significant performance difference?