Supabaseのupsertとは?

💡Tips

前回書いた記事で使ったusersテーブルで同じページで、ユーザーアカウントの新規作成と更新をする機能を作ろうとしていたのですが、うまくいきませんでした💦

https://zenn.dev/joo_hashi/articles/11571e0fb9a36d

今回使うupsertとは?

https://supabase.com/docs/reference/dart/upsert

Upsert data
Perform an UPSERT on the table or view. Depending on the column(s) passed to onConflict, .upsert() allows you to perform the equivalent of .insert() if a row with the corresponding onConflict columns doesn't exist, or if it does exist, perform an alternative action depending on ignoreDuplicates.

Primary keys must be included in values to use upsert.

テーブルまたはビューに対して UPSERT を実行します。onConflict に渡されたカラムに応じて、.upsert() を使用すると、対応する onConflict カラムを持つ行が存在しない場合に .insert() と同等の処理を実行したり、存在する場合に ignoreDuplicates に応じた代替処理を実行したりすることができます。

upsertを使用するには、値に主キーを含める必要があります。

await supabase
  .from('messages')
  .upsert({ 'id': 3, 'message': 'foo', 'username': 'supabot' });

Xでできないこと呟いてたら、タイラーさんに助言をもらいました。多分僕は違うことをしたと思うけど。

ロジックは作ったけどエラーが出た???

Github Copilot flutter: │ 🐛 😇createUserOrUpdate error: PostgrestException(message: there is no unique or exclusion constraint matching the ON CONFLICT specification, code: 42P10, details: Bad Request, hint: null) flutter:

Exception: PostgrestException(message: there is no unique or exclusion constraint matching the ON CONFLICT specification, code: 42P10, details: Bad Request, hint: null)

PostgrestException(message: there is no unique or exclusion constraint matching the ON CONFLICT specification, code: 42P10, details: Bad Request, hint: null)

AIに質問してみた

エラーメッセージから、ON CONFLICTの指定に一致する一意性制約または排他制約が存在しないことがわかります。つまり、uuidカラムに一意性制約が設定されていない可能性があります。

一意性制約は、テーブルの特定のカラムに対して同じ値が存在しないことを保証するための制約です。ON CONFLICT句は、一意性制約違反が発生したときの挙動を指定するためのものです。したがって、ON CONFLICT句を使用するためには、まず対象のカラムに一意性制約を設定する必要があります。

PostgreSQLで一意性制約を設定するには、以下のようなSQLコマンドを使用します：

ALTER TABLE users ADD CONSTRAINT users_uuid_unique UNIQUE (uuid);

このコマンドは、usersテーブルのuuidカラムに一意性制約を追加します。制約の名前はusers_uuid_uniqueとしていますが、これは任意の名前に変更できます。

これをやるとなんとかなった...。このコードが今回使用した追加・更新が両方できるコードです。user?ってのは、FirebaseAuthで言うところのuidみたいなもんです。ログインしていれば取得することができます。session idなるものが世の中にあるけど、あれと同じかな。

// ユーザーの新規登録
  Future<void> createUserOrUpdate(
    String userName,
    String dateText,
    String iconImagePath,
    String profile,
    String jobId,
  ) async {
    try {
      final session = SupabaseInstance().supabase.client.auth.currentSession;
      final user = session?.user;
      if (user?.id == null) {
        throw Exception('User ID is null');
      }
      final jaUtc = DateTime.now().toUtc().toIso8601String();
      await SupabaseInstance().supabase.client.from('users').upsert(
        {
          'uuid': user?.id,
          'user_name': userName,
          'birthday': dateText,
          'iconImagePath': iconImagePath,
          'job_id': jobId,
          'profile': profile,
          'created_at': jaUtc,
          'updated_at': jaUtc,
          'is_delete': false,
        },
        onConflict: 'uuid',
      );
    } on Exception catch (e) {
      logger.d('😇createUserOrUpdate error: $e');
      rethrow;
    }
  }

insertではなくupsertを使う理由は、Firestoreの.doc(id).setと同じことをしたかったんですよね。今回は、onConflictなるものを使用して、uuidを指定する必要がありました。他にはコード書けててもポリシーの許可しないと使えないですよ😅

内部実装はこうなってる。

