Is there any way to use a dynamic schema prefix inside a fragment?
For example (a meaningless but simple one):
iex> prefix="tenant"
iex> Repo.all((from p in Position, join: f in fragment("SELECT * FROM ?.positions", ^prefix)), prefix: prefix)
** (Postgrex.Error) ERROR 42601 (syntax_error): syntax error at or near "$1"
Ok, Postgres does not support shema prefixes as parameters, this makes sense as this is dynamic SQL.
iex> Repo.all((from p in Position, join: f in fragment("SELECT * FROM #{prefix}.positions", ^prefix)), prefix: prefix)
** (Ecto.Query.CompileError) fragment(...) expects the first argument to be a string for SQL fragments, a keyword list, or an interpolated value, got: `"SELECT * FROM #{prefix}.positions"`
iex> fragment_query = "SELECT * FROM #{prefix}.positions"
iex> Repo.all((from p in Position, join: f in fragment(^fragment_query)), prefix: prefix)
** (ArgumentError) to prevent sql injection, only a keyword list may be interpolated as the first argument to `fragment/1` with the `^` operator, got `"SELECT * FROM tenant.positions"`
Nope, this is not supported by Ecto to avoid SQL injections, cf. #1089.
Any other idea or should I forget about using fragments requiring the schema prefix in a multi-tenant application?
I’ve yet to figure out how to do cross-schema joins in Ecto and I’ve tried a lot. If you manage to figure it out then I’d be ecstatic.
Right now I’ve just had to dump down to doing raw SQL calls when I do that because of that significant Ecto limitation…
Currently if using the main Ecto.Query stuff, every table has to have the same prefix (which you specify with the prefix: option on the actual Repo call).
I’ve yet to figure out how to do cross-schema joins in Ecto and I’ve tried a lot. If you manage to figure it out then I’d be ecstatic.
Unfortunately, I saw your thread about cross schema queries and the answers from @josevalim, but it seems pretty clear that it’s not possible at the moment (unless using a fragment for the other schema).
But in my case the prefix is the same for the whole query, the problem is that it’s a dynamic one. As multi-tenant applications seemed to be the main case for prefix support in Ecto2, I wonder if there is way to let Ecto generate the from prefix.table part inside the fragment.
Also I would prefer to avoid full raw SQL to avoid even more potential SQL injections.
You’re right and it works very well, until you need a SELECT ... FROM inside a fragment.
Let’s take for example this CTE query (or my example in my first post):
Repo.all(MyProj.User,
from u in MyProj.User,
join: un in MyProj.UserNode, on: u.id == un.user_id,
join: nt in fragment("""
(
WITH RECURSIVE node_tree AS (
SELECT *
FROM nodes
WHERE nodes.id = ?
UNION ALL
SELECT n.*
FROM nodes n
INNER JOIN node_tree nt ON nt.parent_id == n.id
)
) SELECT * FROM node_tree
""", ^node_id), on: un.node_id == nt.id
)
If you want to use the same dynamic schema prefix for the whole query, I don’t see a way to do it, do you?
My use case is similar.
But I suppose this is a case extreme enough to justify using Ecto.Adapters.SQL.query, fragments can still be useful in where: clauses.
Currently it is possible without hacks. Example from docs:
Setting the prefix in the query changes the default prefix of all from and join expressions. You can override the query prefix by either setting the @schema_prefix in your schema definitions or by passing the prefix option:
from u in User,
prefix: "accounts",
join: p in assoc(u, :posts),
prefix: "public"
Overall, here is the prefix lookup precedence:
The :prefix option given to from/join has the highest precedence
Then it falls back to the @schema_prefix attribute declared in the schema given to from/join
I know about the prefix option, and use it extensively throughout my app. The problem is that fragment does not allow specifying a prefix. So if you need to refer to dynamic schema or table names (i.e. multi-tenant application where each tenant gets their own Postgres schema) and need to refer to them in your fragment, you’re out of luck, as far as I can tell.
I also ran into this problem when calling a database function via a fragment in a multi-tenant app. Obviously I wanted each schema to have its own function. Alas, I opted to move the function to the public schema and change the function to also accept the prefix and use Postgresql EXECUTE FORMAT() command. While this does not solve your exact problem it may be helpful to those that follow this thread.
fragment("myfunc(?, ?)", ^prefix, ^term)
CREATE OR REPLACE FUNCTION myfunc(prefix VARCHAR, term VARCHAR)
RETURNS TABLE(first_name VARCHAR, last_name VARCHAR) AS $$
BEGIN
RETURN QUERY
EXECUTE FORMAT('
SELECT s.first_name, s.last_name
FROM %s.some_table s
WHERE ...', prefix, term);
END; $$
LANGUAGE 'plpgsql';
