ブラウザで動作する地理空間データ処理ライブラリとして DuckDB-wasm を使い、 SQL を TypeScript で管理する仕組みを作る

newmo では、地図データや地理情報を扱う場面が多くあります。

たとえば、タクシーやライドシェアでは、営業区域のような営業していいエリアといった地理的な定義があります。 また、乗り入れ禁止区域のようなタクシーが乗り入れてはいけないエリアといった定義も必要になります。

これらの地理に関する定義は GeoJSON のような地理情報を扱うデータ形式で管理されることが多いです。 しかし、GeoJSONなどの定義をテキストとして手書きするのは困難です。 そのため、地図上に区域を作図するエディタやその定義した区域が正しいかをチェックするような管理ツールが必要です。 管理ツールは、ウェブアプリケーションとして作った方が利用できる環境が広がります。

このような地理情報は一度に扱うデータが多かったり、空間的な計算処理が必要になるため、専用の仕組みを使うことが多いです。 このような技術を、地理情報システム（GIS：Geographic Information System）と呼びます。

ブラウザやウェブ標準には、GIS処理を行う仕組みは特にないため、すでに安定したGISの実装があるサーバ側で処理することも多いです。 しかし、データを変更するたびに、クライアントからサーバにリクエストしてサーバ側で GIS の処理を行うと、変更の反映がリアルタイムにできないため体験が良くありません。 そのため、地図情報の編集ツールといったプレビューの表示はクライアント側でも地理空間データ処理をして、操作内容を即時に反映できると体験を向上させることができます。

これらを行うには、ブラウザで動作する地理空間データ処理を行うライブラリが必要です。

ブラウザで動作する地理空間データ処理ライブラリとして DuckDB-wasm を使う

ブラウザで地理空間データ処理を行うライブラリとして何を使うかを検討して、最終的にDuckDB-wasmを使うことにしました。

DuckDB-wasmは、名前の通りDuckDBの WebAssembly ビルドです。 この、DuckDB-wasm はブラウザや Node.js など Wasm を実行できる環境で、DuckDB を動かすことができます。

DuckDB にはSpatial Extensionというものがあり、地理空間データ処理を扱えます。

• PostGEESE? Introducing The DuckDB Spatial Extension – DuckDB

データベースでは、Postgres 拡張のPostGISや SQLite 拡張のSpatiaLiteなどがあります。 DuckDB のSpatial Extensionもこれと同様の地理空間データ処理を扱う拡張で、面白い点として WebAssembly で動く点があげられます。

PostGIS と DuckDB の Spatial Extension をおおまかに比較してみると次のようになります。 また、JavaScriptで実装された地理空間データ処理ライブラリである Turf.js との比較も併せてみます。

基本的なCategory	DuckDB WASM	PostGIS	Turf
基本データ型	GEOMETRY, POINT_2D, LINESTRING_2D, POLYGON_2D	geometry, geography	Point, LineString, Polygonなど
空間演算	ST_Area, ST_Distance, ST_Union	ST_Area, ST_Distance, ST_Union, ST_Buffer	area, distance, union, buffer
空間関係	ST_Contains, ST_Within, ST_Touches	ST_Contains, ST_Within, ST_Touches, ST_Crosses	booleanContains, booleanWithin, booleanOverlap
座標変換	ST_Transform	ST_Transform, ST_SetSRID	transform
ジオメトリ生成	ST_MakePoint, ST_MakeLine	ST_MakePoint, ST_MakeLine, ST_MakePolygon	point, lineString, polygon
集計関数	ST_Extent, ST_Union	ST_Extent, ST_Union, ST_Collect	collect, combine
クラスタリング	なし	ST_ClusterDBSCAN, ST_ClusterKMeans	clustersKmeans
測地線計算	ST_Distance_Spheroid	ST_Distance_Spheroid, ST_Length_Spheroid	geodesicDistance

