Parser errors when running JOINs, subqueries, and aggregations across ATTACHED SQLite databases using DuckDB Node.js (v1.4.3)

[AjayKuruvath]

We are migrating an existing Node.js application from better-sqlite3 to DuckDB in order to support a composite database connection pattern.

Current working setup (SQLite)

• SQLite database

• Node.js using better-sqlite3

• Queries include:

  • Multiple INNER JOINs
  • Subqueries
  • GROUP BY, ORDER BY

• All queries execute successfully in this setup.

New setup (DuckDB)

• DuckDB Node.js package version: 1.4.3

• Connection initialized as:

  duckdb(':memory:')

• A composite.sql file is executed on startup that:

  • Uses ATTACH to attach one or more SQLite databases
  • Each attached database is given an alias (e.g., tenant_rs)

Example:

ATTACH 'sqlite.db' AS tenant_rs;

We are then querying the attached SQLite databases using fully qualified names such as:

tenant_rs.table_name

Observed Issues

1. Parser errors with JOINs / subqueries

Queries that involve multiple INNER JOINs, subqueries, or window functions across attached databases fail with a parser error.

Example error:

Parser Error: syntax error at or near "."

LINE 11: INNER JOIN tenant_rs.scf_regime_evidence_count AS ec
                            ^

This occurs even though:

• The attached database and views exist
• Simple SELECT * FROM tenant_rs.table_name queries work
• The same query works correctly in SQLite (better-sqlite3)

After debugging, we came across the following explanation (not sure if accurate):

DuckDB Node does not allow mixing multiple attached-database references inside a single prepared statement when subqueries or window functions are present.

We would like clarification on whether this is an actual limitation or an unintended bug.

---

2. Parser errors on GROUP BY queries (no JOIN)

Even relatively simple queries without JOINs fail when using GROUP BY and ORDER BY.

Example query:

SELECT *
FROM aliasname.tablename
WHERE fieldname3 IN ('md','mdx')
GROUP BY fieldname
ORDER BY fieldname1;

Error:

Parser Error: syntax error at or near ")"

The same query:

• Executes successfully in SQLite via better-sqlite3
• Fails consistently when run through DuckDB Node.js

---

Questions / Clarification Requested

1. What are the documented or known limitations when performing:

   • JOINs
   • Subqueries
   • Aggregations
   • Window functions
     across ATTACHED databases, especially when those databases are SQLite?

2. Are there restrictions specific to:

   • DuckDB Node.js bindings?
   • Prepared statements involving attached databases?
   • SQLite-backed attached databases?

3. Is the observed behavior expected, or should these queries be supported?

4. If this is a limitation, is there a recommended workaround or best practice for composite connections in DuckDB?

---

Environment

• DuckDB version: 1.4.3
• Node version: 20.9.0
• SQLite database attached via ATTACH
• Platform: Linux / Windows (reproducible on both)

---