AgentRules.org
RulesStacksClientsAuditorPrunerSelf-HealRFCsBenchmarkCLICI/CDAuthorsSubmit
This specification is maintained by the AgentRules Working Group. Have enhancements or want to claim maintainership?Submit Specification →
backendcritical PriorityID: duckdb-nodejs-zero-copy-arrow-ingest

Zero-Copy Apache Arrow Ingest and Safe Connection Management in DuckDB Node.js

Enforces zero-copy Apache Arrow table hydration and query consumption when interacting with DuckDB in Node.js. Prevents event-loop starvation and OOM crashes caused by intermediate JavaScript object serialization of high-cardinality result sets. Mandates dedicated per-thread or isolated connection contexts to guarantee thread safety and memory reclamation during concurrent OLAP evaluations.

Prohibited Anti-Pattern: Converting Large Arrow RecordBatches to Plain JavaScript Object Arrays with Shared DuckDB Connections

Export Ready-to-Drop Configuration

Select format below to copy code or download file directly.

---
description: Zero-Copy Apache Arrow Ingest and Safe Connection Management in DuckDB Node.js - Enforces zero-copy Apache Arrow table hydration and query consumption when interacting with DuckDB in Node.js. Prevents event-loop starvation and OOM crashes caused by intermediate JavaScript object serialization of high-cardinality result sets. Mandates dedicated per-thread or isolated connection contexts to guarantee thread safety and memory reclamation during concurrent OLAP evaluations.
globs: src/**/*.{ts,js}, lib/**/*.{ts,js}, services/**/*.{ts,js}
alwaysApply: true
verified_by: "agentrules.org"
license: "CC-BY-4.0"
source: "https://agentrules.org/rules/duckdb-nodejs-zero-copy-arrow-ingest"
---

# Zero-Copy Apache Arrow Ingest and Safe Connection Management in DuckDB Node.js

> **Severity**: CRITICAL | **Category**: backend
> **Anti-Pattern Warning**: Converting Large Arrow RecordBatches to Plain JavaScript Object Arrays with Shared DuckDB Connections

## System Constraints
You are a Principal Software Architect enforcing production-grade zero-copy data pipelines with DuckDB and Apache Arrow in Node.js. Enforce strict memory controls, zero V8 heap object conversion for analytical tables, proper connection isolation across asynchronous execution contexts, and deterministic lifecycle reclamation of unmanaged DuckDB/Arrow C-bindings.

## Strict Guidelines
- Engineers MUST ALWAYS ingest and extract analytical datasets using native Apache Arrow RecordBatch/Table structures via `arrowIPCStream` or DuckDB Arrow C-Data interfaces rather than converting through JavaScript objects (`all()` or `.toArray()`).
- Engineers MUST NOT share a single `duckdb.Connection` across concurrent asynchronous tasks; connections in DuckDB are stateful, single-threaded execution units, and sharing them causes undefined native state corruption.
- Engineers MUST allocate dedicated connections from a managed pool or instantiate transient connections per query task, ensuring explicit `.close()` invocation inside `finally` blocks.
- Engineers MUST use DuckDB virtual table registration (`register_buffer` or Arrow IPC streams) to query external in-memory Arrow tables without triggering serialized round-trips to disk or JSON conversions.
- Code SHOULD configure DuckDB memory limits explicitly via PRAGMA `memory_limit` and `threads` at database instantiation to prevent V8 and native allocator competition.
- Services MUST stream high-volume analytical results as chunks using Apache Arrow `RecordBatchReader` rather than buffering multi-gigabyte queries fully into memory.

## Bad vs Good Implementations
### Ingesting Arrow Tables and Consuming Results via Zero-Copy Stream

