The query that broke production
A developer ships a dashboard endpoint that loads user profiles. In local testing, the ORM query finishes in twelve milliseconds. In production, the same endpoint takes four seconds and exhausts the database connection pool. The ORM generated twenty separate queries instead of one join. The developer rewrites the endpoint with a single raw SQL statement, adds explicit column mapping, and the latency drops to eighteen milliseconds. The fix works, but now the codebase contains two different data access patterns, and the team argues about which one belongs in the repository layer.
This tension appears in nearly every Go project that touches a database. Raw SQL gives you exact control over execution plans, indexing, and memory usage. Object-relational mappers hide the mapping work, enforce struct consistency, and speed up early development. Neither approach is universally correct. The right choice depends on query complexity, team size, and how much you value explicitness over convenience.
What raw SQL and ORMs actually do
Raw SQL in Go means sending a string to the database driver and reading the result set row by row. The standard library package database/sql handles the heavy lifting: connection pooling, statement preparation, and type conversion. You write the query, you declare the destination variables, and you scan the results into them. The database does the work. Go just moves the data across the boundary.
An ORM sits between your Go code and the driver. You define structs with tags, and the library translates method calls into SQL. It handles joins, pagination, and updates by inspecting your struct fields. When the query returns, the ORM instantiates structs and populates them automatically. You trade explicit query control for developer velocity and type safety.
Think of raw SQL as a direct line to the database engine. You dial the number, you speak the exact words, and you get exactly what you asked for. An ORM is a translation layer that guesses what you meant, rewrites it into the database's language, and hands you back a familiar Go object. The translation saves time until the guess is wrong.
The simplest raw SQL call
Here is the minimal pattern for querying a single table with the standard library.
package main
import (
"database/sql"
"fmt"
"log"
)
func main() {
// Open returns a pool, not a single connection.
db, err := sql.Open("postgres", "user=postgres dbname=app sslmode=disable")
if err != nil {
log.Fatal(err)
}
defer db.Close() // Close the pool when the program exits.
// Use a placeholder to prevent SQL injection.
rows, err := db.Query("SELECT id, name FROM users WHERE age > $1", 18)
if err != nil {
log.Fatal(err)
}
defer rows.Close() // Mandatory: releases the underlying connection.
// Iterate row by row to keep memory usage constant.
for rows.Next() {
var id int
var name string
// Scan copies data from the driver buffer into Go variables.
if err := rows.Scan(&id, &name); err != nil {
log.Fatal(err)
}
fmt.Printf("ID: %d, Name: %s\n", id, name)
}
// Check for iteration errors after the loop.
if err := rows.Err(); err != nil {
log.Fatal(err)
}
}
The code looks verbose compared to a single ORM call. That verbosity is intentional. Every step is visible: opening the pool, preparing the query, iterating rows, scanning columns, and checking for errors. You know exactly when memory is allocated and when the connection returns to the pool.
Walking through the execution
When db.Query runs, it does not immediately open a network socket. It asks the connection pool for an idle connection. If none exists, it creates a new one up to the configured maximum. The driver then sends the query string to the database. Most drivers automatically prepare the statement behind the scenes, which means the database parses the query once and reuses the execution plan for subsequent calls.
rows.Next() fetches the next row from the driver buffer. It returns true if a row exists and false when the result set is exhausted. You call rows.Scan inside the loop to copy the column values into Go variables. The driver handles type conversion: a PostgreSQL bigint becomes a Go int64, a text column becomes a string. If the types do not match, the scan fails at runtime.
Calling defer rows.Close() is mandatory. The rows object holds a reference to the database connection. If you forget to close it, the connection stays checked out until the garbage collector runs, which can starve the pool and cause timeouts. The rows.Err() check at the end catches network drops or driver errors that occurred during iteration but were deferred until the loop finished.
Go's database/sql package is designed around explicit resource management. You open, you use, you close. The pool handles concurrency. You handle the lifecycle.
A realistic service layer
Production code rarely queries a single row in main. It lives in a repository function that accepts context, handles missing rows, and returns a typed slice.
package repository
import (
"context"
"database/sql"
"fmt"
)
type User struct {
ID int64
Name string
Age int
}
// FetchAdults returns users older than the given age.
func FetchAdults(ctx context.Context, db *sql.DB, minAge int) ([]User, error) {
// Context carries cancellation and deadlines to the driver.
query := `SELECT id, name, age FROM users WHERE age > $1 ORDER BY id`
rows, err := db.QueryContext(ctx, query, minAge)
if err != nil {
return nil, fmt.Errorf("query users: %w", err)
}
defer rows.Close()
var users []User
for rows.Next() {
var u User
// Scan directly into struct fields to reduce boilerplate.
if err := rows.Scan(&u.ID, &u.Name, &u.Age); err != nil {
return nil, fmt.Errorf("scan user row: %w", err)
}
users = append(users, u)
}
if err := rows.Err(); err != nil {
return nil, fmt.Errorf("iterate rows: %w", err)
}
// Return empty slice instead of nil for consistent JSON encoding.
if users == nil {
users = []User{}
}
return users, nil
}
The function follows standard Go conventions. Context is the first parameter, named ctx. Errors are wrapped with fmt.Errorf and the %w verb so callers can unwrap them later. The receiver pattern does not apply here because this is a package-level function, but if it were a method, the receiver name would be a short abbreviation like r or repo. The function returns a slice, not a pointer to a slice, because slices are already reference types and cheap to pass.
Notice the explicit column order in Scan. The database returns columns in the order you wrote them in the SELECT clause. If the schema changes and a column is added or reordered, Scan will panic or return garbage data. That is why many teams pair raw SQL with a lightweight wrapper like sqlx, which lets you scan directly into a struct using column names instead of positional arguments.
Where things go sideways
Raw SQL fails when the query shape changes frequently. Adding a filter, changing a join, or renaming a column requires updating strings, testing the new shape, and verifying the Scan arguments still match. The compiler will not catch a typo in a column name. You will only find out at runtime when the driver returns sql: no rows in result set or sql: expected 3 destination arguments in Scan, got 2.
ORMs fail when the generated query does not match your performance requirements. A single Find call might trigger an N+1 problem if the library loads associated records in separate queries instead of a join. Pagination can silently drop the LIMIT clause when you add an ORDER BY. Complex aggregations often require raw SQL subqueries anyway, which defeats the purpose of the abstraction.
Connection pool misconfiguration is the most common production bug. The default maximum idle connections is two. If your service handles fifty concurrent requests, the pool creates new connections on demand until it hits the OS file descriptor limit or the database max_connections setting. You will see driver: bad connection or too many connections errors under load. Setting db.SetMaxOpenConns and db.SetMaxIdleConns early prevents the pool from thrashing.
SQL injection happens when you concatenate user input into query strings. Always use placeholders. The driver escapes the values safely. If you forget and pass a raw string, the database might reject it with a syntax error, or worse, execute unintended commands. Go's type system does not protect you here. The placeholder syntax is your only defense.
Raw SQL is explicit. ORMs are convenient. Both break when you ignore their boundaries.
Picking your tool
Use raw SQL when you need exact control over execution plans, window functions, or database-specific extensions. Use raw SQL when the query shape is stable and performance profiling shows the mapping layer as a bottleneck. Use a lightweight wrapper like sqlx when you want struct scanning and named parameters without full ORM overhead. Use a full ORM when you are building a CRUD-heavy service, the schema changes frequently, and developer velocity matters more than micro-optimization. Use a query builder when you need dynamic filtering and conditional joins but still want to see the generated SQL. Use plain sequential code when you do not need concurrency: the simplest thing that works is usually the right thing.