Go by Example: Using PostgreSQL

Go 1.23

Connect to PostgreSQL databases using the `lib/pq` driver. This example demonstrates the connection string format for PostgreSQL and shows how to verify the connection and execute basic queries.

Code

package main

import (
    "database/sql"
    "fmt"
    "log"

    _ "github.com/lib/pq"
)

func main() {
    // Connection string
    connStr := "user=pqgotest dbname=pqgotest sslmode=verify-full"
    db, err := sql.Open("postgres", connStr)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // Ping to verify connection
    if err := db.Ping(); err != nil {
        log.Fatal(err)
    }

    fmt.Println("Successfully connected to PostgreSQL")
    
    // Example query
    var version string
    err = db.QueryRow("SELECT version()").Scan(&version)
    if err != nil {
        log.Fatal(err)
    }
    fmt.Println("PostgreSQL version:", version)
}

Explanation

PostgreSQL is a robust, open-source object-relational database system known for its reliability and feature robustness. While the standard library's database/sql interface provides a generic way to interact with databases, choosing the right driver is crucial. Historically, lib/pq was the default choice, but for modern high-performance applications, pgx is highly recommended due to its support for the PostgreSQL binary protocol and active maintenance.

Essential Best Practices:

Connection Pooling: Never open a new connection for every request. Use a connection pool (managed automatically by sql.DB or explicitly via pgxpool) to reuse connections, significantly reducing latency and server load.
Security: Always enforce SSL/TLS in production by setting sslmode=verify-full in your connection string to prevent Man-in-the-Middle attacks.
Prepared Statements: Use prepared statements (db.Prepare) or parameterized queries ($1, $2) to prevent SQL injection vulnerabilities and allow the database to cache execution plans.

Code Breakdown

8

_ "github.com/lib/pq" imports the driver anonymously. This executes the package's init() function, registering "postgres" with the database/sql package without polluting the namespace.

13

The connection string (DSN) defines authentication and connection parameters. "sslmode=verify-full" is critical for production security, ensuring the server's certificate is trusted.

14

sql.Open initializes the database object but does NOT establish a connection immediately. It validates the arguments and prepares the internal structures.

21

db.Ping() forces a connection to the database to verify reachability and credentials. This is often the first time a network packet is actually sent.

29

db.QueryRow executes a query that returns at most one row. It returns a *Row object that we immediately use to Scan values into variables.

29

Scan(&version) copies the column value from the matched row into the variable pointed to by &version. It handles type conversion automatically.

Samplebadu

Go by Example: Using PostgreSQL

Code

Explanation

Code Breakdown