Problem synopsis⌗

I’m a huge fan of sqlc’s approach to code generation. I’ve seen sqlc called a reverse ORM, and I really like that moniker.

However, as a sqlc newbie, I’m still discovering its edge cases. Today’s edge case is using postgres UPSERT queries with sqlc.

The crux is that when upserting, it’s necessary for unique key conflicts to occur so that the UPDATE portion of the query executes. But non-null uuid Go types tend to have a zero-value of 00000000-0000-0000-0000-000000000000. This results in new records inserting with the uuid Go type’s zero value, and every following new record upserts the existing zero ID record.

This is clearly not the desired behavior.

Brief intro to upserts⌗

If you aren’t familiar with upsert queries, they’re queries that either create or update records. That is, if you supply the unique ID of an existing record, along with some updated metadata, the existing record is updated with the provided metadata. If you only supply the metadata, and no unique ID, a new record is created.

Here’s an example upsert query

INSERT INTO users (id, name, email) 
  VALUES ('b5f29e80-8c4e-4dcf-92e2-5c757f563223', 'Julie', 'jule@example.com') 
ON CONFLICT (id) DO UPDATE 
  SET name = $2, email $3
RETURNING *;

This query either updates the user with id = b5f29e80-8c4e-4dcf-92e2-5c757f563223 if such a record exists, or creates a new user with id=b5f29e80-8c4e-4dcf-92e2-5c757f563223, name='Julie', email='julie@example.com'.

Upserts are a great way to avoid additional database round-trips to check if matching records exist before saving new ones.

Problem details⌗

So let’s look at a concrete example. First, we create the users table with a uuid ID.

CREATE TABLE users (
    id    uuid PRIMARY KEY,
    name  text NOT NULL,
    email text NOT NULL
);

And create a SaveUser query for saving user records. When a conflict happens on the id record, when updating existing users, the UPDATE portion of the query executes.

-- name: SaveUser :one
INSERT INTO users (
    id, name, email
) VALUES (
    $1, $2, $3
) ON CONFLICT (id) DO UPDATE 
  SET name = $2, email = $3
RETURNING *;

This query results in sqlc generating the following Params struct which is used for both updating existing records, and creating new ones.

type SaveUserParams struct {
	ID         uuid.UUID
	Name       string
	Email      string
}

As you can see, the ID field is a uuid.UUID, not *uuid.UUID, so it’s not possible to leave empty when calling SaveUser for new users. Using sqlc overrides we can of course instruct it to generate Go code with *uuid.UUID types, but passing nil would mean our INSERT passes NULL to a non-nullable primary key field, which raises null key constraint violation.

The solution⌗

So how do we perform upserts that account for the zero-value of our types? I’ve opted to create a function that provides default values when our type’s zero-value is encountered. While the below function is explicitly written for uuid zero values, it can just as easily be ported to other types.

CREATE OR REPLACE FUNCTION public.uuid_if_empty(id uuid) RETURNS uuid 
    LANGUAGE plpgsql
    AS $$BEGIN 
        IF id = uuid_nil() THEN 
            RETURN uuid_generate_v4();
        ELSE 
            RETURN id;
        END IF;
    END$$;

Thanks to mariusor on lobste.rs for notifying me about uuid_nil()

Now in our upsert statement, we use our new function instead of the raw value passed in

-- name: SaveUser :one
INSERT INTO users (
    id, name, email
) VALUES (
    -- Note how naming the `id` param changes the numeric value of positional args.
    -- It may be preferable to use the sqlc.arg macro here to improve readability.
    uuid_if_empty(sqlc.arg(id))::uuid, $1, $2 
) ON CONFLICT (id) DO UPDATE 
  SET name = $2, email = $3
RETURNING *;

This turned out to be a very simple solution for my current problem. If there’s a better or more idiomatic way to achieve the same result, please let me know in the comments!