Inserting and order of has_many associated records

[silverdr]

I have a parent entity, which has_many children subentities:

has_many :children, PhxApp.Parent.Child, on_replace: :delete

Children are stored along with the parent and come from inputs_for equipped HTML form. Storing the parent record inserts all the children one by one, in the order they come from the form. This allows me to simply ORDER BY children ids on retrieval / preload to have them correctly (in the order they were created in the form) ordered.

But… sending potentially dozens of INSERTs to store associated records seems “suboptimal”. So the question is: am I doing something wrong that Ecto issues new INSERT for each associated record? I build a regular changeset where I cast_assoc(:children) giving it a list of children’s changesets. Something like here (simplified):

def changeset(parent, attrs \\ %{}) do
	parent
	|> cast(attrs, [:identifier, :notes, ...])
	|> cast_assoc(:children)
	|> <some validations follow here>
end

[al2o3cr]

@silverdr

So the question is: am I doing something wrong that Ecto issues new INSERT for each associated record?

This is the standard approach; multiple INSERTs are preferable for both Ecto reasons (insert_all doesn’t fill autogenerate columns, for instance) and for database reasons (IIRC precisely identifying constraint errors on a multiple-row insert are tricky)

[silverdr]

@al2o3cr

This is the standard approach

I see. I was kind of expecting it to use a single INSERT but now that you wrote it I recall other frameworks also using separate INSERTs in equivalent situations (and me wondering about it ). So there’s probably something on the DB level as you mention. Even though my gut feeling is that there shouldn’t be a problem. And it generally feels like a “reverse” N+1. But maybe it’s the lowest denominator across multiple supported RDBMSs. TNX.

[LostKobrakai]

@silverdr

I was kind of expecting it to use a single INSERT but now that you wrote it I recall other frameworks also using separate INSERTs in equivalent situations

There won’t be individual constraint errors on a insert inserting many records. You’ll just know something cause the issue, but not which record.

[silverdr]

@LostKobrakai

There won’t be individual constraint errors on a insert inserting many records

True that. Yet I can imagine situations where I would gladly trade that off.

Anyway, since this is “standard approach”, rather than me doing something wrong, then I’ll start bothering about optimising it once I run into N > {a reasonable number}. TNX guys.

P. S. I thought about the lowest common denominator because I recall that the multiple rows insert construct wasn’t “always” present/supported across all RDBMSs/SQL variants/extensions. I remember being positively surprised once I learnt about it being supported in (don’t remember anymore which RDBMS) some good years ago. So it theoretically could be that something out of the Ecto supported ones doesn’t allow that at all? Although as of today chances for that are pretty slim I guess.