<p>Hey all,</p>
<p>Currently I'm working on a project where I do a lot of database calls. The queries use variables outside of the program, but still by a program I control. To be save I used prepared statements for everything that uses variables that are not created inside the program.</p>
<p>I end up with a function like this:</p>
<pre><code>func createStmts() (*sql.Stmt, *sql.Stmt, *sql.Stmt, *sql.Stmt, *sql.Stmt) {
stmtTld, err := db.Prepare("SELECT id FROM top_level_domain WHERE name = $1")
if err != nil {
log.Println(err)
}
stmtInsert, err := db.Prepare(`INSERT INTO domain_name (name, tld_id, alive, ip_address, has_robots, redirect_id) VALUES (
$1, $2, NULL, NULL, NULL, NULL
)`)
if err != nil {
log.Println(err)
}
stmtExists, err := db.Prepare("SELECT id, has_been_scannend, in_queue FROM domain_name WHERE name = $1")
if err != nil {
log.Println(err)
}
stmtDomainID, err := db.Prepare("SELECT id FROM domain_name WHERE name = $1")
if err != nil {
log.Println(err)
}
stmtInsertExternalLink, err := db.Prepare("INSERT INTO domain_name_external_links (domain_name_id, external_domain_id) VALUES ($1, $2)")
if err != nil {
log.Println(err)
}
return stmtTld, stmtInsert, stmtExists, stmtDomainID, stmtInsertExternalLink
}
</code></pre>
<p>The function that uses these statements loops over an array with structs, again prepared statments make sense here becuase they are used multiple times before being closed.</p>
<ol>
<li>How can I avoid this "utility" function that keeps on growing? Should I split it up, each statments gets its own function?</li>
<li>How about naming them? stmtInsert, stmtInsertDomain, DomainInsert?</li>
</ol>
<p>Sorry I'm just a bit lost at about this.</p>
<p>Thanks in advance.</p>
<hr/>**评论：**<br/><br/>willglynn: <pre><blockquote>
<p>To be save I used prepared statements for everything that uses variables that are not created inside the program.</p>
</blockquote>
<p>You don't need <code>Prepare()</code> for this. <a href="https://golang.org/pkg/database/sql/#DB.Query" rel="nofollow"><code>db.Query()</code> takes parameters</a> and can use placeholders to provide the exact same level of safety. <code>Query()</code> literally prepares the statement and immediately executes it, unless the driver provides a <code>driver.Queryer</code> interface with equivalent semantics. <code>db.Query()</code> and <code>db.Prepare()</code> provide the exact same safety from SQL injection.</p>
<p>Why two interfaces, then? Prepared statements allow the database to cut down on query planning time, a cost which can get significant for queries that are executed many many times. Unfortunately, moving all your queries to a central place like a <code>func createStmts()</code> can have a significant negative impact on code structure and general readability. Is there some option in the middle?</p>
<p><code>pgx</code> thinks so, namely <a href="https://godoc.org/github.com/jackc/pgx#ConnPool.Prepare" rel="nofollow">an idempotent <code>Prepare()</code> function</a>. This lets you write code that prepares and execute statements as needed -- keeping the SQL queries in the code that uses them -- but subsequent <code>Prepare()</code> calls for identical queries are handled from a cache. This gives the performance benefits of preparing once and executing repeatedly without the maintenance drawbacks. If you need prepared statements, I would encourage you to do something similar on top of <code>database/sql</code>.</p></pre>j0holo: <pre><p>Aha, not needing the prepare statement makes the code a lot cleaner. Each database function gets its own go function. Removes a bunch of statement arguments from my functions.</p>
<p>Thanks a lot for your advice.</p></pre>lluad: <pre><p>Yes, all of this.</p>
<p>Also, you should be aware that prepared statements may be slower, sometimes drastically slower, than non-prepared statements. You save time on the planning, but the tradeoff is that the "generic" plans that are produced may not be as efficient as one that's planned with the knowledge of the parameters.</p>
<p>Parameterized queries are always a good idea, if you can use them, but prepared queries aren't <em>always</em> better.</p></pre>j0holo: <pre><p>Never knew prepared statements could be slower. I thought that prepared statements optimized the query in advance somehow.</p></pre>mcouturier: <pre><p>I may talk out of my ass but didn't prepared statements also reserve a connection right away?</p></pre>lluad: <pre><p>At the database level, typically yes. The statement is prepared on a single connection and can only be used there (at least on the RDBMs I'm familiar with).</p>
<p>But the Go db library - database/sql - provides pooling, and abstracts away the prepared statements. The db.Prepare() you run just tells the library about the statement, and it'll then run real prepare statements on each underlying session as needed (or it won't - there's no obligation for db.Prepare() to map on to an underlying PREPARE statement, though I'd usually expect it to). I've not looked at the code, but I'd expect it to do that lazily.</p></pre>piatochkin: <pre><p>I did it like this</p>
<pre><code>const (
stmtCreateUser = iota
stmtGetUser
stmtUpdateUser
)
var stmtPairs = []struct {
id int
stmt string
}{
{stmtGetUser, `SELECT u.email, u.data
FROM user u
WHERE u.id=$1
FOR UPDATE`},
{stmtUpdateUser, `UPDATE user SET data=$1
WHERE id=$2`},
}
func (d *DB) prepareStmts() error {
for _, stmtPair := range stmtPairs {
st, err := d.DB.Prepare(stmtPair.stmt)
if err != nil {
return fmt.Errorf("Failed to prepare statement %s: %s", stmtPair.stmt, err.Error())
}
d.stmts[stmtPair.id] = st
}
return nil
}
// NewDB creates new Postgres connection
func NewDB(dataSource string) (*DB, error) {
db, err := sql.Open("postgres", dataSource)
if err != nil {
return nil, err
}
res := &DB{DB: db, stmts: map[int]*sql.Stmt{}}
if err := res.prepareStmts(); err != nil {
return nil, err
}
return res, nil
}
</code></pre></pre>j0holo: <pre><p>Okay thanks alot, this looks a lot cleaner. Will take a look at it tomorrow. Thanks</p></pre>tcrypt: <pre><p>database/sql will always use prepared statements when you pass in parameters; no need to make them manually.</p></pre>mastercactapus: <pre><pre><code>type Backend struct {
db *sql.DB
insert *sql.Stmt
delete *sql.Stmt
}
func NewBackend(db *sql.DB) (*Backend, error) {
var err error
var s *sql.Stmt
prep := func(q string) *sql.Stmt {
if err != nil {
return nil
}
s, err = db.Prepare(q)
// err = errors.Wrapf(err, "prepare query: %s", q)
return s
}
return &Backend{
db: db,
insert: prep("insert ..."),
delete: prep("delete ..."),
}, err
}
</code></pre>
<p>If you have a lot (more than one "Backend"-type struct) it works better to break it out into a struct that has db, err and a "Prepare" method (whatever you want to call it).</p>
<p>The idea is to store the first error and jump out if it's != nil. It makes subsequent calls after the first failure cheap enough that it doesn't matter that you don't check the error until the end. It's not the right choice for <em>every</em> situation, but it's good for initialization and places where you can tolerate a little inefficiency to improve clarity.</p>
<p>Inspiration came from bufio.Scanner and how it "stores" the error state:
<a href="https://golang.org/pkg/bufio/#Scanner" rel="nofollow">https://golang.org/pkg/bufio/#Scanner</a></p>
<p>There may have been a blog post at some point in the past too, I vaguely remember reading something. If I find it I'll post the link.</p></pre>
