Passing a map as an argument to a query

[bruth]

Thank you for creating this library (and just merging #40 for static builds)!

I created a table with a column with a map datatype and when I attempt to insert a record using the Map type I am getting the error:

unsupported type duckdb.Map, a map

I noticed this line has a TODO. Is this for supporting additional data types as passed arguments to queries? Is it a matter at filling out more of these cases?

[marcboeker]

Thanks for reporting this. Do you have some sample code that I can use to probably add the missing data type. That would be great. Thanks!

[bruth]

Thanks for the response and sorry for the delay! I actually hope to have a PR opened up next week some time.

[marcboeker]

No worries, I've already started with a PR. But the serialisation of the Map{"foo": "bar", "bam": "baz"} to the SQL representation map(['foo', 'bam'], ['bar', 'baz']) is not that trivial, as a map could contain another map as its key or value. For numericals and strings my implementation already works.

[marcboeker]

After I have racked my brain about serializing a map to a query parameter, I have checked the DuckDB APIs for prepared statements and have found no way to bind a map to a parameter. After thinking twice about it, I'm unsure what your use case is.
Could you please show me the query you want to use. This would help me to understand the problem better. Thanks!

[andyyu2004]

Hey @marcboeker, I have a motivating example for the same issue except with a slice.

This fails with the error sql: converting argument $1 type: unsupported type []string, a slice of string.

func TestSliceParameter(t *testing.T) {
	db := openDB(t)
	defer db.Close()

	_, err := db.Exec("CREATE TABLE foo (x text)")
	require.NoError(t, err)

	_, err = db.Exec("INSERT INTO foo VALUES ('test')")
	require.NoError(t, err)

	_, err = db.Query("SELECT x FROM foo WHERE x = ANY($1)", []string{"test"})
	require.NoError(t, err)
}

[marcboeker]

Hi @andyyu2004, thanks for the example. As we're internally using the DuckDB parameter bind API, there is currently no function for binding slices.

A dirty hack would be to interpolate the placeholder with multiple placeholders an then iterating over the slice and binding each item separately. But I would highly suggest to avoid this, as it can get ugly quickly.

For these kind of use-cases I have used sqlx in the past. See the example under the "IN queries" section on this page. Maybe you could try this?