`sqlc.slice()` breaks queries when using pgx/v5

[actatum]

Version

1.19.1

What happened?

sqlc generates a query that attempts to pass in a slice as a single parameter instead of expanding the slice inside of the WHERE IN clause.

example schema and query:

CREATE TABLE foo (
	hash TEXT NOT NULL PRIMARY KEY,
    updated_at TIMESTAMPTZ NOT NULL
);

-- name: UpdateFoo :exec
UPDATE foo SET updated_at = now() WHERE hash IN (sqlc.slice(hashes));

Results in

// Code generated by sqlc. DO NOT EDIT.
// versions:
//   sqlc v1.19.1
// source: query.sql

package db

import (
	"context"
	"strings"
)

const updateFoo = `-- name: UpdateFoo :exec
UPDATE foo SET updated_at = now() WHERE hash IN ($1)
`

func (q *Queries) UpdateFoo(ctx context.Context, hashes []string) error {
	_, err := q.db.Exec(ctx, updateFoo, hashes)
	return err
}

It should look like this

// Code generated by sqlc. DO NOT EDIT.
// versions:
//   sqlc v1.19.1
// source: query.sql

package db

import (
	"context"
	"strings"
)

const updateFoo = `-- name: UpdateFoo :exec
UPDATE foo SET updated_at = now() WHERE hash IN ($1)
`

func (q *Queries) UpdateFoo(ctx context.Context, hashes []string) error {
	query := updateFoo
	var queryParams []interface{}
	if len(hashes) > 0 {
		for _, v := range hashes {
			queryParams = append(queryParams, v)
		}
		query = strings.Replace(query, "/*SLICE:hashes*/?", strings.Repeat(",?", len(hashes))[1:], 1)
	} else {
		query = strings.Replace(query, "/*SLICE:hashes*/?", "NULL", 1)
	}
	_, err := q.db.ExecContext(ctx, query, queryParams...)
	return err
}

Relevant log output

failed to encode args[0]: unable to encode []uuid.UUID{uuid.UUID{0xed, 0x87, 0x78, 0xb7, 0x3c, 0x0, 0x4c, 0x1a, 0x88, 0x28, 0x68, 0x37, 0x31, 0x48, 0x43, 0xa5}} into binary format for uuid (OID 2950): cannot find encode plan

Database schema

CREATE TABLE foo (
	hash TEXT NOT NULL PRIMARY KEY,
    updated_at TIMESTAMPTZ NOT NULL
);

SQL queries

-- name: UpdateFoo :exec
UPDATE foo SET updated_at = now() WHERE hash IN (sqlc.slice(hashes));

Configuration

{
  "version": "1",
  "packages": [
    {
      "path": "db",
      "engine": "postgresql",
      "schema": "query.sql",
      "queries": "query.sql",
      "sql_package": "pgx/v5"
    }
  ]
}

Playground URL

https://play.sqlc.dev/p/ebc7cba592d1da8390f2e976fefa37f1e131a64bdb4a6e03538dfda665f99467

What operating system are you using?

macOS

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

[kyleconroy]

sqlc.slice is only needed for MySQL and SQLite, as they do not support arrays. For PostgreSQL, you can use slices directly. Here' s how:

https://play.sqlc.dev/p/3d6d0563cc3582587864c80ec8aadb1915dce7fc9f01ad09dacde18c9a7752cd

[actatum]

Oh awesome, thanks for the quick response and love this tool you've built!