Would I be better by using a prepared statement here?

Question

I still struggle understanding the benefits of prepared statement in Go / psql.

Let's assume I have a struct

type Brand struct {
    Id        int        `json:"id,omitempty"`
    Name      string     `json:"name,omitempty"`
    Issued_at *time.Time `json:"issued_at,omitempty"`
}

And some table brands, where id is a unique field. Now I want to retrieve the element from that table using and id.

I can write the following function using QueryRow.

func GetBrand1(id int) (Brand, error) {
    brand := Brand{}
    if err := Db.QueryRow("SELECT name, issued_at FROM brands WHERE id = $1", id).Scan(&brand.Name, &brand.Issued_at); err != nil {
        if err == sql.ErrNoRows {
            return brand, nil
        }

        return brand, err
    }

    brand.Id = id
    return brand, nil
}

and I can do the same (I hope it is the same) using prepared statement:

func GetBrand2(id int) (Brand, error) {
    brand := Brand{}

    stmt, err := Db.Prepare("SELECT name, issued_at FROM brands WHERE id = $1")
    if err != nil {
        return brand, err
    }
    defer stmt.Close()

    rows, err := stmt.Query(id)
    if err != nil {
        return brand, err
    }
    defer rows.Close()

    for rows.Next() {
        rows.Scan(&brand.Name, &brand.Issued_at)
        brand.Id = id
        return brand, err
    }
    if err = rows.Err(); err != nil {
        return brand, err
    }

    return brand, err
}

---

Now in my application I am planning to execute GetBrand* function many times (with different parameters). Will is one of this implementations is more preferable to another (in terms of sql-requests/memory/anything). Or may be they both suck and I would be better doing something else.

I have read this and a followed up link and I saw that:

db.Query() actually prepares, executes, and closes a prepared statement. That’s three round-trips to the database. If you’re not careful, you can triple the number of database interactions your application makes

but I think that prepared statement in the second case will be removed at the end of the function.

Answer 1

In both of those examples, there's roughly the same database overhead. If you're going to use a statement a lot, prepare it once in a wider scope so it's reusable.

You would only be making one round trip to the database with that pattern.