SQLite: interface{} instead of time.Time for json extracted queries

[jeromewir]

Version

1.26.0

What happened?

When generating some queries for some extracted JSON data on SQLite, the generated query row model uses interface{} instead of time.Time.

I reproduced with the following query: SELECT id, DATETIME(data ->> createdAt) as created_at FROM authors; Which produces:

type GetAuthorRow struct {
    ID        string
    CreatedAt interface{}
}

I've tried casting the output but SQLite only supports basic casting: https://www.sqlite.org/lang_expr.html#castexpr Using DATETIME with CAST(DATETIME(data ->> createdAt) AS DATETIME)` results in the correct type being generated on go side with SQLC, but the data is not correctly extracted from the SQLite database.

I also tried to use the overrides with no sucess as I'm not dealing with existing SQLite columns but "generating" them with the queries.

Relevant log output

No response

Database schema

CREATE TABLE authors (
  id    text PRIMARY KEY,
  data  text
);

SQL queries

SELECT id, DATETIME(data ->> createdAt) as created_at FROM authors;

Configuration

- engine: "sqlite"
    queries:
      - "queries.sql"
    schema: "./sql/sqlite/migrations/"
    gen:
      go:
        package: "queries"
        out: "internal/services/sqlite/queries"
        emit_interface: true
        emit_empty_slices: true
        emit_all_enum_values: true

Playground URL

https://play.sqlc.dev/p/596c5ead0510249c37a5f0aefda2441ce806c9ed7827e361b6568b0e1f18fe93

What operating system are you using?

Linux, macOS

What database engines are you using?

SQLite

What type of code are you generating?

Go

[orisano]

SQLite does not have a DATETIME type, which is why it behaves this way. However, a string should be returned. We need to add the definition of the DATETIME function to sqlc's catalog.