Right, let’s talk about prepared statements. This is one of those topics where if you’re not using them, you’re not just leaving performance on the table—you’re actively leaving the back door to your database wide open. I’m going to show you how to use them properly in Go’s database/sql package so you can stop doing that.

The core idea is simple: instead of concatenating user input into your SQL string like it’s 1999, you use a placeholder (? or $1 depending on your database) in your query template. You then provide the actual values separately. The database driver handles the rest. This gives you two monumental advantages:

  1. Security: It completely prevents SQL injection. Because the values are passed after the SQL statement is parsed, they are treated as data, not as part of the code. A malicious string might still end up in your first_name column, but it will never, ever be executed as a command. This is non-negotiable.
  2. Performance: The database can compile (or “prepare”) the query plan once and reuse it for multiple executions with different values. This avoids the overhead of parsing and planning the same query structure over and over again.

The Two Ways to Prepare: Stmt and Concurrency

The database/sql package gives you two primary methods, and the difference is crucial.

// Method 1: Prepare a statement explicitly (db.Prepare)
stmt, err := db.Prepare("SELECT id, name FROM users WHERE email = ?")
if err != nil {
    log.Fatal(err)
}
defer stmt.Close() // This is important! Don't leak prepared statements.

// Now you can execute it multiple times
var user User
err = stmt.QueryRow("alice@example.com").Scan(&user.ID, &user.Name)
// ... handle err and scan

err = stmt.QueryRow("bob@example.com").Scan(&user.ID, &user.Name)
// ... do it again

This is great if you’re going to use the same query repeatedly in a tight loop. However, note that stmt is bound to a single database connection underneath the hood. The database/sql package manages this for you, but it’s an implementation detail worth knowing.

// Method 2: Let DB.Query/Exec prepare implicitly
err := db.QueryRow(
    "SELECT id, name FROM users WHERE email = ?",
    "alice@example.com",
).Scan(&user.ID, &user.Name)

This is what you’ll use 90% of the time. It’s cleaner and just as safe. Under the covers, db.QueryRow (and Query, Exec) prepares the statement on a connection, executes it, and then closes the statement. The driver and connection pool are smart enough to often cache the prepared statement on that connection, so you still get many of the performance benefits without the boilerplate. It’s the best of both worlds.

The Parameter Placeholder Paradox

Here’s the first “questionable choice” you’ll run into. The SQL standard uses ? as a placeholder. PostgreSQL, in its infinite wisdom, uses numbered placeholders like $1, $2. MySQL and SQLite use ?. Oracle uses :val. It’s a mess.

The database/sql package tries to abstract this away. You should always use ? as the placeholder in your query string, regardless of the database you’re writing for. The driver is responsible for rewriting it to the correct syntax before sending it to the database.

Yes, it’s a bit absurd. You write ? for a PostgreSQL database in your Go code, and the lib/pq driver (or pgx) silently changes it to $1 for you. Just roll with it. It keeps your code portable.

The Edge Cases and Pitfalls

Prepared statements aren’t magic fairy dust you sprinkle on everything.

Don’t prepare everything. If you’re running a query exactly once in the lifetime of your application, preparing it explicitly is just unnecessary overhead. The implicit method is perfect for this. The explicit db.Prepare is for queries you’ll run many times.

Watch out for IN clauses. This is a classic headache. You can’t do SELECT * FROM items WHERE id IN (?) and pass a slice []int{1, 2, 3}. The database expects a single value for that single ?. You have to build the query with the correct number of placeholders. It’s annoying, but it’s a SQL limitation, not a Go one.

// You have to do this manually:
ids := []int{1, 2, 3}
query := fmt.Sprintf("SELECT * FROM items WHERE id IN (%s)", placeholders(len(ids)))
// which would create: "SELECT * FROM items WHERE id IN (?, ?, ?)"

// Then you have to convert the slice to a slice of interface{} for the variadic function.
args := make([]interface{}, len(ids))
for i, id := range ids {
    args[i] = id
}

rows, err := db.Query(query, args...)

Some ORMs or helper libraries can abstract this away, but in pure database/sql, this is your life now. Embrace it.

The defer stmt.Close() is not optional. When you prepare a statement explicitly, it creates a structure on a specific database connection. If you don’t close it, that connection remains occupied and can’t be returned to the pool. You will eventually run out of connections. It’s a guaranteed, frustrating leak. Always defer stmt.Close() immediately after checking the preparation error.

So, the rule is simple: use query methods with placeholders (?) for everything. Use explicit preparation only for hot loops. You’ll sleep better knowing you’re not the person who wiped a production table because you used fmt.Sprintf on a user’s last name.