Skip to content
New issue

Have a question about this project? # for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “#”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? # to your account

Configure search_path #3663

Open
prog8 opened this issue Oct 20, 2024 · 4 comments
Open

Configure search_path #3663

prog8 opened this issue Oct 20, 2024 · 4 comments
Labels
📚 postgresql enhancement New feature or request

Comments

@prog8
Copy link

prog8 commented Oct 20, 2024

What do you want to change?

Today catalog.Catalog has a field called SearchPath. The only place it is configured is hardcoded and is part of catalog initialization here.

It's a common use case to have search_path per connection and then one expects not to use full name like schema.table but just call table explicitly. It'd be good to have ability to configure search_path when sqlc generate is triggered.

What database engines need to be changed?

No response

What programming language backends need to be changed?

No response

@prog8 prog8 added the enhancement New feature or request label Oct 20, 2024
@prog8
Copy link
Author

prog8 commented Oct 21, 2024

Similar situation applies to DefaultSchema.

This later impacts the name of structs that are generated here. I'd like my struct to skip schema name in the struct name and if I change DefaultSchema code generation works nice.

@smainz
Copy link

smainz commented Nov 10, 2024

@prog8 I need some inspiration of how to use the generated code with multiple schema. Can you share, how you are using the code without hard-coding the schema name?

Suppose I am able to generate the code without schema and Install the application not in public, but in "my_schema".
Are you setting the search path in the connect string or dynamically before creating *Queries?

@prog8
Copy link
Author

prog8 commented Nov 11, 2024

@smainz There are in fact 2 things. First of all I added this PR where I let users to configure default_schema parameter in yaml file. Imagine I configure default_schema parameter to name foo (schema name foo). Then I create regular SQL queries. Imagine I have a table called bar. I don't explicitly mention schema name but something like SELECT * from bar. I don't mention full path to table like foo.bar.

Thanks to default_schema parameter introduced in PR SQLC will know how to generate the code. Then in my connection string to Postgres I have to add &search_path=foo. With this connection I can query Postgres using methods generated by SQLC and everything works fine.

Advantages of having default_scehma and search_path are that SQLC don't create strange struct names which contain schema name. In addition to that I can send queries to many schemas name (for any reasons like sharding, scalability, schema versioning, etc.)

@smainz
Copy link

smainz commented Nov 11, 2024

Thank you for the explanation. In my case, I need to run the executable against different schema, so passing &search_path=... works. As I am using tern to generate the DDL, for code generation I am passing public as schema name just for code generation. Your PR helps a lot.

# for free to join this conversation on GitHub. Already have an account? # to comment
Labels
📚 postgresql enhancement New feature or request
Projects
None yet
Development

No branches or pull requests

3 participants
@prog8 @smainz and others