Basic node red node for DuckDB.

config database path, such as /tmp/duckdb.db. Or use :memory: Please read offical docs DuckDB Docs

There are some sql execution options.

• msg.sql exec: executes the sql query(ies) from input msg.sql, and does not return any result. DuckDB exec
• sql exec: execute the sql query(ies) from code editor, and does not return any results. DuckDB exec
• sql all: execute one sql query from code editor, and returns execution results. DuckDB all
• sql each: execute one sql query from code editor, and returns row by row. DuckDB each
• PS: execute the sql procedure statement from code editor, taken msg.params as parameters. msg.params must be an array. And does not return any results. example

Input SQL queries.

This node prototyped a node-red data transform node which read from database, transform data and then insert into database. The drive behind of this idea is that I think for the personal use all the data platform exist I knew are too heavy and difficult to setup and use. Data linage would also be difficult to achive.

This node provided an javascript code editor for transforming each row by function msg.proc and insert the processed data into database, also be able to output the result. This node also provided a template for user. The template is

msg.beforeProc = "CREATE TABLE <table name>(...);"
msg.procQuery = "SELECT * FROM <prev table name>";
msg.proc = function(row) {
    // transform row from proc query
    // return insert to new table
    return "INSERT INTO <table name> VALUES(" + JSON.stringify(Object.values(row)).slice(1, -1).replaceAll('"', '\'') + ");";
}

msg.afterProc = "SELECT * FROM <table name> LIMIT 10;";

Batch input defined the batch size of the process query. The value default to 100.

msg.beforeProc defined a sql that will be executed before the process function. Usually it should create a table to which new data insert. This field is optional.

msg.procQuery defined a sql that return data which will be processed from database. It should be a SELECT and MUST NOT end with ;. The code will add the limit and offset for batch process. This field is required for get data from db.

msg.proc defined a function which input is the row returned from sql defined in msg.procQuery. Function body should transform the data into some format and then return an INSERT query. The code running on background will handle the insert.

msg.afterProc defined a query which will be executed after all the rows being processed. The result of this query will be added to msg.payload and pass to the next node(s). This field is optional.

A node for importing csv or parquet file to duckdb. User can pass advanced sql import to msg.import as input. DuckDB Import

config database path, such as /tmp/duckdb.db. Or use :memory:. Please read offical docs duck db docs

• csv: load csv file from local and create table given file path and table name. DuckDB Import CSV
• parquet: load parquet file from local and create table given file path and table name. DuckDB Import Parquet
• msg.import: execute the import sql get from input msg.import . DuckDB Import SQLs

Input the create table name if choose csv or parquet.

Input the csv or parquet file path.

A node for exporting csv or parquet file to duckdb. User can pass advanced sql from msg.export as input. DuckDB Export

config database path, such as /tmp/duckdb.db. Or use :memory:. Please read offical docs duck db docs

• parquet: from table export parquet file to local given file path and table name. DuckDB Export Parquet
• msg.export: execute the export sql get from input msg.export . DuckDB export SQLs

Input the table name if choose parquet.

Input the parquet file path.