#Bulk inserting with sqlc

46 messages · Page 1 of 1 (latest)

trail sand
#

you would have to build a dynamic string but you just need to do something like

var records []record

var sqlstr = `INSERT INTO records
   (name, email, displayname, firstname, lastname)
VALUES`
sqlstr += strings.Repeat(" (?, ?, ?, ?, ?)", len(records))

args := make([]any, 0, len(records)*5)
for _, r := range records {
  args = append(r.name, r.email, r.dname, r.fname, r.lname)
}

db.ExecContext(context.TODO(), sqlstr, args...)```
oblique rampart
#

What database engine are you targeting?

trail sand
oblique rampart
#

I mean MySQL, PostgreSQL gopherconfused

trail sand
#

ngl idk why but i interpreted sqlc as sqlite

#

jsonb_array_elements is postgres though

#

and that ->> syntax

oblique rampart
#

So if speed would be desired then an optimized COPY FROM is preferred over what sqlc can generate.

median copper
#

oh

#

i didnt know

#

that

#

-- name: CreateAuthors :copyfrom
INSERT INTO authors (name, bio) VALUES ($1, $2);

#

was a thing

#

so this may be what i was looking for since it seems to accept an array for CreateAuthors fn

oblique rampart
#

Yeah

#

So it seems like they support that 🙂

#

It'll be significantly faster if you make lots of inserts at once.

#

What does the code it generats for that look like?

median copper
#

i dont know about bulk insert ive not tried copyfrom yet

#

but ive been going through hell trying to do a bulk update without sqlc, as sqlc does not support it

trail sand
#

copyfrom lets you do just that?

median copper
#

well i tried on sqlc but it says only insert is supported

#

maybe that's their limitation though

#

ive just not been able to do this at all

#

either i get something like failed to encode args[0]: unable to encode 4 into text format for text

#

or operator does not exist: integer = text

#

well thats me manually doing with pgx

#

it's just that i cant even find any info about bulk update with sqlc

trail sand
#

i dont use sqlc, so i dont know

#

but maybe at this point it's worth just ditching sqlc entirely for this one use case and just writing the function (similar to what i supplied) yourself so you can move on with your life 😛

median copper
#

yeah never mind im just dull

#

there's a :batchexec

#

which is pretty much the same

oblique rampart
#

Not quite the same.

#

The benefit of the copy protocol is that it doesn't parse a lot of your data, you send the data in the proper format over the wire and it's inserted directly.

#

That's why it's much faster beyond just 3 records

#

A batch is just a faster version of a normal insert but it doesn't scale as well as the copy protocol.

median copper
#

i dont think i will have more than 10-15 records at once with what im trying to do so it shouldnt be a problem. also it's something that's not gonna be done often anyway

oblique rampart
#

Okay

trail sand
#

smh should have just used a for loop then

oblique rampart
#

Manual implementation of this bulk insert is low hanging fruit. But if you want sqlc to do it then just do whatever it allows you to do.

median copper
trail sand
#

eh, you learned something at least