Issues with Query Result Mapper

[smk1992]

Describe the bug I'm not sure if I'm doing something wrong but I'm running into an issue with the results not mapping to struct.

Environment (please complete the following information):

• OS: macosx
• Database: postgres
  • Database driver: pg
• Jet version 2.11.0

Code snippet

package main

import (
    "context"
    s "database/sql"
    "fmt"
    _ "github.com/lib/pq"
    "ksm_booking_marketplace_api/internal/db/ksm_booking_development/public/model"
    "ksm_booking_marketplace_api/internal/db/ksm_booking_development/public/table"
    "ksm_booking_marketplace_api/internal/settings"
)

func main() {
    settings.Load()

    //category := Category{Name: "Hello World"}
    db, err := s.Open("postgres", settings.DatabaseUrl())
    if err != nil {
        panic(err)
    }

    defer db.Close()

    sql := table.Categories.SELECT(table.Categories.AllColumns).DebugSql()
    fmt.Println(sql)

    rows, err := db.Query(sql)
    if err != nil {
        panic(err)
    }

    defer rows.Close()
    for rows.Next() {
        var category model.Category
        err = rows.Scan(&category.ID, &category.Name, &category.CreatedAt, &category.UpdatedAt)
        fmt.Println("Category Row: ", category)

        if err != nil {
            panic(err)
        }
    }

    var result []model.Category
    err = table.Categories.SELECT(table.Categories.AllColumns).QueryContext(context.Background(), db, &result)
    if err != nil {
        panic(err)
    }

    fmt.Println("JET CATEGORIES PRESENT:", result)
}

Output:

Loading env file: .env.development

SELECT categories.id AS "categories.id",
     categories.name AS "categories.name",
     categories.created_at AS "categories.created_at",
     categories.updated_at AS "categories.updated_at"
FROM public.categories;

Category Row:  {36 Canned Fish/Meat 2024-03-02 19:09:56.098138 +0000 +0000 2024-03-02 19:09:56.098138 +0000 +0000}
Category Row:  {37 Hello World 2024-03-02 19:35:07.241426 +0000 +0000 2024-03-02 19:35:07.241426 +0000 +0000}
Category Row:  {38 Hello World 2024-03-02 19:35:27.335885 +0000 +0000 2024-03-02 19:35:27.335885 +0000 +0000}
Category Row:  {39 Hello World 2024-03-02 19:36:14.047971 +0000 +0000 2024-03-02 19:36:14.047971 +0000 +0000}
Category Row:  {40 Hello World 2024-03-02 19:37:28.039077 +0000 +0000 2024-03-02 19:37:28.039077 +0000 +0000}
Category Row:  {41 Hello World 2024-03-02 19:40:37.27293 +0000 +0000 2024-03-02 19:40:37.27293 +0000 +0000}
JET CATEGORIES PRESENT: []

Expected behavior Query Results should map to struct

[houten11]

If you want to use custom model types, your struct type needs to match the table name.
One way to fix your code is to use generated model types:

var result []model.Categories

[smk1992]

If you want to use custom model types, your struct type needs to match the table name. One way to fix your code is to use generated model types:

var result []model.Categories

Category is model generated model type. I like to import tables & models with .. So I have the model name as singlular while table as plural. I have the updated the code to have it be table.Categories & model.Category to avoid confusion.

EDIT: I think I figured out the problem.... Thank you @houten11. Didn't realize renaming the model generated type would break the scanning.

[smk1992]

But I am curious, how are we suppose to implement model type renaming for scanning to work? 🤔

This is my generator

postgres.Generate("./internal/db/.",
    postgres.DBConnection{
        Host:       settings.DatabaseHost(),
        Port:       5432,
        User:       settings.DatabaseUsername(),
        Password:   settings.DatabasePassword(),
        DBName:     settings.DatabaseName(),
        SchemaName: "public",
        SslMode:    "disable",
    },
    template.Default(postgres2.Dialect).
        UseSchema(func(schema metadata.Schema) template.Schema {
            return template.DefaultSchema(schema).
                UseModel(template.DefaultModel().
                    UseTable(func(table metadata.Table) template.TableModel {
                        if table.Name == "z_migrations" {
                            return template.TableModel{Skip: true}
                        }

                        return template.DefaultTableModel(table).
                            UseFileName(schema.Name + "_" + table.Name).
                            UseTypeName(utils.Titleize(table.Name)).
                            UseField(func(column metadata.Column) template.TableModelField {
                                defaultTableModelField := template.DefaultTableModelField(column)

                                if schema.Name == "public" &&
                                    table.Name == "clients" &&
                                    column.Name == "phone_numbers" {
                                    defaultTableModelField.Type = template.NewType(dbtypes.PhoneNumbersJSONB{})
                                }

                                switch defaultTableModelField.Type.Name {
                                case "uuid":
                                    defaultTableModelField.Type = template.NewType(uuid.UUID{})
                                case "*string":
                                    defaultTableModelField.Type = template.NewType(sql.NullString{})
                                case "*int32":
                                    defaultTableModelField.Type = template.NewType(sql.NullInt32{})
                                case "*int64":
                                    defaultTableModelField.Type = template.NewType(sql.NullInt64{})
                                case "*bool":
                                    defaultTableModelField.Type = template.NewType(sql.NullBool{})
                                case "*float64":
                                    defaultTableModelField.Type = template.NewType(sql.NullFloat64{})
                                case "*time.Time":
                                    defaultTableModelField.Type = template.NewType(sql.NullTime{})
                                }

                                return defaultTableModelField
                            })
                    }),
                )
        }),
)

[houten11]

If you renamed model type, you can customize SQL Builder default alias:

https://github.com/go-jet/jet/blob/1b63280b742ba28750ecd37a7472c16ae57a22ff/tests/postgres/generator_template_test.go#L345-L360

[smk1992]

Sweet thanks that helps a lot