4 ms·
I'm a big fan of the database first code generator approach to talking to an SQL database, so much so that I wrote pggen[1] (not to be confused with pggen[2], a
by ethanpailes 5y ago
I'm a big fan of the database first code generator approach to talking to an SQL database, so much so that I wrote pggen[1] (not to be confused with pggen[2], as far as I can tell a sqlc fork, which I just recently learned about).
I'm a really big partisan of this approach, but I think I'd like to play the devil's advocate here and lay out some of the weaknesses of both a database first approach in general and sqlc in particular.
All database first approaches struggle with SQL metaprogramming when compared with a query builder library or an ORM. For the most part, this isn't an issue. Just writing SQL and using parameters correctly can get you very far, but there are a few times when you really need it. In particular, faceted search and pagination are both most naturally expressed via runtime metaprogramming of the SQL queries that you want to execute.
Another drawback is poor support from the database for this kind of approach. I only really know how postgres does here, and I'm not sure how well other databases expose their queries. When writing one of these tools you have to resort to tricks like creating temporary views in order infer the argument and return types of a query. This is mostly opaque to the user, but results in weird stuff bubbling up to the API like the tool not being able to infer nullability of arguments and return values well and not being able to support stuff like RETURNING in statements. sqlc is pretty brilliant because it works around this by reimplementing the whole parser and type checker for postgres in go, which is awesome, but also a lot of work to maintain and potentially subtlety wrong.
A minor drawback is that you have to retrain your users to write `x = ANY($1)` instead of `x IN ?`. Most ORMs and query builders seem to lean on their metaprogramming abilities to auto-convert array arguments in the host language into tuples. This is terrible and makes it really annoying when you want to actually pass an array into a query with an ORM/query builder, but it's the convention that everyone is used to.
There are some other issues that most of these tools seem to get wrong, but are not impossible in principle to deal with for a database first code generator. The biggest one is correct handling of migrations. Most of these tools, sqlc included, spit out the straight line "obvious" go code that most people would write to scan some data out of a db. They make a struct, then pass each of the field into Scan by reference to get filled in. This works great until you have a query like `SELECT * FROM foos WHERE field = $1` and then run `ALTER TABLE foos ADD COLUMN new_field text`. Now the deployed server is broken and you need to redeploy really fast as soon as you've run migrations. opendoor/pggen handles this, but I'm not aware of other database first code generators that do (though I could definitely have missed one).
Also the article is missing a few more tools in this space. https://github.com/xo/xo https://github.com/xo/xo. https://github.com/gnormal/gnorm https://github.com/gnormal/gnorm.
[1]: https://github.com/opendoor/pggen https://github.com/opendoor/pggen
[2]: https://github.com/jschaf/pggen https://github.com/jschaf/pggen
- conroy 5y agoReally 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; You can see it in action here: https://play.sqlc.dev/p/2ea889b6d14ae7a91afdcdf4eebe7d100408e6c11b627dce6012aee401b4e2d7 https://play.sqlc.dev/p/2ea889b6d14ae7a91afdcdf4eebe7d100408...
- ethanpailes 5y agoThanks 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.
- ethanpailes 5y agoOh, 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).