Proposal: prepare_query for write operations


This is a question about Ecto’s prepare_query/3 callback. Specifically, on the fact that it only works for the “Query API” (all, get, one, aggregate and friends).

I can’t find any detailed information on why it doesn’t support the “Schema API”, which mostly contains write operations.

I’ve found a couple of GitHub issues (issues/3608 and issues/3702) where this was briefly discussed, but nothing was said on the why. I’ve also found this old topic here on the forum, which however didn’t receive any replies.

At the moment the Query API contains mostly read operations, with two exceptions: delete_all/2 and update_all/3, which support prepare_query/3.
At the same time, the Schema API contains delete/2 and update/2, which do not support prepare_query/3.

I suspect that the reason is that the Query API works on free-form queries composed with the Ecto DSL, where as the Schema API works on concrete structs that represent a DB row (that is, a schema). I can see the point that allowing applications to modify SQL that is intended to work on a predefined struct could be confusing. If the API promises that you’ll be inserting data for a struct, then if that SQL gets modified on the fly that promise is broken. This my just my guess: I couldn’t find it documented or explained anywhere.

And yet, I would argue that this is an inconsistency that we don’t need. Following the same line of reasoning, the Query API promises the developer that a certain query will be performed, but that query could be modified on the fly by the application using prepare_query/3. That’s also a promise being broken, and that’s fine. It’s a useful feature.

So, my questions are: is there is any reason why we can’t have an API to prepare queries for write operations? Can it be added?


Hey there, I’m bumping this because I think it’s still relevant to something I’m working on.

I believe this can be solved by using the :autogenerate option for your field in your schema Ecto.Schema — Ecto v3.9.4.

And I think it derverse to be mentioned in the guide: Multi tenancy with foreign keys — Ecto v3.9.4

defmodule MyApp.Post do
  use Ecto.Schema

  schema "posts" do
    field :title
    field :org_id, :integer, autogenerate: {MyApp.Repo, :get_org_id, []}
    has_many :comments, MyApp.Comment