Batch insert

[kevinburke]

Currently Model.create([record1, record2, ..., recordN]) opens N connections and issues N inserts of one record each.

Postgres supports inserting multiple records at once, for example:

INSERT INTO films (code, title, did, date_prod, kind) VALUES
    ('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'),
    ('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy');

I believe this would be a good change, since it opens 1 connection instead of N connections, and it maps more intuitively to what you think would happen if you called Model.create([]).

However, in the current state, if a record fails validation in the database (a uniqueness constraint, foreign key reference, not null constraint, or check constraint, for example), the current master will insert every other record. Multi-row INSERT INTO ... VALUES () will not insert any rows if one row fails constraint checks. This is a breaking change and would probably require a major version bump. (That said I'm not sure whether sails-postgresql guarantees all N-1 rows get written if one row fails? does async.each early exit if one function throws?)

Another option might be to issue the queries sequentially on one connection:

INSERT INTO films (code, title, did, date_prod, kind) VALUES
    ('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy') RETURNING *;
INSERT INTO films (code, title, did, date_prod, kind) VALUES
    ('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy') RETURNING *;

I'm not sure how the client would handle multiple RETURNING * lines, maybe it would and maybe it wouldn't. This would not write any rows after a failing row, but it would write any rows beforehand.

I believe createEach would have to be modified to avoid the async.each call, and I don't believe waterline-sequel supports batch insert so we'd need to build our own query there.

[tjwebb]

@kevinburke I know I've mentioned this before, but this issue is also resolved in the es6/knex re-write:

• https://github.com/waterlinejs/postgresql-adapter/blob/master/lib/adapter.js#L271
• https://github.com/waterlinejs/postgresql-adapter/blob/master/lib/adapter.js#L286-L288

Knex supports batch insert ootb, and I bypass waterline-sequel altogether. I understand if you guys aren't ready to jump to a new adapter, but I'm interested in any feedback you might be able to offer.

[tjwebb]

if a record fails validation in the database (a uniqueness constraint, foreign key reference, not null constraint, or check constraint, for example), the current master will insert every other record.

This is probably because, without an explicit createEach implementation, each create is treated by Waterline as a separate operation. I think also the new adapter would solve this problem by implementing the createEach method.

It does constitute an unfortunate semantic difference between the reference implementation and the adapter-specific implementation. In this case I think it's correct to fail the entire insert if any one of the records fails; this would require a fix in Waterline core.

[particlebanana]

Part of the issue with the non-optimized createEach is because we stupidly added nested creates which looking back was a horrible idea.

https://github.com/balderdashy/waterline/issues/1007#issuecomment-145177976

[sailsbot]

Thanks for posting, @kevinburke. I'm a repo bot-- nice to meet you!

It has been 30 days since there have been any updates or new comments on this page. If this issue has been resolved, feel free to disregard the rest of this message. On the other hand, if you are still waiting on a patch, please:

• review our contribution guide to make sure this submission meets our criteria (only verified bugs with documented features, please; no questions, commentary, or bug reports about undocumented features or unofficial plugins)
• create a new issue with the latest information, including updated version details with error messages, failing tests, and a link back to the original issue. This allows GitHub to automatically create a back-reference for future visitors arriving from search engines.

Thanks so much for your help!

[devinivy]

Hey @kevinburke @kevinburkeshyp,
I saw three great feature requests of yours were closed over night by the bot. Do you mind making a ROADMAP.md file in this repo with #198, #195, and this issue added? For an example, see the waterline roadmap: balderdashy/waterline@e40dd02

If you don't want to do this or don't have time, let me know here and I'll reopen those issues until they are placed in a roadmap file.

[particlebanana]

@kevinburkeshyp @devinivy I went ahead and did this so we don't lose them https://github.com/balderdashy/sails-postgresql/blob/master/ROADMAP.md

[mikermcneil]

For posterity: you can check out more info on where this stands in the conversation here: https://gitter.im/balderdashy/sails?at=5812a124806316005dc9e6fb