[Go] Handle queries containing IN clauses with NamedStmt with sqlx

This is a sample code of how to handle a query containing an IN clause in NamedStmt in sqlx. It can also be applied to the EXISTS clause.

• No error is expected

Sample code

main.go

func main() {
	userIds := []int{1, 2, 3, 4, 5}
	age := 20
	select(ids, createdAt)
}

select.go

var db *sqlx.DB

func select(userIds []int, age int) []User {

	//SQL template
	originQuery := `SELECT * FROM USER WHERE USER_ID IN (:USER_ID) AND AGE > :AGE`

	//Value to bind
	arg := map[string]interface{}{
		"USER_ID":  userIds,
		"AGE": age,
	}

	//Make the IN clause usable in NamedStmt
	query, params := formatQuery(&originQuery, &arg)

	//Execute Select statement
	namedStmt, _ := db.PrepareNamedContext(context.Background(), *query)

	var users []User
	namedStmt.SelectContext(context.Background(), &sqlxSamples, *params)

	return users
}

format.go

func formatQuery(q *string, a *map[string]interface{}) (*string, *map[string]interface{}) {

	query, args, _ := sqlx.Named(*q, *a)
	fmt.Println(query) // SELECT * FROM USER WHERE USER_ID IN (?) AND AGE > ?
	fmt.Println(args)  // [[1 2 3 4 5] 20]

	query, args, _ = sqlx.In(query, args...)
	fmt.Println(query) // SELECT * FROM USER WHERE USER_ID IN (?, ?, ?, ?, ?) AND AGE > ?
	fmt.Println(args)  // [1 2 3 4 5 20]

	query = sqlx.Rebind(sqlx.NAMED, query)
	params := map[string]interface{}{}
	for i, arg := range args {
		key := fmt.Sprintf("arg%d", i+1)
		params[key] = arg
	}
	fmt.Println(query)  // SELECT * FROM USER WHERE USER_ID IN (:arg1, :arg2, :arg3, :arg4, :arg5) AND AGE > :arg6
	fmt.Println(params) // map[arg1:1 arg2:2 arg3:3 arg4:4 arg5:5 arg6:20]

	return &query, &params
}

If you define the SQL bind variable with ? In advance, you may not need sqlx.Named ().