pq being used when "slice" is set to true in a column override with sqlite

[Rick-Phoenix]

Version

1.29.0

What happened?

Hi there, I was trying out sqlc with sqlite in a small experiment and I was trying to find the right away to get related rows as a slice in the go code. So I tried setting sqlc.yaml as you can see in the config.

However, when doing that, in the output query.sql.go file, it tries to use pq even if the driver is set to sqlite:

package gofirst

import (
	"context"

	"github.com/lib/pq"
)

const getUserWithPostsFromView = `-- name: GetUserWithPostsFromView :one
SELECT id, name, created_at, posts FROM user_with_posts
`

func (q *Queries) GetUserWithPostsFromView(ctx context.Context) (UserWithPost, error) {
	row := q.db.QueryRowContext(ctx, getUserWithPostsFromView)
	var i UserWithPost
	err := row.Scan(
		&i.ID,
		&i.Name,
		&i.CreatedAt,
		pq.Array(&i.Posts),
	)
	return i, err
}

So that when running this simple test:

package main

import (
	"context"
	"database/sql"
	_ "embed"
	"fmt"
	"log"

	gofirst "github.com/Rick-Phoenix/gofirst/db/queries/gen"
	_ "modernc.org/sqlite"
)

func main() {
	database, err := sql.Open("sqlite", "db/database.sqlite3")
	if err != nil {
		log.Fatalf("Failed to open database: %v", err)
	}
	defer database.Close()

	queries := gofirst.New(database)
	ctx := context.Background()

	userWithPosts, err := queries.GetUserWithPostsFromView(ctx)
	if err != nil {
		log.Fatalf("Failed to get user with posts from view: %v", err)
	}

	var data = userWithPosts.Posts

	fmt.Printf("%+v", data)
}

It fails as it says

Failed to get user with posts from view: sql: Scan error on column index 3, name "posts": pq: unable to parse a
rray; expected '{' at offset 0
exit status 1

Database schema

CREATE TABLE users (
    id integer primary key,
    name text not null unique,
    created_at text default current_timestamp
);
CREATE TABLE subreddits (
    id integer primary key,
    name text not null unique, 
    description text,
    created_at text default current_timestamp,
    creator_id integer, 
    foreign key (creator_id) references users (id) on delete set null
);
CREATE TABLE posts (
    id integer primary key,
    title text not null, 
    content text,
    created_at text default current_timestamp,
    author_id integer not null,
    subreddit_id integer not null,
    foreign key (author_id) references users (id) on delete cascade,
    foreign key (subreddit_id) references subreddits (id) on delete cascade
);
CREATE VIEW user_with_posts AS
SELECT
    u.id,
    u.name,
    u.created_at,
    COALESCE(
        JSONB_GROUP_ARRAY(
            JSONB_OBJECT(
                'id', p.id,
                'title', p.title,
                'content', p.content,
                'created_at', p.created_at,
                'author_id', p.author_id,
                'subreddit_id', p.subreddit_id
            )
        ) FILTER (WHERE p.id IS NOT NULL),
        '[]'
    ) AS posts
FROM
    users AS u
LEFT JOIN
    posts AS p
    ON u.id = p.author_id
GROUP BY
    u.id, u.name, u.created_at

SQL queries

-- name: GetUserWithPostsFromView :one
SELECT * FROM user_with_posts;

Configuration

version: "2"
sql:
  - engine: "sqlite"
    queries: "db/query.sql"
    schema: "db/schema.sql"
    gen:
      go:
        package: "gofirst"
        out: "db/queries/gen"
        emit_json_tags: true
        overrides:
          - column: "user_with_posts.posts"
            go_type:
              type: "Post"
              slice: true

Playground URL

Repro

What operating system are you using?

Linux

What database engines are you using?

SQLite

What type of code are you generating?

Go

[Rick-Phoenix]

Another thing to note (not strictly a bug like above but something that could be improved):

When using []bytes as an override instead:

        overrides:
          - column: "user_with_posts.posts"
            go_type:
              type: "[]byte"

One can then unmarshal the data like this:

type UserData struct {
	gofirst.User
	Posts []gofirst.Post
}

func main() {
	// Usual boilerplate here...

	userWithPosts, err := queries.GetUserWithPostsFromView(ctx)
	if err != nil {
		log.Fatalf("Failed to get user with posts from view: %v", err)
	}

	var posts []gofirst.Post
	err = json.Unmarshal(userWithPosts.Posts, &posts)

	userData := UserData{User: gofirst.User{ID: userWithPosts.ID, Name: userWithPosts.Name, CreatedAt: userWithPosts.CreatedAt}, Posts: posts}

	fmt.Printf("%+v", userData)
}

But this would require adding some extra handlers for each table where this method is being used. It would be great if we could specify a type for the output so that sqlc could automatically unmarshal the data with the given type and return the result in the query.

[andylin-0]

Also happens with MySQL!