SQLBoilerで INSERT ON DUPLICATE KEY UPDATE したい

# モチベーション

多くのプロジェクトではレコードを一度で挿入または変更したいという要望があると思います。そんなときDBがMySQLであればBULK INSERTをしたいと思います。ですが、残念ながら今のプロジェクトで用いているSQLBoilerにはBULK INSERTの機能がありません（というか、多くのORMでないと思います）。しかし、SQLBoilerでは独自テンプレートを記述できるため、誰かがやっているだろうということで探すとこちらの記事が見つかりました。

https://qiita.com/touyu/items/4b25fbf12804f12778b7

今のプロジェクトではモデルでIDの生成を行っているため、何も問題なく利用させてもらいました。めでたしめでたし・・・のはずだったのですが、プロジェクトで開発を進めていくうちに、今度は `INSERT ON DUPLICATE KEY UPDATE` に相当する機能が必要となってきました。

せっかくテンプレートを作れて、元々SQLBoilerには`Upsert`があって、先程の記事を見つけたことでBULK INSERTの機能もあるのなら、それらを組み合わせることで`UpsertAll`という`INSERT ON DUPLICATE KEY UPDATE`を用いる機能を作れないか？というモチベーションがわいたので今回実装してみました。

# 実装

## 環境

今回は以下の環境にて実装を行いました。多少違っても動くと信じています :joy: 

* Go 1.18.4
* SQLBoiler v4.12.0
* MySQL 8.0

## 目標とするクエリ

今回目標とするクエリは以下のようなクエリです。このクエリでは`VALUES`関数を用いて元々INSERTする予定だった値をそのままUPDATEの内容に変更しています。このクエリでは少し難しい変更（例えば、IF文やCASE文を用いた複雑な分岐など）はできませんが、既存の値をSELECTで取得してサーバー側で計算することによって、SELECT文一回分ぐらいのクエリの追加で同じことが実装可能です。

```sql
INSERT INTO `Table1` (`Column1`,`Column2`,`Column3`,`Column4`) VALUES (?,?,?,?), (?,?,?,?), (?,?,?,?), (?,?,?,?), (?,?,?,?), (?,?,?,?) ON DUPLICATE KEY UPDATE `Table1`.`Column2` = VALUES(`Table1`.`Column2`)
```

## カスタムテンプレート読み込みの準備

カスタムテンプレートは`sqlboiler.toml`の`templates`という項目に設定または実行時に`--templates`オプションで渡すことによって、読み込むディレクトリを指定することができます。

設定ファイルに記載する場合：
```sqlboiler.toml
output   = "generated"
wipe     = true
no-tests = true
add-enum-types = true
sslmode  = "disabled"
templates = [
  "/path/to/templates1",
  "/path/to/templates2"
]

[mysql]
  sslmode = "false"
  user    = "root"
  dbname  = "sqlboiler_upsert_all"
  host    = "127.0.0.1"
  port    = 3306
```

コマンド実行時に指定する場合：
```sh
$ sqlboiler mysql --templates /path/to/templates1,/path/to/tempaltes2
```

今回は`templates`というディレクトリに`UpsertAll`用のファイルを配置します。

## カスタムテンプレートの実装

