Type casting not supported in SQLite

[EwenQuim]

Version

1.27.0

What happened?

I want to type-cast some query variables but it doesn't compile

Relevant log output

❯ sqlc generate
line 15:8 no viable alternative at input ';'
line 23:14 extraneous input '(' expecting {<EOF>, ';', ALTER_, ANALYZE_, ATTACH_, BEGIN_, COMMIT_, CREATE_, DEFAULT_, DELETE_, DETACH_, DROP_, END_, EXPLAIN_, INSERT_, PRAGMA_, REINDEX_, RELEASE_, REPLACE_, ROLLBACK_, SAVEPOINT_, SELECT_, UPDATE_, VACUUM_, VALUES_, WITH_}
# package store
database/query.sql:1:1: extraneous input '(' expecting {<EOF>, ';', ALTER_, ANALYZE_, ATTACH_, BEGIN_, COMMIT_, CREATE_, DEFAULT_, DELETE_, DETACH_, DROP_, END_, EXPLAIN_, INSERT_, PRAGMA_, REINDEX_, RELEASE_, REPLACE_, ROLLBACK_, SAVEPOINT_, SELECT_, UPDATE_, VACUUM_, VALUES_, WITH_}

Database schema

CREATE TABLE IF NOT EXISTS notes (
  id text PRIMARY KEY,
  slug text NOT NULL CHECK (length(slug) > 0 AND length(slug) < 100),
  content text NOT NULL CHECK (length(content) < 10000),
  user text NOT NULL,
  public BOOLEAN NOT NULL DEFAULT FALSE,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE(slug, user)
);

SQL queries

SELECT * FROM notes WHERE user = @author
	AND (public = true OR user = @user)
ORDER BY created_at DESC
LIMIT coalesce(sqlc.narg('limit')::int, 50);

Configuration

version: "2"
sql:
  - engine: "sqlite"
    schema: "database/schema.sql"
    queries: "database/query.sql"
    gen:
      go:
        package: "store"
        out: "store"

Playground URL

https://play.sqlc.dev/p/6785d32c61ea6a4b0f4a671f52132e16a70cb1ef27065de2172d6f9ae9aeeb25

Remove the ::int and it works

What operating system are you using?

macOS

What database engines are you using?

SQLite

What type of code are you generating?

Go

[ciricc]

Same problem

[waterfountain1996]

The :: cast syntax is specific to PostgreSQL. You can use cast(<expr> AS <type>) in sqlite.

[EwenQuim]

Indeed, it works. Thank you very much !

Why isn't it the same API between databases ? I guess because of RDBMS compatibility (even if sqlc.xxx functions are not natively supported by the platforms...).

Another issue that #2800 will solve, I guess

---

Now, while #2800 is still not implemented, we should at least

• document the syntax https://docs.sqlc.dev website (why isn't it already the case? It's a common issue!)
• or raise an understandable error log (this one was very obscure)