Drizzleのupsert(onConflictDoUpdate)はCOALESCEが無いと直前の値をnullで潰す

結論

Drizzleの.onConflictDoUpdate()は、setに渡した値をそのまま既存行に上書きします。 新データの取得元が一時的に一部の列だけnullを返す時間帯があると、素朴にset: { pm25, uvIndex, ... }と書いただけでは、直前まで保持していた正常な実測値がnullで潰されます。直し方は、値を保持したい列だけcoalesce(excluded.列, テーブル.列)に変えることです。

症状

サイネージに表示している大気質（PM2.5・光化学オキシダント・UV指数）と暑さ指数（WBGT）は、外部データ（そらまめ君・環境省）を定期取得してPostgreSQLへupsertしています。ある時間帯だけ、その日ずっと表示されていたはずの実測PM2.5や、記録していたはずのWBGTピーク値が、ウィジェット上で「—」に変わる現象がありました。

エラーは出ません。取得ジョブ自体は成功しています。

原因

そらまめ君・環境省のCSVは、HTMLフォールバックに切り替わったり該当測定局のデータが無かったりする時間帯に、値が全てnullで返ってくることがあります。

修正前のair-quality.tsは、upsert時にその新しい値をそのままsetへ渡していました。

.onConflictDoUpdate({
  target: [airQualityIndex.areaCode, airQualityIndex.source, airQualityIndex.forecastDate],
  set: {
    areaName: input.areaName ?? null,
    fetchedAt: input.fetchedAt ?? new Date(),
    pm25,
    pm25Band,
    oxidant,
    uvIndex,
    uvBand,
    raw: rawValue,
    updatedAt: new Date(),
    updatedBy: null,
  },
})

areaCode / source / forecastDate（＝当日）が競合キーなので、同じ日に何度も再取得するたびにこのonConflictDoUpdateが走ります。取得結果がnullに倒れた回の実行では、pm25やuvIndexにもnullが入ったままsetに渡り、既存行にあった直前の実測値をそのままnullで上書きしていました。heat-alerts.tsのWBGT（wbgtMax / wbgtBand）も同じ構造で同じ事故が起きます。

競合キーにforecastDate（当日）が含まれるため、この上書きは「同日内」に限られます。日をまたげば別行になるので影響は当日のうちに収まりますが、その当日のうちはウィジェットが残りの時間帯すべて「—」のままになります。

直し方

保持したい列だけ、sqlテンプレートでcoalesce(excluded.列, テーブル.列)に変えました。excluded.列が今回のINSERT値（取れなければnull）、テーブル.列が上書き前の既存行の値です。

.onConflictDoUpdate({
  target: [airQualityIndex.areaCode, airQualityIndex.source, airQualityIndex.forecastDate],
  set: {
    areaName: input.areaName ?? null,
    fetchedAt: input.fetchedAt ?? new Date(),
    pm25: sql`coalesce(excluded.${sql.raw(airQualityIndex.pm25.name)}, ${airQualityIndex.pm25})`,
    pm25Band: sql`coalesce(excluded.${sql.raw(airQualityIndex.pm25Band.name)}, ${airQualityIndex.pm25Band})`,
    oxidant: sql`coalesce(excluded.${sql.raw(airQualityIndex.oxidant.name)}, ${airQualityIndex.oxidant})`,
    uvIndex: sql`coalesce(excluded.${sql.raw(airQualityIndex.uvIndex.name)}, ${airQualityIndex.uvIndex})`,
    uvBand: sql`coalesce(excluded.${sql.raw(airQualityIndex.uvBand.name)}, ${airQualityIndex.uvBand})`,
    raw: rawValue,
    updatedAt: new Date(),
    updatedBy: null,
  },
})

coalesceは左から順に見て最初のnon-null値を返すので、excluded.pm25（新データ）がnullのときだけ既存のpm25（直前の実測値）が残ります。新データが値を持っていれば、そちらが優先されて正しく更新されます。

heat-alerts.ts側は、全列一律にはしていません。alertLevelは無条件更新のままです。

set: {
  areaName: input.areaName ?? null,
  fetchedAt: input.fetchedAt ?? new Date(),
  alertLevel,
  wbgtMax: sql`coalesce(excluded.${sql.raw(heatAlerts.wbgtMax.name)}, ${heatAlerts.wbgtMax})`,
  wbgtBand: sql`coalesce(excluded.${sql.raw(heatAlerts.wbgtBand.name)}, ${heatAlerts.wbgtBand})`,
  raw: rawValue,
  updatedAt: new Date(),
  updatedBy: null,
}

alertLevelは夕方にsevereからnoneへ落ちるような、値そのものが変わることが正しい列です。ここまでCOALESCEにすると、本来なら反映すべき「警戒レベルが下がった」という更新まで直前の値に固定されてしまいます。COALESCEで守るべきは「一時的にnullへ倒れうる測定値」だけで、「正当にnullや別の値へ遷移しうる状態」まで一律に保護すると別の不具合を生みます。

schema（列定義）自体は変えていないため、この修正にマイグレーションは不要でした。

再発防止

このパターンは、weather_forecastsの気温列で先に採用されていた対処です。今回はそれを大気質とWBGTに横展開したもので、コード側にも判断の理由をコメントとして残しています。

// ★ 測定値は新値が null なら既存値を保持（weather-forecasts.ts の気温と同じ対処）。
// そらまめが HTML フォールバック / 該当局なしで全 null に倒れる時間帯があり、非 COALESCE の
// 上書きだと同日先行時間帯の実測 PM2.5 を null で潰し、サイネージが残りの一日「—」になる。

外部データソースをupsertで取り込む処理を新しく書くときは、「新しい取得が失敗ではなく成功しつつも部分的にnullを返しうるか」を先に確認し、そうであればsetに渡す前に素朴な代入とCOALESCEのどちらが適切かを列ごとに判断する、という基準がこのコメントの形で残っています。