In Go's database/sql, what does db.PrepareContext return and why must you Close it?
answer
- the server keeps something for you
- parse once, execute many times
- not just a Go struct
- defer it under the error check
- Close reaches every pooled connection
basics
~20 sIt returns a *sql.Stmt, a handle to a statement the database server has already parsed and is holding open. Stmt.Close discards it on every pooled connection it was prepared on; skip that Close and the server-side state stays.
solid answer
~50 s`db.PrepareContext` sends the SQL to the server once, gets a handle back, and returns a `*sql.Stmt` you then run many times with different arguments through `stmt.ExecContext`, `stmt.QueryContext` or `stmt.QueryRowContext`. The point is reuse: the server parses and plans the statement once instead of on every call, which matters in a job that runs one statement shape over thousands of rows. But a `*sql.Stmt` is not a plain Go object — it owns state on the database server, and potentially on more than one connection, because `database/sql` prepares it again wherever the pool sends it. `stmt.Close()` releases all of that, so the idiom is `defer stmt.Close()` immediately after the error check. A `*sql.Stmt` is safe for concurrent use by multiple goroutines, so one long-lived handle per query shape is the normal shape — not one per request.
code
go · 11 linesstmt, err := db.PrepareContext(ctx, "UPDATE docs SET idx = ? WHERE id = ?")
if err != nil {
return err
}
defer stmt.Close()
for _, d := range batch {
if _, err := stmt.ExecContext(ctx, d.Index, d.ID); err != nil {
return err
}
}go deeper
Be ready to say what PrepareContext hands back, that a *sql.Stmt holds state on the database server rather than only in Go, and that defer stmt.Close() belongs directly under the error check.
Explain what the Close actually releases and why reuse is the whole point: one parse and plan on the server against many executions, versus a hidden prepare, execute and close per call on drivers with no one-shot path.
Show where the handle lives in a real service: prepared once when the component is built, held on that component, closed at shutdown, never re-prepared per request — and say when you would skip preparing entirely.
Own the convention the codebase follows. Decide whether data-access types keep long-lived statement handles at all, or whether plain parameterised calls are easier to review and the reuse is not worth the lifetime bookkeeping.
## The type `db.PrepareContext(ctx, query)` returns `(*sql.Stmt, error)`. A `*sql.Stmt` is a handle to a statement that the database server has been asked to parse, validate and keep ready under a name of its own. From then on you execute it by sending only the arguments: - `stmt.ExecContext(ctx, args...)` for statements that do not return rows, - `stmt.QueryContext(ctx, args...)` for many rows, - `stmt.QueryRowContext(ctx, args...)` for one. The SQL text is fixed at preparation time. You never pass it again, and you never rebuild it per call — the arguments travel separately. ## Why prepare at all Two reasons, and only one of them is speed. **Reuse.** Parsing and planning a statement is real work on the server. If a background job runs the same `UPDATE` over a million rows, doing that work once instead of a million times is a measurable win. This is exactly the workload prepared statements exist for: one query shape, an ordered batch of parameter sets behind it. **A parameterised call shape.** Whatever else it does, a prepared statement forces arguments to travel as arguments. That property is not unique to explicit preparation, though — `db.ExecContext(ctx, sql, args...)` has it too. Which is why the honest answer to "should I prepare?" is: prepare when you will reuse the handle. For a query a process runs three times, an explicit `PrepareContext` buys nothing and adds a lifetime you now have to manage. ## What happens if you never prepare explicitly `database/sql` may still prepare on your behalf. When you call `db.QueryContext` with arguments, it asks the driver's connection whether it implements the one-shot path — `driver.QueryerContext` (or `driver.ExecerContext` for `ExecContext`). If it does, the query and its arguments go to the server in a single call. If it does not, `database/sql` falls back to preparing the statement on that connection, executing it, and closing it again. That fallback is invisible in your code, and it is the reason "does preparing help?" cannot be answered without knowing the driver. The important part for lifetime: in the fallback path `database/sql` closes the statement it created. Nothing accumulates. It is the handle *you* hold and forget that accumulates. ## Why Close is not optional A `*sql.Stmt` is a Go pointer, so the garbage collector will happily reclaim the Go-side object once nothing references it. That reclaims nothing on the database server. The server is holding a parsed statement, its plan and its buffers, keyed to a session, and it will keep holding them until it is told to drop the statement or the session goes away. `stmt.Close()` is that instruction. It releases the statement on every connection the handle has been prepared on, not just the first. The standard form is: ```go stmt, err := db.PrepareContext(ctx, q) if err != nil { return err } defer stmt.Close() ``` The `defer` goes directly under the error check, so there is no path out of the function that skips it. The failure mode when you skip it is not a Go memory leak — the Go process can look perfectly healthy — it is server-side state that climbs for as long as the connections live. That is the classic "prepared statement leak": a `PrepareContext` inside a loop or a request handler, with no matching `Close`. ## Where a statement should live Because a `*sql.Stmt` is safe for concurrent use by multiple goroutines, the natural placement is once, not per call: - prepare at construction time (or lazily, once) in the component that owns the query, - store the handle on that component's struct, - close it when the component shuts down. Preparing per request is the anti-pattern: it pays a round trip every time, gains no reuse, and turns a single missed `Close` on an error path into unbounded growth. ## The short version `PrepareContext` gives you a reusable, concurrency-safe handle to server-side state. `Close` is what gives that state back. Everything else about prepared statements in Go — which connection they run on, what a transaction does to them, why a server's statement count climbs — follows from those two sentences.
- Does db.QueryContext with arguments prepare a statement even if you never call PrepareContext?It depends on the driver. If the connection implements `driver.QueryerContext`, `database/sql` sends the query and its arguments in one call. If it does not, `database/sql` prepares the statement on that connection, executes it, and closes it again — a hidden prepare, execute and close on every call. Explicit `PrepareContext` is what lets you pay that once and reuse the handle.
- Where should a long-lived *sql.Stmt be created in a service?Once, when the component that owns the query is constructed, stored on its struct, and closed when that component shuts down. A `*sql.Stmt` is safe for concurrent use by multiple goroutines, so preparing per request buys nothing: it costs a round trip each time and turns one missed `Close` on an error path into steady growth of server-side state.
- What happens to a *sql.Stmt if the *sql.DB it came from is closed?Closing the `*sql.DB` shuts its connections, and the server-side statements go with them; further use of the handle returns an error rather than working. Relying on that is still sloppy — a statement scoped to a function should be closed in that function, not at process shutdown.
Preparing is like leaving a standing order at a counter: the clerk writes it down once and you just call out the quantities. If you never tell the clerk to tear the note up, it stays on their spike forever.
saying these in an interview costs you the question
- Calls a *sql.Stmt just a wrapper around a SQL string
- Prepares a new statement per request and never closes it
- Says the Go garbage collector will release the server-side statement
- Claims preparing is always faster, even for a one-shot query
- Believes a *sql.Stmt cannot be shared between goroutines