/// Perform an UPSERT on the table or view.
  ///
  /// By specifying the [onConflict] parameter, you can make UPSERT work on a column(s) that has a UNIQUE constraint.
  /// [ignoreDuplicates] Specifies if duplicate rows should be ignored and not inserted.
  ///
  /// By default no data is returned. Use a trailing `select` to return data.
  ///
  /// When inserting multiple rows in bulk, [defaultToNull] is used to set the values of fields missing in a proper subset of rows
  /// to be either `NULL` or the default value of these columns.
  /// Fields missing in all rows always use the default value of these columns.
  ///
  /// For single row insertions, missing fields will be set to default values when applicable.
  ///
  /// Default (not returning data):
  /// ```dart
  /// await supabase.from('messages').upsert({
  ///   'id': 3,
  ///   'message': 'foo',
  ///   'username': 'supabot',
  ///   'channel_id': 2
  /// });
  /// ```
  ///
  /// Returning data:
  /// ```dart
  /// final data = await supabase.from('messages').upsert({
  ///   'message': 'foo',
  ///   'username': 'supabot',
  ///   'channel_id': 1
  /// }).select();
  /// ```
  PostgrestFilterBuilder<T> upsert(
    Object values, {
    String? onConflict,
    bool ignoreDuplicates = false,
    bool defaultToNull = true,
  }) {
    final newHeaders = {..._headers};
    newHeaders['Prefer'] =
        'resolution=${ignoreDuplicates ? 'ignore' : 'merge'}-duplicates';

    if (!defaultToNull) {
      newHeaders['Prefer'] = '${newHeaders['Prefer']!},missing=default';
    }
    Uri url = _url;
    if (onConflict != null) {
      url = _url.replace(
        queryParameters: {
          'on_conflict': onConflict,
          ..._url.queryParameters,
        },
      );
    }

    if (values is List) {
      url = _setColumnsSearchParam(values);
    }

    return PostgrestFilterBuilder<T>(_copyWith(
      method: METHOD_POST,
      headers: newHeaders,
      body: values,
      url: url,
    ));
  }

コメントを翻訳すると

/// テーブルまたはビューに対してUPSERTを実行する。
///
/// [onConflict] パラメータを指定することで、UNIQUE 制約を持つ列に対して UPSERT を動作させることができます。
/// ignoreDuplicates] 重複行を無視して挿入しないかどうかを指定します。
///
/// デフォルトではデータは返されない。データを返すには末尾の select を使用する。
///
/// 複数の行を一括挿入する場合、[defaultToNull] を使用して、適切な行のサブセットで欠落しているフィールドの値を設定する。
/// NULL` またはこれらのカラムのデフォルト値のいずれかになるように設定する。
/// すべての行で欠落しているフィールドは、常にこれらの列のデフォルト値を使用する。
///
/// 1行の挿入の場合、欠落しているフィールドは、該当する場合、デフォルト値に設定される。
///
/// デフォルト（データを返さない）：
/// dart /// await supabase.from('messages').upsert({ /// 'id'： 3, /// 'message': 'foo'、 /// 'username': 'supabot'、 /// 'channel_id': 2 /// }); /// ``` /// /// データを返す： /// dart
/// final data = await supabase.from('messages').upsert({
/// 'message': 'foo'、
/// 'username': 'supabot'、
/// 'channel_id'： 1
/// }).select()；
/// ```

この長いコメントは重要ではなくて、onConflictってパラメーターが重要なんですよね。String型のこのパラメーターに、uuidを指定すると、認証したユーザーが２回目にメソッドを実行すると、データが上書きされます。もししてないと、Firestoreの.addみたいに何個でもデータが作れてしまいます。

まとめ

失礼コメントに書いてありました😅

/// [onConflict] パラメータを指定することで、UNIQUE 制約を持つ列に対して UPSERT を動作させることができます。

今回だと、uuidのColumnに対して、UNIQUE 制約をつけています。これができてれば、認証が通っているユーザーは、updateできるみたい。認可されてるってことかな???
単純にuuid使って、upsertすれば２回目は登録したデータを上書きできるというお話でした。

await SupabaseInstance().supabase.client.from('users').upsert(
        {
          'uuid': user?.id,
          'user_name': userName,
          'birthday': dateText,
          'iconImagePath': iconImagePath,
          'job_id': jobId,
          'profile': profile,
          'created_at': jaUtc,
          'updated_at': jaUtc,
          'is_delete': false,
        },
        onConflict: 'uuid',
      );