基本はリンク先の記事を参考に、`VALUES`以降の内容をfor文で繰り返し追加していきながら、クエリの最後に`ON DUPLICATE KEY UPDATE`文を追加します。最後に、 [目標とするクエリ](#目標とするクエリ)で述べたように、キーによる重複があった場合はINSERTしようとしてた値でそのままUPDATEするために`ColumnA = VALUES(ColumnA)`という代入を行っています。

あとは、SQLBoilerがテンプレートとして持っている`Upsert`や`Update`テンプレートを参考に、引数として受け取っているINSERTカラムやUPDATEカラムの内容を実装したら完成です。

```templates/16_upsert_all.go.tpl
{{- $alias := .Aliases.Table .Table.Name -}}
{{- $schemaTable := .Table.Name | .SchemaTable}}

// UpsertAll upserts all rows with the specified column values, using an executor.
func (o {{$alias.UpSingular}}Slice) UpsertAll({{if .NoContext}}exec boil.Executor{{else}}ctx context.Context, exec boil.ContextExecutor{{end}}, updateColumns, insertColumns boil.Columns) error {    ln := int64(len(o))
    if ln == 0 {
        return nil
    }
    var sql string
    vals := []interface{}{}
    for i, row := range o {
        {{- template "timestamp_upsert_all_helper" . }}

        {{if not .NoHooks -}}
        if err := row.doBeforeInsertHooks(ctx, exec); err != nil {
            return err
        }
        {{- end}}

        nzDefaults := queries.NonZeroDefaultSet({{$alias.DownSingular}}ColumnsWithDefault, row)
        insert, _ := insertColumns.InsertColumnSet(
            {{$alias.DownSingular}}AllColumns,
            {{$alias.DownSingular}}ColumnsWithDefault,
            {{$alias.DownSingular}}ColumnsWithoutDefault,
            nzDefaults,
        )
        if i == 0 {
            sql = "INSERT INTO {{$schemaTable}} " + "({{.LQ}}" + strings.Join(insert, "{{.RQ}},{{.LQ}}") + "{{.RQ}})" + " VALUES "
        }
        sql += strmangle.Placeholders(dialect.UseIndexPlaceholders, len(insert), len(vals)+1, len(insert))
        if i != len(o)-1 {
            sql += ", "
        }
        valMapping, err := queries.BindMapping({{$alias.DownSingular}}Type, {{$alias.DownSingular}}Mapping, insert)
        if err != nil {
            return err
        }
        value := reflect.Indirect(reflect.ValueOf(row))
        vals = append(vals, queries.ValuesFromMapping(value, valMapping)...)
    }

	update := updateColumns.UpdateColumnSet(
	    {{$alias.DownSingular}}AllColumns,
	    {{$alias.DownSingular}}PrimaryKeyColumns,
	)
	if !updateColumns.IsNone() && len(update) == 0 {
	    return errors.New("sqlboilerdao: unable to upsert account_patients, could not build update column list")
	}

    sql += " ON DUPLICATE KEY UPDATE "
    for i, v := range update {
        if i != 0 {
            sql += ","
        }
	    quoted := strmangle.IdentQuote(dialect.LQ, dialect.RQ, v)
        sql += quoted + " = VALUES(" + quoted + ")"
    }

    if boil.DebugMode {
        fmt.Fprintln(boil.DebugWriter, sql)
        fmt.Fprintln(boil.DebugWriter, vals...)
    }

    {{if .NoContext -}}
    _, err := exec.Exec(ctx, sql, vals...)
    {{else -}}
    _, err := exec.ExecContext(ctx, sql, vals...)
    {{end -}}

    if err != nil {
        return errors.Wrap(err, "{{.PkgName}}: unable to insert into {{.Table.Name}}")
    }

    return nil
}

{{- define "timestamp_upsert_all_helper" -}}
    {{- if not .NoAutoTimestamps -}}
    {{- $colNames := .Table.Columns | columnNames -}}
    {{if containsAny $colNames "created_at" "updated_at"}}
        {{if not .NoContext -}}
    if !boil.TimestampsAreSkipped(ctx) {
        {{end -}}
        currTime := time.Now().In(boil.GetLocation())
        {{range $ind, $col := .Table.Columns}}
            {{- if eq $col.Name "created_at" -}}
                {{- if eq $col.Type "time.Time" }}
        if row.CreatedAt.IsZero() {
            row.CreatedAt = currTime
        }
                {{- else}}
        if queries.MustTime(row.CreatedAt).IsZero() {
            queries.SetScanner(&row.CreatedAt, currTime)
        }
                {{- end -}}
            {{- end -}}
            {{- if eq $col.Name "updated_at" -}}
                {{- if eq $col.Type "time.Time"}}
        if row.UpdatedAt.IsZero() {
            row.UpdatedAt = currTime
        }
                {{- else}}
        if queries.MustTime(row.UpdatedAt).IsZero() {
            queries.SetScanner(&row.UpdatedAt, currTime)
        }
                {{- end -}}
            {{- end -}}
        {{end}}
        {{if not .NoContext -}}
    }
        {{end -}}
    {{end}}
    {{- end}}
{{- end -}}
```

以下は実際にこのテンプレートを用いて作られたクエリです。

```sql
INSERT INTO `users` (`id`,`name`,`created_at`,`updated_at`) VALUES (?,?,?,?), (?,?,?,?), (?,?,?,?), (?,?,?,?), (?,?,?,?), (?,?,?,?) ON DUPLICATE KEY UPDATE `users`.`name` = VALUES(`users`.`name`)
```

（`updated_at`が更新されていませんが、ご容赦を :joy: ）

# 感想

今回の内容は実は「なんとなくできそうだな〜」ぐらいのノリが始まりでした :joy: そのためSQLBoilerのテンプレートについて深いところまでは理解していないのですが、模倣できそうなテンプレートさえ見つかればカスタムテンプレートを作成できることが分かったので、MySQL以外の他のDB特有のクエリを実装することもできそうだなと思いました。

一応動くサンプルコードをGithubにアップロードしているので、よろしければ試しにご利用ください。

https://github.com/shou8/sqlboiler_upsert_all

もし間違いやバグなど見つけていただけましたらご連絡いただけますと幸いです。