Query options for unsafe_validate_unique/4

Hey guys, I ran into a slight issue today:

I’m currently building a multi-tenant setup using foreign keys, similar to the docs.
The tenant key is enforced for every query on the repo level using prepare_query (as seen here), which means I have to provide either a tenant key or a skip parameter to my queries.

This doesn’t work, though, when doing validations with unsafe_validate_unique/4 (and maybe others) because it doesn’t let me add options to the query.

I removed the validation for now and will wire up my own check on uniqueness. Still, I was just curious how you guys would solve such problems, as I’m sure other functions also execute queries under the hood where I can’t provide my tenant Infos.

Thanks! :slightly_smiling_face:

I’m not sure how it would interact with the prepare_query plumbing, but making the validation match [:field_that_should_be_unique, :org_id] (where org_id is the tenant FK) seems like it would get the right query.


Thanks, Matt! Yea, the plumbing is actually the issue. In general, the validation works, but the query doesn’t get executed because it needs the org_id added as an option to the query. The options I can provide to unsafe_validate_query/4 are used by that function and not being passed down to the query - unless you do multi-tenancy with prefixes. And this is what I just ended up doing: switched the app from foreign key to prefix-based multi-tenancy.

While the former appealed to me at first by being straightforward to implement, it needs little tweaks and changes all over the place, and sometimes I’d run into a wall, as the example above shows. :man_shrugging:

Thanks again! :slight_smile:

I think, in theory, you have to pass your :skip_person_id to the :repo_opts as per this notation:

  * `:repo_opts` - the options to pass to the `Ecto.Repo` call.

It would appear the :repo_opts are not getting passed down to the prepare_query/3 function. But, I don’t know. :thinking: :blush:

1 Like

Oh, interesting! As part of the changeset… :thinking: I might try this, but to be honest, that was the point when I switched to prefixes :grimacing: Too many things that kept popping up… But I still have the old branch and see if I can get it to work that way. Thank you!

1 Like

Just for reference I ran into the same problem today and found that a fix for this should be coming in the next Ecto release. unsafe_validate_unique/4 has been updated to accept a keyword list of repo_opts.

See the change here: ecto/changeset.ex at 0b990278ca6b418c0137dd0bdf9f0c5003062629 · elixir-ecto/ecto · GitHub


Oh that’s good news, Andy! Thanks for letting us know! :slight_smile:

I ran into the same problem and could fix it based on your post:

unsafe_validate_unique(:email, MyApp.Repo, repo_opts: [skip_org_id: true])

Thx andyjones11!