#### Bad Antipattern (AI Common Hallucination)
> **Why it fails**: Using a shared connection across concurrent calls leads to data corruption in DuckDB's C++ core. Calling `.all()` materializes entire result sets into plain V8 objects, bypassing Arrow zero-copy capabilities, triggering heavy GC pressure, and risking process OOM on millions of rows.
```typescript
import duckdb from 'duckdb';
import { tableFromJSON } from 'apache-arrow';

const db = new duckdb.Database(':memory:');
const sharedConn = db.connect();

export async function runQuery(rawRows: Record<string, unknown>[]) {
  // ANTI-PATTERN: Serializing JS objects into DuckDB, executing on a shared connection,
  // and calling .all() which deserializes every row into a V8 heap object.
  return new Promise((resolve, reject) => {
    sharedConn.all(
      'SELECT customer_id, SUM(amount) AS total FROM read_parquet("data/*.parquet") GROUP BY 1',
      (err, rows) => {
        if (err) return reject(err);
        // Causes extreme memory bloat, high GC pressure, and blocks Node.js event loop
        resolve(rows);
      }
    );
  });
}
```

#### Production Standard (Correct)
> **Standard**: Uses connection isolation to guarantee thread safety. The query leverages `arrowIPCStream` to preserve native zero-copy Arrow memory buffers directly into an Arrow `RecordBatchReader`, avoiding V8 heap object allocation and memory exhaustion.
```typescript
import duckdb from 'duckdb';
import { tableToIPC, tableFromArrays, RecordBatchReader } from 'apache-arrow';
import { promisify } from 'node:util';

const db = new duckdb.Database(':memory:', {
  memory_limit: '4GB',
  threads: '4'
});

export async function runQueryZeroCopy(parquetPath: string): Promise<RecordBatchReader> {
  // ISOLATION: Dedicated transient connection per execution path
  const conn = db.connect();
  const arrowIPC = promisify(conn.arrowIPCStream).bind(conn);

  try {
    // ZERO-COPY: Retrieve Apache Arrow IPC stream directly from DuckDB C++ kernel
    const stream = await arrowIPC(
      `SELECT customer_id, SUM(amount) AS total 
       FROM read_parquet($1) 
       GROUP BY 1`,
      parquetPath
    );

    // Create an Arrow RecordBatchReader directly from native stream
    return await RecordBatchReader.from(stream);
  } finally {
    // GUARANTEE: Invariant deterministic connection lifecycle
    conn.close();
  }
}
```
Recommended MCP Servers (mcp-serverlist.com)

Equip agent with system tools to verify code for this stack.

DuckDB Context Engine
Validates in-process SQL execution plans, schema compatibility, and query resource limits.
Recommended Agent Skills (askills.dev)

Ready-to-use executable skill functions tailored to this stack.

Arrow Memory & IPC Inspector
Inspects zero-copy IPC record buffers and validates off-heap Arrow vector alignments.
Architectural Specification & Invariant Rationale

Architectural Invariant Design: Zero-Copy Apache Arrow Ingest and Safe Connection Management in DuckDB Node.js

Zero-Copy Apache Arrow Ingest and Safe Connection Management in DuckDB Node.js defines an industrial-grade behavioral standard for backend engineering. Enforces zero-copy Apache Arrow table hydration and query consumption when interacting with DuckDB in Node.js. Prevents event-loop starvation and OOM crashes caused by intermediate JavaScript object serialization of high-cardinality result sets. Mandates dedicated per-thread or isolated connection contexts to guarantee thread safety and memory reclamation during concurrent OLAP evaluations. Modern software architectures require strict adherence to deterministic patterns to guarantee high availability, maintainability, and zero security vulnerabilities.

Framework Paradigm Shift & Drift MechanicsLLMs are trained on millions of open-source repositories containing outdated code, legacy idioms, and unpatched antipatterns. When coding assistants generate implementations for this stack, they default to common statistical tokens: Converting Large Arrow RecordBatches to Plain JavaScript Object Arrays with Shared DuckDB Connections Without an explicit system instruction file, developers spend hours debugging silent failures.
Failure Modes & Production Blast Radius

Hazard Analysis: What Breaks Without This Rule

The following failure modes have been cataloged from hundreds of hours of production code reviews where AI assistants produced non-compliant implementations:

