search

sqlc-dev / sqlc

Generate type-safe code from SQL
https://sqlc.dev
MIT License
13.51k stars 810 forks source link

Configure search_path #3663

Open prog8 opened 1 month ago

prog8 commented 1 month ago

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 commented 1 month ago

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 commented 1 week ago

@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 commented 1 week ago

@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 commented 1 week ago

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.