範囲列 tstzrange による重複チェックを簡略化【Supabase】
TL;DR
https://supabase.com/blog/range-columns の supabase 公式ブログを参考に、
範囲列 tstzrange を使用することで、重複チェックを簡略化できます！
はじめに
先日、Supabase を使ったプロジェクトで、予約システムの実装に取り組んでいました 📅
Web アプリケーション開発において、日時に関連するデータ処理やバリデーションは重要でありながら、意外と複雑になりがちな要素です。
今回は、Supabase と PostgreSQL の範囲列を使った効率的な予約システムについて調査したので、基礎的な内容をまとめました！
時間の節約になれば、嬉しいです 🙌
遭遇した問題：予約の重複チェック
予約システムやカレンダーアプリのような、時間ベースのアプリケーションを構築する際、よく直面する問題があります。
それは予約の重複チェックです。
例えば、レストランの予約システムを考えてみましょう。同じテーブルに対して、時間が重複する予約を防ぎたいですよね。
通常、このような予約データを保存する場合、以下のようなテーブル設計を考えがちです：
create table reservations (
id serial primary key,
title text,
table_id int,
start_at timestamptz,
end_at timestamptz
);
この構造で、重複チェックを実装しようとすると、次のような問題が生じます：
- クエリの複雑化：重複を検出するクエリが複雑で、バグが入り込みやすい
- データ整合性の確保が難しい：アプリケーションロジックでチェックするため、不整合が生じる可能性がある
従来の重複チェック実装例
従来の方法では、重複をチェックするために次のようなコードを書く必要がありました：
async function isTableAvailable(
supabase: SupabaseClient,
tableId: number,
startTime: string,
endTime: string
) {
const { data, error } = await supabase
.from("reservations")
.select("id")
.eq("table_id", tableId)
.lte("start_at", endTime) // 開始時間が予約終了時間以前
.gte("end_at", startTime); // 終了時間が予約開始時間以降
if (error) {
console.error("Error checking availability:", error);
throw error;
}
// 重複する予約がなければ true を返す
return data.length === 0;
}
このコードには以下のような課題があります：
- 条件が複雑で、見落としやバグの原因になりやすい
- すべての重複パターンを正確に捕捉できているか確認が難しい
- データベースレベルでの制約がないため、不整合が発生する可能性がある
より良い方法：範囲列 tstzrange
良い実装がないかな、、と調べていたら、
すでに素晴らしい方法が、上記で紹介されていました。。！
上記によると、
PostgreSQL（Supabase が利用しているデータベース）には、このような問題を解決するための強力な機能が用意されているようです。
それが「範囲型（Range Types）」です。
範囲型とは、ある基本型の始点と終点を持つデータ型です。
PostgreSQL には様々な範囲型が用意されていますが、時間ベースの予約に最適なのが tstzrange です。
これは timestamptz（タイムゾーン付きタイムスタンプ）の範囲を表します。
範囲列を使ったテーブル設計
従来の start_at と end_at の 2 つのカラムの代わりに、1 つの範囲列を使用します：
create table reservations (
id serial primary key,
title text,
table_id int,
duration tstzrange
);
ここで、duration カラムは予約の開始時間と終了時間を範囲として格納します。
範囲列のメリット
範囲列を使用する主なメリットは以下の通りです：
- クエリの簡素化：重複チェックや特定期間の予約検索が簡単になる
- データベースレベルの制約：重複を防ぐ制約を簡単に追加できる
- パフォーマンスの向上：範囲型に最適化されたインデックスを使用できる
重複防止の制約を追加する
範囲列のさらなる魅力は、重複を防ぐ制約を簡単に追加できることです。
以下の SQL 文は、同じテーブルに対して重複する予約を防ぐ制約を追加します：
-- btree_gist拡張機能を有効化（制約に必要）
create extension btree_gist;
-- 同じtable_idに対して重複する期間を持つ予約を防ぐ制約を追加
alter table reservations
add constraint exclude_overlapping_reservations
exclude using gist (table_id with =, duration with &&);
この制約の意味は：
「同じ table_id を持ち、かつ duration が重複（&& 演算子）する予約を禁止する」です。
これにより、データベース自体が整合性を保証してくれますね！
実装例
それでは、Next.js と Supabase を使用して、
範囲列を活用した予約システムを実装してみましょう。
データベース設定
まずは、Supabase プロジェクトでテーブルと制約を設定します：
-- btree_gist拡張機能を有効化
create extension btree_gist;
-- 予約テーブルを作成
create table reservations (
id serial primary key,
title text not null,
table_id int not null,
duration tstzrange not null
);
-- 制約を追加して重複予約を防止
alter table reservations
add constraint exclude_overlapping_reservations
exclude using gist (table_id with =, duration with &&);
-- テーブル（席）のマスターデータ
create table tables (
id serial primary key,
name text not null
);
Supabase クライアントのセットアップ
Next.js プロジェクトで Supabase クライアントをセットアップします：
// lib/supabase.ts
import { createClient } from "@supabase/supabase-js";
const supabaseUrl = process.env.NEXT_PUBLIC_SUPABASE_URL || "";
const supabaseAnonKey = process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY || "";
export const supabase = createClient(supabaseUrl, supabaseAnonKey);
予約チェック関数の実装
範囲列を使用した予約チェック関数を実装しましょう：
// utils/reservationUtils.ts
import { SupabaseClient } from "@supabase/supabase-js";
/**
* テーブルが指定時間範囲で利用可能かチェックする
*/
export async function checkTableAvailability(
supabase: SupabaseClient,
tableId: number,
startTime: string,
endTime: string
) {
try {
// PostgreSQL形式の範囲文字列を作成
// 形式: [開始時間, 終了時間) - 開始時間は含み、終了時間は含まない
const requestedRange = `[${startTime}, ${endTime})`;
// 重複する予約を検索
const { data, error } = await supabase
.from("reservations")
.select("id, title, duration")
.eq("table_id", tableId)
.overlaps("duration", requestedRange);
if (error) throw error;
return {
available: data.length === 0,
conflictingReservations: data.length > 0 ? data : undefined,
};
} catch (error) {
console.error("Error checking table availability:", error);
throw error;
}
}
/**
* 新しい予約を作成する（テーブルが利用可能な場合）
*/
export async function createReservation(
supabase: SupabaseClient,
tableId: number,
title: string,
startTime: string,
endTime: string
) {
try {
// まずテーブルの利用可能性をチェック
const availability = await checkTableAvailability(
supabase,
tableId,
startTime,
endTime
);
if (!availability.available) {
return {
success: false,
error: "指定した時間にはテーブルが利用できません",
conflictingReservations: availability.conflictingReservations,
};
}
// PostgreSQL形式の範囲文字列を作成
const reservationRange = `[${startTime}, ${endTime})`;
// 新しい予約を挿入
const { data, error } = await supabase
.from("reservations")
.insert([
{
table_id: tableId,
title: title,
duration: reservationRange,
},
])
.select();
if (error) throw error;
return {
success: true,
reservation: data[0],
};
} catch (error) {
console.error("Error creating reservation:", error);
return {
success: false,
error: error.message,
};
}
}
この実装では、Supabase の JavaScript クライアントが提供する .overlaps() メソッドを使用しています。このメソッドは PostgreSQL の && 演算子（範囲が重複するかをチェック）にマッピングされています。
まとめ
PostgreSQL の範囲列と Supabase を活用することで、
時間や日付ベースの実装が、格段に簡単になりました！
特に予約システムでは、以下のメリットがあります：
-
クエリの簡素化：
- 範囲演算子 (
&&,@>,<@など) を使って、簡潔で読みやすいクエリが書ける - Supabase クライアントの
.overlaps()メソッドで簡単に実装できる
- 範囲演算子 (
-
データ整合性の保証：
- データベースレベルでの制約により、重複予約を確実に防止できる
- アプリケーションロジックの複雑さが軽減される
-
パフォーマンスの最適化：
- GiST インデックスを使用した効率的な範囲検索が可能
- 大量のデータでも高速に処理できる
おわりに
最後まで読んでいただき、ありがとうございます 🥳
自分の作業中の記録のような記事ですが、当記事が参考になれば幸いです！
そして、もし、間違いや補足情報などがありましたら、
ぜひコメントを追加してください！
Happy Hacking :)
参考
Discussion