n8n-nodes-motherduck

This is an n8n community node that lets you use MotherDuck (DuckDB in the cloud) in your n8n workflows.

MotherDuck is a serverless analytics platform built on DuckDB. It allows you to run SQL queries on your data without managing infrastructure.

n8n is a fair-code licensed workflow automation platform.

Important: only n8n:latest-debian is supported.

This node uses the @duckdb/node-api package which includes native bindings compiled for glibc-based systems. Alpine Linux uses musl libc instead, which is incompatible with these bindings. If you're running n8n on Alpine Linux (common in Docker images), you'll need to use a glibc-based distribution like Debian or Ubuntu instead.

Operations

Row Operations

• Get: Query rows from a table with optional filters
• Insert: Insert rows into a table
• Upsert: Insert or update rows based on matching columns (uses MERGE)
• Delete: Delete rows matching specified conditions

Table Operations

• List: List all tables in a database/schema
• Import from File: Create or load data from CSV, JSON, or Parquet files
• Insert from Data: Batch insert an array of objects into a table (create or append)
• Upsert from Data: Batch upsert an array of objects into a table

Credentials

You need a MotherDuck access token to use this node. You can create one at app.motherduck.com/settings/tokens.

Installation

Follow the installation guide in the n8n community nodes documentation.

npm

npm install n8n-nodes-motherduck

Manual Installation

1. Clone this repository
2. Run npm install
3. Run npm run build
4. Copy the dist folder to your n8n custom nodes directory

Usage

Get Rows

Query data from a MotherDuck table:

1. Select Row resource and Get operation
2. Choose the database, schema, and table
3. Optionally add filter conditions
4. Set limit, order by, or return all rows

Insert Rows

Insert rows from your workflow into a table:

Using an existing table:

1. Select Row resource and Insert operation
2. Choose the database, schema, and table
3. Optionally specify an Input Data Field to map from a specific field in your input (e.g., data or user.profile) - leave empty to use the entire input
4. Map columns using the resource mapper (auto-map or define manually)

Creating a new table:

1. Select Row resource and Insert operation
2. Set Table Mode to "Create New Table"
3. Enter the new table name
4. Optionally specify an Input Data Field to map from a specific field
5. Optionally define column types in Column Definitions (defaults to VARCHAR for all columns)

Upsert Rows

Insert new rows or update existing ones based on matching columns:

Using an existing table:

1. Select Row resource and Upsert operation
2. Choose the database, schema, and table
3. Optionally specify an Input Data Field to map from a specific field in your input
4. Select which columns to match on using the resource mapper (no unique constraint required - uses MERGE)
5. Map the values to insert/update

Creating a new table:

1. Select Row resource and Upsert operation
2. Set Table Mode to "Create New Table"
3. Enter the new table name
4. Specify Match Columns as a comma-separated list (e.g., id, email)
5. Optionally specify an Input Data Field and Column Definitions

Delete Rows

Delete rows matching specified conditions:

1. Select Row resource and Delete operation
2. Choose the database, schema, and table
3. Add filter conditions (at least one required)

List Tables

List all tables in a database:

1. Select Table resource and List operation
2. Choose the database
3. Optionally filter by schema

Import from File

Create or append to a table from external files:

1. Select Table resource and Import from File operation
2. Choose the database and schema
3. Enter the target table name
4. Enter the file URL(s):
   • Single file: https://example.com/data.csv
   • Multiple files: https://example.com/file1.csv, https://example.com/file2.csv
   • Glob pattern: https://example.com/data/*.parquet
   • S3: s3://bucket/path/file.json
5. Select format (auto-detect, CSV, JSON, or Parquet)
6. Choose import mode:
   • Create Table: Create new table (fails if exists)
   • Create or Replace: Drop and recreate table
   • Create If Not Exists: Only create if table doesn't exist
   • Append: Insert into existing table
7. For CSV files, configure delimiter, header, quote character, etc.

Insert from Data (Batch Insert)

Batch insert an array of objects into a table. Perfect for processing aggregated data from previous nodes:

1. Select Table resource and Insert from Data operation
2. Choose the database and schema
3. Enter the target table name
4. Specify the Data Field containing your array (e.g., movies, data.items), or leave empty to use input items directly
5. Choose mode:
   • Create Table: Create new table (fails if exists)
   • Create or Replace: Drop and recreate table
   • Create If Not Exists: Only create if table doesn't exist
   • Append: Insert into existing table
6. Optionally define column types in Column Definitions

Example: A workflow that fetches 200 movies from an API, aggregates them into one item with a movies array, then uses Insert from Data to bulk insert all 200 movies in a single operation.

Upsert from Data (Batch Upsert)

Batch upsert an array of objects into a table:

1. Select Table resource and Upsert from Data operation
2. Choose the database and schema
3. Enter the target table name
4. Specify the Data Field containing your array
5. Enter Match Columns (comma-separated, e.g., id, email)
6. Choose mode:
   • Create or Replace Then Upsert: Drop and recreate table, then upsert
   • Create If Not Exists Then Upsert: Create table if needed, then upsert
   • Upsert Only: Table must exist
7. Optionally define column types

Development

Quick Start with Docker

The easiest way to test the node locally is with Docker:

# Install dependencies and build
make install
make build

# Start n8n with the custom node
make start

# View logs
make logs

# Stop n8n
make stop

Then open http://localhost:5678 in your browser. The MotherDuck node will be available in the nodes panel.

Development Workflow

# Terminal 1: Watch for TypeScript changes
make dev

# Terminal 2: Run n8n (restart after changes)
make start

After making changes to the node code:

1. The TypeScript watcher will recompile automatically
2. Restart n8n to pick up changes: make restart

Manual Setup

# Install dependencies
npm install

# Build the node
npm run build

# Run in development mode (watch)
npm run dev

# Lint
npm run lint

Makefile Commands

Resources

• n8n community nodes documentation
• MotherDuck documentation
• DuckDB documentation

License

MIT