GeoJSON 周りの比較	DuckDB WASM	PostGIS	Turf
GeoJSON 変換	ST_AsGeoJSON	ST_AsGeoJSON	feature, featureCollection
ジオメトリ → GeoJSON	SELECT ST_AsGeoJSON(geom)	SELECT ST_AsGeoJSON(geom)	turf.feature(geometry)
プロパティ付き Feature	json extension	ST_AsGeoJSON(t.*)Â	turf.feature(geometry, properties)
FeatureCollection 生成	json extension	json_build_object('type','FeatureCollection','features',json_agg(ST_AsGeoJSON(t.*)::json))	turf.featureCollection(features)Â
CRS 指定	ST_Transform + ST_AsGeoJSON	ST_Transform + ST_AsGeoJSON1	なし（WGS84 固定）
オプション	基本的な GeoJSON 出力のみ	maxdecimaldigits, bbox, CRS 指定など	bbox, id 指定可能
基本作成	ST_MakePolygon, ST_Polygon	ST_MakePolygon, ST_Polygon, ST_PolygonFromText	polygon, multiPolygon

Polygon 操作	DuckDB WASM	PostGIS	Turf
ポリゴン演算	ST_Union, ST_Intersection, ST_Difference	ST_Union, ST_Intersection, ST_Difference, ST_3DUnion	union, intersect, difference
空間分析	ST_Area, ST_Perimeter	ST_Area, ST_Perimeter, ST_3DArea, ST_3DPerimeter	area, perimeter
空間関係	ST_Contains, ST_Within, ST_Overlaps7	ST_Contains, ST_Within, ST_Overlaps, ST_3DIntersects	booleanContains, booleanWithin, booleanOverlap
検証	ST_IsValid, ST_IsSimple	ST_IsValid, ST_IsSimple, ST_IsValidReason	isPolygon, isMultiPolygon
変換	ST_Transform	ST_Transform, ST_Force3D	transformScale, transformRotate
単純化関数	ST_Simplify	ST_Simplify	simplify,polygonSmooth

サポートしているデータ形式	DuckDB WASM	PostGIS	Turf
入力形式	GeoJSON, Shapefile, GeoPackage, KML, GML	GeoJSON, Shapefile, GeoPackage, KML, GML, WKT, WKB	GeoJSON のみ
出力形式	GeoJSON, WKT, WKB	GeoJSON, KML, SVG, WKT, WKB	GeoJSON のみ
GeoJSON 操作	ST_AsGeoJSON, ST_GeomFromGeoJSON	ST_AsGeoJSON, ST_GeomFromGeoJSON	feature, featureCollection10
KML 操作	ST_AsKML	ST_AsKML, ST_GeomFromKML	なし
WKT 操作	ST_AsText, ST_GeomFromText	ST_AsText, ST_GeomFromText	なし
データ読み込み	ST_Read	ST_Read, ST_AsBinary	JSON.parse
データ書き出し	ST_AsGeoJSON, ST_AsText	ST_AsGeoJSON, ST_AsKML, ST_AsSVG	JSON.stringify

おおまかに比較しても、DuckDB の Spatial Extension は PostGIS と同等の機能性を持っていることがわかります。 逆にTurf.jsは演算のみなので、データ形式の変換などについてはスコープ外となっていることもわかります。

• how to convert wkt to geojson? · Issue #2057 · Turfjs/turf

ブラウザ上で動作する地理空間データ処理ライブラリを決めるために、このような機能比較、実装イメージ、ユースケース、サイズ、メリット/デメリットなどを書いた Design Doc を書いて議論しました。 その結果、DuckDB-wasm を使うことにしました。

📢 newmoでの Design Doc について

newmo のフロントエンドでは、大きなライブラリを導入する際には Design Doc を書いて議論してから決めることが多いです。 これについては、以前書いた One Version Rule を実践するためと、なぜそのライブラリを使っているのかという経緯が Design Doc(Architectural Decision Records としての役割)として残るためです。

• monorepo 内でのパッケージのバージョンを 1 つだけに統一する One Version Rule ã‚’ pnpm catalog で実装する - newmo 技術ブログ

