Objection.js: "Batch" inserts on non-postgres connections?

[ackerdev]

Why is it that QueryBuilder.prototype.insert throws an error when the input is an array on non-postgres connections, while insertGraph implements a workaround to allow batch inserts regardless of connection type? Why doesn't insert work the same way, inserting one-by-one to work around the implementation issues while providing a consistent interface to users?

I understand there is a performance loss on these other SQL implementations, but the alternatives are Promise.all(severalInsertQueries) or dropping to a knexQuery and losing the benefits of Objection's querybuilder, neither of which seem to be any better than if insert handled things internally.

[devinivy]

We already chatted a bit on gitter about this, but for posterity–

My personal perspective on this is that I'd like to see anything that has non-transparent perf or transactional characteristics to not leak into more fundamental parts of the API such as insert(). The graphInsert() and eager() methods in the API are very transparent about the fact that they perform bulk queries– and they build upon fundamental methods in the API, such as insert(), that do not have any perf/transactional magic going on.

That said, I wouldn't mind seeing the community provide recipes/modules for extending the QueryBuilder with features such as this!

[koskimas]

You can use insertGraph to insert the array. insertGraph makes it clear that multiple queries are executed while insert doesn't imply that. Why can't you use insertGraph for this? I totally agree with @devinivy.

[ackerdev]

insertGraph comes with it's own concerns; mistakenly omitting allowInsert creates a security flaw. I'd always choose Promise.all(insertQueries) in comparison, just to avoid the potential for a contributor opening holes with some sloppy copy-pasting.

Also I have to say I don't really understand the implication that insertGraph is somehow more semantically apparent in it's behavior here. Yes, it's apparent that a graph containing one model with a one-to-one relation will result in two queries; but I don't think it's inherently obvious that a graph containing one model with n one-to-many relations will result n+1 queries. For someone familiar with SQL, looking at the queries in debug would raise eyebrows all the same if they didn't read the documentation explaining that limitation.

I think either way if you don't understand the implications of how batch inserting works with different SQL implementations, you run into a gotcha. Either you run into performance concerns, or you run into inconsistent API.

And while not at all a common situation, if one were to change SQL implementations under the hood, their application can break unexpectedly because the abstraction was inconsistent.

[koskimas]

Good point about allowInsert. I didn't think of that.

Also I have to say I don't really understand the implication that insertGraph is somehow more semantically apparent in it's behavior here.

I think it's more apparent if you think of objection as a query builder: insert call compiles into a INSERT INTO statement and nothing more. That's what objection really is: a query builder and not an ORM in the traditional sense. I think it's clear that insertGraph is not just a query builder method since there is no such keyword in SQL.

And while not at all a common situation, if one were to change SQL implementations under the hood, their application can break unexpectedly because the abstraction was inconsistent.

Yeah, this really isn't something to consider. I have never seen a project that doesn't start using database specific stuff after the first week. If one's app is so trivial that this can be done, then objection probably isn't the right tool. 100% compatibility between database engines has never been a goal for objection. If you want this, use hibernate :trollface:

but I don't think it's inherently obvious that a graph containing one model with n one-to-many relations will result n+1 queries.

This is beside the point, but actually this results in two queries instead of n + 1 on postgresql.

Having said all this, I do understand your concern about the inconsistent API. I'm ready to change my mind about all this if other people agree with you. So everyone, vote!

[ackerdev]

I think it's more apparent if you think of objection as a query builder: insert call compiles into a INSERT INTO statement and nothing more. That's what objection really is: a query builder and not an ORM in the traditional sense.

Well, if that was literally true, then this functionality wouldn't make sense because these SQL implementations do support multiple value lists per insert, the thing they don't support is returning all inserted IDs which is a requirement of the ORM to do ORM-y things 😛

I have never seen a project that doesn't start using database specific stuff after the first week.

Well, I did say it's not common; though I will also say that non-Postgres DBs don't really have a lot of particularly attractive features that are commonly used at the query-level. This kind of thing also matters for plugin developers because they also need to perform a check on the implementation being used to determine which type of insert to do, otherwise they may unexpectedly lock their plugin to being used only in Postgres environments.

this results in two queries instead of n + 1 on postgresql.

Right; my point was about that not being the case on non-postgres SQL implementations though. Anyone used to writing SQL queries in those implementations is still going to find that behavior bizarre if they dig into the debug and haven't read the explanation on why this is done.

I'll also say that the performance difference is pretty negligible on the low end of the scale which I imagine is the most common usecase here. I don't exactly expect people looking for high performance mass inserts to be wanting to do so through an ORM.

[ackerdev]

I'd like to add a note that if there is an expectation that users (and plugin developers) should be able to override objection to behave in this way, the way of doing so currently is pretty awkward, as it requires overriding behavior of InsertOperation instances in QueryBuilder.

[koskimas]

I'm starting to agree with @ackerdev. It's less hacky if the insert method works the same for all database engines. I'll start working on this now.

@ackerdev is also right about the plugins. There is no way to modify basic stuff like insert on a lower level.

[koskimas]

Dammit! This simply doesn't fit the current architecture. I've tried fixing this every which way and I always end up with a hack. Objection just doesn't easily support batch operations at the moment. Maybe this will become easier to implement later, after refactoring for 2.0 or something, but for now, I'll just leave this one alone.

[koskimas]

Since this hasn't attracted much attention from other people, I'll close this for now. (the issues are starting to pile up)

[marufmax]

Any update regarding this issue? I want batch insert for MySQL database

[mroushdy]

Any updates on this? Or is promise.all almost as fast as a bulk insert?