sqldatabasegosqlx

How to read foreign keys in sqlx

In my database I have a few tables:

Team

| Field | Type    |
| ----- | ------- |
| ID    | int     |
| Name  | varchar |

Fixture

| Field | Type |
| ----- | ---- |
| ID    | int  |
| Home  | int  |
| Away  | int  |

Perhaps obviously, the Home and Away columns are foreign keys to Teams.

In Go, I have these defined as the following types:

type Team struct {
  ID   int64  `json:"id" db:"id"`
  Name string `json:"name" db:"name"`
}

type Fixture struct {
  ID   int64 `json:"id" db:"id"`
  Home int64 `json:"home" db:"home"`
  Away int64 `json:"away" db:"away"`
}

I'd like to be able read a slice of fixtures from the database, something like:

var fs []Fixture
err := sqlx.DB.SelectContext(ctx, &fs, `SELECT * FROM fixture`)

but this results in an error:

Scan error on column index 2, name "home": unsupported Scan, storing driver.Value type int64 into type *teams.Team

I've read about embedding here, but couldn't really see this helps because both Home and Away are of the same type, so there would be naming conflicts.

Any help greatly appreciated

Solution

  • First of all, there is no way to do this directly, e.g. declare some fields of type X on struct Y and have the database driver automatically deserialize them, because the data model of the two ends is fundamentally different: the database is relational (assuming this since you're using sqlx) and Go is object-oriented structural. So this would be the job for an ORM (object-relational mapping, hence the name). But let's say you don't want to use an ORM just yet.

    Secondly, regardless of existing FK constraints, the SQL query you are using returns only data in the fixture table. It won't magically return other data. So you either query the DB twice, or you have to JOIN the two tables:

    SELECT 
  f.id, 
  t1.id as home_team_id, 
  t1.name as home_team_name,
  t2.id as away_team_id,
  t2.name as away_team_name
FROM
  fixture f
  JOIN team t1 ON t1.id = f.home
  JOIN team t2 ON t2.id = f.away

    Note the aliases with as that help differentiate the column names. This returns the necessary data fields. Now you can use a third bridge struct to properly scan the rows gotten from the DB, and initialize your main structs with it:

        // Here I'm using (a slice of) anonymous structs, 
    // but you can declare a named one instead.
    bridge := []struct {
        FixtureId    int64  `db:"id"`
        HomeTeamId   int64  `db:"home_team_id"`
        HomeTeamName string `db:"home_team_name"`
        AwayTeamId   int64  `db:"away_team_id"`
        AwayTeamName string `db:"away_team_name"`
    }{}
    err := db.SelectContext(ctx, &bridge, query)
    if err != nil {
        // handle err...
    }
    mystructs := make([]Fixture, len(bridge))
    for i, b := range bridge {
        mystructs[i] = Fixture{
            ID: b.FixtureId,
            Home: Team{
                ID:   b.HomeTeamId,
                Name: b.HomeTeamName,
            },
            Away: Team{
                ID:   b.AwayTeamId,
                Name: b.AwayTeamName,
            },
        }
    }

    Other less handy alternatives are: