Dynamic schema selection

[claudioap]

Hello,
I have a software to edit some standard structures. These data structures are 1:1 to SQL tables.
Each different work in the software initializes an empty copy of the said data structures in the database, however, under different schemas.

So, I have one schema to keep track of the projects, metadata et all, and N schemas, each named with a random identifier, which contains the very same data structure.

The question that brings me here is that I'm considering porting the said software to Rust, however, it doesn't seem trivial to do dynamic schema selection in SQLX. SELECT * FROM $1.foo doesn't seem to be legal, and I understand why; because SQLX has no way to learn about types if it doesn't know about the schema at build time.

Can I somehow force SQLX to compile against information from an hardcoded schema and then dynamically select the schema?

Assuming I don't get type checking, how would I do it without opening myself to SQL injections?

Thanks

[OppositeDragon]

Hello, I am facing a very similar issue, were you able to do this with sqlx, if yes, how?
Or did you have to use something different?

[abonander]

Assuming you're talking about Postgres, if you add the schema to search_path then you can reference objects in it (types, tables, etc.) unqualified: https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATH

We don't recommend this unless you know what you're doing because changing the search_path can break things.