Open RadhiFadlillah opened 1 year ago
Hi @RadhiFadlillah tell me more about your sql generator https://github.com/RadhiFadlillah/dbgen
Oh god, just pretend that doesn't exist :laughing:
I make it back in 2021 when MySQL support in sqlc
is still not really good (IIRC sqlc
still not supported CTE and subquery back then). In one hand, I'm not smart enough to contribute additional MySQL parser to sqlc. In other hand, I'm burned out writing CRUD command one by one. So, I decided to build my own SQL generator, as ugly as it is.
The main objective for that generator was to add support for CTE and subquery. The idea is simple: instead of parsing the SQL manually, we just need to run the SELECT
query in MySQL then read the returned columns using rows.ColumnTypes
function in stdlib.
So, here is how it works:
query.sql
as input, and an empty database that only used for generator.dbgen
run each SELECT
queries in that database, then collects the returned columns using rows.ColumnTypes
function.Fortunately, around October 2021 MySQL supports for sqlc
is already good enough, so I stopped working on it. In 2023, there are no reasons to use that anymore since sqlc
is superior in every aspect.
What do you want to change?
Background
One of the most common feature request for CRUD and BI apps is to make an advanced data filter where the users can freely define the parameters to specify which data to be shown. Example of that data filter can be seen in spreadsheet apps like Excel or Libre Calc:
Currently, the suggested approach for dynamic query is using
OR
operator inWHERE
clause like this:Unfortunately that approach won't work for advanced data filter, because:
AND
andOR
condition? And so on.To solve this issue, I propose we allow additional SQL as parameter for
:one
and:many
queries.Current behavior
For example (playground), let's say I have
query.sql
like this:From this query,
sqlc
will generate following Go code:As you can see, currently our query for
FetchProduct
andFetchProducts
is run immediately bydb.Query
, making it impossible for us to define custom SQL filter.Proposed solution
To allow additional SQL, what if we put the
SELECT
queries inside derived table subquery? Still using thequery.sql
above as source, here is how the generated Go code will look like:With that new
AdditionalSql
parameter, we can apply additional SQL query while fetching data. For example, here is how we will fetch the products using filter in the screenshot above:Pros and cons
The advantages of this method are:
sqlc
users, since theAdditionalSql
parameter is variadic, it means the generated code can be used as it is so there are no need to modify the existing code.sqlc
developers, (AFAIK) the only place we need to modify for this proposal is only in code template and unit tests, so hopefully it should be easy enough to implement.For the cons, honestly I don't have any right now. I haven't make any tests, but I've made a private fork with this feature and use it for several months and so far there are no trouble though. However, I'm not really experienced with databases so I'm sure there are issues that I missed.
Related issues and discussions
364
580
645
975
1062
1908
What database engines need to be changed?
PostgreSQL, MySQL
What programming language backends need to be changed?
Go