Golang db query using slice IN clause

Because database/sql does not inspect your query and it passes your arguments directly to the driver, it makes dealing with queries with IN clauses difficult:

SELECT * FROM users WHERE level IN (?);

When this gets prepared as a statement on the backend, the bindvar ? will only correspond to a single argument, but what is often desired is for that to be a variable number of arguments depending on the length of some slice

var levels = []int{4, 6, 7}
rows, err := db.Query("SELECT * FROM users WHERE level IN (?);", levels)

There is a way to handle these types of queries using sqlx package which provide more control over database queries.

This pattern is possible by first processing the query with sqlx.In:

var levels = []int{4, 6, 7}
query, args, err := sqlx.In("SELECT * FROM users WHERE level IN (?);", levels)

For more information Go through Godoc for InQueries

You need the number of "?" in the "IN" clause to match the number of arguments , so you need to do something like this:

inq := "6,7" //strings.Join(artIds, ",")
qms := strings.Repeat("?,", len(inq))
qms = params[:len(params)-1] // remove the trailing ","

rows, err = db.Query("SELECT DISTINCT title FROM tags_for_articles LEFT JOIN tags ON tags.id = tags_for_articles.tag_id WHERE article_id IN (" + qms + ")", inq)

I just had the same problem then I came up with another solution in more secure way. So, you don't need to aware of SQL injection.

params := []string{"A", "B"}
jsonParams, err := json.Marshal(params)
if err != nil {
    return err
}

rows, err = db.Query(`
 WITH compared_values as(
   SELECT * 
     FROM JSON_TABLE(
      ?,
      "$[*]" COLUMNS( 
       value TEXT PATH "$"
     )
   ) as compared_values
 ) 
 SELECT * FROM target_table
 WHERE target_value IN (SELECT value FROM compared_values);
`, jsonParams)

you can also use pure select part in SQL query like this but I'm not sure about performace. I think using WITH cause can prevent repeatly JSON parsing.

 SELECT * FROM  target_table
 WHERE target_value IN (
   SELECT value FROM JSON_TABLE(
     ?,
     "$[*]" COLUMNS( 
       value TEXT PATH "$"
     )
   ) as compared_values
  );