René's URL Explorer Experiment

{"@context":"https://schema.org","@type":"DiscussionForumPosting","headline":"RFC: nullability (and other metadata) for input arguments / parameters to a query ","articleBody":"### What do you want to change?\n\n**Kudos**\r\nBefore I start, I wanted to give a huge kudos for sqlc - I love it. As a backend developer who knows SQL very well, I fight every single ORM I have used trying to do any SQL more complicated than CRUD.  Thank you for creating this.\r\n\r\n**Background**\r\nI use sqlc alongside Postgres, as sort of a foreign function interface to SQL.  I manage a PostgreSQL database with about 100 different tables and working in Typescript and Go to support every stage of solar operations for [Bright](https://www.ycombinator.com/companies/bright).  We do not currently use sqlc at Bright - but I do use it personally, but I am evaluating it Bright as well.\r\n\r\nThe types of problems I work on are not always CRUD-y (though sqlc works really well if they are).  And the challenge I am facing is that SQL is a little less-expressive than the target languages in some cases, and therefore using sqlc as a FFI is a little clunky.  A quick glance through the [some](https://github.com/kyleconroy/sqlc/issues/1446) [open](https://github.com/kyleconroy/sqlc/pull/1491) [issues](https://github.com/kyleconroy/sqlc/pull/1462), shows that \"nullability\" is one of those deficiencies.  sqlc does its best to always \"do the right thing\" and that has gotten it very far indeed. But I think a simple change can give developers full control, _and_ allow for sqlc to keep getting smarter (better interred mappings / types).\r\n\r\nSay for example, instead of finding a really good GeoJSON library in my target language, I wanted to rely on PostGIS.\r\n```sql\r\n- name: WKBToGeoJSON :one\r\nselect ST_AsGeoJSON(ST_GeomFromWKB(sqlc.arg('wkb_bytes'))::text as output;\r\n```\r\n\r\nI would love for this Go code to be generated:\r\n```Go\r\nfunc (q *Queries) WKBToGeoJSON(ctx context.Context, db DBTX, wkbBytes []byte) (string, error) {\r\n    // ...\r\n}\r\n```\r\n\r\n**Or perhaps a more common use case, partial updates**\r\n```sql\r\n-- name: EditThing :exec\r\nupdate thing\r\nset\r\n  name = coalesce(sqlc.arg('name'), name),\r\n  description = coalesce(sqlc.arg('description'), description),\r\n  status = coalesce(sqlc.arg('status'), status)\r\nwhere thing.id = sqlc.arg(id)\r\n\r\n-- name, description and status are all nullable arguments, id is not.\r\n```\r\n\r\n**Proposal**\r\nI think sqlc should expose a way (like overrides) _in the query itself_ to control aspects of the generated code in the target languages.  \r\n\r\nAllow `sqlc.arg(name)` to accept more variadic arguments (similar to jsonb_build_object) to build up string key, and json value pairs of \"configuration\" for that arguments.  I think sqlc itself should not really know too much about the semantics of these key/value pairs, and just pass them along to the code generators in the target language.\r\n\r\n```sql\r\n-- name: EditThing :exec\r\nupdate thing\r\nset\r\n  name = coalesce(sqlc.arg('new_name', 'nullable', true, 'type', 'string'), name),\r\n  description = coalesce(sqlc.arg('description', nullable, true), description),\r\n  status = coalesce(sqlc.arg('status', 'nullable', true), status)\r\nwhere thing.id = sqlc.arg(id)\r\n```\r\n\r\nThis also allows the developer to control the API interface much better, even if the database is a little wacky.  Imagine if there was a column `name` that was nullable for historical reasons, but any new insert to `name` it should be non null.  sqlc should support that use case too.\r\n\r\nI am not a super-polyglot, but if there were a C++ backend for sqlc, you probably wouldn't just want `nullable` but also maybe an option for the type of pointer that should be generated raw pointer? std::shared_ptr? std::unique_ptr? boost::? std::optional ?\r\n\r\n```sql\r\n-- name: GetThingForCpp :one\r\nselect\r\n  id,\r\n  first_name,\r\n  last_name,\r\n  sqlc.output(concat(first_name, ' ', last_name), 'nullable', false, 'ref_counted', true) as display_name\r\nwhere ....\r\n```\r\n\r\nThis is very similar to [overrides](https://docs.sqlc.dev/en/stable/reference/config.html?highlight=overrides#type-overrides), but on a query-by-query basis, and expressed directly in SQL instead of away from the code in a config file.\r\n\r\n**Implementation**\r\nI got pretty far along in the implementation, and I'd be happy to push it all the way through - but I hit a big snag at the SQL rewriting. Rewriting the AST is no big deal (and I had the code working for that). But the way in which sqlc rewrites the original source seems to be engine independent, and a [little hacky](https://github.com/kyleconroy/sqlc/blob/a83dba243990ae2c9e13665af3ed525960ea767a/internal/sql/rewrite/parameters.go#L82-L99).  Performing the source rewrites with an AST \"deparse\" (like in [pg_query_go](https://github.com/pganalyze/pg_query_go/blob/main/pg_query.go#L36)) would solve this with almost no problem.  Or if the AST has more source location info in it (not just a start location, but also an end location), that might work too. But this seemed like a bigger refactor, and I wanted to get feedback on it first.\r\n\r\nI'd love to know your thoughts @kyleconroy and from the general sqlc community too.  I'd be happy to help and make this a reality.\n\n### What database engines need to be changed?\n\nPostgreSQL, MySQL\n\n### What programming language backends need to be changed?\n\n_No response_","author":{"url":"https://github.com/skabbes","@type":"Person","name":"skabbes"},"datePublished":"2022-04-02T16:27:11.000Z","interactionStatistic":{"@type":"InteractionCounter","interactionType":"https://schema.org/CommentAction","userInteractionCount":15},"url":"https://github.com/1525/sqlc/issues/1525"}