Appender with array

[jpambrun]

I can't seem to figure out how to use an appender with with a column of type float[2]

appender.appendFloat([1.1,2.2]);

gives me TypeError: A number was expected

I thought maybe

appender.appendFloat(1.1);
appender.appendFloat(2.2);

but that gives me error: Failed to cast value: Unimplemented type for cast (FLOAT -> FLOAT[2]) which now feels circular. I can't really find any example, tests or documentation.

[jraymakers]

The DuckDB type for float[2] is ARRAY. So you need to use appendArray.

Note that appendArray requires a second argument describing the data type. In this case, you'd pass ARRAY(FLOAT, 2).

[jpambrun]

Thanks for the help. With these pointers I managed to get it to work the following, but it's quite the set or arcane incantations..

import { DuckDBInstance, FLOAT, ARRAY, DuckDBArrayValue } from '@duckdb/node-api';

const instance = await DuckDBInstance.create(':memory:');
const connection = await instance.connect();

await connection.run(` CREATE TABLE IF NOT EXISTS test ( name TEXT, fl_array FLOAT[2], ); `);

const appender = await connection.createAppender('test');

appender.appendVarchar('test');
appender.appendArray(new DuckDBArrayValue([1.01, 2.0]), ARRAY(FLOAT, 2));
appender.endRow();


appender.close();

const results = await connection.runAndReadAll(` SELECT * FROM test`);

console.log(results.getRows());
connection.close();

[jraymakers]

Glad you got it to work!

I'm curious which parts you found the most cumbersome. (I'm always looking for opportunities to improve the API.)

Note that you can use the slightly more concise helper arrayValue instead of new DuckDBArrayValue.

Also, there's an alternate way to use the appender. You can create a data chunk, set the data in that chunk, and then append the chunk. There are convenience methods to set the data in a chunk all at once, such as setColumns and setRows. See the "Append Data Chunk" section of the docs.

[jpambrun]

I imagine the goal here is to keep close to the underlying C api, but a external user like me expect a slight higher level of abstraction.
For instance, this is very verbose:

appender.appendArray(new DuckDBArrayValue([1.01, 2.0]), ARRAY(FLOAT, 2));

and I feel like the following has the same information:

appender.appendFloat([1.1, 2.2])

Also, the discoverability of arrayValue, DuckDBArrayValue, ARRAY is nil. I just could not have figure that out on my own even after peaking at the source.

I did see the chunk approach, but at first I only say setColumns and it didn't align well with my use case, but this works

const chunk = DuckDBDataChunk.create([VARCHAR, ARRAY(FLOAT, 2)]);
chunk.setRows([
  ['duck', new DuckDBArrayValue([1,2])],
  ['mallard', new DuckDBArrayValue([3.1,4.5])],
  ['goose', new DuckDBArrayValue([1.21,2.324])],
]);
appender.appendDataChunk(chunk);

Again, I wish I could pass normal arrays instead of new DuckDBArrayValue(..). I can't really see why this is required by the API. It feels like it should be handled internally.

[jraymakers]

Thanks for the feedback. I agree many parts could be documented better.

Regarding the need for arrayValue (or new DuckDBArrayValue(...)): a JavaScript array could represent either a DuckDB ARRAY or LIST, hence the need for arrayValue and listValue, at least in some contexts. However, in some of the above cases, the API can likely know which type is meant, and do the necessary wrapping. I'll see what's possible.