Sqlx: Named queries for multiple values group

[huygn]

Hi,
I need to create multiple users at once, using $1 $2 $3 feels clunky, is below syntax for Postgres possible?

stmt, err := tx.PrepareNamed(
  INSERT INTO user_info (first_name, last_name, email)
  VALUES (:first_name, :last_name, :email),(:first_name, :last_name, :email),(:first_name, :last_name, :email)
)

_, err = stmt.Exec(mySliceOfStructs)

Running above throws a panic reflect: call of github.com/gnhuy91/goweb/vendor/github.com/jmoiron/sqlx/reflectx.(*Mapper).TraversalsByName on slice Value. If above syntax is not supported, is there anyway to achieve beside using $1 $2 $3?

[kujenga]

I just ran into this same situation as well. What is the best way of inserting multiple rows at once?

[limoli]

Why this commit is not pulled to the main master branch?

[cgebe]

@jmoiron can you pull https://github.com/hmgle/sqlx/commit/018bd2dc4495148c6bc575e693573ea68c9ad506?

[tanishiking]

FYI, we can construct SQL that inserts multiple rows at once using sqlx.In like this

package main

import (
    "fmt"

    "github.com/jmoiron/sqlx"
)

func main() {
    query, args, _ := sqlx.In(
        "INSERT INTO t1 (col_1, col_2) VALUES (?), (?), (?)",
        []string{"1", "2"},
        []string{"3", "4"},
        []string{"5", "6"},
    )
    fmt.Println(query) // INSERT INTO t1 (col_1, col_2) VALUES (?, ?), (?, ?), (?, ?)
    fmt.Println(args) // [1 2 3 4 5 6]
}

or

package main

import (
    "database/sql/driver"
    "fmt"

    "github.com/jmoiron/sqlx"
)

type Row struct {
    Col1 string
    Col2 string
}

func (r Row) Value() (driver.Value, error) {
    return []string{r.Col1, r.Col2}, nil
}

func main() {
    row1 := Row{Col1: "1", Col2: "2"}
    row2 := Row{Col1: "3", Col2: "4"}
    row3 := Row{Col1: "5", Col2: "6"}
    rows := []interface{}{row1, row2, row3}

    query, args, _ := sqlx.In(
        "INSERT INTO t1 (col_1, col_2) VALUES (?), (?), (?)",
        rows...
        // if the arg implements driver.Valuer, sqlx.In will expand it by calling Value()
        // https://github.com/jmoiron/sqlx/blob/82935fac6c1a317907c8f43ed3f7f85ea844a78b/bind.go#L119-L121
    )
    fmt.Println(query) // INSERT INTO t1 (col_1, col_2) VALUES (?, ?), (?, ?), (?, ?)
    fmt.Println(args) // [1 2 3 4 5 6]
}

because what sqlx.In does is

What sqlx.In does is expand any bindvars in the query passed to it that correspond to a slice in the arguments to the length of that slice, and then append those slice elements to a new arglist. It does this with the ? bindvar only; you can use db.Rebind to get a query suitable for your backend.
http://jmoiron.github.io/sqlx/#inQueries

---

https://github.com/jmoiron/sqlx/pull/285 looks very useful for us to constructing SQL that insert multiple rows using Named Queries, we'll be able to construct such a SQL safer using that:+1:

[Prajna]

FYI, we can construct SQL that inserts multiple rows at once using sqlx.In like this

package main

import (
  "fmt"

  "github.com/jmoiron/sqlx"
)

func main() {
  query, args, _ := sqlx.In(
      "INSERT INTO t1 (col_1, col_2) VALUES (?), (?), (?)",
      []string{"1", "2"},
      []string{"3", "4"},
      []string{"5", "6"},
  )
  fmt.Println(query) // INSERT INTO t1 (col_1, col_2) VALUES (?, ?), (?, ?), (?, ?)
  fmt.Println(args) // [1 2 3 4 5 6]
}

or

package main

import (
  "database/sql/driver"
  "fmt"

  "github.com/jmoiron/sqlx"
)

type Row struct {
  Col1 string
  Col2 string
}

func (r Row) Value() (driver.Value, error) {
  return []string{r.Col1, r.Col2}, nil
}

func main() {
  row1 := Row{Col1: "1", Col2: "2"}
  row2 := Row{Col1: "3", Col2: "4"}
  row3 := Row{Col1: "5", Col2: "6"}
  rows := []interface{}{row1, row2, row3}

  query, args, _ := sqlx.In(
      "INSERT INTO t1 (col_1, col_2) VALUES (?), (?), (?)",
      rows...
      // if the arg implements driver.Valuer, sqlx.In will expand it by calling Value()
      // https://github.com/jmoiron/sqlx/blob/82935fac6c1a317907c8f43ed3f7f85ea844a78b/bind.go#L119-L121
  )
  fmt.Println(query) // INSERT INTO t1 (col_1, col_2) VALUES (?, ?), (?, ?), (?, ?)
  fmt.Println(args) // [1 2 3 4 5 6]
}

because what sqlx.In does is

What sqlx.In does is expand any bindvars in the query passed to it that correspond to a slice in the arguments to the length of that slice, and then append those slice elements to a new arglist. It does this with the ? bindvar only; you can use db.Rebind to get a query suitable for your backend.
http://jmoiron.github.io/sqlx/#inQueries

285 looks very useful for us to constructing SQL that insert multiple rows using Named Queries, we'll be able to construct such a SQL safer using that👍

Does this work with Postgres $1 $2 $3?

[dcormier]

Does this work with Postgres $1 $2 $3?

Call (*sqlx.DB).Rebind(query) to make sure the placeholders are set appropriately for your DB type.