SQLite engine not supporting $AAA params

[luigiMinardi]

Version

1.29.0

What happened?

If I use $AAA parameters with SQLite I will get an error but this is supported by the engine as said on SQLite SQL Language Expressions > Parameters.

In sqlite3_bind() its said:

"In the SQL statement text input to sqlite3_prepare_v2() and its variants, literals may be replaced by a parameter that matches one of the following templates:

? ?NNN :VVV @VVV $VVV In the templates above, NNN represents an integer literal, and VVV represents an alphanumeric identifier. The values of these parameters (also called "host parameter names" or "SQL parameters") can be set using the sqlite3_bind_*() routines defined here."

Relevant log output

line 17:15 no viable alternative at input 'UPDATE handles\n    SET name = $'
# package 
sql/queries/handles.sql:1:1: no viable alternative at input 'UPDATE handles\n    SET name = $'

Database schema

-- +goose Up
-- +goose StatementBegin
CREATE TABLE handles(
    id TEXT PRIMARY KEY CHECK (length(id) == 36), -- UUID
    name TEXT NOT NULL UNIQUE CHECK (length(name) <= 32), -- @foo, no symbols/spaces
    created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')), -- Unix timestamp
    updated_at INTEGER NOT NULL -- Unix timestamp
);
-- +goose StatementEnd

-- +goose Down
-- +goose StatementBegin
DROP TABLE handles;
-- +goose StatementEnd

SQL queries

-- name: UpdateHandle :one
UPDATE handles
    SET name = $2,
    updated_at = NOW()
    WHERE id = $1
RETURNING id, created_at, updated_at, name;

Configuration

version: "2"
sql:
  - engine: "sqlite"
    schema: "sql/schema/migrations"
    queries: "sql/queries"
    gen:
      go:
        out: "internal/database"

Playground URL

https://play.sqlc.dev/p/7ff6c3d5268c762b76276042ccfe9bf9bf067d52c5eae4644f6dcf329fb65b74

What operating system are you using?

Linux

What database engines are you using?

SQLite

What type of code are you generating?

Go

[gamebox]

I could be wrong, but I think the problem here is that SQLC is expecting you to use their arg syntax, either sqlc.arg(<name>) or @<name>, so I would try something like this:

-- name: UpdateHandle :one
UPDATE handles
    SET name = @name,
    updated_at = NOW()
    WHERE id = @id
RETURNING id, created_at, updated_at, name;

And sqlc will replace those with the appropriate placeholders

[luigiMinardi]

I could be wrong, but I think the problem here is that SQLC is expecting you to use their arg syntax, either sqlc.arg(<name>) or @<name>, so I would try something like this:

-- name: UpdateHandle :one
UPDATE handles
    SET name = @name,
    updated_at = NOW()
    WHERE id = @id
RETURNING id, created_at, updated_at, name;

And sqlc will replace those with the appropriate placeholders

I dont remember testing with @VVV but using ?NNN works

-- name: UpdateHandle :one
UPDATE handles
    SET name = ?2,
    updated_at = strftime('%s', 'now')
    WHERE id = ?1
RETURNING *;

This one above is a valid SQLc query for SQLite. And if you're using Postgresql the $VVV works without problem.

My point is that aside from the ?NNN using $VVV or @VVV or :VVV should also work on the SQLite engine.

[PhilipTrauner]

there are a couple constraints around where parametric arguments are picked up right now, e.g. they aren't supported within function parenthesis other than that, @<name> parameters should work 😬