Fetching Database Records & Returning JSON in Go

In modern web service architectures built with Go, querying a relational database, mapping tabular result sets into strongly typed memory structures, and serializing those models into JSON for HTTP delivery represents one of the most critical execution paths. This guide breaks down the end-to-end implementation of an idiomatic database read handler using Go’s standard library (net/http and database/sql), highlighting memory safety, resource management, cursor mechanics, and JSON struct marshaling.

1. Architectural Context: Handlers and the Closure Factory Pattern

When building modular Go backends without bloated frameworks, handlers frequently need access to shared dependencies, such as an active database connection pool (*sql.DB). Rather than relying on package-level global variables—which introduce hidden side-effects, race conditions, and testing bottlenecks—idiomatic Go leverages functional closures.

Application Bootstrap (main.go)
   |
   | 1. Opens DB Connection Pool (*sql.DB)
   v
Handler Registration (routes.go)
   |
   | 2. Passes *sql.DB into ListListingsHandler(db)
   v
Closure Factory Execution
   |
   | 3. Returns closure: func(w http.ResponseWriter, r *http.Request)
   v
HTTP Router
   |
   | 4. Dispatches incoming GET /listings requests directly into closure

This pattern is known technically as a Closure Factory. The outer function accepts the dependencies and returns an http.HandlerFunc. The returned inner function forms a closure over those dependencies, retaining access to the database pool throughout the server's lifecycle.

package handlers

import (
	"database/sql"
	"net/http"
)

// ListListingsHandler acts as a Closure Factory, injecting *sql.DB
func ListListingsHandler(db *sql.DB) http.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) {
		// Handler logic has full access to `db`
	}
}

2. Executing the Relational Query (db.Query)

To retrieve multiple rows from an SQL database, the standard library provides the Query method on *sql.DB.

query := `
    SELECT id, title, description, price, city, created_at 
    FROM listings 
    ORDER BY created_at DESC 
    LIMIT 100;
`
rows, err := db.Query(query)
if err != nil {
    log.Printf("query error: %v\n", err)
    http.Error(w, "internal server error", http.StatusInternalServerError)
    return
}

Key Considerations:

• Explicit Column Selection: Never execute SELECT * in production code. Explicitly selecting column names (id, title, etc.) guarantees that column order and data types remain stable even if database migrations alter column positions or add new fields.
• Sorting and Bounding: Default database iteration orders are indeterminate without an explicit ORDER BY. Adding ORDER BY created_at DESC ensures that newly created records appear first. Appending a hard LIMIT 100 shields the database engine and the application memory allocator from unbounded queries before pagination is introduced.
• Context Awareness: While db.Query() is the standard entry-level call, production-grade applications should transition to db.QueryContext(ctx, query). This allows client cancellations, network drops, or gateway timeouts to automatically cancel the running SQL query on the database server.

3. Resource Management & Connection Leaks (defer rows.Close())

The object returned by db.Query is of type *sql.Rows. Behind the scenes, *sql.Rows holds an active, reserved database connection from the connection pool. If this resource is not explicitly released, that database connection remains permanently checked out and unavailable to other routines. Under production traffic, omitting row cleanup will exhaust the connection pool, bringing the entire application to an abrupt standstill.

rows, err := db.Query(query)
if err != nil {
    log.Printf("query error: %v\n", err)
    http.Error(w, "internal server error", http.StatusInternalServerError)
    return
}
defer rows.Close()

Why Position Matters for defer

The statement defer rows.Close() must be placed immediately after checking that err == nil.

• If placed before the error check and db.Query() fails, rows will be nil, causing a runtime panic when calling .Close() on a nil pointer.
• If placed at the end of the function instead of deferred early, any error or early return occurring midway through the function will bypass the cleanup, silently leaking the connection.

4. The Mental Model of Row Cursors

A common beginner misconception is assuming that db.Query loads all matching database records into an in-memory array immediately. It does not. Instead, *sql.Rows represents an open cursor (a pointer) over an active database result stream.

Real-Life Analogy: The Flatbed Document Scanner

Imagine an office document scanner with a stack of paper. The scanner does not magically ingest the entire stack simultaneously. Instead, the scanner arm rests above the first sheet. Calling rows.Next() pulls the next physical sheet under the glass. Calling rows.Scan() takes the digital snapshot of that page and saves the values into designated storage slots on your computer. When the paper tray is empty, the feeder stops.

When initialized, the cursor rests immediately before the first row. Calling rows.Next() shifts the cursor down to the first record, returning true if a row exists, and false when the cursor passes the final record or experiences a read error.

5. Mapping Relational Rows to Structs (rows.Scan)

To transfer tabular data from the database cursor into Go’s runtime memory, a domain struct must be defined to model the entity.

