A while back, I was able to convince my team to use sqlc.

I already used it on some personal projects, but those weren’t complex queries whatsoever. Using sqlc in a commercial setting allowed me to see some of its limitations, or perhaps the shift in the paradigm that you experience while using it.

In this article, I want to show you some hacks I found to be useful.

Configuring the project

First things first, if you have a lot of packages, here’s a cool trick on how to make sure similar config is reused accross different queries: 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

---
version: "2"
sql:
  - engine: postgresql
    queries: internal/account/repository/query.sql
    schema: &schema db/postgres/migrations
    gen:
      go: &go_gen_opts
        package: repository
        out: internal/account/repository
        emit_enum_valid_method: true
        emit_all_enum_values: true
        emit_sql_as_comment: true
        omit_unused_structs: true
        overrides:
          # numeric is decimal.Decimal
          - db_type: pg_catalog.numeric
            go_type:
              import: github.com/shopspring/decimal
              type: Decimal
          - db_type: pg_catalog.numeric
            go_type:
              import: github.com/shopspring/decimal
              type: Decimal
            nullable: true # nullable decimal is valid too, need to add it separately
  - engine: postgresql
    queries: internal/order/repository/query.sql
    schema: *schema
    gen:
      go:
        <<: *go_gen_opts
        out: internal/order/repository
        overrides:
          - column: "order.currency"
            go_type:
              import: "gitlab.com/letssay/our/custom/moneylib"
              type: "Currency"

As you can see on line 8, we are using yaml anchors to reuse the schema configuration. This is a neat little trick, which is actually vanilla yaml.

We are also going to employ the same trick to streamline go gen options config. Say we are are writing something that works with money. Using type overrides we can save ourselves from writing useless mappers. Instead, sqlc will automatically map db types and generate type safe code for us.

We needn’t worry about out: being different, because we are overriding it on line 32 (in each gen.go.out entry).

Notice

Make sure your type implements appropriate interfaces.

And you can also add some more overrides for custom types for specific domain (custom currency type or another enum).

For example if you use go-enum, you could do something like this.

1
2
3
4

//go:generate go run github.com/abice/[email protected] -f=$GOFILE -f=$GOFILE --lower --sql --marshal

//ENUM(usd, eur)
type Currency string
Notice

--sql flag is used to generate sql Scanner/Valuer compatible enums.

There are also some emit_* and omit_* config flags I find to be convenient. You can see the full list here in config .

Tip

You can also use the whole folder as queries param as such:

queries: internal/account/repository/

This way you can have multiple files with queries in one folder, and sqlc will generate code for all of them. It may help with keeping your codebase clean and organized.

Queries

Dynamic update params

Sometimes we need to write a query that is a bit more complex. For example, we need to apply some filters dynamically. There is a great article that describes it in more detail.

TL;DR: You can use @param::boolean to conditionally apply filters.

1
2
3
4
5
6
7

-- name: UpdateOrder :one
UPDATE order
SET status = CASE WHEN @status_do_update::boolean THEN @status ELSE status END,
    amount = CASE WHEN @amount_do_update::boolean THEN @amount ELSE amount END,
    updated_at = @updated_at
WHERE id = @id
RETURNING *;

This might look like this in repository layer:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

/*...*/
upd := UpdateOrderParams{
    OrderID: params.OrderID.ToUUID(),
}

if params.Status != nil && *params.Status != "" {
    upd.StatusDoUpdate = true
    upd.Status = *params.Status
}

if params.Amount != nil {
    upd.AmountDoUpdate = true
    upd.Amount = *params.Amount
}

dbOrder, err := r.queries.UpdateOrder(ctx, upd)
if err != nil {
    return Order{}, fmt.Errorf("updating order: %w", err)
}
/*...*/

Dynamic where clause

1
2
3
4
5
6
7
8

-- name: ListOrders :many
SELECT o.*
FROM orders o
WHERE 
    -- optional dynamic filters
    (array_length(@id::uuid[], 1) IS NULL OR o.id = ANY(@id::uuid[]))
    AND (array_length(@statuses::text[], 1) IS NULL OR o.status = ANY(@statuses::text[]))
ORDER BY o.created_at DESC;
  • array_length(@param, 1) checks the first dimension of the array @param. Our array is always 1-dimensional anyway.
  • If the array is NULL or empty, array_length returns NULL and the second part after OR is ignored.
  • This ensures the condition on the right param = ANY(@param) is omitted completely when the parameter is not provided.

This pattern allows you to write queries where filters can be dynamically applied only when needed.

Left join with embed

There is a known issue open at the time of writing: sqlc.embed() generates code that cannot handle NULL items.

Let’s say we have a query that joins two tables and we want to embed the result in a struct. We can use sqlc.embed() to generate the code for us that will reuse the same structure.

1
2
3
4
5

-- name: ListAccountsAndOrders :many
SELECT sqlc.embed(a), sqlc.embed(o)
FROM accounts a
LEFT JOIN orders o ON a.id = o.account_id
ORDER BY a.created_at DESC;

This won’t work ^-^

But there is a known workaround. We can just create a view that will guarantee to have ID fields, and the rest of those can be NULL.

Migration:

1
2
3
4
5
6

CREATE VIEW account_orders AS
SELECT
    a.id AS account_id, -- always not null
    o.*
FROM accounts a
LEFT JOIN orders o ON o.account_id = a.id;

query.sql:

1
2
3
4

SELECT sqlc.embed(a), sqlc.embed(o)
FROM accounts a
JOIN account_orders o ON a.id = o.account_id
ORDER BY a.created_at DESC;

Read also

Heavily inspired by: