TIL — SQLite `UPSERT` with `extends`

Today I was trying setting up a personal project that uses sqlc, a Go tool that generates code based off defined SQL queries. It's not an ORM, but looks and feels kind of like one. Generally I prefer using ORMs, but none of Go's feel great. After some conversations, I wanted to give sqlc a shot.

Setting up sqlc is straight forward. You define a SQL schema, SQL queries, and a config file. I set it up using SQLite. This worked fine, until I wrote an UPSERT query.

-- name: SavePin :one
INSERT INTO pins (
  title, url, image_url, created_at
) VALUES (
  ?, ?, ?, ?
)
ON CONFLICT (url)
DO UPDATE SET title = ?, image_url = ?
RETURNING *;

sqlc happily generated Go code for this query, but at runtime the query failed: query_test.go:43: missing argument with index 5.

I also tried using named params, but to no avail. I created an issue in the GitHub repo for sqlc and dove into the source code to try to figure our what was going on.

The root cause of the bug is that the SQL query expects six params, but only four were getting passed in the generated code:

func (q *Queries) SavePin(ctx context.Context, arg SavePinParams) (Pin, error) {
  row := q.db.QueryRowContext(ctx, savePin,
    arg.Title,
    arg.Url,
    arg.ImageUrl,
    arg.CreatedAt,
  )
  var i Pin
  err := row.Scan(
    &i.ID,
    &i.Title,
    &i.Url,
    &i.ImageUrl,
    &i.CreatedAt,
  )
  return i, err
}

Manually updating the code to pass QueryRowContext the params arg.Title and arg.ImageUrl fixed the runtime error.

I forked the repo and wrote a simple, failing test to help demonstrate the error I saw. Taking a second look through the issues in the repo and I saw that other people had ran into similar issues when using SQLite.

I wrote a simple VS Code launch.json config to debug the code generation:

{
  "configurations": [
  {
    "name": "debug",
    "type": "go",
    "request": "launch",
    "mode": "debug",
    "program": "${workspaceFolder}/cmd/sqlc/main.go",
    "cwd": "${workspaceFolder}/examples/booktest",
    "args": ["generate"]
  }
  ]
}

I also needed to configure VS Code to add "go.buildTags": "examples" to .vscode/settings.json because all the examples use that build tag.

After stepping line-by-line through the internal/codegen/golang package, I realized the error was further upstream, so I set my breakpoints earlier and stepped through the internal/compiler package. Eventually, I reached this line:

It appears that the anaylzer isn't even parsing the RETURNING code, nor the DO UPDATE SET line. I dug into the SQLite docs to get a better understanding of how the UPSERT works. Reading the first line yields the following:

UPSERT is not standard SQL.

So, it seems that sqlc simply does not currently support parsing the custom UPSERT syntax of SQLite. Fortuantely, there is a solution. A few lines down is the following:

To use the value that would have been inserted had the constraint not failed, add the special "excluded." table qualifier to the column name.

So I updated my query to the following and everything worked:

-- name: SavePin :one
INSERT INTO pins (
  title, url, image_url, created_at
) VALUES (
  ?, ?, ?, ?
)
ON CONFLICT (url)
DO UPDATE SET title = excluded.title, image_url = excluded.image_url
RETURNING *;

What's the lesson? ~~What is the takeaway? Don't mess with Maui when he's on the breakaway.~~ Read the docs.

TIL — SQLite UPSERT with extends

TIL — SQLite `UPSERT` with `extends`