Cloudflare D1の、複数テーブルのJOINで同名のカラムが取得できないバグとその理由、そしてDrizzle ORMでの対処法

2023/10/14 18:05:08

どういうこと？ってなりますよね。私はなりました。

🐛 BUG: when trying to left join 2 tables that have same name columns - second column is not returned · Issue #3160 · cloudflare/workers-sdk https://github.com/cloudflare/workers-sdk/issues/3160

こういうこと

たとえばこういうスキーマ。

categories {
  TEXT slug PK
  TEXT name
}

posts {
  TEXT slug PK
  TEXT category_slug FK
  TEXT title
}

各ポストは、単一のカテゴリに属するとする。まあ、ありがちなテーブル構成かと。

重要なのは、slugという同名のカラムがあるところ。

で、この状態で、以下のSQLを発行すると、

SELECT
  posts.slug, categories.slug
FROM
  posts
  LEFT JOIN categories ON posts.category_slug == categories.slug

期待するのはこういう返り値。

| slug   | slug       |
| ------ | ---------- |
| post-1 | category-1 |

素のSQLiteとか（その他のDBでも）であれば、このように返ってくる。

が、現状のD1だとこうなっちゃう。

| slug       |
| ---------- |
| category-1 |

なんてこった。

なぜか

D1の実装（たぶんworkerdというより、D1を捌いてるらしいDOの実装かな？）がそうなってるから。

おそらくSQLiteから返ってきた結果を、JSONでレスポンスするために詰め替えるところで、同名のキーを上書きしちゃってる。

なのでwrangler d1 execute --command="..."だろうと、D1.prepare("...").all()だろうとD1.prepare("...").raw()だろうと、どうしようもなくこうなる。

何が困るのか

wranglerからSQLを発行したり、D1のAPIを直接使ってる場合は、そもそも同じカラム名にはせずASとか使うはず。なので、問題にならない。

困るのは、いわゆるORMを通した場合。個人的に言うと、DrizzleORMを使ってた場合。

DrizzleはD1のAPIでいう.raw()を使ってるので、この影響をもろに受けるとのこと。

[BUG]: Broken shifted columns with leftJoin and same column name (on D1) · Issue #555 · drizzle-team/drizzle-orm https://github.com/drizzle-team/drizzle-orm/issues/555

Drizzleは、こういうコードを書くと、

const { sql } = drizzle(D1Database)
  .select({ ps: posts.slug, cs: categories.slug })
  .from(posts)
  .leftJoin(categories, eq(posts.categorySlug, categories.slug))
  .toSQL();

こういうSQLに変換するらしく、

SELECT
  "posts"."slug",
  "categories"."slug"
FROM 
  "posts"
  LEFT JOIN "categories" ON "posts"."category_slug" = "categories"."slug"

するとさっきのD1の罠にかかって、思った通りの結果が返ってこない。なのでundefined？！なんで？！ってなる。

ドキュメントではASが使われる風に書いてあるけど、実際は使ってないっぽい。

SQL Select - DrizzleORM https://orm.drizzle.team/docs/select#basic-and-partial-select

どうすればいいか

SQL文にASを含めるためには、sqlオペレータなる機能を明示的に使う必要がある。

const { sql } = drizzle(D1Database)
  .select({ ps: posts.slug, cs: sql<string>`${categories.slug}`.as("cs") })
  .from(posts)
  .leftJoin(categories, eq(posts.categorySlug, categories.slug))
  .toSQL();

Magical sql operator 🪄 - DrizzleORM https://orm.drizzle.team/docs/sql#sqlast

TSの場合、型を明示しないと、unknownになっちゃう。

やや冗長ではあるが、まあなんとか許容できる範囲の対応かな・・。

We are working on this. Current priority is larger databases, which is the biggest ask from most users. https://github.com/cloudflare/workers-sdk/issues/3160#issuecomment-1661009761

信じて待ちましょう！