Dynamic schema name


I’m trying to dynamically pass a schema name into a function for use in a query. Can’t quite figure out in which format to pass in the schema name.

Pseudo code (not working):

def test(schema_name) do
  Test.Repo.all(from a in schema_name, where: a.name == "ACME")

Have tried various ways of passing the schema_name in, including in the form of a {“source”, Schema} tuple, as discussed here: https://stackoverflow.com/questions/40687186/how-can-you-dynamically-set-a-schema-table-name-for-a-ecto-model

But no luck so far… Any ideas how one could do this?

Thank you!

What’s the error? Works for me, but used like you did gives you a error that says “queries that do not have a schema need to explicitly pass a :select clause in query”.

Also you might need the ^ operator in front of that constant "ACME" string.

Try this:

schema_name = "some_table"
Test.Repo.all(from a in schema_name, where: a.name == ^"ACME", select: a.id)

EDIT: by the way, do you mean Ecto schema (which is actually a table) or PostgreSQL schema (which is a different problem and answer altogether)? I assume it’s the former.

Yes, setting the variable inside the function works (for table names as well as for schema names). It’s also no problem to pass in a table name via the function head parameters.

My problem is passing in an Ecto schema name (not a table name) via the function head.

This works (inside the function):

schema_name = Account
Test.Repo.all(from a in schema_name, where: a.name == "ACME", select: a.id)

This I cannot get to work for a schema:

def test(schema_name) do
  Test.Repo.all(from a in schema_name, where: a.name == "ACME", select: a.id) 

P.S.: I don’t think one needs the pin before “ACME”, that seems to work fine as is also.

Btw, this is the error I get, when trying to pass the schema name in via function head parameter. My schema is called Account:

** (Protocol.UndefinedError) protocol Ecto.Queryable not implemented for Account of type Atom, the given module does not exist. This protocol is implemented for the following type(s): Tuple, BitString, Ecto.SubQuery, Ecto.Query, Atom

Ah, gotcha.

The use Ecto.Schema macro creates a __schema__ function that exposes some introspection details about your schema, and you can use that to find the actual table name as string (https://hexdocs.pm/ecto/Ecto.Schema.html#module-reflection)

def test(schema_name) when is_atom(schema_name) do
  # Get the table name as string
  table_name = schema_name.__schema__(:source)
  Test.Repo.all(from a in table_name, where: a.name == "ACME", select: a.id)

Hope this helps!

1 Like

Sorry for the double post, but using the schema module actually works!

Your problem is using the Account atom, which isn’t probably aliased to its fully qualified name.

You’ll see that if you alias it, or use the fully qualified module name, such as YourApp.Data.Account, it will work.

alias YourApp.Data.Account

# Without the alias above, Ecto does not know what Account is. 
# You might have multiple Account modules in different namespaces.

# This will always work, alias or not

Awesome, thanks a lot, that fixed it, it was the aliasing :slightly_smiling_face:

Great help, really appreciate it!

1 Like