How to use DuckDB in Node-RED(just in node-red function)

General
1

Hi guys, the existing DuckDB node in Node-RED is broken.
How can I do the same with a function node?

Here is an example, but when i try it again, the Node-RED will broken down. The log information said that [error] Error: IO Error: Cannot open file "demo.duckdb"

[
    {
        "id": "f7d4d3866eecb39f",
        "type": "function",
        "z": "c86c4b4c464dad69",
        "name": "function 2",
        "func": "// 2. 主函数（IIFE）\n(async () => {\n    // 2.1 创建/打开数据库（磁盘文件）\n    const db = await duckdbNodeApi.DuckDBInstance.create('demo.duckdb', { threads: '4' });\n    const conn = await db.connect();\n\n    // 2.2 建表\n    await conn.run(`\n    CREATE OR REPLACE TABLE users (\n      id    INTEGER,\n      name  VARCHAR,\n      age   INTEGER\n    )\n  `);\n\n    // 2.3 准备批量插入\n    const appender = await conn.createAppender('users');\n    const rows = [\n        [4, 'ddac', 45]\n    ];\n    for (const [id, name, age] of rows) {\n        appender.appendInteger(id);\n        appender.appendVarchar(name);\n        appender.appendInteger(age);\n        appender.endRow();\n    }\n    appender.flushSync();   // 立即落盘\n    appender.closeSync();\n\n    // 2.4 查询\n    const reader = await conn.runAndReadAll(\n        'SELECT * FROM users WHERE age > 30 ORDER BY age DESC'\n    );\n\n    // 2.5 输出结果（JSON 形式）\n    console.log(reader.getRowObjectsJson());\n\n    // 2.6 清理\n  await conn.disconnectSync();\n  await db.duckdb_\n})();\nreturn msg;",
        "outputs": 1,
        "timeout": 0,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [
            {
                "var": "duckdbNodeApi",
                "module": "@duckdb/node-api"
            }
        ],
        "x": 260,
        "y": 80,
        "wires": [
            []
        ]
    }
]
2

Assuming the following import:

image
image1130×226 6.95 KB

Function:

const instance = await duckdb.DuckDBInstance.create('my_duckdb.db');
node.warn({instance})

return msg;

debug result:

{"instance":{"db":{}}}

and the my_duckdb.db has been created in my homepath.

1 Like
3

Now, when you try to connect with DuckDB CLI to my_duckdb.db, you get:

Error: unable to open database "my_duckdb.db": IO Error: File is already open in node.exe

How can I close the database without shutting down Node-RED/Node.exe?

4

The documentation states:

Multiple instances in the same process should not attach the same database.

As the db is already created, you can use the cache method:

const instance = await duckdb.DuckDBInstance.fromCache('my_duckdb.db');

node.warn({instance})

const connection = await instance.connect();
node.warn({connection})

connection.closeSync();

return null
5

Probably won't answer your question but I once used DuckDB in the browser --> FlowHub: [DuckDB] In-Browser DuckDB with Discourse API

It worked but just for demonstration purposes!

6

It still not work

7

We cannot guess what you are trying to do, what does the code in your function node look like ?