scanning to nested structs

[kfirufk]

Hello I was using sqlx and I moved to pgx with scany.

I have an issue here that I didn't resolve with sqlx also, i hope that there is some sort of resolution to this instead of manually scanning every row.

I use PostgreSQL 12.4 and I'm executing a function that returns a table with the following:

CREATE FUNCTION ... RETURNS TABLE (
             type text,
              name text,
              based_on text[],
              source text,
              ingredients json[],
              accessories json[],
              glasses json[],
              created_at timestamptz
              )
...

I use sqlgen for GraphQL and it created the following classes:

type SearchResultRow struct {
    Name        string            `json:"name" db:"name"`
    Type        string            `json:"type" db:"type"`
    AddedBy     *string           `json:"added_by" db:"added_by"`
    Source      *string           `json:"source" db:"source"`
    Ratings     *int              `json:"ratings" db:"ratings"`
    BasedOn     pq.StringArray    `json:"based_on" db:"based_on"`
    Ingredients []*IngredientType `json:"ingredients" db:"ingredients"`
    Accessories []*AccessoryType  `json:"accessories" db:"accessories"`
    Glasses     []*GlassType      `json:"glasses" db:"glasses"`
    CreatedAt   string            `json:"created_at" db:"created_at"`
}

type IngredientType struct {
    Name       string         `json:"name" db:"name"`
    Amount     float64        `json:"amount" db:"amount"`
    AmountType pq.StringArray `json:"amount_type" db:"amount_type"`
}

type AccessoryType struct {
    Name       string         `json:"name" db:"name"`
    Amount     float64        `json:"amount" db:"amount"`
    AmountType pq.StringArray `json:"amount_type" db:"amount_type"`
}

type GlassType struct {
    Name       string         `json:"name" db:"name"`
    Amount     float64        `json:"amount" db:"amount"`
    AmountType pq.StringArray `json:"amount_type" db:"amount_type"`
}

I'm querying the database using pgxscan with the following code:

var rows []*model.SearchResultRow
err := pgxscan.Select(context.Background(), Connection, &rows, sqlQuery, query);

and the error that I got is this: scany: scan row into struct fields: can't scan into dest[4]: unknown oid 199 cannot be scanned into *[]*model.IngredientType

is there anything I can do to overcome this ? I wouldn't mind returning different types from the database, changing it from json array to one json object, anything that can help me resolve this.

thank you

[georgysavva]

Hi. Sorry for the long response.

This error happens because pgx can't handle your custom type model.IngredientType. Your custom type must implement a specific pgx interface to be handled. You can read more about it in the pgx docs here.

I also created a note about such case in scany docs here.

[snowzach]

I have a similar issue to this. I actually use sqlx currently and JOIN tables with structs.. for example:

type Person struct {
  Name string `db:"name"`
  LocationID string `db:"location_id"`
  Location *Location `db:"location"
}

type Location struct {
  ID string `db:"id"`
  City string `db:"city"
}

I will use Get or Select with a query like: SELECT person.name AS name, person.location_id as location_id, location.id AS location.id, location.city AS location.city FROM person JOIN location ON person.location_id = location.id

When I use SQLX it works perfectly and loads the person struct and creates the location struct and assigns it under the person result and all fields are filled out appropriately. This doesn't work with scany

[georgysavva]

Hi @snowzach. Thanks for writing. Your issue isn’t similar and it should work. Can you post the exact code of your structs, query and how you call scany so I could I investigate and debug.

[snowzach]

@georgysavva I started modifying the code a little to see if I could make it work... It looks like it's only designed to work with embedded structs from what I could tell. I commented out the bit here https://github.com/georgysavva/scany/blob/master/dbscan/structref.go#L40 about being an anonymous struct and it started to kind of work. Because time.Time is a struct it wouldn't scan those columns right. There was nothing special about what I was doing with the example code above.

Here's an example that should do it..

CREATE TABLE person (
  name TEXT,
  location_id INTEGER
);

CREATE TABLE location(
  id INTEGER,
  city TEXT
);

INSERT INTO location (id, city) VALUES(1, 'Detroit');
INSERT INTO person (name, location_id) VALUES('bob', 1);

package main

import (
        "context"
        "fmt"
        "log"
        "os"

        "github.com/georgysavva/scany/pgxscan"
        "github.com/jackc/pgx/v4/pgxpool"
)

