DOCSEARCHbyAlgolia
Submit Docs

lib-pq Documentation

repository·master·Indexed 27 days ago

https://github.com/lib/pq

A Go driver for PostgreSQL that implements the database/sql interface. It supports all maintained versions of PostgreSQL and provides features such as bulk COPY imports, LISTEN/NOTIFY, and GSS/Kerberos authentication. The library includes specialized types for handling PostgreSQL arrays (e.g., pq.StringArray, pq.Int64Array) and provides the pq.Error type for inspecting specific database error codes.

Tokens
9K
Snippets
15
Records
83
Agent score
94%

What's inside lib-pq

  1. Connect to PostgreSQL using pq

    master

    To use pq with database/sql, import the driver with a blank identifier to register it. You can connect using a DSN string (key=value or postgresql:// URL) or by using the pq.Config struct.

    Important: sql.Open() only creates a connection pool and does not establish a connection. Always call db.Ping() to verify the connection is actually working. It is also recommended to include connect_timeout in your DSN to prevent indefinite waits during asynchronous connection attempts.

    package main

import (
    "database/sql"
    "log"

    _ "github.com/lib/pq" // To register the driver.
)

func main() {
    // Using DSN string
    db, err := sql.Open("postgres", "host=localhost dbname=pqgo connect_timeout=5")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // Verify connection
    err = db.Ping()
    if err != nil {
        log.Fatal(err)
    }
}

  2. Enable GSS/Kerberos authentication

    master

    By default, pq supports PASSWORD, MD5, and SCRAM-SHA256. For GSS/Kerberos, you must import the github.com/lib/pq/auth/kerberos module and register the provider in your init() function.

    import "github.com/lib/pq/auth/kerberos"

func init() {
	pq.RegisterGSSProvider(func() (pq.Gss, error) { return kerberos.NewGSS() })
}

  3. Perform bulk imports with COPY

    master
    To perform high-performance bulk imports, prepare a COPY [..] FROM STDIN statement within a transaction. Use the returned sql.Stmt to repeatedly execute the copy command. Once all data is sent, call Exec() with no arguments to flush the remaining buffered data.

  4. Use Kerberos (GSSAPI) authentication

    master

    To use Kerberos/GSSAPI authentication, you must register a GSSAPI provider by importing github.com/lib/pq/auth/kerberos.

    If you provide a KrbSpn, the driver will use that Service Principal Name. Otherwise, it defaults to the postgres service, which can be overridden using the KrbSrvname configuration option.

  5. Perform bulk imports using the COPY protocol

    master

    The pq driver supports the PostgreSQL COPY FROM STDIN protocol for high-performance bulk data loading. To use it, you must execute the COPY command within a transaction.

    There are two ways to stream data:

    1. Structured Data: Use Exec([]driver.Value) to pass a slice of values. The driver handles text encoding and tab-separation.
    2. Raw Text: Use CopyData(context.Context, string) to insert raw, pre-formatted text lines into the stream.

    Important: Because data insertion is asynchronous, you must call Exec(nil) or Close() to synchronize the stream and retrieve any errors that occurred during the background data transfer. Stmt.Close() does not return these errors directly to the user.

  6. Register the pq driver with database/sql

    master
    The pq package automatically registers itself as a PostgreSQL driver named postgres during initialization. You can then use it with the standard library's database/sql package.

  7. Run development database environments with Docker Compose

    master

    The pqgo project provides a compose.yaml file to spin up various PostgreSQL-compatible database environments for testing and development. You can use Docker Compose profiles to select specific database versions or middleware like PgBouncer or PgPool.

    Available profiles include:

    • pgbouncer: Starts a PgBouncer service.
    • pgpool: Starts a PgPool service.
    • cockroach: Starts a CockroachDB instance.
    • pg14, pg15, pg16, pg17, pg18, pg19: Starts specific PostgreSQL versions.

    To run a specific environment, use the --profile flag with your docker compose command.

  8. Listen to PostgreSQL notifications with Listener

    master

    The Listener type provides a high-level interface for receiving LISTEN/NOTIFY messages from a PostgreSQL database. It automatically handles connection loss and re-establishes connections using an exponential backoff strategy.

    To use a Listener:

    1. Create a new listener using NewListener.
    2. Start listening to specific channels using Listen(channel).
    3. Consume notifications from the Notify channel.

    Note: When a connection is re-established, a nil notification is sent on the Notify channel to signal a reconnection event.

  9. Debug PostgreSQL protocol communication

    master

    To debug the communication between the driver and PostgreSQL, set the PQGO_DEBUG=1 environment variable. This will print the raw client/server messages to stderr.

    PQGO_DEBUG=1 go test -run TestSimpleQuery

  10. Configure connection using pq.Config

    master

    For more programmatic control, you can use the pq.Config struct and sql.OpenDB with a connector. This allows you to set parameters like Host, Port, User, and ConnectTimeout directly.

    cfg := pq.Config{
	Host:           "localhost",
	Port:           5432,
	User:           "pqgo",
	ConnectTimeout: 5 * time.Second,
}

c, err := pq.NewConnectorConfig(cfg)
if err != nil {
	log.Fatal(err)
}

// Create connection pool.
db := sql.OpenDB(c)
defer db.Close()

err = db.Ping()
if err != nil {
	log.Fatal(err)
}

  11. Handle PostgreSQL errors with pq.Error

    master

    PostgreSQL errors are returned as pq.Error. You can use pq.As to convert a standard error into a pq.Error to inspect specific error codes (e.g., pqerror.UniqueViolation).

    • Error(): Contains the error message and code (e.g., pq: duplicate key value... (23505)).
    • ErrorWithDetail(): Includes DETAIL and CONTEXT fields if provided by the server.
    pqErr := pq.As(err, pqerror.UniqueViolation)
if pqErr != nil {
  return fmt.Errorf("email %q already exsts", email)
}

  12. Use LISTEN/NOTIFY for real-time notifications

    master

    You can use pq.Listener to listen for notifications on specific PostgreSQL channels. The listener provides a Notify channel that receives notifications containing the channel name and extra data.

    l := pq.NewListener("dbname=pqgo", time.Second, time.Minute, nil)
defer l.Close()

err := l.Listen("coconut")
if err != nil {
    log.Fatal(err)
}

for {
    n := <-l.Notify
    if n == nil {
        fmt.Println("nil notify: closing Listener")
        return
    }
    fmt.Printf("notification on %q with data %q\n", n.Channel, n.Extra)
}