この Design Doc とライブラリの管理などについては、yui_tang が 2024年11月23日(土曜) のJSConf JPで発表する予定です。

• Modular Monolith Monorepo - 坂本結衣(yui_tang) | JSConf JP

DuckDB-wasm + TypeScript で SQL を管理する

地理空間データ処理ライブラリとしてDuckDB-wasmを使うことにしました。 これは、クライアントサイドで SQL を書いて、クライアントサイドのWasm上で SQL を実行する必要があるということを意味しています。

今回は DuckDB のデータを永続化はせずに In-Memory DB として利用しているので、マイグレーションのような複雑な問題はありませんが、 それでも SQL を管理する方法は考える必要があります。

DuckDB-wasmはまだ新しいライブラリであるため、どのように扱うかのベストプラクティスが確立されていません。 そのため、今回 DuckDB-wasm を扱うにあたって、DuckDB で実行する SQL を TypeScript で管理する仕組みを作りました。

仕組みと言っても単純で、DuckDB で実行する SQL に型をつけて定義する Utility 関数を用意しただけです。

次のような SQL を実行できる関数を定義できる Utility 関数を提供しています。

• defineQueryOne: 一つの結果を返すクエリを実行する関数を定義する
• defineQueryMany: 複数の結果を返すクエリを実行する関数を定義する
• defineQueryExec: 結果を返さないクエリを実行する関数を定義する
• transformQuery: クエリの実行結果を変換して、変換した結果を返すようにするクエリのラッパー

また、どの関数も第一引数にDuckDBContextを受け取り、DuckDBContextは DuckDB と接続するための情報を持っています。

defineQuery.ts: SQLを管理するUtility関数のコード(クリックで開く)

import type { AsyncDuckDB, AsyncDuckDBConnection } from "@duckdb/duckdb-wasm";

/**
 * 構文エラーなのでクエリの書き方の問題がある
 */
export type DuckDBParserError = {
  type: "DuckDBParserError";
  message: string;
  query: string;
  cause: Error;
};
/**
 * 変換エラーなのでデータの問題がある
 */
export type DuckDBConversionError = {
  type: "DuckDBConversionError";
  message: string;
  query: string;
  cause: Error;
};
/**
 * データがない場合のエラー
 */
export type DuckDBNoRowError = {
  type: "DuckDBNoRowError";
  message: string;
  query: string;
  cause: Error;
};
/**
 * その他のエラー
 */
export type DuckDBUnknownError = {
  type: "DuckDBUnknownError";
  message: string;
  query: string;
  cause: Error;
};
export type DuckDBSQLError =
  | DuckDBParserError
  | DuckDBConversionError
  | DuckDBNoRowError
  | DuckDBUnknownError;
/**
 * DuckDBのクエリに渡すContext
 */
export type DuckDBContext = {
  db: AsyncDuckDB;
  conn: AsyncDuckDBConnection;
};
/**
 * DuckDBのエラーをエラーオブジェクトにする
 */
export const translateDuckDbError = ({
  message,
  query,
  error,
}: {
  message: string;
  query: string;
  error: unknown;
}): DuckDBSQLError => {
  if (error instanceof Error) {
    if (error.message.includes("Parser Error")) {
      return {
        type: "DuckDBParserError",
        message,
        query,
        cause: error,
      };
    }
    if (error.message.includes("Conversion Error")) {
      return {
        type: "DuckDBConversionError",
        message,
        query,
        cause: error,
      };
    }
  }
  return {
    type: "DuckDBUnknownError",
    message,
    query,
    cause: error as Error,
  };
};

// Inputが {} の場合は、 Inputをoptionalにする
// keyof {} は never になるのを利用して判定している
// https://stackoverflow.com/questions/62403425/conditional-type-for-empty-objects
export type QueryFunction<Input, Output> = keyof Input extends never
  ? QueryFunctionWithOptionalArgs<Input, Output>
  : QueryFunctionWithArgsRequiredArgs<Input, Output>;