func main() {

        poolConfig, err := pgxpool.ParseConfig(os.Getenv("DATABASE_URL"))
        if err != nil {
                log.Fatalf("Unable to parse DATABASE_URL", "error", err)
        }
                                                                                                                                                                                                                                                     db, err := pgxpool.ConnectConfig(context.Background(), poolConfig)
        if err != nil {
                log.Fatalf("Unable to create connection pool", "error", err)
        }

        type Location struct {
                ID   int    `db:"id"`
                City string `db:"city"`
        }

        type Person struct {
                Name       string    `db:"name"`
                LocationID int       `db:"location_id"`
                Location   *Location `db:"location"`
        }

        var person Person
        err = pgxscan.Get(context.Background(), db, &person, `SELECT person.name AS "name", person.location_id as "location_id", location.id AS "location.id", location.city AS "location.city" FROM person JOIN location ON person.location_
id = location.id LIMIT 1`)
        if err != nil {
                fmt.Printf("Get error: %v\n", err)
        }

        fmt.Printf("DUMP: %+v\n", person)                                                                                                                                                                                                    
}

It generates error: Get error: scany: column: 'location.id': no corresponding field found, or it's unexported in main.Person

If I comment out the bit about being an anonymous struct in structref.go it works but it won't work for time.Time structs.

[georgysavva]

Hi. Sorry, I mislead use. Your example indeed should not work, because scany supports only embedded structs to reuse models:

type Person struct {
  Name string `db:"name"`
  LocationID string `db:"location_id"`
  Location *Location `db:"location"
}

This is by design and follows the sqlx behavior, here is the docs But it has nothing to do with time.Time fields, they will work because it's an independent field anot not a way to reuse another model. I added an explicit test for this case you can review it here https://github.com/georgysavva/scany/commit/6f188349367f334f83e3cdd988ce8c4b1f654d57

[snowzach]

FWIW sqlx works with non-embedded structs. I am currently using that pattern above.

[georgysavva]

The documentation says differently: https://jmoiron.github.io/sqlx/#advancedScanning

Note that sqlx historically supported this feature for non-embedded structs, this ended up being confusing because users were using this feature to define relationships and embedding the same structs twice:

And as far as I tested It worked according to the documentation. Maybe you are using an old version of sqlx?

[snowzach]

I think the documentation is worded confusingly. It does indeed support it and I think the suggestion is that you need to make sure you use column aliases when you do it to ensure things are scanned properly... ie SELECT location.id AS "location.id" in my example above will ensure that the column is selected into the location sub-struct id field.

If you use the same struct twice inside of another struct it won't be able to tell what belongs with what unless you use the db tags on the struct and the struct fields and provide a column alias that gives sqlx hints where the value belongs.

[sammy-tam]

We are also in the process of attempting to move from sqlx + pgx/stdlib to pgx native + this project, and this is one of the current roadblock for us. as @snowzach said the wording with the sqlx doc is indeed misleading it actually works and only time it does not is when the same struct is used twice and without the db tag like the

type Child struct {
    Father Person
    Mother Person
}

example

[georgysavva]

@snowzach @sammy-tam thanks for all the details I will investigate and develop a solution in a short time. I will keep you updated here about the decisions.

[georgysavva]

Hey guys @snowzach @sammy-tam . I almost finished the change for this. I have one question. Why do you say that in case of the same struct used twice sqlx needs the db tag? Like in this example

type Child struct {
    Father Person
    Mother Person
}

As I understand if you don't specify the db tag for a nested struct the default behavior is to use the lowercased field name as prefix. So the first struct will have "father." prefix and the second one - "mother.". And you should use column aliases with the same prefixes in your SQL query.

[snowzach]

@georgysavva you're right. That behavior seems correct to me.

[sammy-tam]

@georgysavva thanks! Actually that is a good question, I don't have specific knowledge on that with sqlx because in our code base we always make the db tag mandatory in order to be clear that a field is to be mapped with a query.

I mentioned that only based on the sqlx's doc saying that it is not good, but I think you are right that should work as you described

[georgysavva]

Got it. You can check the PR https://github.com/georgysavva/scany/pull/21. I just need to update the docs

[georgysavva]

@snowzach @sammy-tam Hi. I published a new release v0.2.7 that includes this feature. Please let me know if something still doesn't work for you.

[sammy-tam]

we will definitely try it out @georgysavva thank you very much for your work.