How does this work for bulk operations? #216

Activity

1. kyleconroy

   added

   duplicateThis issue or pull request already existsThis issue or pull request already exists

   on Dec 25, 2019
2. kyleconroy commented on Dec 25, 2019

   

   kyleconroy

   on Dec 25, 2019

   Last edited by kyleconroy

   Collaborator

   More actions

   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

   👍React with 👍1Reacted by Christiaan B van Zyl
3. kyleconroy

   closed this as completedon Dec 25, 2019
4. matheuscscp commented on Dec 25, 2019

   

   matheuscscp

   on Dec 25, 2019

   Author

   More actions

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

   👍React with 👍6Reacted by Jacob Powers, Oscar Beaumont, Amir Ghaffari, David Brouwer, Zafer and Andrey Moiseev
5. kyleconroy commented on Dec 25, 2019

   

   kyleconroy

   on Dec 25, 2019

   Collaborator

   More actions

   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.

   👍React with 👍6Reacted by Christiaan B van Zyl, Steve Dang, Roman Odermatt, Rayne, Danny Hermes and saito-yoshikazu-ab

   😕React with 😕1Reacted by Puji Setiawan
6. BienTrinh commented on Aug 30, 2020

   

   BienTrinh

   on Aug 30, 2020

   More actions

   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 "]"
7. kyleconroy commented on Aug 30, 2020

   

   kyleconroy

   on Aug 30, 2020

   Collaborator

   More actions

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