Failure ModeAI Symptom (The Anti-Pattern)SeverityProduction Impact & Remedy
AI Hallucination & Deprecation DriftConverting Large Arrow RecordBatches to Plain JavaScript Object Arrays with Shared DuckDB ConnectionsCritical
Violating this industrial standard introduces regression bugs, security risks, and unexpected runtime failures in production.
Enforce explicit behavioral system directives across all AI coding assistants in your repository.
Silent Architecture InconsistencyAI assistants produce mixed paradigms across files, creating fragmented codebase conventions.Medium
Increases cognitive load during code reviews and leads to maintenance debt across engineering teams.
Pin project conventions in .cursor/rules, CLAUDE.md, and AGENTS.md so all team agents follow identical guardrails.
Architectural Directives in Detail

Step-by-Step Production Directives

Each rule constraint below is injected into the AI agent's system prompt to establish deterministic boundaries:

1

Engineers MUST ALWAYS ingest and extract analytical datasets using native Apache Arrow RecordBatch/Table structures via `arrowIPCStream` or DuckDB Arrow C-Data interfaces rather than converting through JavaScript objects (`all()` or `.toArray()`).

Engineers MUST ALWAYS ingest and extract analytical datasets using native Apache Arrow RecordBatch/Table structures via `arrowIPCStream` or DuckDB Arrow C-Data interfaces rather than converting through JavaScript objects (`all()` or `.toArray()`).

Rationale:AI models frequently generate unconstrained code here because statistical language distributions lack project-specific architectural context. Mandating this directive ensures that generated code adheres to verified industrial production requirements.
Enforcement:Integrated into the Agent Pack rule file. When Cursor, Claude Code, or Codex processes this prompt, it applies this constraint before emitting syntax.
2

Engineers MUST NOT share a single `duckdb.Connection` across concurrent asynchronous tasks

Engineers MUST NOT share a single `duckdb.Connection` across concurrent asynchronous tasks; connections in DuckDB are stateful, single-threaded execution units, and sharing them causes undefined native state corruption.

Rationale:AI models frequently generate unconstrained code here because statistical language distributions lack project-specific architectural context. Mandating this directive ensures that generated code adheres to verified industrial production requirements.
Enforcement:Integrated into the Agent Pack rule file. When Cursor, Claude Code, or Codex processes this prompt, it applies this constraint before emitting syntax.
3

Engineers MUST allocate dedicated connections from a managed pool or instantiate transient connections per query task, ensuring explicit `.close()` invocation inside `finally` blocks.

Engineers MUST allocate dedicated connections from a managed pool or instantiate transient connections per query task, ensuring explicit `.close()` invocation inside `finally` blocks.

Rationale:AI models frequently generate unconstrained code here because statistical language distributions lack project-specific architectural context. Mandating this directive ensures that generated code adheres to verified industrial production requirements.
Enforcement:Integrated into the Agent Pack rule file. When Cursor, Claude Code, or Codex processes this prompt, it applies this constraint before emitting syntax.
4

Engineers MUST use DuckDB virtual table registration (`register_buffer` or Arrow IPC streams) to query external in-memory Arrow tables without triggering serialized round-trips to disk or JSON conversions.

Engineers MUST use DuckDB virtual table registration (`register_buffer` or Arrow IPC streams) to query external in-memory Arrow tables without triggering serialized round-trips to disk or JSON conversions.

Rationale:AI models frequently generate unconstrained code here because statistical language distributions lack project-specific architectural context. Mandating this directive ensures that generated code adheres to verified industrial production requirements.
Enforcement:Integrated into the Agent Pack rule file. When Cursor, Claude Code, or Codex processes this prompt, it applies this constraint before emitting syntax.
5

Code SHOULD configure DuckDB memory limits explicitly via PRAGMA `memory_limit` and `threads` at database instantiation to prevent V8 and native allocator competition.

Code SHOULD configure DuckDB memory limits explicitly via PRAGMA `memory_limit` and `threads` at database instantiation to prevent V8 and native allocator competition.

Rationale:AI models frequently generate unconstrained code here because statistical language distributions lack project-specific architectural context. Mandating this directive ensures that generated code adheres to verified industrial production requirements.
Enforcement:Integrated into the Agent Pack rule file. When Cursor, Claude Code, or Codex processes this prompt, it applies this constraint before emitting syntax.
6

