Description

K-odesome

opened on Feb 7, 2024

Issue body actions

What do you want to change?

Usecase is to have seperate set of queries generated for read access and write access.
For example if I have read user of Mysql , I will not be able to prepare the update , insert statement from it because sqlc generates everything in one compiled list of queries.

The solution is to generate two separate prepared folder but then it would result in lot of code duplication of models also getting generated.

Can we have a solution where we can get the objects in separate set of queries which could be used to prepare from corresponding db users.

Current Behaviour :

-- name: SelectTable
Select * from table;

-- name: InsertTable
Insert into Table values (1,2,3);

output generated from it

type Queries struct {
	db                                DBTX
	tx                                *sql.Tx
	selectTable  *sql.Stmt
	insertTable      *sql.Stmt
}

Now I cant use this Queries struct with my read db user and it fails during the prepare method call becase the queries have both select and insert queries.

Feature request : On generate command it should generate something like

type ReadQueries struct {
	selectTable  *sql.Stmt
}
type WriteQueries struct {
	insertTable  *sql.Stmt
}

and these corresponding queries can be used with corresponding mysql db users context while preparing.

What database engines need to be changed?

PostgreSQL, MySQL, SQLite

What programming language backends need to be changed?

Go

Reacted by Jarri Abidi, jethwaniamit, Muhammad Younus, Usman Ashraf, yarik-careem, Muhammad Hammad, dan-pulley, Wojtek Siudzinski and Jialun Cai

Reacted by Jarri Abidi and Wojtek Siudzinski

Activity

1. K-odesome

   added

   enhancementNew feature or requestNew feature or request

   triageNew issues that hasn't been reviewedNew issues that hasn't been reviewed

   on Feb 7, 2024
2. kyleconroy

   removed

   triageNew issues that hasn't been reviewedNew issues that hasn't been reviewed

   on Mar 4, 2024
3. kyleconroy commented on Mar 4, 2024

   

   kyleconroy

   on Mar 4, 2024

   Collaborator

   More actions

   It's an interesting idea. We've kicked around generating different interfaces for reader and writers, but that wouldn't solve your issue as you're preparing the queries before hand. The best I can offer right now is moving the read-only queries into a separate query file and create two different packages.
4. K-odesome commented on Mar 4, 2024

   

   K-odesome

   on Mar 4, 2024

   Author

   More actions

   @kyleconroy , we currently thought of doing what you suggested , but It is resulting in lot of code duplication in terms of models that we being generated.

   Reacted by Kyle Gray
5. jarri-abidi commented on Mar 12, 2024

   

   jarri-abidi

   on Mar 12, 2024

   More actions

   suggestion for slight improvement:

   type ReadQueries struct {
   	...
   	selectTable  *sql.Stmt
   }
   
   type Queries struct {
   	...
   	selectTable  *sql.Stmt
   	insertTable  *sql.Stmt
   }
   

   this would maintain backwards compatibility and can be treated as an optional flag in the config to generate read-only queries

   Reacted by jethwaniamit, yarik-careem, Muhammad Hammad, Waseem, Ahmad Nabas and Abdullah Asad
6. jarri-abidi commented on Mar 26, 2024

   

   jarri-abidi

   on Mar 26, 2024

   More actions

   @kyleconroy can we merge PR #3291 to support this?
7. dan-pulley commented on Oct 10, 2024

   

   dan-pulley

   on Oct 10, 2024

   More actions

   @kyleconroy our company would highly appreciate this feature.

   The 2 main reasons:

   • It allows us to use a readonly connection with its own limits and rules
   • It allows us to skip some expensive setup we do to enrich mutation tracking in our DB

   Please advise

Issue actions