export type QueryFunctionWithArgsRequiredArgs<Input, Output> = (
  context: DuckDBContext,
  args: Input
) => Promise<
  | {
      ok: true;
      data: Output;
    }
  | {
      ok: false;
      errors: DuckDBSQLError[];
    }
>;
export type QueryFunctionWithOptionalArgs<Input, Output> = (
  context: DuckDBContext,
  args?: Input
) => Promise<
  | {
      ok: true;
      data: Output;
    }
  | {
      ok: false;
      errors: DuckDBSQLError[];
    }
>;
/**
 * 一つの結果を返すクエリを定義する
 * データが存在しないときは、DuckDBNoRowErrorのエラーを返す
 * @param name 関数名
 * @param sql クエリ
 * @example
 * ```ts
 * const selectId = defineFunction<{id: string}, { id: string }>({
 *   name: "selectId",
 *   query: () => `SELECT * FROM table WHERE id = ${id`,
 * });
 * const result = await selectId(context, { id: "1" });
 * console.log(result.data.id); // => "1"
 * const notFound = await selectId(context, { id: "2" });
 * console.log(notFound.ok); // => false
 * ```
 *
 */
export const defineQueryOne = <
  /**
   * クエリの引数
   * 引数がない場合は {} を指定する
   */
  Input,
  /**
   * クエリの実行結果で取得できるデータ型
   */
  Output
>({
  name,
  sql,
}: {
  name: string;
  sql: (args: Input) => string;
}): QueryFunction<Input, Output> => {
  const fn = async (context: DuckDBContext, args?: Input) => {
    const query = `-- name: ${name} :one
${sql(args ?? ({} as Input))}`;
    try {
      const q = await context.conn.prepare(query);
      const resultTable = await q.query(args);
      const firstData = resultTable.toArray()[0];
      if (!firstData) {
        return {
          ok: false,
          errors: [
            {
              type: "DuckDBNoRowError",
              message: `No row found: ${name}`,
              query,
            },
          ],
        };
      }
      return {
        ok: true,
        // それぞれのアイテムはPlainなオブジェクトではないので、spread syntaxでnon-enumerableなプロパティを落とす
        // 型には定義されてない、生のプロパティをできるだけ触れないようにする
        // TODO: JSON.parse(JSON.stringify(firstData)) なら全て落とせるが、パフォーマンスが悪い
        data: { ...firstData },
      };
    } catch (error: unknown) {
      return {
        ok: false,
        errors: [
          translateDuckDbError({
            message: `Failed to query: ${name}`,
            query,
            error,
          }),
        ],
      };
    }
  };
  // nameを関数名に設定する
  Object.defineProperty(fn, "name", { value: name, configurable: true });
  return fn as QueryFunction<Input, Output>;
};

/**
 * 複数の結果を返すクエリを定義する
 * @param name 関数名
 * @param sql クエリ
 * @example
 * ```ts
 * const selectAll = defineFunction<{id: string}, { id: string }>({
 *   name: "selectAll",
 *   query: "SELECT * FROM table",
 * });
 * const result = await selectAll(context, {});
 * console.log(result.data); // => [{ id: "1" }, { id: "2" }]
 * ```
 */
export const defineQueryMany = <
  /**
   * クエリの引数
   * 引数がない場合は {} を指定する
   */
  Input,
  /**
   * クエリの実行結果で取得できるデータ型(要素の型なので、[]は不要)
   */
  Output
>({
  name,
  sql,
}: {
  name: string;
  sql: (args: Input) => string;
}): QueryFunction<Input, Output[]> => {
  const fn = async (context: DuckDBContext, args?: Input) => {
    const query = `-- name: ${name} :many
${sql(args ?? ({} as Input))}`;
    try {
      const q = await context.conn.prepare(query);
      const resultTable = await q.query(args);
      return {
        ok: true,
        data: resultTable.toArray(),
      };
    } catch (error: unknown) {
      return {
        ok: false,
        errors: [
          translateDuckDbError({
            message: `Failed to query: ${name}`,
            query,
            error,
          }),
        ],
      };
    }
  };
  // nameを関数名に設定する
  Object.defineProperty(fn, "name", { value: name, configurable: true });
  return fn as QueryFunction<Input, Output[]>;
};

