The form you fill out once, use many times
You are building a dashboard that loads user activity. The frontend sends a request for user ID 42. Your code constructs a SQL query, sends it to the database, and returns the result. The frontend then asks for user ID 43. You build the query again, send it, and return the result. This pattern works for a few requests. It breaks down when traffic scales. The database spends significant time parsing the SQL string and building an execution plan for every single request. Even worse, if you construct the query by concatenating the user ID directly into the string, you create a security vulnerability. Prepared statements solve both problems. They separate the query structure from the data, allowing the database to reuse the execution plan while treating parameters strictly as values.
How prepared statements work
A prepared statement is a query template. You send the template to the database once. The database parses the SQL, checks permissions, looks up indexes, and builds an execution plan. This plan is cached on the server and associated with a statement handle. When you want to run the query, you send only the data values. The database plugs the values into the cached plan and executes it.
This approach is faster because parsing and planning are expensive operations. Skipping them for repeated queries reduces CPU load on the database and lowers latency. It is also safer. The database knows exactly where each parameter goes. A parameter can never be interpreted as part of the SQL command. Even if the parameter contains malicious text, it remains data. The database receives the parameter as a typed value, not as a string fragment. This type safety prevents SQL injection and avoids implicit type conversions that can break index usage.
Think of a prepared statement like a stamped envelope at a post office. The envelope has the return address and postage already applied. You just write the recipient's address and drop it in the mail. The postal service does not need to verify the postage or format the envelope each time. They just read the address and route the letter. In the database, the SQL structure is the stamped envelope. The parameters are the recipient addresses.
Minimal example
Here is the basic pattern: prepare the statement once, execute it with values, and close it when done.
package main
import (
"database/sql"
"fmt"
"log"
)
func main() {
db, err := sql.Open("sqlite3", ":memory:")
if err != nil {
log.Fatal(err)
}
// Close the database connection when main returns.
defer db.Close()
// Prepare the statement once. The ? is a placeholder for a value.
stmt, err := db.Prepare("SELECT name FROM users WHERE age > ?")
if err != nil {
log.Fatal(err)
}
// Always close the statement to release server resources.
defer stmt.Close()
// Execute with a specific value. The database reuses the plan.
rows, err := stmt.Query(21)
if err != nil {
log.Fatal(err)
}
// Close rows to release the server-side cursor.
defer rows.Close()
// Iterate over results.
for rows.Next() {
var name string
if err := rows.Scan(&name); err != nil {
log.Fatal(err)
}
fmt.Println(name)
}
}
Prepare once, execute many, close always.
Walkthrough of execution
When you call db.Prepare, Go sends the SQL string to the database driver. The driver forwards the string to the database server. The server parses the SQL, validates syntax, checks table permissions, and builds an execution plan. This plan includes decisions like which indexes to use and the order of joins. The server stores the plan and returns a statement handle to the driver. Go wraps that handle in a *sql.Stmt object and returns it to your code.
When you call stmt.Query(21), Go sends the value 21 to the server, bound to the placeholder. The server does not parse the SQL again. It retrieves the cached plan, binds the value to the parameter slot, and executes the plan. The result set is sent back to Go. The rows object holds a cursor over the results. You must call rows.Close() to release the cursor on the server.
When you call stmt.Close(), Go sends a message to the server to discard the cached plan and free the statement handle. This is crucial. Database servers limit the number of open statements per connection. If you leak statements, you will eventually hit the limit and get errors like too many open statements or server closed the connection unexpectedly. The compiler does not check for resource leaks. You must manage them with defer.
The database/sql package follows a convention for error handling. Every function that can fail returns an error as the last return value. The standard pattern is if err != nil { return err }. This is verbose by design. It makes the unhappy path visible and forces you to handle failures explicitly. Wrapping errors with fmt.Errorf("context: %w", err) preserves the error chain, which helps debugging.
Realistic usage in production code
Real applications often insert or update multiple rows. Prepared statements shine in loops. Here is a function that inserts a batch of users.
// InsertUsers inserts a slice of user records into the database.
// It uses a prepared statement to reuse the execution plan for each row.
func InsertUsers(db *sql.DB, users []User) error {
// Prepare the insert statement with placeholders for each column.
stmt, err := db.Prepare("INSERT INTO users (name, email) VALUES (?, ?)")
if err != nil {
return fmt.Errorf("prepare insert: %w", err)
}
// Close the statement immediately after the loop, even if an error occurs.
defer stmt.Close()
for _, u := range users {
// Execute the statement with the current user's data.
result, err := stmt.Exec(u.Name, u.Email)
if err != nil {
// Return early on error. The deferred Close will still run.
return fmt.Errorf("insert user %s: %w", u.Name, err)
}
// Retrieve the auto-generated ID if needed.
id, err := result.LastInsertId()
if err != nil {
return fmt.Errorf("get last insert id: %w", err)
}
fmt.Printf("Inserted user %s with ID %d\n", u.Name, id)
}
return nil
}
Wrap errors, defer closes, and let the loop do the work.
The stmt.Exec call returns a sql.Result interface. You can call LastInsertId() to get the ID of the newly inserted row, or RowsAffected() to see how many rows were modified. This avoids a second query to fetch the ID. The defer stmt.Close() ensures the statement is closed even if the loop returns early due to an error. This is a common pattern: prepare, loop, execute, close.
Concurrency and context
Prepared statements are safe for concurrent use. The *sql.Stmt object can be shared across multiple goroutines. You do not need a mutex. The database/sql package handles synchronization internally. This makes prepared statements ideal for high-concurrency servers. You can prepare a statement once at startup and let every request goroutine execute it.
However, preparation itself is not instantaneous. It requires a network round-trip and server-side work. If the database is slow or under load, db.Prepare might block. In critical paths, use db.PrepareContext to respect deadlines and cancellation.
import (
"context"
"database/sql"
"fmt"
)
// PrepareSearchStmt creates a prepared statement with a timeout.
func PrepareSearchStmt(db *sql.DB, ctx context.Context) (*sql.Stmt, error) {
// Use PrepareContext to respect deadlines and cancellation.
stmt, err := db.PrepareContext(ctx, "SELECT id, title FROM posts WHERE tags LIKE ?")
if err != nil {
return nil, fmt.Errorf("prepare search: %w", err)
}
return stmt, nil
}
Context is plumbing. Run it through every long-lived call site.
The context.Context parameter always goes first in Go functions, conventionally named ctx. Functions that accept a context should check for cancellation and respect deadlines. If the context is cancelled during preparation, the driver aborts the operation and returns an error. This prevents goroutines from hanging indefinitely.
Pitfalls and common errors
Prepared statements have quirks that trip up developers. The placeholder syntax depends on the database driver. SQLite and MySQL use ?. PostgreSQL uses $1, $2, $3. If you use the wrong syntax, the compiler will not catch it. The driver will return an error at runtime. You might see syntax error near "?" or there is no parameter $1. Always check your driver documentation for the correct placeholder format.
Another pitfall is using a statement after it is closed. If you accidentally call stmt.Query after stmt.Close, the driver returns an error like sql: statement is closed. This often happens when you forget defer or misuse scoping. Keep the statement and its usage in the same function scope to avoid this.
Do not prepare a statement for a one-off query. Preparation adds overhead. If you run a query once, use db.Query or db.Exec. The database might cache the plan internally anyway, but you save the network round-trip for preparation. Reserve db.Prepare for loops, repeated calls, or long-lived handlers where the statement is reused.
Some drivers do not support prepared statements at all. In-memory databases or certain lightweight drivers might ignore Prepare and just execute the query immediately. Check your driver's capabilities. If the driver does not support preparation, db.Prepare might succeed but provide no performance benefit.
The worst goroutine bug is the one that never logs. If you leak a statement or a row cursor, your application might slow down gradually until it crashes. Monitor your database connection pool and statement counts in production. Use db.Stats() to inspect pool usage.
When to use prepared statements
Use db.Query or db.Exec for one-off queries that run once per request. The overhead of preparation is not worth it for a single execution.
Use db.Prepare when you execute the same query structure multiple times with different parameters, such as in a loop or a long-lived handler.
Use db.Prepare when you need to protect against SQL injection by separating data from code, though parameterized queries via db.Query also provide this protection.
Use db.PrepareContext when your application handles deadlines or cancellation, ensuring preparation does not block indefinitely.
Use transactions with db.Begin when multiple statements must succeed or fail together to maintain data consistency.
Use a connection pool via sql.Open rather than managing connections manually, as the database/sql package handles pooling automatically.
Simple queries stay simple. Batch queries get prepared.