Really appreciate the in-depth comment. Also, thanks for open-sourcing pggen. I love have more projects in this space. I'll have to take a look at how it works.
I did want to address the last point in your comment.
> This works great until you have a query like `SELECT * FROM foos WHERE field = $1`
When sqlc sees a query with a *, it rewrites the query in the generated code to have explicit column references. For example, if you have an authors table with three columns (id, name, bio), the following query:
SELECT * FROM authors WHERE name = $1;
will have the * replaced in the final output.
SELECT id, name, bio FROM authors WHERE name = $1;
Thanks for the correction! When I read the generated code, I just focused on the scan call. Since pggen doesn't parse the SQL, there is no great way to detect `SELECT ` and rewrite the query, so I hadn't considered this as a possibility. Parsing the SQL really opens up a lot of cool possibilities for you.
You already know this but in case anyone else is reading, another super cool thing that sqlc can do is infer good names for query arguments in go code by looking at what they are compared to in the SQL code. Thus for a query like `SELECT FROM foos WHERE created_at > $1`, the generated go wrapper would have a `createdAt` arg instead of having it be named something like `arg1`. Since opendoor/pggen doesn't parse the SQL, you need to explicitly override the argument names if you want to provide better names. Of course the names won't be perfect with sqlc's approach, but they will be better than `arg1` and it's still a very cool detail. It might not be obvious how neat this is if you haven't had to implement it, which is why I mention it.
Oh, another issue worth mentioning specifically when you are using the jackc/pgx driver is that pgx maintains a prepared statement cache under the hood for performance reasons, but the statements in the cache can be invalidated by query migrations. So if you do `SELECT * FROM foos WHERE field = $1` and then run `ALTER TABLE foos DROP COLUMN bar`, the next time you run that query you will get an error. This used to just be broken until you restarted the process to clear the cache, but I've patched pgx to make it invalidate the cached prepared statement when it sees one of these errors (it still returns the error though since jackc thought a retry would be too complicated). I added an automatic retry to opendoor/pggen to make it so users don't need to worry about this kind of low level detail. You may want to consider that for sqlc as well. The other option is to advise users to disable the cache by setting it's size to zero (I forget the exact config option, but jackc said there is one when I first brought up the issue).
I did want to address the last point in your comment.
> This works great until you have a query like `SELECT * FROM foos WHERE field = $1`
When sqlc sees a query with a *, it rewrites the query in the generated code to have explicit column references. For example, if you have an authors table with three columns (id, name, bio), the following query:
will have the * replaced in the final output. You can see it in action here: https://play.sqlc.dev/p/2ea889b6d14ae7a91afdcdf4eebe7d100408...