How does this work for bulk operations?

[matheuscscp]

Imagine my program receives a list of Book unique keys, and it needs to return the list of these Books. The query would be something like this:

SELECT * FROM books WHERE key IN ('key1', ..., 'keyN');

How would I use sqlc for that?

[kyleconroy]

You're in luck! sqlc already supports this using ANY. The following SQL:

-- name: GetBulkBooks :many
SELECT * FROM books
WHERE key = ANY($1::text[]);

generate this method signature

func (q *Queries) GetBulkBooks(ctx context.Context, keys []string) ([]Book, error) {
  ...
}

Duplicate of #167

[matheuscscp]

Sorry for not being specific enough: What about bulk inserts?

[kyleconroy]

There are a few ways to do bulk inserts in Go. This article from 2017 goes into two approaches. The first approach, insering multiple records inside a transaction, can be used today with sqlc.

-- name: InsertBook :exec
INSERT INTO books (key, title) VALUES ($1, $2)

However, this isn't really a true bulk insert. You can insert multiple records in a single query:

-- name: BulkInsert :exec
INSERT INTO books (key, title) VALUES
    ($1, $2),
    ($3, $4),
    ($5, $6)

This approach can't support a dynamic number of records.

The suggested approach is to use COPY (see this excellent StackOverflow answer). lib/pq has special support for this use case. I've opened up #218 for the feature.

[BienTrinh]

It does not work on MySQL. My table and schema below:
schema:
CREATE TABLE pkg (
id text NOT NULL,
vic VARCHAR(50) NOT NULL,
kim text NOT NULL,
order INTEGER NOT NULL,
PRIMARY KEY (id)
);
/* name: GetPkgID :many */
SELECT id,vic,kim FROM pkg WHERE vic = ANY($1::int[]) AND shop_id = ?;

sqlc generate
sql/query/pkg.sql:2:67: syntax error at or near "$"
sql/query/pkg.sql:2:76: syntax error at or near "]"

[kyleconroy]

MySQL does not support arrays so it's not expected for this to work. I've opened #695 to track this issue.