2023/03/07/reasons-against-sqlc/

Hello, I have something to say from my experience of using sqlc for almost 2 years.

Can confirm for refactoring, this is a real pain. Only tests and/or a good IDE can help you with it.

low-level SQL abstractions ... Thus, get prepared for some mapping back and forth

If you build your application around generated models, you are perfectly good with it. Some sqlc options for generated data will give you some help, such as emit_json_tags or json_tags_case_style. Also, you can use rename option to get more consistent names across generated models. Do it once and we are good for the rest of the project lifetime. Of course this approach have some limitations, there is no recipe to fit for all.

If you have such dynamic queries and still want to use sqlc, you have to accept that one big dynamic query would turn into multiple smaller ones that you have to stitch together manually. Either that, or you’d have to end up with numerous, almost identical versions of the same SQL you’d assign to different functions.

THE WHOLE POWER OF THE RAW SQL IN YOUR HANDS! There are some tricks that helps you to avoid to write numerous, almost identical versions of the same SQL:

First one is this https://docs.sqlc.dev/en/latest/howto/named_parameters.html#nullable-parameters. "Nullable parameters" will help you to write one UPDATE query and dynamically choice which field you want to update;

Second one is the using CASE ... WHEN ... END approach to dynamically modify WHERE part of the query. For example:

-- name: GetData :many
SELECT *
FROM table t
WHERE CASE WHEN LENGTH(@field::text) != 0 THEN t.field = @field::text ELSE TRUE END
  AND CASE WHEN LENGTH(@field2::text) != 0 THEN t.field2 = @field2::text ELSE TRUE END
ORDER BY t.id
LIMIT @lim::int OFFSET @off::int;

From the Go standpoint, sqlc will generate GetDataParam struct like that:

type GetDataParam struct {
    Field string
    Field2 string
    Off int32
    Lim int32
}

Due to the nature of the golang, default values for string is the ""(empty string), so if application does not pass any values to it we've got(internally):

WHERE TRUE AND TRUE

For the "limits", application should pass default values if nothing was sent by a client.

All in all, there are some pros and cons for the using sqlc, but between ORM and raw sql, I definitely choose the raw sql.

preslavrachev / preslav.me-comments

2023/03/07/reasons-against-sqlc/ #19

Things to Consider When Going With sqlc · Preslav Rachev