The missing piece in your stack
You just finished building a REST API. The in-memory slice is holding your data, but production needs a real database. You spin up PostgreSQL, grab the credentials, and realize Go does not ship with a built-in PostgreSQL driver. The standard library gives you database/sql, but it is an interface, not a driver. You need a concrete implementation to actually talk to the wire.
How Go talks to databases
Go separates the database interface from the driver. database/sql defines how you query, but it needs a third-party package to translate those calls into PostgreSQL protocol messages. The pgx library is the modern standard for this. It speaks PostgreSQL directly, supports all the latest features, and gives you type-safe queries. Instead of fighting abstraction layers, you get a driver that maps closely to what the database actually understands.
A connection string tells the driver where to find the server and how to authenticate. It looks like a series of key-value pairs separated by spaces. The driver parses it, opens a TCP socket, negotiates the protocol version, and hands you a ready-to-use connection object.
Goroutines are cheap. Connections are not.
The simplest connection
Here is the most direct way to open a single connection to a local PostgreSQL instance.
package main
import (
"context"
"log"
"github.com/jackc/pgx/v5"
)
func main() {
// context.Background() provides a base context for the connection attempt.
// It carries cancellation signals and deadlines for the handshake.
ctx := context.Background()
// pgx.Connect parses the DSN, opens a TCP socket, and authenticates.
// It returns a single connection, not a pool.
conn, err := pgx.Connect(ctx, "host=localhost port=5432 user=postgres password=secret dbname=app")
if err != nil {
// log.Fatal prints the error and exits with code 1.
// Database failures at startup should stop the program.
log.Fatal(err)
}
// defer ensures the connection closes when main returns.
// Always pass a context to Close so it can respect timeouts.
defer conn.Close(ctx)
log.Println("Connected successfully")
}
The code above works for scripts and quick checks. It opens one TCP connection, uses it, and tears it down. Production services need more than one connection. They need a pool.
Don't open a new connection per request. Reuse what you have.
What happens under the hood
When pgx.Connect runs, it does several things in sequence. First, it resolves the hostname to an IP address. If you passed localhost, it checks both IPv4 and IPv6. Next, it opens a TCP socket to port 5432. The driver then sends a startup message containing the database name and user. PostgreSQL responds with authentication requests. pgx handles password hashing and sends the credentials. Once authenticated, the server switches to the normal query protocol.
The context.Context parameter is not optional. Go conventions require context as the first parameter for any function that might block or make network calls. The context carries cancellation signals. If your service receives a shutdown signal, the context tells the driver to abort the handshake and release resources. Without it, your program could hang indefinitely during a network partition.
TLS negotiation happens automatically if the server requires it. pgx reads the sslmode parameter from the connection string. The default is prefer, which means the driver attempts an encrypted connection but falls back to plaintext if the server does not support it. Set it to require or verify-full in production to enforce encryption and certificate validation.
Context is plumbing. Run it through every long-lived call site.
Running a real query
A connection is useless without data. Here is how you execute a parameterized query and scan the results into Go structs.
// QueryUser fetches a single user by ID using a prepared statement.
// It returns the user data or an error if the query fails.
func QueryUser(ctx context.Context, conn *pgx.Conn, id int) (map[string]interface{}, error) {
// Row executes the query and returns a single row.
// $1 is a placeholder that pgx safely escapes.
row := conn.QueryRow(ctx, "SELECT name, email FROM users WHERE id = $1", id)
// Scan maps the result columns into Go variables.
// It handles type conversion from PostgreSQL types to Go types.
var name, email string
if err := row.Scan(&name, &email); err != nil {
// pgx returns pgx.ErrNoRows when the query matches nothing.
// Distinguish between missing data and actual failures.
if err == pgx.ErrNoRows {
return nil, fmt.Errorf("user %d not found", id)
}
return nil, err
}
return map[string]interface{}{
"name": name,
"email": email,
}, nil
}
Parameterized queries prevent SQL injection. The driver sends the query structure and the values separately. PostgreSQL compiles the query plan once and reuses it. Never concatenate user input into SQL strings. The compiler will not stop you, but the database will reject malformed syntax or your application will leak data.
Trust the parameterized interface. Never build SQL with string concatenation.
Pool configuration and context
Opening a TCP connection takes milliseconds. Doing it for every HTTP request adds up fast. Connection pools keep a set of active connections alive and hand them out to goroutines as needed. When a goroutine finishes, it returns the connection to the pool instead of closing it.
Here is how you set up a pool with pgxpool.
package main
import (
"context"
"log"
"github.com/jackc/pgx/v5/pgxpool"
)
func main() {
ctx := context.Background()
// pgxpool.New creates a pool of connections instead of a single one.
// It starts with zero connections and grows up to MaxConns.
pool, err := pgxpool.New(ctx, "host=localhost port=5432 user=postgres password=secret dbname=app")
if err != nil {
log.Fatal(err)
}
// defer pool.Close() releases all connections when the program exits.
// It waits for active queries to finish before shutting down.
defer pool.Close()
// Ping verifies the pool can reach the database.
// It borrows a connection, sends a lightweight query, and returns it.
if err := pool.Ping(ctx); err != nil {
log.Fatal("pool ping failed:", err)
}
log.Println("Pool is ready")
}
The pool manages lifecycle automatically. It creates connections lazily, closes idle ones, and enforces maximum limits. You interact with the pool using the same query methods you would use on a single connection. The pool handles routing, error recovery, and connection validation behind the scenes.
Pool configuration matters. The default MaxConns is 4. That is fine for a small service. A busy API might need 20 or 50. Set MinConns to keep a baseline of warm connections ready. Use MaxConnLifetime to rotate connections before they hit PostgreSQL's statement_timeout or idle_in_transaction_session_timeout limits.
Monitor your pool metrics. pool.Stat() shows how many connections are in use, idle, and waiting. If the wait queue grows, your application is hitting the database harder than it can handle.
Common pitfalls and compiler errors
New Go developers run into a few predictable traps when working with databases.
Forgetting to pass a context causes goroutine leaks. If a query hangs and you never cancel the context, the goroutine waiting on the network call stays alive forever. The worst goroutine bug is the one that never logs. Always attach a timeout to long-running queries. context.WithTimeout(ctx, 5*time.Second) prevents a single slow query from holding a connection hostage.
Mixing database/sql and pgx directly creates type mismatches. database/sql uses generic sql.DB and sql.Rows types. pgx uses pgxpool.Pool and pgx.Rows. They are not interchangeable. If you pass a pgxpool.Pool to a function expecting *sql.DB, the compiler rejects it with cannot use pool (variable of type *pgxpool.Pool) as *sql.DB value in argument. Pick one style and stick to it.
Connection pool exhaustion happens when you set MaxConns too low or forget to return connections. Every active query holds a connection. If all connections are busy, new queries block until one frees up. If they block too long, the context times out and returns context deadline exceeded.
Transaction handling requires explicit commits. Opening a transaction with pool.Begin(ctx) returns a pgx.Tx object. You must call tx.Commit(ctx) or tx.Rollback(ctx). Forgetting to commit leaves data in a limbo state. Forgetting to rollback on error locks rows until the server kills the session. Wrap transaction logic in a closure and use defer tx.Rollback(ctx) as a safety net. Call tx.Commit(ctx) at the end and ignore the rollback error if commit succeeds.
The compiler rejects programs that ignore return values incorrectly. If you try to assign a connection to a variable without handling the error, you get assignment mismatch: 2 variables but pgx.Connect returns 3 values. Always capture the error. If you intentionally want to drop a return value, use the underscore. conn, _ := pgx.Connect(...) tells the compiler you considered the error and chose to ignore it. Use that sparingly with database calls. Ignoring connection errors usually means your application will crash later when it tries to query.
Goroutine leaks happen when the goroutine waits on a channel that never gets closed. Always have a cancellation path. The same rule applies to database queries. If a context is cancelled, the driver aborts the network read and returns control to your goroutine.
When to use what
Use pgx.Connect when you need a single connection for a script, a migration tool, or a quick diagnostic check. Use pgxpool.New when building a long-running service that handles concurrent requests. Use pgxpool.ParseConfig when you need to adjust pool settings, inject credentials from a secrets manager, or set custom timeouts. Use database/sql with the pgx driver when you are integrating with a legacy library that strictly requires the standard library interface. Use plain sequential code when you don't need concurrency: the simplest thing that works is usually the right thing.