/**
 * 結果を返さないクエリを定義する
 * @param name 関数名
 * @param sql クエリ
 * @example
 * ```ts
 * const update = defineFunction<{id: string}, undefined>({
 *   name: "update",
 *   query: "UPDATE table SET id = $id",
 * });
 * const result = await update(context, { id: "1" });
 * console.log(result.ok); // => true
 * console.log(result.data); // => undefined
 * ```
 */
export const defineQueryExec = <
  /**
   * クエリの引数
   * 引数がない場合は {} を指定する
   */
  Input
>({
  name,
  sql,
}: {
  name: string;
  sql: (args: Input) => string;
}): QueryFunction<Input, undefined> => {
  const fn = async (context: DuckDBContext, args?: Input) => {
    const query = `-- name: ${name} :exec
${sql(args ?? ({} as Input))}`;
    try {
      const q = await context.conn.prepare(query);
      await q.query(args);
      return {
        ok: true,
        data: undefined,
      };
    } catch (error: unknown) {
      return {
        ok: false,
        errors: [
          translateDuckDbError({
            message: `Failed to query: ${name}`,
            query,
            error,
          }),
        ],
      };
    }
  };
  // nameを関数名に設定する
  Object.defineProperty(fn, "name", { value: name, configurable: true });
  return fn as QueryFunction<Input, undefined>;
};

/**
 * クエリの実行結果を変換して、変換した結果を返すようにするクエリのラッパー
 * @example
 * ```ts
 * const selectId = defineQueryOne<{id: string}, { id: string }>({
 *    name: "selectId",
 *    sql: ({ id }) => `SELECT * FROM table WHERE id = ${id}`,
 * });
 * const selectIdWithTransformed = transformQuery(selectId, (data) => {
 *   return {
 *     id: Number(data.id),
 *   }
 * });
 * const result = await selectIdWithTransformed(context, { id: "1" });
 * console.log(result.data.id); // => 1
 */
export const transformQuery = <TransformOutput, Input, Output>(
  query: QueryFunction<Input, Output>,
  transformFn: (data: Output) => TransformOutput
): QueryFunction<Input, TransformOutput> => {
  const fn = async (context: DuckDBContext, args?: Input) => {
    const result = await query(context, args ?? ({} as Input));
    if (!result.ok) {
      return result;
    }
    return {
      ok: true,
      data: transformFn(result.data),
    };
  };
  // nameを関数名に設定する
  Object.defineProperty(fn, "name", {
    value: `${query.name}WithTransformed`,
    configurable: true,
  });
  return fn as QueryFunction<Input, TransformOutput>;
};

これらの Utility 関数を使って、次のように SQL を実行する関数を定義できます。

import { defineQueryOne, defineQueryMany, defineQueryExec } from "./defineQuery.ts";

/**
 * DuckDBにSpatial拡張をインストールするクエリ
 */
export const installSpatialExtension = defineQueryExec({
  name: "installSpatialExtension",
  sql: () => `
    INSTALL spatial;
    LOAD spatial;
  `,
});
/**
 * テーブルを作成するクエリ
 */
export const createTable = defineQueryExec({
  name: "createTable",
  sql: () => `
    CREATE TABLE table (
      id STRING
      name STRING
    );
  `,
});
/**
 * idを指定してデータを取得するクエリ
 */
export const selectId = defineQueryOne<
  { id: string },
  { id: string; name: string }
>({
  name: "selectId",
  sql: ({ id }) => `SELECT * FROM table WHERE id = ${id}`,
});
/**
 * 全てのデータを取得するクエリ
 */
export const selectAll = defineQueryMany<{}, { id: string; name: string }>({
  name: "selectAll",
  sql: () => `SELECT * FROM table`,
});
/**
 * データを挿入するクエリ
 */
