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 (@id, @name, @email) ON CONFLICT (id) DO UPDATE 
  SET name = @name, email = @email
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 combine nullif and coalesce to achieve the upsert effect. While the solution below specifically implements upserts for UUID types, the nullif call can be adapted to any type’s zero value.

-- name: SaveUser :one
INSERT INTO users (id, name, email) VALUES (coalesce(nullif(@id, uuid_nil()), uuid_generate_v4()), @name, @email) ON CONFLICT (id) DO UPDATE 
  SET name = @name, email = @email
RETURNING *;

This statement coalesces to uuid_generate_v4() when @id has the UUID zero value, and uses @id as-is otherwise. Using @id when it is not the type’s zero value is what allows the ON CONFLICT portion of the query to activate when we’re updating existing records.

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!