将一对多关系检索到JSON sql pure，Golang，Performance中

Question

Suppose that I've the following structures that it's the mapped tables.

type Publisher struct{
   ID int       `db:"id"`
   Name  string `db:"name"`
   Books []*Book  
}

type Book struct {
   ID int       `db:"id"`
   Name string  `db:"name"`
   PublisherID   `db:"publisher_id"` 
}

So, What if I wanna retrieve all the Publisher with all related Books I would like to get a JSON like this:

[ //Publisher 1 
  {
    "id" : "10001", 
    "name":"Publisher1",
    "books" : [
       { "id":321,"name": "Book1"}, 
       { "id":333,"name": "Book2"}
    ]
  },
  //Publisher 2
  {
    "id" : "10002", 
    "name":"Slytherin Publisher",
    "books" : [
       { "id":4021,"name": "Harry Potter and the Chamber of Secrets"}, 
       { "id":433,"name": "Harry Potter and the Order of the Phoenix"}
    ]
  },
]

So I've the following structure that I use to retrieve all kind of query related with Publisher

type PublisherRepository struct{
   Connection *sql.DB
}
// GetEbooks return all the books related with a publisher
func (r *PublisherRepository) GetBooks(idPublisher int) []*Book {
    bs := make([]Book,0)
    sql := "SELECT * FROM books b WHERE b.publisher_id =$1 "
    row, err := r.Connection.Query(sql,idPublisher)
    if err != nil {
      //log
    }
    for rows.Next() {
      b := &Book{}
      rows.Scan(&b.ID, &b.Name, &b.PublisherID)
      bs := append(bs,b)
    }
    return bs
}
func (r *PublisherRepository) GetAllPublishers() []*Publisher {
    sql := "SELECT * FROM publishers"
    ps := make([]Publisher,0)
    rows, err := r.Connection.Query(sql)
    if err != nil { 
       // log 
    }
    for rows.Next() {
       p := &Publisher{}
       rows.Scan(&p.ID,&p.Name)
       // Is this the best way? 
       books := r.GetBooks(p.ID)
       p.Books  = books
    }
    return ps

}

So , here my questions

1. What is the best approach to retrieve all the publisher with the best performance, because a for inside a for is not the best solution, what if I've 200 publisher and in the average of each publisher has 100 books.

2. Is in GoLang idiomatic PublisherRepository or is there another way to create something to manage the transactions of an entity with pure sql?

Answer 1

1) Bad about this would be the sql request per iteration. So here a solution that does not make an extra request per Publisher:

func (r *PublisherRepository) GetAllPublishers() []*Publisher {
    sql := "SELECT * FROM publishers"
    ps := make(map[int]*Publisher)
    rows, err := connection.Query(sql)
    if err != nil { 
       // log 
    }
    for rows.Next() {
       p := &Publisher{}
       rows.Scan(&p.ID,&p.Name)
       ps[p.ID] = p
    }

    sql = "SELECT * FROM books"
    rows, err := connection.Query(sql)
    if err != nil {
      //log
    }
    for rows.Next() {
      b := &Book{}
      rows.Scan(&b.ID, &b.Name, &b.PublisherID)

      ps[b.PublisherID].Books = append(ps[b.PublisherID].Books, b)
    }

    // you might choose to keep the map as a return value, but otherwise:

    // preallocate memory for the slice
    publishers := make([]*Publisher, 0, len(ps))
    for _, p := range ps {
        publishers = append(publishers, p)
    }

    return publishers
}

2) Unless you create the PublisherRepository only once, this might be a bad idea creating and closing loads of connections. Depending also on your sql client implementation I would suggest (and also have seen it for many other go database clients) to have one connection for the entire server. Pooling is done internally by many of the sql clients, that is why you should check your sql client. If your sql client library does pooling internally use a global variable for the "connection" (it's not really one connection if pooling is done internally):

connection *sql.DB

func New () *PublisherRepository {
    repo := &PublisherRepository{}
    return repo.connect()
}

type PublisherRepository struct{
}

func (r *PublisherRepository) connect() *PublisherRepository {
    // open new connection if connection is nil 
    // or not open (if there is such a state)
    // you can also check "once.Do" if that suits your needs better
    if connection == nil {
        // ...
    }
    return r
}

So each time you create a new PublisherRepository, it will only check if connection already exists. If you use once.Do, go will only create the "connection" once and you are done with it.

If you have other structs that will use the connection as well, you need a global place for your connection variable or (even better) you write a little wrapper package for your sql client, that is in turn used in all your structs.