duck_parser_utils.js

const {
    DuckDBBitValue,
    DuckDBBlobValue, DuckDBDateValue, DuckDBDecimalValue,
    DuckDBIntervalValue, DuckDBListType,
    DuckDBListValue, DuckDBMapType,
    DuckDBMapValue, DuckDBStructType,
    DuckDBStructValue,
    DuckDBTimestampMillisecondsValue,
    DuckDBTimestampNanosecondsValue,
    DuckDBTimestampSecondsValue,
    DuckDBTimestampTZValue,
    DuckDBTimestampValue,
    DuckDBTimeTZValue, DuckDBTimeValue, DuckDBTypeId, DuckDBUnionValue, DuckDBUUIDValue
} = require("@duckdb/node-api");

function getSchemaType(columnType) {
    if (columnType instanceof DuckDBListType) {
        // return [generateDuckDBSchema(columnType.valueType.entryNames, columnType.valueType.entryTypes)];
        if(columnType.valueType.entryNames) {
            return {
                "arrayOf": generateDuckDBSchema(columnType.valueType.entryNames, columnType.valueType.entryTypes)
            };
        } else {
            return {
                "arrayOf": getSchemaType(columnType.valueType)
            };
        }
    }


    if (columnType instanceof DuckDBStructType) {
        // return generateDuckDBSchema(columnType.entryNames, columnType.entryTypes);
        return {
            "structOf": generateDuckDBSchema(columnType.entryNames, columnType.entryTypes)
        };
    }

    if (columnType instanceof DuckDBMapType) {
        // return {
        //     "keyType": getSchemaType(columnType.keyType),
        //     "valueType": getSchemaType(columnType.valueType)
        // };
        return {
            "mapOf": {
                "keyType": getSchemaType(columnType.keyType),
                "valueType": getSchemaType(columnType.valueType)
            }
        };
    }


    const typeId = columnType.typeId;
    switch (typeId) {
        case DuckDBTypeId.BIT:
            return 'bit';
        case DuckDBTypeId.BLOB:
            return 'blob';
        case DuckDBTypeId.DATE:
            return 'date';
        case DuckDBTypeId.DECIMAL:
            return 'decimal';
        case DuckDBTypeId.INTERVAL:
            return 'interval';
        case DuckDBTypeId.TIMESTAMP_MS:
            return 'timestamp_ms';
        case DuckDBTypeId.TIMESTAMP_NS:
            return 'timestamp_ns';
        case DuckDBTypeId.TIMESTAMP_S:
            return 'timestamp_s';
        case DuckDBTypeId.TIMESTAMP_TZ:
            return 'timestamp_tz';
        case DuckDBTypeId.TIMESTAMP:
            return 'timestamp';
        case DuckDBTypeId.TIME_TZ:
            return 'time_tz';
        case DuckDBTypeId.TIME:
            return 'time';
        case DuckDBTypeId.UNION:
            return "union"
        case DuckDBTypeId.UUID:
            return "uuid"
        case DuckDBTypeId.HUGEINT:
            return 'hugeint';
        case DuckDBTypeId.VARCHAR:
            return 'varchar';
        case DuckDBTypeId.BOOLEAN:
            return 'boolean';
        case DuckDBTypeId.TINYINT:
            return "tinyint"
        case DuckDBTypeId.SMALLINT:
            return "smallint"
        case DuckDBTypeId.INTEGER:
            return 'int';
        case DuckDBTypeId.BIGINT:
            return "bigint";
        case DuckDBTypeId.FLOAT:
            return 'float';
        case DuckDBTypeId.DOUBLE:
            return "double"
        default:
            return 'unknown';
    }
}

// row is array of values of native duckdb type
function stringifyDuckDBRow(row) {
    return JSON.stringify(row, (key, value) => {
        if (typeof value === 'undefined' || value === null) {
            return null;
        }

        if (value instanceof DuckDBListValue) {
            return convertDuckDBListValue(value);
        } else if (value instanceof DuckDBStructValue) {
            return convertDuckDBStructValue(value);
        } else if (value instanceof DuckDBMapValue) {
            return convertDuckDBMapValue(value);
        }

        return convertDuckDBValue(value);
    });
}

function convertDuckDBValue(value, convertBooleanToString = false) {
    if (typeof value === 'bigint') {
        return Number(value);
    }

    if (convertBooleanToString && typeof value === 'boolean') {
        return value.toString();
    }

    if (typeof value === 'undefined') {
        return null;
    }

    if (typeof value === 'number' && !isFinite(value)) {
        return null;
    }

    if (value instanceof DuckDBListValue) {
        return convertDuckDBListValue(value);
    }

    if (value instanceof DuckDBStructValue) {
        return convertDuckDBStructValue(value);
    }

    if (value instanceof DuckDBMapValue) {
        return convertDuckDBMapValue(value);
    }

    if (value instanceof DuckDBTimestampMillisecondsValue){
        return value.milliseconds ? Number(value.milliseconds) : null;
    }

    if (value instanceof DuckDBTimestampNanosecondsValue){
        return value.nanoseconds ? Number(value.nanoseconds) : null;
    }

    if (value instanceof DuckDBTimestampSecondsValue) {
        return value.seconds ? Number(value.seconds) : null;
    }

    if(value instanceof DuckDBTimestampTZValue){
        return value.micros ? Number(value.micros) : null;
    }

    if(value instanceof DuckDBTimestampValue) {
        return value.micros ? Number(value.micros) : null;
    }

    if(value instanceof DuckDBTimeTZValue) {
        return value.micros ? Number(value.micros) : null;
    }
    if (value instanceof DuckDBTimeValue){
        return value.micros ? Number(value.micros) : null;
    }

    if(value instanceof DuckDBIntervalValue) {
        return {
            months: value.months,
            days: value.days,
            micros: value.micros ? Number(value.micros) : null
        }
    }

    if (value instanceof DuckDBBlobValue) {
        return value.toString(); // Or potentially `Array.from(value.bytes)` if you need the byte array.
    }

    if (value instanceof DuckDBDateValue){
        return value.days;
    }

    if (value instanceof DuckDBDecimalValue) {
        return {
            width: value.width,
            scale: value.scale,
            value: value.value ? Number(value.value) : null,
            double: value.toDouble()
        }
    }
    if(value instanceof DuckDBBitValue){
        return value.toBits()
    }
    if(value instanceof DuckDBUnionValue){
        return {
            tag: value.tag,
            value: convertDuckDBValue(value.value)
        }
    }

    if(value instanceof DuckDBUUIDValue) {
        return value.hugeint ? Number(value.hugeint) : null;
    }

    return value;
}

function convertDuckDBListValue(value) {
    return value.items.map(item => convertDuckDBValue(item));
}

function convertDuckDBStructValue(value) {
    return Object.fromEntries(Object.entries(value.entries).map(([key,val]) => [key, convertDuckDBValue(val)]));
}

function convertDuckDBMapValue(value) {
    return Object.fromEntries(value.entries.map(obj => [obj.key, convertDuckDBValue(obj.value)]));
}

function generateDuckDBSchema(columnNames, columnTypes) {
    const schema = {};
    for (let i = 0; i < columnNames.length; i++) {
        schema[columnNames[i]] = getSchemaType(columnTypes[i]);
    }
    return schema;
}

Usage

const result = await connection.stream(query);
const columnNames = result.columnNames();

// extract schema
const schema = generateDuckDBSchema(columnNames, result.columnTypes());

// convert rows to native javascript type
for(row of result.rows()) {
	const parsedRow = stringifyDuckDBRow(row); // This returns parsed and stringified version. Wrap in JSON.parse(..) if needed native array object
}