export const insert = defineQueryExec<{ id: string; name: string }>({
  name: "insert",
  sql: ({ id, name }) => `INSERT INTO table VALUES (${id}, ${name})`,
});

これらのクエリは次のように実行できます。 DuckDB-wasm の使い方については、公式ドキュメントも参照してください。

• DuckDB Wasm – DuckDB

import * as duckdb from "@duckdb/duckdb-wasm";
import duckdb_wasm from "@duckdb/duckdb-wasm/dist/duckdb-mvp.wasm";
import duckdb_wasm_next from "@duckdb/duckdb-wasm/dist/duckdb-eh.wasm";
import { installSpatialExtension } from "./sql.ts";

const setupDuckDBForBrowser = async () => {
  const MANUAL_BUNDLES: duckdb.DuckDBBundles = {
    mvp: {
      mainModule: duckdb_wasm,
      mainWorker: new URL(
        "@duckdb/duckdb-wasm/dist/duckdb-browser-mvp.worker.js",
        import.meta.url
      ).toString(),
    },
    eh: {
      mainModule: duckdb_wasm_next,
      mainWorker: new URL(
        "@duckdb/duckdb-wasm/dist/duckdb-browser-eh.worker.js",
        import.meta.url
      ).toString(),
    },
  };
  const bundle = await duckdb.selectBundle(MANUAL_BUNDLES);
  const worker = new Worker(bundle.mainWorker!);
  const logger = new duckdb.ConsoleLogger();
  const db = new duckdb.AsyncDuckDB(logger, worker);
  await db.instantiate(bundle.mainModule, bundle.pthreadWorker);

  const conn = await db.connect();
  const duckDBContext = {
    conn,
    db,
  };
  const installedResult = await installSpatialExtension(duckDBContext);
  if (!installedResult.ok) {
    throw new Error("Failed to install spatial extension", {
      cause: installedResult.errors,
    });
  }
  return duckDBContext;
};

const duckDBContext = await setupDuckDBForBrowser();
// insert
const insertResult = await insert(duckDBContext, { id: "1", name: "name" });
if (!insertResult.ok) {
  console.error("Failed to insert", insertResult.errors);
  return;
}
// select
const selectResult = await selectId(duckDBContext, {
  id: insertResult.data.id,
});
if (!selectResult.ok) {
  console.error("Failed to select", selectResult.errors);
  return;
}
console.log("select", selectResult.data); // => { id: "1", name: "name" }
// select all
const selectAllResult = await selectAll(duckDBContext);
if (!selectAllResult.ok) {
  console.error("Failed to select all", selectAllResult.errors);
  return;
}
console.log("select all", selectAllResult.data); // => [{ id: "1", name: "name" }]

このdefineQuery*関数などのUtilityは200-300行程度の小さなUtilityですが、クエリを定義する側はシンプルにSQLを書くだけで良くなります。 SQLのInputとOutputはTypeScriptで型定義することで、クエリを実行する側は型安全にクエリを実行できます。

また、クエリの実行結果は{ ok: true, data: Output }または{ ok: false, errors: DuckDBSQLError[] }というResult型のような値を返すようになっています。 これは、エラーも値として返したほうが型安全にエラーハンドリングを書きやすいためです。

DuckDB-wasm のテストをNode.jsで動かす

DuckDB-wasm はブラウザで動作するライブラリですが、WebAssemblyなのでNode.jsでも動かすことができます。 DuckDBで行う処理は特にブラウザに依存はしていないので、Node.jsで動くとテストが簡単に動かせるようになります。

Node.js向けの公式のドキュメントがまだ整備されていないので、参考程度になりますが、次のようにNode.jsでもDuckDB-wasmを使うことができます。 次のテストコードでは、"@duckdb/duckdb-wasm/blocking"を使って、Node.jsでBlocking APIのDuckDBインスタンスを動かしています。

