pgx

The pgconn package is a low-level PostgreSQL database driver that operates at a level similar to the C library libpq. It is designed to be the foundation for higher-level libraries like pgx.

Recommendation: Most applications should use a higher-level library (like pgx) for normal queries. Use pgconn directly only when you require low-level access to specific PostgreSQL functionality that higher-level abstractions do not expose.

The pgx toolkit and driver include several advanced features for PostgreSQL development:

• Performance: Automatic statement preparation and caching, single-round trip query mode, and binary format support for custom types.
• Data Handling: Support for ~70 PostgreSQL types, COPY protocol for bulk loads, and conversion of PostgreSQL arrays to Go slices.
• Advanced PostgreSQL Features: LISTEN / NOTIFY, hstore, json/jsonb, and large object support.
• Connectivity: Full TLS control and a connection pool with after-connect hooks.
• Type Mapping: Maps inet and cidr to netip.Addr and netip.Prefix; supports database/sql.Scanner and driver.Valuer for custom types.

The pgx driver provides two ways to interact with PostgreSQL:

1. The pgx interface: A low-level, high-performance interface that exposes PostgreSQL-specific features like LISTEN / NOTIFY and COPY. This is the recommended interface if your application only targets PostgreSQL and you do not require other libraries that depend on the standard database/sql interface.

2. The database/sql interface: An adapter that allows pgx to work with Go's standard database/sql package. This is useful for compatibility with existing libraries, but it is slower and lacks access to many PostgreSQL-specific features.

You can also convert a database/sql connection to the lower-level pgx interface as needed.

The URL shortener is a sample REST service that uses pgx to connect to a PostgreSQL database. To run it, follow these steps:

1. Initialize the database schema: Create a PostgreSQL database and execute the contents of structure.sql to set up the required tables.
2. Configure connection settings: Provide the database connection details using the DATABASE_URL environment variable or standard PostgreSQL environment variables (e.g., PGHOST, PGPORT, PGUSER, PGPASSWORD, PGDATABASE).
3. Run the service: Execute the main Go application.

go run main.go

The todo application configures its connection to PostgreSQL using the DATABASE_URL or standard PostgreSQL environment variables (such as PGHOST, PGUSER, PGDATABASE, etc.).

You can either export the variables in your shell session or prefix the command execution with them.

# Option 1: Export variables first
export PGDATABASE=todo
./todo list

# Option 2: Prefix the command
PGDATABASE=todo ./todo list

Run the pgfortune mock PostgreSQL server by executing the command in your terminal. By default, it listens on 127.0.0.1:15432 and responds to every query with the output of fortune | cowsay -f elephant.

You can customize the behavior using the following arguments:

• listen: Specify the address and port to listen on.
• response-command: Specify a custom command to run for every query response.

$ pgfortune

Follow these steps to build and run the complete todo lifecycle:

1. Initialize the database and schema.
2. Build the Go binary.
3. Set the PGDATABASE environment variable.
4. Execute commands to add, list, update, and remove tasks.

createdb todo
psql todo < structure.sql
go build
export PGDATABASE=todo
./todo list
./todo add 'Learn Go'
./todo list
./todo update 1 'Learn more Go'
./todo list
./todo remove 1
./todo list

To use pgfortune, you must first install the system dependencies fortune and cowsay using your Unix package manager (e.g., apt, yum, or brew). Then, install the pgfortune tool using Go:

go get -u github.com/jackc/pgproto3/example/pgfortune

The chat program configures its database connection using the DATABASE_URL environment variable or standard PostgreSQL environment variables (such as PGHOST, PGUSER, PGPASSWORD, etc.).

You can provide these variables by exporting them before execution or by prefixing the command.

# Option 1: Export variables first
export PGHOST=/private/tmp
./chat

# Option 2: Prefix the execution
PGHOST=/private/tmp ./chat

To use the todo example, you must first create a PostgreSQL database and initialize the schema using the provided structure.sql file.

1. Create a new database named todo.
2. Run the structure.sql file against that database using psql.

createdb todo
psql todo < structure.sql

To use Conn.CopyFrom, you must provide an object that implements the CopyFromSource interface. This interface allows pgx to iterate through your data rows.

Interface Definition:

type CopyFromSource interface {
    // Next returns true if there is another row and makes the next row data
    // available to Values(). When there are no more rows available or an error
    // has occurred it returns false.
    Next() bool

    // Values returns the values for the current row.
    Values() ([]any, error)

    // Err returns any error that has been encountered by the CopyFromSource. If
    // this is not nil *Conn.CopyFrom will abort the copy.
    Err() error
}