type listing struct {
	ID          string    `json:"id"`
	Title       string    `json:"title"`
	Description string    `json:"description"`
	Price       string    `json:"price"`
	City        string    `json:"city"`
	CreatedAt   time.Time `json:"created_at"`
}

Design Choice: The struct name begins with a lowercase letter (listing), designating it as unexported (package-private). If this struct is exclusively used inside this handler package to format responses, keeping it package-private prevents external packages from coupling directly to its internal schema.

Implementing the Iteration Loop

Scanning requires passing the memory addresses (pointers) of each struct field directly into rows.Scan().

var listings []listing

for rows.Next() {
    var l listing
    err := rows.Scan(
        &l.ID,
        &l.Title,
        &l.Description,
        &l.Price,
        &l.City,
        &l.CreatedAt,
    )
    if err != nil {
        log.Printf("scan error: %v\n", err)
        http.Error(w, "internal server error", http.StatusInternalServerError)
        return
    }
    listings = append(listings, l)
}

Strict Rules of rows.Scan:

1. Pointer Semantics: rows.Scan mutates variables in place. Passing values (l.ID) instead of memory addresses (&l.ID) triggers a reflection panic at runtime.
2. Positional Alignment: The arguments in rows.Scan(...) must match the columns in the SQL SELECT statement in exact 1-to-1 order and matching data types:
   • Column 1: id $\rightarrow$ &l.ID
   • Column 2: title $\rightarrow$ &l.Title
   • Column 3: description $\rightarrow$ &l.Description
   • Column 4: price $\rightarrow$ &l.Price
   • Column 5: city $\rightarrow$ &l.City
   • Column 6: created_at $\rightarrow$ &l.CreatedAt

6. The Silent Failure Trap: Checking rows.Err()

A critical edge case frequently missed in naive tutorials involves how rows.Next() terminates.

According to Go's standard library specification:

• rows.Next() returns true upon finding a valid row.
• rows.Next() returns false when:
  1. The result set reaches the end of its records (clean EOF).
  2. A fatal network failure, socket timeout, or database engine crash occurs while streaming the next row.

If you do not inspect rows.Err() after the loop terminates, your application cannot differentiate between reaching the end of the query and an abnormal mid-stream termination. In that failure scenario, the code proceeds forward, serializing a partial list of 50 rows instead of 100, silently corrupting client-side state.

Defensive Post-Iteration Verification

if err := rows.Err(); err != nil {
    log.Printf("rows iteration error: %v\n", err)
    http.Error(w, "internal server error", http.StatusInternalServerError)
    return
}

Always invoke rows.Err() immediately after the for rows.Next() loop exits to verify that iteration completed without network or driver interruptions.

7. HTTP Response Hygiene: Headers and Status Codes

Before serializing the payload onto the network, the HTTP response envelope must be properly configured.

w.Header().Set("Content-Type", "application/json")
w.WriteHeader(http.StatusOK)

Note on Execution Order: In Go's http.ResponseWriter, the first call to w.Write() or w.WriteHeader() commits the HTTP status code and header block to the wire. Any subsequent attempts to alter w.Header().Set() after bytes are written will have no effect.

8. JSON Serialization: json.Marshal vs. json.NewEncoder

Go offers two primary mechanisms in encoding/json to turn structs into JSON:

// Approach B: Production Streaming Pipeline
if err := json.NewEncoder(w).Encode(listings); err != nil {
    log.Printf("encoding error: %v\n", err)
}

Comparative Analysis: Serialization Strategies

Because http.ResponseWriter implements the standard io.Writer interface, json.NewEncoder(w).Encode(listings) streams the payload directly over the connection.

9. Controlling Data Leaks via JSON Struct Tags

By default, the Go JSON serializer mirrors the exact field names defined on the struct. Because exported struct fields in Go must start with an uppercase letter to be visible outside their immediate package (e.g., Title), untagged structs leak PascalCase fields to clients:

{"ID":"123","Title":"Cozy Apartment","Description":"...","Price":"1200"}

This violates web API conventions (which expect camelCase or snake_case) and reveals the internal language structures of your backend.

Struct Tag Annotations

To remap struct keys during serialization and deserialization, Go provides Struct Field Tags:

type listing struct {
	ID          string    `json:"id"`
	Title       string    `json:"title"`
	Description string    `json:"description"`
	Price       string    `json:"price"`
	City        string    `json:"city"`
	CreatedAt   time.Time `json:"created_at"`
}

Capabilities of Struct Tags:

• Field Renaming: json:"created_at" transforms an internal Go identifier like CreatedAt into standard database/API snake-case.
• Complete Decoupling: If an internal database column changes or a struct field is refactored, the outward-facing JSON contract remains stable, avoiding breaking changes for downstream API consumers.
• Bidirectional Functionality: Struct tags govern both outbound serialization (Encoder / Marshal) and inbound payload parsing (Decoder / Unmarshal).

