deno-postgres で prepared statement の query を条件分岐して組み立てる QueryBuilder を作ってみた

条件分岐しつつ prepared statement を組み立てたいというのが動機です。

まずはテストコード

const LIMIT = 10;

function build({ deptId, age }: { deptId: number; age?: number }) {
  const builder = new QueryBuilder()
    .append`SELECT * FROM emp WHERE dept_id = ${deptId}`; // (A)
  if (age) {
    builder.append`AND age > ${age}`; // (B)
  }
  builder.append(`ORDER BY DESC salary LIMIT ${LIMIT}`); // (C)
  return builder;
}

Deno.test("with age", () => {
  const builder = build({ deptId: 1, age: 50 });
  assertEquals(
    builder.query,
    "SELECT * FROM emp WHERE dept_id = $1 AND age > $2 ORDER BY DESC salary LIMIT 10",
  );
  assertEquals(builder.args, [1, 50]);
});

Deno.test("without age", () => {
  const builder = build({ deptId: 1 });
  assertEquals(
    builder.query,
    "SELECT * FROM emp WHERE dept_id = $1 ORDER BY DESC salary LIMIT 10",
  );
  assertEquals(builder.args, [1]);
});

• (A),(B) は、タグ付きテンプレートリテラルで prepared statement の placeholder として解釈されます。
• (C) は、通常のテンプレートリテラルで、事前に文字列として展開されます。

QueryBuilder（実装）

export class QueryBuilder {
  #index = 1;
  #query = "";
  readonly #args: unknown[] = [];

  get query() {
    return this.#query;
  }

  get args() {
    return this.#args;
  }

  append(query: TemplateStringsArray | string, ...args: unknown[]) {
    if (this.#query !== "") {
      this.#query += " ";
    }
    if (typeof query === "string") {
      // query: string
      this.#query += query;
      return this;
    }
    // query: TemplateStringsArray
    query.forEach((value, index) => {
      this.#query += value +
        ((index < query.length - 1) ? "$" + (this.#index++) : "");
    });
    this.#args.push(...args);
    return this;
  }
}