sqlite: bulk insert / copyfrom equivalent

[wilsonehusin]

What do you want to change?

SQLite 3.7.11 and above supports bulk inserts with the syntax:

INSERT INTO 'tablename' ('column1', 'column2') VALUES
  ('1data1', '1data2'),
  ('2data1', '2data2'),
  ...
  ('Ndata1', 'Ndata2');

How difficult would it be to support this in sqlc? Happy to contribute if that's what's required to make it happen!

What database engines need to be changed?

SQLite

What programming language backends need to be changed?

No response

[msbatarce]

That type of multiple inserts is supported by most engines, but, at the moment, not suported by sqlc which would by nice IMO. That being said, it isnt really a robust bulk instert like COPY on postgres or LOAD DATA and shouldn't use :copyfrom. The thing is that multiple inserts with VALUES has a limit on how many rows can be instered at one time (somewhere I read that it was 500 rows for sqllite, nbut not sure).

[rishi-kulkarni]

If you want to get really cute about it, you can already use json_each on a single parameter:

INSERT INTO users(name, email) SELECT e.value ->> 'name', e.value ->> 'email' FROM json_each(?) e

EDIT: Forgot the .value bits.

[mondy]

I too need this feature.
I want to bulk insert a BLOB value.
Inserting BLOB values using json is a circuitous process.

[Arthur-Sk]

Any update on this?

[brandur]

@rishi-kulkarni OOC, does your example above work for you? I'm basically looking for any workaround to get a multi-value into SQLite, but when I plug that into sqlc, it doesn't seem to respect a parameter to json_each:

-- name: JSONEachTest :execresult
INSERT INTO users(name, email) SELECT e.value ->> 'name', e.value ->> 'email' FROM json_each(?) e;

const jSONEachTest = `-- name: JSONEachTest :execresult
INSERT INTO users(name, email) SELECT e.value ->> 'name', e.value ->> 'email' FROM json_each(?) e
`

func (q *Queries) JSONEachTest(ctx context.Context, db DBTX) (sql.Result, error) {
	return db.ExecContext(ctx, jSONEachTest)
}

(Note, no parameters extracted to JSONEachTest.)