go-pg efficient multi table/row insert What is an efficient way to write this raw PostgreSQL query using in go? I'm using go-pg but something with just the pg library should translate well to go-pg.

Table:

companies:
  - id string, primary key
  - name string, not null

services:
  - id string, primary key
  - company_id string, not null, foreign key (company.id)
  - name string, not null


Query:

WITH company AS ( INSERT INTO companies(id, name) VALUES('1', 'acme') RETURNING id)
INSERT INTO services(id, company_id, name) VALUES
('1', (select company.id from company), 'cool service'),
('2', (select company.id from company), 'cooler service');


This is what I came up with. It's very hacky and I feel there is a more "idiomatic" way of doing this.

type Company struct {
    ID string
    Name string
    Services []*Service
}

type Service struct {
    ID string
    CompanyID string
    Name string
}

c := &amp;Company{
    ID: uuid.NewV4().String(),
    Name: "test comp",
}

s := []*Service{
    &amp;Service{
        ID: uuid.NewV4().String(),
        Name: "test svc",
    },
}

c.Service = s

values := []interface{}{
    c.ID,
    c.Name,
}

q := `
    WITH company as (INSERT INTO companies(id, name) VALUES ($1, $2) RETURNING id) INSERT INTO services(id, company_id, name) VALUES
`

var i int = 3
for _, row := range c.Services {
    q += fmt.Sprintf("($%d, (select company.id from company), $%d),", i, i+1)
    values = append(values, row.ID, row.Name)
    i += 2
}

q = strings.TrimSuffix(q, ",")

stmt, err := DB.Prepare(q)
if err != nil {
    return err
}

if _, err := stmt.Exec(values...); err != nil {
    return err
} 


I'm somewhat new to using go's sql library and I know I have to be missing something. I saw the pg library allows for bulk imports  but it's a bit confusing how I would do that in my case where I need to populate two separate tables the and the bulk import occurs on the second table.
It doesn't seem that the pq library has a query builder or a specific type. So building a query in an idiomatic and readable way should be done as you would do for a normal string, and that is using the text/template package. This will allow you to get rid of many code smells:


the line containing your query is long and not easily readable, maybe skip more lines
you can write the skeleton to your query once, instead of using successive appends — this is also more efficient if you have many services
the call to strings.TrimSuffix can be avoided using a simple condition in your template
you can replace (select company.id from company) directly by its value, that you previously computed in your Go code
and you can get rid of the []interface{} variable that you pass to Prepare &amp; Exec, just do all the substitution logic in the go code.


Various unrelated things:


Don't write var int i = 3, write simply i := 3.
I'm not sure why Company and Service have to be exported, since you seem to only use them to build a query.
When doing successive += operations on a string, consider using a buffer instead, see this question. (But here, it's moot, since using a template is more idiomatic.)