Optional create and update parameters with sqlc

July 26th, 2022

sqlc is nice. I’m never going back to ORMs. It feels right that Postgres interfaces are defined in .sql and not a DSL. One of the unexpected benefits: all my DML is “type checked” against the schema, so if one of my queries references a deleted column, sqlc refuses to compile.

That being said, I still want to be able to have optional parameters for my create and update API endpoints and utilize JSON struct tags (instead of needing to write that code myself). This was a problem mentioned by brandur’s sqlc post.¹ (Also, stripe mafia strikes again!)

The recent addition of actually opens up this possibility. My initial approach had columns with NOT NULL constraints should ideally have a go zero value default. For updates, I could COALESCE(sqlc.narg(argname), colname). For inserts, you’d like to use the same coalesce trick: INSERT INTO t(colname) VALUES (COALESCE(sqlc.narg(argname), DEFAULT)). The problem is for creates, you can’t use the Postgres keyword DEFAULT in expressions.

Let’s take this simple schema as an example:

CREATE TYPE color AS ENUM(
    'red',
    'white',
    'blue',
);

CREATE TABLE users (
    id serial primary key,
    created timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    name text NOT NULL,
    favorite_color color NOT NULL DEFAULT 'red'
);

DEFAULTs on columns are not useful for data insertion, but rather backward compatibility when adding not null columns. Instead, creation defaults should be defined in the INSERT statement.

-- name: CreateUser :one
INSERT INTO users(name, favorite_color)
VALUES (@name, coalesce(sqlc.narg(favorite_color), 'red'));

-- name: UpdateUser :one
UPDATE users
SET 
    name = coalesce(sqlc.narg(name), name),
    favorite_color = coalesce(sqlc.narg(favorite_color), favorite_color)
WHERE id = @id

The final question is dealing with JSON, which is used at the API layer instead of the database model layer. The default sqlc type for nullable values is go’s sql.NullString, which doesn’t have JSON marshalling an unmarshalling interfaces. Here, I created my own NullString (or in this case NullColor) type and used sqlc config overrides to match it. This is especially necessary because sqlc cannot match sqlc.narg parameters to a nullable version for enums.

type NullColor struct {
    sql.NullString
}

func (n *NullColor) MarshalJSON() ([]byte, error) { ... }
func (n *NullColor) UnmarshalJSON(b []byte) error { ... }

overrides:
  - db_type: "pg_catalog.int8"
    nullable: true
    go_type:
      type: "NullInt64"
  - db_type: "color"
    nullable: true
    go_type:
      type: "NullColor"

Feature requests

My one feature request for sqlc is variables. Given the choice, I’d have the syntax look like a mix of plpgsql variable declarations and psql session parameters. Similar to SASS variables, it’s feels cleaner when you want to reuse constants.

Taking from the above user table example:

default_color text := 'red';

CREATE TABLE users(
    ...
    favorite_color color NOT NULL DEFAULT :default_color
)

INSERT INTO users(name, favorite_color) VALUES (@name, coalesce(sqlc.narg(favorite_color), :default_color));

UPDATE users SET favorite_color = coalesce(sqlc.narg(favorite_color), favorite_color);

Finally, for the sake of brevity, it would be nice if instead of having to coalesce(sqlc.narg(favorite_color), :default_color), you could just provide a second argument like this: sqlc.narg(favorite_color, :default_color). This feels more like syntatic sugar, but might guide others towards their own “optional parameters” solution.