Why can't I just use fmt.Sprintf to build my queries?

I've seen this a lot in early-stage projects, and honestly, it's the most dangerous habit you can develop in backend engineering. If you use fmt.Sprintf to plug a variable directly into a SQL string, you're opening the door to SQL Injection. An attacker could pass a string like 'admin'; DROP TABLE users; -- as their username, and your database will happily execute it.

Prepared statements solve this by separating the query logic from the data. When you use a placeholder (like ? in MySQL or $1 in PostgreSQL), the database compiles the SQL template first. The data is sent over the wire separately. The database engine treats the input strictly as a value, not as executable code. It's not just a "best practice"—it's a non-negotiable requirement for any app touching real user data.

How do I actually set one up in Go?

You use the Prepare method on your *sql.DB instance. This returns a *sql.Stmt object that you can reuse. Let's look at a real-world example: updating a user's email address in a profile system.

stmt, err := db.Prepare("UPDATE users SET email = ? WHERE id = ?")
if err != nil {
    log.Fatal(err)
}
// Don't forget to close the statement when the function ends
defer stmt.Close()

// Now you can execute it with different values
_, err = stmt.Exec("new-email@example.com", 42)
if err != nil {
    log.Printf("Update failed: %v", err)
}

Notice how the SQL string doesn't contain the actual email or ID? Those ? marks are the placeholders. When you call stmt.Exec, Go sends those arguments to the database to be slotted into the template.

Is there a performance benefit, or is this just for security?

There is a performance win, but only if you're doing the same operation repeatedly. Every time you send a raw SQL string to a database, the engine has to parse the string, validate the syntax, and figure out the most efficient way to find the data (the "execution plan").

If you prepare a statement once and execute it 1,000 times in a loop, the database only does that hard work once. I usually recommend this pattern for batch imports or high-frequency updates. If you're just running a query once during a web request, db.Exec or db.Query actually handles the preparation and closing for you under the hood, so you don't need to manually call Prepare unless you're optimizing for a loop.

Do I really need to call Close() on the statement?

Yes. Absolutely. I cannot stress this enough. A prepared statement isn't just a Go object; it's a resource allocated on the database server itself. If you call db.Prepare inside a loop or a frequently called HTTP handler and forget to call stmt.Close(), you will eventually hit the database's maximum prepared statement limit.

When that happens, your app won't just slow down—it'll start throwing errors and refusing to execute any more queries. Use defer stmt.Close() immediately after checking the error from Prepare. It's the only way to be sure you're not leaking server resources.

📋 Practical Task

Exercise: Batch Product Price Updater

You are building a tool to update prices for a catalog of products. Instead of sending 100 individual update queries, you need to use a single prepared statement to handle the batch efficiently.

Your Task:

• Create a slice of structs called Product containing ID (int) and NewPrice (float64).
• Initialize a *sql.DB (you can mock the connection or use a local SQLite/Postgres instance).
• Prepare a SQL statement: UPDATE products SET price = ? WHERE id = ?.
• Iterate through your slice of products and execute the prepared statement for each one.
• Ensure the statement is closed properly to avoid resource leaks.

Expected Result: The code should compile and execute without leaving any open statements on the database server, successfully updating every product in the list using the same prepared template.