web-workerなどのNode.js向けのWeb Worker APIを使うと、ブラウザと同じ非同期APIのDuckDBを使うこともできます。 ただ、余計なライブラリが必要だったり、テスト目的ならBlocking APIでもあまり困らなかったので、"@duckdb/duckdb-wasm/blocking"を使っています。

import { createDuckDB, NODE_RUNTIME } from "@duckdb/duckdb-wasm/blocking";
import { createRequire } from "module";
import { dirname, resolve } from "path";
import * as duckdb from "@duckdb/duckdb-wasm";
import {
  defineQueryExec,
  defineQueryMany,
  defineQueryOne,
  type DuckDBSQLError,
  type DuckDBContext,
  transformQuery,
} from "./defineQuery.ts";
import { describe, it, expect } from "vitest";

const require = createRequire(import.meta.url);
const DUCKDB_DIST = dirname(require.resolve("@duckdb/duckdb-wasm"));

/**
 * create initialized duckDB for Node.js
 * @returns {Promise<void>}
 */
export async function setupDuckDBForNodejs(): Promise<DuckDBContext> {
  const DUCKDB_BUNDLES = {
    mvp: {
      mainModule: resolve(DUCKDB_DIST, "./duckdb-mvp.wasm"),
      mainWorker: resolve(DUCKDB_DIST, "./duckdb-node-mvp.worker.cjs"),
    },
    eh: {
      mainModule: resolve(DUCKDB_DIST, "./duckdb-eh.wasm"),
      mainWorker: resolve(DUCKDB_DIST, "./duckdb-node-eh.worker.cjs"),
    },
  };
  // SyncDBとして作成してしまう
  // .thenや.catchなどを使わなければ、特に違いは意識しなくていい
  // TODO: Syncは公式にサポートされているがAsyncはWebWorkerに依存しているため工夫が必要
  // https://github.com/duckdb/duckdb-wasm/blob/6fcc50318b3a0e6b4e30c78bfdda19b9f86f4012/packages/duckdb-wasm/test/index_node.ts#L56
  const logger = new duckdb.ConsoleLogger();
  const db = await createDuckDB(DUCKDB_BUNDLES, logger, NODE_RUNTIME);
  await db.instantiate();
  const conn = db.connect();
  // @ts-expect-error -- syncのものをasyncとして渡しているため
  const duckDBContext = {
    db,
    conn,
  } as DuckDBContext;
  // spatial extensionをインストール
  const installResult = await installSpatialExtension(duckDBContext);
  if (!installResult.ok) {
    throw new Error("Failed to install spatial extension", {
      cause: installResult.errors,
    });
  }
  return duckDBContext;
}

/**
 * クエリの実行結果が成功しているかAssertionする
 * 失敗してる時のログを出力する
 * @param result
 */
export function assertQueryResultOk(result: {
  ok: boolean;
  errors?: any[];
}): asserts result is { ok: true } {
  if (!result.ok) {
    const error = new Error(
      "Assertion failed: query result is not ok. expected result.ok is true",
    );
    console.error(error, {
      errors: result.errors,
    });
    throw error;
  }
}

const createTestTable = defineQueryExec<{}>({
  name: "createTestTable",
  sql: () => `
    CREATE TABLE test_table (
      id UUID PRIMARY KEY DEFAULT uuid(),
      name TEXT
    )
  `,
});
const insertTestItem = defineQueryOne<
  { name: string },
  {
    id: string;
  }
>({
  name: "insertTestItem",
  sql: ({ name }) => `
    INSERT INTO test_table (name) VALUES ('${name}')
    RETURNING id
  `,
});
const getTestItem = defineQueryOne<
  { id: string },
  { id: string; name: string }
>({
  name: "getTestItem",
  sql: ({ id }) => `
    SELECT id, name FROM test_table WHERE id = '${id}'
  `,
});

