---
date: 2024-03-07
cover: https://sqlc.dev/logo.png
title: Upsert UUIDs with sqlc
comments:
  host: indieweb.social
  username: adriano
  id: 112055381013904491
---

## Problem synopsis 

I'm a huge fan of [sqlc's](https://github.com/sqlc-dev/sqlc) approach to code generation. I've seen `sqlc` called a reverse [ORM](https://en.wikipedia.org/wiki/Object%E2%80%93relational_mapping), 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](https://wiki.postgresql.org/wiki/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

```sql
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.

```sql
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.

```sql
-- 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.

```go 
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. 


```sql
-- 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!