Services MUST stream high-volume analytical results as chunks using Apache Arrow `RecordBatchReader` rather than buffering multi-gigabyte queries fully into memory.

Services MUST stream high-volume analytical results as chunks using Apache Arrow `RecordBatchReader` rather than buffering multi-gigabyte queries fully into memory.

Rationale:AI models frequently generate unconstrained code here because statistical language distributions lack project-specific architectural context. Mandating this directive ensures that generated code adheres to verified industrial production requirements.
Enforcement:Integrated into the Agent Pack rule file. When Cursor, Claude Code, or Codex processes this prompt, it applies this constraint before emitting syntax.
Technical Questions & Answers

Frequently Asked Technical Questions

Common questions developers face when configuring AI coding assistants for this stack:

Q:Why is Zero-Copy Apache Arrow Ingest and Safe Connection Management in DuckDB Node.js critical for AI coding assistants?

Enforces zero-copy Apache Arrow table hydration and query consumption when interacting with DuckDB in Node.js. Prevents event-loop starvation and OOM crashes caused by intermediate JavaScript object serialization of high-cardinality result sets. Mandates dedicated per-thread or isolated connection contexts to guarantee thread safety and memory reclamation during concurrent OLAP evaluations. Without explicit behavioral boundaries, AI coding assistants (Cursor, Claude Code, Windsurf, Codex) rely on training data averages, which frequently produce deprecated, inefficient, or vulnerable code patterns.

Q:How does adopting this rule prevent AI hallucinations?

This rule explicitly declares what the AI must avoid (Converting Large Arrow RecordBatches to Plain JavaScript Object Arrays with Shared DuckDB Connections) and prescribes verified positive examples. Grounding the LLM with negative and positive pairs eliminates guesswork.

Q:Which AI coding environments are supported?

This specification is multi-platform compatible: Cursor (.cursor/rules/*.mdc & .cursorrules), Anthropic Claude Code (CLAUDE.md), Windsurf (.windsurfrules), Codex / Cline (AGENTS.md), and GitHub Copilot (.github/copilot-instructions.md).

Q:Can I combine this rule with other technology stacks?

Yes. Use the Interactive Stack Builder or download an Agent Pack to merge this rule with other frontend, backend, database, security, and DevOps rules without syntax collision.

Open Specification · CC BY 4.0·Free for Personal, Team & Commercial In-Repo Use

Citation & Open Source Attribution Guidelines

This specification is licensed under Creative Commons Attribution 4.0 International (CC BY 4.0). You are completely free to adapt and share this rule in your workspace. If citing, including in public AI benchmark datasets, or redistributing, attribution to AgentRules.org is required.

Related BACKEND Rules

View all rules →
backend
42

Fastify 5 Schema Compilers & Encapsulation Production Standard

Fastify relies on lexical encapsulation contexts to sandbox decorators, hooks, and compiled validation/serialization schemas without memory leaks or cross-tenant bleed. In Fastify 5, misuse of global compilers or failing to encapsulate plugin boundaries causes compiled `fast-json-stringify` serializers and Ajv validators to either crash on duplicate IDs, leak private data across sibling scopes, or bypass JIT compilation optimizations. Production services must enforce strict encapsulation hierarchies and type-safe schema compilation across every routing subtree.

backend
68

Bun Embedded SQLite WAL Mode & Transaction Synchronization Standard

Production database standard for bun:sqlite. Mandates enabling Write-Ahead Logging (PRAGMA journal_mode = WAL), busy timeouts, and using db.transaction() for atomic operations without lock starvation.

backend
68

Drizzle ORM Batch API & Interactive Transaction Isolation Standard

Production database standard for Drizzle ORM (PostgreSQL & MySQL). Mandates using db.batch() over HTTP drivers to prevent round-trip waterfalls, explicit db.transaction() with serializable/read-committed isolation, and strictly avoids unawaited query promises.

1 Rule in Stack

Zero-Copy