converting argument $ 1 type: unsupported type [] string, a slice of string

Overview

This is the first post in my first year as an engineer. When I was operating the DB using sqlx, I was troubled by the following error, so I will record it. I would be grateful if you could comment and give me a bad idea.

converting argument $1 type: unsupported type []string, a slice of string

scenario

When I was trying to perform an operation using the IN condition in the WHERE clause of the SELECT statement, I got an error. This time, I will write an operation to extract USER who can use go and python from the following USER table.

Various tools

• go 1.14
• github.com/jmoiron/sqlx v1.2.0
• github.com/go-sql-driver/mysql v1.5.0
• mysql:5.7
• VSCode（1.50.1）

Bad code

	query := "SELECT Name,Skill  FROM USER WHERE Skill IN (?,?);"
	searchSkills := []string{"go", "python"}

	var records []User
	err := db.Select(&records, query, searchSkills)
	if err != nil {
		log.Fatal(err)
	}

	//converting argument $1 type: unsupported type []string, a slice of string

Pass [] string as a parameter to db.Select. I wonder if it will work because it compiles, but I get an error in the subject. By the way, if the third argument is searchSkills ..., the compilation will not pass.

What happened

I somehow understand that the type passed as an argument as an error message is not good. For the time being, read the document. https://godoc.org/github.com/jmoiron/sqlx#DB.Select

Since the third argument was a variadic argument of ʻinterface {}, let's expand [] interface {}` and throw it.

	query := "SELECT Name,Skill FROM USER WHERE Skill IN (?,?)"
	// searchSkills := []string{"go", "python"}
	searchSkills := []interface{}{"go", "python"}

	var records []User
	err := db.Select(&records, query, searchSkills...)
	if err != nil {
		log.Fatal(err)
	}

	// Name:Jiro,Skill:go
	// Name:Saburo,Skill:go
	// Name:Goro,Skill:python

You can do what you want with less error. But it's not very smart ... It is not good that you have to change the number of ? Depending on the extraction conditions. It feels strange to define it with [] interface {}.

sqlx.In There was something convenient. You can make it smarter by using the In () function provided by sqlx.

func In(query string, args ...interface{}) (string, []interface{}, error) https://godoc.org/github.com/jmoiron/sqlx#In

	queryBase := "SELECT Name,Skill FROM USER WHERE Skill IN (?)"
	searchSkills := []string{"go", "python"}

	query, param, err := sqlx.In(queryBase, searchSkills)
	if err != nil {
		log.Fatal(err)
	}
	var records []User
	err = db.Select(&records, query, param...)
	if err != nil {
		log.Fatal(err)
	}

	// Name:Jiro,Skill:go
	// Name:Saburo,Skill:go
	// Name:Goro,Skill:python

By passing the underlying SQL statement to the In () function and the slice you want to use as a parameter, it will create a query that adjusts the number of placeholders according to the number of elements in the slice. After that, if you pass it to db.Select () together with param which became[] interface {}, you can search using In condition. In this case, it seems that there is no need to worry about the number of extraction conditions and the type of parameters. Great.

By the way, if you pass a slice with 0 elements to the In () function, the following error will occur, so be careful.

empty slice passed to 'in' query

reference

golang: sql: converting argument $1 type: unsupported type []int32, a slice of int32