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.