10. Complete Production-Grade Implementation

Below is the complete, cohesive implementation of the database read handler incorporating dependency injection, structured queries, cursor cleanup, post-scan verification, and JSON streaming.

package handlers

import (
	"database/sql"
	"encoding/json"
	"log"
	"net/http"
	"time"
)

type listing struct {
	ID          string    `json:"id"`
	Title       string    `json:"title"`
	Description string    `json:"description"`
	Price       string    `json:"price"`
	City        string    `json:"city"`
	CreatedAt   time.Time `json:"created_at"`
}

// ListListingsHandler returns an http.HandlerFunc closed over *sql.DB.
func ListListingsHandler(db *sql.DB) http.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) {
		// 1. Prepare deterministic, bounded SQL query
		query := `
			SELECT id, title, description, price, city, created_at 
			FROM listings 
			ORDER BY created_at DESC 
			LIMIT 100;
		`

		// 2. Execute query against pool
		rows, err := db.Query(query)
		if err != nil {
			log.Printf("query execution failed: %v\n", err)
			http.Error(w, "internal server error", http.StatusInternalServerError)
			return
		}
		// 3. Guarantee pool connection release
		defer rows.Close()

		// 4. Initialize storage slice (defaults to empty, non-nil array in JSON)
		listings := make([]listing, 0)

		// 5. Iterate through cursor
		for rows.Next() {
			var l listing
			err := rows.Scan(
				&l.ID,
				&l.Title,
				&l.Description,
				&l.Price,
				&l.City,
				&l.CreatedAt,
			)
			if err != nil {
				log.Printf("row scan failed: %v\n", err)
				http.Error(w, "internal server error", http.StatusInternalServerError)
				return
			}
			listings = append(listings, l)
		}

		// 6. Check for mid-iteration network/cursor failures
		if err := rows.Err(); err != nil {
			log.Printf("cursor iteration interrupted: %v\n", err)
			http.Error(w, "internal server error", http.StatusInternalServerError)
			return
		}

		// 7. Set HTTP response headers
		w.Header().Set("Content-Type", "application/json")
		w.WriteHeader(http.StatusOK)

		// 8. Stream serialized JSON directly to response writer
		if err := json.NewEncoder(w).Encode(listings); err != nil {
			log.Printf("json encoding failed: %v\n", err)
		}
	}
}

11. Verification and Production Debugging

When testing the endpoint locally or observing deployed workloads in production, use standard CLI tools (curl) to verify both data payloads and response headers.

Executing the Request

curl -i http://localhost:8080/listings

Analyzing the Response Envelope

HTTP/1.1 200 OK
Content-Type: application/json
Date: Mon, 24 Feb 2025 12:00:00 GMT
Content-Length: 324

[
  {
    "id": "8f8373b4-5ef9-40eb-8fcf-52a51c9d2f62",
    "title": "Downtown Studio",
    "description": "Charming loft apartment in city center",
    "price": "1500",
    "city": "Seattle",
    "created_at": "2025-02-24T11:45:00Z"
  }
]

Common Production Debugging Checkpoints:

1. Empty JSON Response ([]):
   • Ensure the database is seeded.
   • If using var listings []listing without initialization, an empty result set marshals to null in JSON. Using make([]listing, 0) ensures an empty query serializes to [].
2. Missing or Incomplete Output Fields:
   • Verify that all struct field names are Exported (capitalized). Fields declared as id string (lowercase) are unexported in Go and will be ignored by the encoding/json package, resulting in missing keys in the output payload.
3. Database Connection Starvation:
   • Check database metrics. If connections climb monotonically until reaching max_connections, locate missing defer rows.Close() statements across your query handlers.

Summary and Key Takeaways

• Closure Factory Pattern: Injects runtime dependencies like *sql.DB into HTTP handlers cleanly without resorting to dangerous package-level global variables.
• Resource Discipline: Every call to db.Query must be accompanied by an immediate, deferred rows.Close() placed after the initial err != nil check to prevent pool exhaustion.
• Active Cursor Semantics: *sql.Rows functions as a cursor, pulling one row at a time across the network via rows.Next() rather than loading the entire query into memory upfront.
• Pointer-Based Scanning: rows.Scan() requires pointers to struct fields. The order of arguments passed to rows.Scan() must match the SQL SELECT projection identically.
• Preventing Silent Truncation: rows.Next() returns false both on EOF and on stream failures. You must check rows.Err() after loop termination to catch aborted reads.
• Streaming Over Buffering: Utilizing json.NewEncoder(w).Encode(...) streams JSON directly to the underlying http.ResponseWriter socket, bypassing unnecessary intermediary buffer allocations.
• Schema Decoupling: Struct tags (json:"...") decouple internal Go struct names from the public API schema, enforcing consistent JSON naming conventions while protecting internal domain structures.