DuckDB无法返回查询数据？Node.js调用问题求助

问题 #

我写了一个Node.js函数，用DuckDB做全文检索：

import duckdb from "duckdb";

const db = new duckdb.Database("./database/knowledge.duck");
const con = db.connect();

export const keyword_search = async (keywords) => {

    const test = await con.prepare(
        `
        SELECT id, content, score
        FROM ( SELECT *, fts_main_knowledge.match_bm25( id, '${keywords}' ) AS score FROM knowledge ) sq
        WHERE score IS NOT NULL
        ORDER BY score DESC
        LIMIT 2;`).all();

    return test
};

调用后没拿到数据，只返回了Statement对象：

RESULTS:  [
  Statement {
    sql: "\n\t\tSELECT id, content, score\n\t\tFROM ( SELECT *, fts_main_knowledge.match_bm25( id, 'hello' ) AS score FROM knowledge ) sq\n\t\tWHERE score IS NOT NULL\n\t\tORDER BY score DESC\n\t\tLIMIT 2;",
    run: [class run],
    all: [class all],
    arrowIPCAll: [class arrowIPCAll],
    each: [class each],
    finalize: [class finalize],
    stream: [class stream],
    columns: [class columns],
    get: [Function],
  }]

换成带回调的con.all方法，能在回调里打印rows，但没法返回数据：

const test = con.all(
    `SELECT id, content, score
    FROM ( SELECT *, fts_main_knowledge.match_bm25( id, '${keywords}' ) AS score FROM knowledge ) sq
    WHERE score IS NOT NULL
    ORDER BY score DESC
    LIMIT 2;`,
    (err, rows) => {
        if (err) return;
        console.log(rows)
    },
);

请问怎么修改才能正确获取并返回查询数据？

解决方案 #

问题根源 #

1. 第一种写法里，con.prepare()同步返回Statement对象，你直接链式调用.all()但错误地await了prepare的结果，而非.all()返回的Promise，导致拿到的是Statement而非查询结果。
2. 第二种写法用了回调式con.all，但回调函数的执行时机晚于函数返回，无法直接把回调里的rows作为函数返回值。

正确修改方式 #

方式一：规范使用prepare + async/await

拆分prepare和.all()的调用，先获取Statement，再await其异步的.all()方法，同时用参数占位符避免SQL注入：

import duckdb from "duckdb";

const db = new duckdb.Database("./database/knowledge.duck");
const con = db.connect();

export const keyword_search = async (keywords) => {
    // 预编译SQL，用?作为参数占位符
    const stmt = con.prepare(`
        SELECT id, content, score
        FROM ( SELECT *, fts_main_knowledge.match_bm25( id, ? ) AS score FROM knowledge ) sq
        WHERE score IS NOT NULL
        ORDER BY score DESC
        LIMIT 2;`);
    // 传入参数并await查询结果
    const results = await stmt.all(keywords);
    // 释放Statement资源
    await stmt.finalize();
    return results;
};

方式二：直接使用Promise版con.all

DuckDB的Node.js API支持Promise化调用，无需回调，直接awaitcon.all()即可：

import duckdb from "duckdb";

const db = new duckdb.Database("./database/knowledge.duck");
const con = db.connect();

export const keyword_search = async (keywords) => {
    const results = await con.all(`
        SELECT id, content, score
        FROM ( SELECT *, fts_main_knowledge.match_bm25( id, ? ) AS score FROM knowledge ) sq
        WHERE score IS NOT NULL
        ORDER BY score DESC
        LIMIT 2;`, [keywords]);
    return results;
};

方式三：封装回调为Promise（兼容旧场景）

如果必须用回调，可将con.all封装成Promise：

import duckdb from "duckdb";

const db = new duckdb.Database("./database/knowledge.duck");
const con = db.connect();

export const keyword_search = (keywords) => {
    return new Promise((resolve, reject) => {
        con.all(`
            SELECT id, content, score
            FROM ( SELECT *, fts_main_knowledge.match_bm25( id, ? ) AS score FROM knowledge ) sq
            WHERE score IS NOT NULL
            ORDER BY score DESC
            LIMIT 2;`, [keywords], (err, rows) => {
                if (err) reject(err);
                else resolve(rows);
            });
    });
};

调用时依然可以用await keyword_search(keywords)获取结果。

内容的提问来源于stack exchange，提问作者charnould