モチベーション

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

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

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

実装

環境

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

Go 1.18.4
SQLBoiler v4.12.0
MySQL 8.0

目標とするクエリ

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

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

コマンド実行時に指定する場合：

$ 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 -}}

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

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

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

感想

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

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

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