Support for multiple values in INSERT

[kirs]

I'm currently working on speeding up ActiveRecord fixtures. One of the bottlenecks is that we build and execute multiple INSERT queries, while we could do the same with a single query:

INSERT INTO users (id, name) VALUES (1, 'dhh');
INSERT INTO users (id, name) VALUES (2, 'aaron');

vs

INSERT INTO users (id, name) VALUES (1, 'dhh'), (2, 'aaron');

I've implemented that in a PR (https://github.com/rails/rails/pull/26901) but I had to manually build the query: https://github.com/rails/rails/pull/26901/files#diff-694eb08d69d73b367d06242dd0aba454R321

I've been looking into using Arel instead of manual query building, but I found that Arel::InsertManager doesn't support multiple values.

There are multiple gems that implement bulk insert for ActiveRecord: 1, 2. All of them manually build INSERT with multiple values. As you see, my use case is not the only one that could benefit of multiple INSERT support in Arel.

Any thoughts of whether we should add this feature to Arel::InsertManager?

@sgrif

[sgrif]

Any thoughts of whether we should add this feature to Arel::InsertManager?

I'm surprised it's not already there. Yes, we should add this feature to Arel::InsertManager. Let's also add a Nodes::Default singleton if it's not already present, and make sure that the SQLite3 visitor raises if it reaches that node.

[yahonda]

Hi, I'm interested in this pull request and would like to give some comments as Oracle enhanced database adapter maintainer. Oracle database also supports multi insert but its syntax is different from other bundled adapter ones.

Then I'd like to implement database own query builder but do not know if we can implement by inheriting Arel::InsertManager as Arel::Visitors::ToSql allows.

• Multi insert syntax for Oracle database, number of insert value is limited to 999 if database version is older.

INSERT ALL
INTO "SCHEMA_MIGRATIONS" (version) VALUES ('20160101000000')
INTO "SCHEMA_MIGRATIONS" (version) VALUES ('20160102000000')
INTO "SCHEMA_MIGRATIONS" (version) VALUES ('20160103000000')
INTO "SCHEMA_MIGRATIONS" (version) VALUES ('20160104000000')
INTO "SCHEMA_MIGRATIONS" (version) VALUES ('20160105000000')
INTO "SCHEMA_MIGRATIONS" (version) VALUES ('20160106000000')
INTO "SCHEMA_MIGRATIONS" (version) VALUES ('20160107000000')
INTO "SCHEMA_MIGRATIONS" (version) VALUES ('20160108000000')
INTO "SCHEMA_MIGRATIONS" (version) VALUES ('20160109000000')
INTO "SCHEMA_MIGRATIONS" (version) VALUES ('20160110000000')
SELECT * FROM DUAL

Thanks.

[Soleone]

I'm also surprised this isn't supported yet by the framework and seems like a valid use case to want to insert many records at once, e.g. when you have a many to many relationship and want to create many associations at once.

That being said I realize it might not be so straightforward than just doing it on the AREL layer if e.g. multiple validations might have to run on the models as well and the queries batched up before being executed potentially.

TL;DR: :+1:

[kirs]

@Soleone absolutely. Adding support for multiple INSERT to Arel will be the first step to bring this feature as a first class citizen in Rails.

[henrytseng]

+1 Also surprised this isn't a feature yet. Can we also add support for other options such as DELAYED or LOW_PRIORITY?