describe("DuckDB Utils", () => {
  describe("defineQueryOne", () => {
    it("should return one result", async () => {
      const duckDBContext = await setupDuckDBForNodejs();
      assertQueryResultOk(await createTestTable(duckDBContext));
      const insertResult = await insertTestItem(duckDBContext, {
        name: "test",
      });
      assertQueryResultOk(insertResult);
      const insertedId = insertResult.data.id;
      const result = await getTestItem(duckDBContext, {
        id: insertedId,
      });
      assertQueryResultOk(result);
      expect(result.data).toEqual({ id: insertedId, name: "test" });
    });
  });
});

これで定義したクエリのテストをNode.jsでも動かせるので、Unit Testなども簡単に書けるようになっています。

今後の展望

defineQuery*関数で発行されるSQLは、-- name: ${name} :oneのような形式コメントを入れていることに気づいた人もいるかもしれません。 これはsqlcを意識して作った仕組みであるため、defineQuery*関数もそれぞれsqlcのQuery annotationsに対応した形で作成しています。

sqlcは、SQLを書いてGoのコードやTypeScriptのコードを生成できるツールです。 現状のsqlcはDuckDBには対応していません。将来的には、SQLを書いてそのクエリを実行できるコードを生成するような仕組みに置き換えることも検討しています。

• Any planning to supported duckdb? · sqlc-dev/sqlc · Discussion #2515

現状のUtilityはInputとOutputの型定義が完全に手動ですが、これらのツールが対応されるとDBのスキーマからTypeScriptで型定義を生成できたりしてより効率的に開発できるようになるかもしれません。

今回紹介した実装では、SQLのエスケープやprepared statementは特に対応を書いていません。 これは、実行するSQLの対象がブラウザ上の一時的な計算のためのデータで、漏れたり変更しても問題ないデータであるためです(あくまでデータはそのブラウザ内の値で、ページ内に閉じています)。 まだDuckDB Wasmのprepared statementの挙動がまだおかしい部分もあるため、できるだけシンプルな仕組みにしたかったのもあります。

• [DuckDB Wasm] How to use named prepared statements? · duckdb/duckdb · Discussion #11782
• Support array arguments in prepared statements · Issue #447 · duckdb/duckdb-wasm

おそらく、今後prepared statementの対応や@vercel/postgresのようなTagged Functionを使ったエスケープが必要になるかもしれません。 そのため、この記事のコードを利用する場合は、この点に留意してください。

• Why SQL template tags are not vulnerable to SQL injection attacks - Neon

また、defineQuery*関数とは別にクエリの実行結果を変換できるtransformQuery関数を提供しています。 クエリを実行できる関数の定義と変換処理を分けたのは、将来的にはクエリを実行できる関数は自動生成する可能性があると思ったためです。 クエリの定義と変換処理を分けておくことで、クエリの定義だけを自動生成するような仕組みを作りやすくなります。

そのため、defineQuery*関数の中には、クエリの実行結果をあまり変換する処理は入れないようにしていて、シンプルな実行結果を返すだけの関数にしています。

まとめ

DuckDBのWebAssembly版であるDuckDB-wasmを使って、ブラウザ上で地理空間データ処理をするSQLの管理をする仕組みを作りました。

小さな仕組みですが、SQLはSQLとしてある程度独立したものとして定義できるようになり、型定義も明示的に書く必要があるのでTypeScriptからも扱いやすくなったと思います。 秩序なくアプリケーションのコードのSQLをベタがきしてしまうと、後から変更もできなくなってしまいます。 将来的には、SQLからコード生成をして、もっと安全で楽にDuckDB-wasmを使うような仕組みを作ることも検討しています。

宣伝

2024年11月16日(土曜)に開催されるTSKaigi Kansai 2024で、ブラウザで完結！DuckDB Wasmでタクシー地図情報を可視化というタイトルでスポンサーLTをするので、ぜひ聴きに来てください！

また、スポンサーブースでは、DuckDB-wasmの選定に使ったDesign Docや、今回のSQL管理の仕組みを議論したDesign Docなども展示する予定です

newmoでは地理情報システム（GIS：Geographic Information System）に興味のあるエンジニアを積極的に採用中です！

• 採用情報｜ newmo（ニューモ）