Is it possible to handle cross-schema references while generating?

[Ghawaneda]

What do you want to change?

Hi,

I am currently facing a challenge in generating SQL that involves cross-schema references.

I'm working with two distinct schemas, and in some scenarios, I need to generate SQL that performs a JOIN operation across these schemas. Currently, SQLC throws an error that says "schema not found."

This issue seems to indicate that SQLC doesn't maintain the state of multiple schemas or "remember" them between different sections. As a result, referencing another schema in a JOIN operation results in an error.

I've considered several workarounds such as:

1. Aggregating all SQL files into a single list under one schema.
2. Merging all schemas into a single schema.
3. Repeating the necessary parts of the schema definition within the required entry.

However, these solutions are not optimal as they either compromise the modular structure of my schemas or result in redundancy.

A direct support for handling cross-schema references would greatly enhance SQLC's ability to work with complex database structures. This feature could potentially include:

• Tracking state across multiple schemas.
• Providing a syntax or mechanism to specify which schema a particular table or column belongs to.

I hope you will consider this feature for an upcoming release. This would greatly improve the flexibility and comprehensiveness of SQLC, enabling it to handle a wider range of database architectures.

I greatly appreciate your work on SQLC, and I find this feature very important.

What database engines need to be changed?

No response

What programming language backends need to be changed?

No response

[Ghawaneda]

@kyleconroy, could you please explain why this particular feature is not yet supported in SQLC? Additionally, I would appreciate guidance on how to handle situations where I have a large number of schemas (even 2) and need to work with them?

I find it very important feature to work with different schemas.