What do you want to change?
Kudos
Before 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.
Background
I 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. We do not currently use sqlc at Bright - but I do use it personally, but I am evaluating it Bright as well.
The 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 open issues, 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).
Say for example, instead of finding a really good GeoJSON library in my target language, I wanted to rely on PostGIS.
- name: WKBToGeoJSON :one
select ST_AsGeoJSON(ST_GeomFromWKB(sqlc.arg('wkb_bytes'))::text as output;
I would love for this Go code to be generated:
func (q *Queries) WKBToGeoJSON(ctx context.Context, db DBTX, wkbBytes []byte) (string, error) {
// ...
}
Or perhaps a more common use case, partial updates
-- name: EditThing :exec
update thing
set
name = coalesce(sqlc.arg('name'), name),
description = coalesce(sqlc.arg('description'), description),
status = coalesce(sqlc.arg('status'), status)
where thing.id = sqlc.arg(id)
-- name, description and status are all nullable arguments, id is not.
Proposal
I think sqlc should expose a way (like overrides) in the query itself to control aspects of the generated code in the target languages.
Allow 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.
-- name: EditThing :exec
update thing
set
name = coalesce(sqlc.arg('new_name', 'nullable', true, 'type', 'string'), name),
description = coalesce(sqlc.arg('description', nullable, true), description),
status = coalesce(sqlc.arg('status', 'nullable', true), status)
where thing.id = sqlc.arg(id)
This 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.
I 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 ?
-- name: GetThingForCpp :one
select
id,
first_name,
last_name,
sqlc.output(concat(first_name, ' ', last_name), 'nullable', false, 'ref_counted', true) as display_name
where ....
This is very similar to overrides, but on a query-by-query basis, and expressed directly in SQL instead of away from the code in a config file.
Implementation
I 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. Performing the source rewrites with an AST "deparse" (like in pg_query_go) 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.
I'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.
What database engines need to be changed?
PostgreSQL, MySQL
What programming language backends need to be changed?
No response
enhancement triage
185 Dec 19, 2022
3.3k Jan 8, 2023
460 Dec 19, 2022
3.2k Jan 5, 2023
653 Dec 27, 2022
1.4k Dec 26, 2022
56 Nov 17, 2022
1.8k Dec 30, 2022
27 Dec 12, 2022
589 Dec 31, 2022
26 Dec 25, 2022
92 Dec 23, 2022
274 Jan 2, 2023
245 Dec 16, 2022
5.4k Jan 6, 2023
2.5k Jan 4, 2023
169 Dec 22, 2022
86 Mar 24, 2022
379 Nov 29, 2022