Scan a Sql Query sqlx.Rows into a Nested Structure in golang

Question

I have three following tables:

create table A (
  a_id     varchar(256) not null unique,
  a_name   varchar(256)
);

create table B (
  b_id     varchar(256) not null,
  b_a_id   varchar(256) not null,
  b_name   varchar(256),
  FOREIGN KEY (b_a_id) REFERENCES a (a_id)
);

create table C (
  c_id     varchar(256) not null,
  c_a_id   varchar(256) not null,
  c_name   varchar(256),
  FOREIGN KEY (c_a_id) REFERENCES a (a_id)
);

insert into A(a_id, a_name) values('1234', 'a_name_1');

insert into B(b_id, b_a_id, b_name) values('B1','1234', 'b_name_1');
insert into B(b_id, b_a_id, b_name) values('B2','1234', 'b_name_2');

insert into C(c_id, c_a_id, c_name) values('C1','1234', 'c_name_1');
insert into C(c_id, c_a_id, c_name) values('C2','1234', 'c_name_2');
insert into C(c_id, c_a_id, c_name) values('C3','1234', 'c_name_3');

I have the following Structs in golang:

type A struct {
     a_id   string    `db:"a_id"`
     a_name string    `db:"a_name"`
     b      *B        `db:"b"`
     c      *C        `db:"c"`
}

type B struct {
    b_id    string     `db:"b_id"`
    b_name  string     `db:"b_name"`
    b_a_id  string     `db:"b_a_id"`
}

type C struct {
    c_id    string     `db:"c_id"`
    c_name  string     `db:"c_name"`
    c_a_id  string     `db:"c_a_id"`
}

I want to scan the rows I get from executing the join query: SELECT * from A INNER JOIN B ON a_id=b_a_id inner join C on c_a_id=a_id;

Into Struct A using rows.StructScan() in Golang but I am not able to do that. How Do I scan a join query result into a nested struct and I don't want to individually scan each column as there are a lot of columns as a result of the join query going forward.

Answer 1

After some investigation, I came up with a solution that should work also for you. First, let me present the working code, and then I'll explain the relevant parts:

package main

import (
    "fmt"

    "github.com/jmoiron/sqlx"
    _ "github.com/lib/pq"
)

type A struct {
    Id   string `db:"a_id"`
    Name string `db:"a_name"`
    B
    C
}

type B struct {
    Id   string `db:"b_id"`
    AId  string `db:"b_a_id"`
    Name string `db:"b_name"`
}

type C struct {
    Id   string `db:"c_id"`
    AId  string `db:"c_a_id"`
    Name string `db:"c_name"`
}

func main() {
    db, err := sqlx.Open("postgres", "host=localhost user=postgres password=postgres dbname=postgres port=5432 sslmode=disable")
    if err != nil {
        panic(err)
    }
    defer db.Close()

    a := []A{}
    rows, err := db.Queryx("SELECT * from A INNER JOIN B ON a_id=b_a_id inner join C on c_a_id=a_id;")
    if err != nil {
        panic(err)
    }
    defer rows.Close()

    for rows.Next() {
        var record A
        if err := rows.StructScan(&record); err != nil {
            panic(err)
        }
        a = append(a, record)
    }

    if err := rows.Err(); err != nil {
        panic(err)
    }

    for _, v := range a {
        fmt.Println(v)
    }
}

Structs definition

Here, I fixed the structs' definition by embedding B and C into the A struct. Furthermore, I also fixed the name as long as they didn't collide with the others (e.g. it's completely safe to have the Id field in all of the three structs).

Fetching data

The other relevant part is fetching the data from Postgres. Here you've to use the Queryx method and provide to it the SQL query you've correctly written.
However, you should use the methods provided by the database/sql package when dealing with multiple rows (as in our case). Next and Err are self-explanatory so I won't spend any time over them.
StructScan is the method that does the trick. It puts the current query into a loop-scoped variable called record of type A (which is our parent struct).

If you give a try to this code it should work also for you, if not let me know!

Answer 2

Please have a look at this answer Stackoverflow

Using a light weight library Carta can map SQL results to nested structs.