pgx

package
v1.2.0 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: May 7, 2014 License: MIT, MIT Imports: 18 Imported by: 0

README

pgx

PostgreSQL client library for Go

Description

Pgx is a database connection library designed specifically for PostgreSQL. There are features of PostgreSQL that are difficult or impossible to use with the standard Go library SQL interface. This library trades conformity with the standard interface for ease of use and the power that is available when working directly with PostgreSQL.

Features

Below are some of the standout features of pgx.

Simple Query Interface

Pgx has easy to use functions for common query operations like SelectValue, SelectValues, SelectRow, and SelectRows. These can be easier to use than the standard Scan interface. These directly return interface{}, []interface{}, map[string]interface{}, and []map[string]interface{} respectively. SelectFunc offers custom row by row processing.

if widgets, err := conn.SelectRows("select name, weight from widgets where type=$1", type); err != nil {
    for w := range widgets {
        fmt.Printf("%v has a weight of %v.", widgets["name"], widgets["weight"])
    }
}
Prepared Statements

Prepared statements are easy to use in pgx. Just call Prepare with the name of the statement and the SQL. To execute a prepared statement just pass the name of the statement into a Select* or Execute command as the SQL text. It will automatically detect that it is the name of a prepared statement and execute it.

if err := conn.Prepare("getTime", "select now()"); err == nil {
    // handle err
}
if time, err := conn.SelectValue("getTime"); err != nil {
    // do something with time
}

Prepared statements will use the binary transmission format for types that have a binary transcoder available (this can substantially reduce overhead when using the bytea type).

Explicit Connection Pool

Connection pool usage is explicit and configurable. In pgx, a connection can be created and managed directly, or a connection pool with a configurable maximum connections can be used. Also, the connection pool offers an after connect hook that allows every connection to be automatically setup before being made available in the connection pool. This is especially useful to ensure all connections have the same prepared statements available or to change any other connection settings.

It also delegates Select* and Execute functions to an automatically checked out and released connection so you can avoid manually acquiring and releasing connections when you do not need that level of control.

if widgets, err := pool.SelectRows("select * from widgets where type=$1", type); err != nil {
    // do something with widgets
}
Transactions

Transactions are are used by passing a function to the Transaction function. This function ensures that the transaction is committed or rolled back automatically. The TransactionIso variant creates a transaction with a specified isolation level.

committed, err := conn.TransactionIso("serializable", func() bool {
    // do something with transaction
    return true // return true to commit / false to rollback
})
Listen / Notify

Pgx can listen to the PostgreSQL notification system with the WaitForNotification function. It takes a maximum time to wait for a notification.

if notification, err := conn.WaitForNotification(time.Second); err != nil {
    // do something with notification
}
TLS

The pgx ConnectionParameters struct has a TLSConfig field. If this field is nil, then TLS will be disabled. If it is present, then it will be used to configure the TLS connection.

Custom Transcoder Support

Pgx includes transcoders for the common data types like integers, floats, strings, dates, and times that have direct mappings between Go and SQL. Transcoders can be added for additional types like point, hstore, numeric, etc. that do not have direct mappings in Go. pgx.ValueTranscoders is a map of PostgreSQL OID's to transcoders. All that is needed to add or change how a data type is to set that OID's transcoder. See example_value_transcoder_test.go for an example of a custom transcoder for the PostgreSQL point type.

SelectValueTo

There are some cases where Go is used as an HTTP server that is directly relaying single values from PostgreSQL (such as JSON or binary blobs). SelectValueTo copies the single returned value directly from PostgreSQL to a io.Writer. This can be faster than SelectValue then write especially when the values are at least many KB in size.

Null Mapping

As pgx uses interface{} for all values SQL nulls are mapped to nil. This eliminates the need for wrapping values in structs that include a boolean for the null possibility. On the other hand, returned values usually must be type asserted before use. It also presents difficulties dealing with complex types such as arrays. pgx directly maps a Go []int32 to a PostgreSQL int4[]. The problem is the PostgreSQL array can include nulls, but the Go slice cannot. Array transcoding should be considered experimental. On the plus side, because of the pluggable transcoder support, an application that wished to handle arrays (or any types) differently can easily override the default transcoding (so even using a strict with value and null fields would simply be a matter of changing transcoders).

Logging

Pgx defines the pgx.Logger interface. A value that satisfies this interface used as part of ConnectionOptions or ConnectionPoolOptions to enable logging of pgx activities.

Testing

Pgx supports multiple connection and authentication types. Setting up a test environment that can test all of them can be cumbersome. In particular, Windows cannot test Unix domain socket connections. Because of this pgx will skip tests for connection types that are not configured.

Normal Test Environment

To setup the normal test environment run the following SQL:

create user pgx_md5 password 'secret';
create database pgx_test;

Next open connection_settings_test.go.example and make a copy without the .example. If your PostgreSQL server is accepting connections on 127.0.0.1, then you are done.

Connection and Authentication Test Environment

Complete the normal test environment setup and also do the following.

Run the following SQL:

create user pgx_none;
create user pgx_pw password 'secret';

Add the following to your pg_hba.conf:

If you are developing on Unix with domain socket connections:

local  pgx_test  pgx_none  trust
local  pgx_test  pgx_pw    password
local  pgx_test  pgx_md5   md5

If you are developing on Windows with TCP connections:

host  pgx_test  pgx_none  127.0.0.1/32 trust
host  pgx_test  pgx_pw    127.0.0.1/32 password
host  pgx_test  pgx_md5   127.0.0.1/32 md5

Documentation

Overview

Package pgx is a PostgreSQL database driver.

It does not implement the standard database/sql interface.

Example (ConnectionSelectFunc) 
conn, err := pgx.Connect(*defaultConnectionParameters)
if err != nil {
	fmt.Printf("Unable to establish connection: %v", err)
	return
}

onDataRow := func(r *pgx.DataRowReader) error {
	fmt.Println(r.ReadValue())
	return nil
}

err = conn.SelectFunc("select generate_series(1,$1)", onDataRow, 5)
if err != nil {
	fmt.Println(err)
}

Output:

1
2
3
4
5
Example (ConnectionSelectRows) 
conn, err := pgx.Connect(*defaultConnectionParameters)
if err != nil {
	fmt.Printf("Unable to establish connection: %v", err)
	return
}

var rows []map[string]interface{}
if rows, err = conn.SelectRows("select generate_series(1,$1) as number", 5); err != nil {
	fmt.Printf("Error selecting rows: %v", err)
	return
}
for _, r := range rows {
	fmt.Println(r["number"])
}

Output:

1
2
3
4
5
Example (CustomValueTranscoder) 
package main

import (
	"fmt"
	"github.com/JackC/pgx"
	"regexp"
	"strconv"
)

var pointRegexp *regexp.Regexp = regexp.MustCompile(`^\((.*),(.*)\)$`)

type Point struct {
	x float64
	y float64
}

func (p Point) String() string {
	return fmt.Sprintf("%v, %v", p.x, p.y)
}

func main() {
	pgx.ValueTranscoders[pgx.Oid(600)] = &pgx.ValueTranscoder{
		DecodeText: decodePointFromText,
		EncodeTo:   encodePoint}

	conn, err := pgx.Connect(*defaultConnectionParameters)
	if err != nil {
		fmt.Printf("Unable to establish connection: %v", err)
		return
	}

	v, _ := conn.SelectValue("select point(1.5,2.5)")
	fmt.Println(v)
}

func decodePointFromText(mr *pgx.MessageReader, size int32) interface{} {
	s := mr.ReadString(size)
	match := pointRegexp.FindStringSubmatch(s)
	if match == nil {
		return pgx.ProtocolError(fmt.Sprintf("Received invalid point: %v", s))
	}

	var err error
	var p Point
	p.x, err = strconv.ParseFloat(match[1], 64)
	if err != nil {
		return pgx.ProtocolError(fmt.Sprintf("Received invalid point: %v", s))
	}
	p.y, err = strconv.ParseFloat(match[2], 64)
	if err != nil {
		return pgx.ProtocolError(fmt.Sprintf("Received invalid point: %v", s))
	}
	return p
}

func encodePoint(w *pgx.MessageWriter, value interface{}) {
	p := value.(Point)
	s := fmt.Sprintf("point(%v,%v)", p.x, p.y)
	w.Write(int32(len(s)))
	w.WriteString(s)
}

Output:

1.5, 2.5

Index

Examples

Constants

This section is empty.

Variables

View Source 
var NotificationTimeoutError = errors.New("Notification Timeout")
View Source 
var ValueTranscoders map[Oid]*ValueTranscoder

ValueTranscoders is used to transcode values being sent to and received from the PostgreSQL server. Additional types can be transcoded by adding a *ValueTranscoder for the appropriate Oid to the map.

Functions

func SplitArrayText 

func SplitArrayText(text string) (elements []string)

SplitArrayText is used by array transcoders to split array text into elements

Types

type Connection 

type Connection struct {
	Pid           int32             // backend pid
	SecretKey     int32             // key to use to send a cancel query message to the server
	RuntimeParams map[string]string // parameters that have been reported by the server

	TxStatus byte
	// contains filtered or unexported fields
}

Connection is a PostgreSQL connection handle. It is not safe for concurrent usage. Use ConnectionPool to manage access to multiple database connections from multiple goroutines.

func Connect 

func Connect(parameters ConnectionParameters) (c *Connection, err error)

Connect establishes a connection with a PostgreSQL server using parameters. One of parameters.Socket or parameters.Host must be specified. parameters.User will default to the OS user name. Other parameters fields are optional.

func (*Connection) CauseOfDeath 

func (c *Connection) CauseOfDeath() error

func (*Connection) Close 

func (c *Connection) Close() (err error)

func (*Connection) Deallocate 

func (c *Connection) Deallocate(name string) (err error)

Deallocate released a prepared statement

func (*Connection) Execute 

func (c *Connection) Execute(sql string, arguments ...interface{}) (commandTag string, err error)

Execute executes sql. sql can be either a prepared statement name or an SQL string. arguments will be sanitized before being interpolated into sql strings. arguments should be referenced positionally from the sql string as $1, $2, etc.

func (*Connection) IsAlive 

func (c *Connection) IsAlive() bool

func (*Connection) Listen 

func (c *Connection) Listen(channel string) (err error)

Listen establishes a PostgreSQL listen/notify to channel

func (*Connection) Prepare 

func (c *Connection) Prepare(name, sql string) (err error)

Prepare creates a prepared statement with name and sql. sql can contain placeholders for bound parameters. These placeholders are referenced positional as $1, $2, etc.

func (*Connection) QuoteIdentifier 

func (c *Connection) QuoteIdentifier(input string) (output string)

QuoteIdentifier escapes and quotes an identifier making it safe for interpolation into an SQL string

func (*Connection) QuoteString 

func (c *Connection) QuoteString(input string) (output string)

QuoteString escapes and quotes a string making it safe for interpolation into an SQL string.

func (*Connection) SanitizeSql 

func (c *Connection) SanitizeSql(sql string, args ...interface{}) (output string, err error)

SanitizeSql substitutely args positionaly into sql. Placeholder values are $ prefixed integers like $1, $2, $3, etc. args are sanitized and quoted as appropriate.

func (*Connection) SelectFunc 

func (c *Connection) SelectFunc(sql string, onDataRow func(*DataRowReader) error, arguments ...interface{}) (err error)

SelectFunc executes sql and for each row returned calls onDataRow. sql can be either a prepared statement name or an SQL string. arguments will be sanitized before being interpolated into sql strings. arguments should be referenced positionally from the sql string as $1, $2, etc.

SelectFunc calls onDataRow as the rows are received. This means that it does not need to simultaneously store the entire result set in memory. It also means that it is possible to process some rows and then for an error to occur. Callers should be aware of this possibility.

func (*Connection) SelectRow 

func (c *Connection) SelectRow(sql string, arguments ...interface{}) (row map[string]interface{}, err error)

SelectRow executes sql and returns a map representing the found row. sql can be either a prepared statement name or an SQL string. arguments will be sanitized before being interpolated into sql strings. arguments should be referenced positionally from the sql string as $1, $2, etc.

Returns a NotSingleRowError if exactly one row is not found

func (*Connection) SelectRows 

func (c *Connection) SelectRows(sql string, arguments ...interface{}) (rows []map[string]interface{}, err error)

SelectRows executes sql and returns a slice of maps representing the found rows. sql can be either a prepared statement name or an SQL string. arguments will be sanitized before being interpolated into sql strings. arguments should be referenced positionally from the sql string as $1, $2, etc.

func (*Connection) SelectValue 

func (c *Connection) SelectValue(sql string, arguments ...interface{}) (v interface{}, err error)

SelectValue executes sql and returns a single value. sql can be either a prepared statement name or an SQL string. arguments will be sanitized before being interpolated into sql strings. arguments should be referenced positionally from the sql string as $1, $2, etc.

Returns a UnexpectedColumnCountError if exactly one column is not found Returns a NotSingleRowError if exactly one row is not found

func (*Connection) SelectValueTo 

func (c *Connection) SelectValueTo(w io.Writer, sql string, arguments ...interface{}) (err error)

sql can be either a prepared statement name or an SQL string. arguments will be sanitized before being interpolated into sql strings. arguments should be referenced positionally from the sql string as $1, $2, etc.

Returns a UnexpectedColumnCountError if exactly one column is not found Returns a NotSingleRowError if exactly one row is not found

func (*Connection) SelectValues 

func (c *Connection) SelectValues(sql string, arguments ...interface{}) (values []interface{}, err error)

SelectValues executes sql and returns a slice of values. sql can be either a prepared statement name or an SQL string. arguments will be sanitized before being interpolated into sql strings. arguments should be referenced positionally from the sql string as $1, $2, etc.

Returns a UnexpectedColumnCountError if exactly one column is not found

func (*Connection) Transaction 

func (c *Connection) Transaction(f func() bool) (committed bool, err error)

Transaction runs f in a transaction. f should return true if the transaction should be committed or false if it should be rolled back. Return value committed is if the transaction was committed or not. committed should be checked separately from err as an explicit rollback is not an error. Transaction will use the default isolation level for the current connection. To use a specific isolation level see TransactionIso

func (*Connection) TransactionIso 

func (c *Connection) TransactionIso(isoLevel string, f func() bool) (committed bool, err error)

TransactionIso is the same as Transaction except it takes an isoLevel argument that it uses as the transaction isolation level.

Valid isolation levels are: 

serializable
repeatable read
read committed
read uncommitted

func (*Connection) WaitForNotification 

func (c *Connection) WaitForNotification(timeout time.Duration) (*Notification, error)

WaitForNotification waits for a PostgreSQL notification for up to timeout. If the timeout occurs it returns pgx.NotificationTimeoutError

type ConnectionParameters 

type ConnectionParameters struct {
	Socket     string // path to unix domain socket (e.g. /private/tmp/.s.PGSQL.5432)
	Host       string // url (e.g. localhost)
	Port       uint16 // default: 5432
	Database   string
	User       string // default: OS user name
	Password   string
	MsgBufSize int         // Size of work buffer used for transcoding messages. For optimal performance, it should be large enough to store a single row from any result set. Default: 1024
	TLSConfig  *tls.Config // config for TLS connection -- nil disables TLS
	Logger     Logger
}

ConnectionParameters contains all the options used to establish a connection.

type ConnectionPool 

type ConnectionPool struct {
	// contains filtered or unexported fields
}

func NewConnectionPool 

func NewConnectionPool(parameters ConnectionParameters, options ConnectionPoolOptions) (p *ConnectionPool, err error)

NewConnectionPool creates a new ConnectionPool. parameters are passed through to Connect directly.

func (*ConnectionPool) Acquire 

func (p *ConnectionPool) Acquire() (c *Connection, err error)

Acquire takes exclusive use of a connection until it is released.

func (*ConnectionPool) Close 

func (p *ConnectionPool) Close()

Close ends the use of a connection by closing all underlying connections.

func (*ConnectionPool) CurrentConnectionCount 

func (p *ConnectionPool) CurrentConnectionCount() int

func (*ConnectionPool) Execute 

func (p *ConnectionPool) Execute(sql string, arguments ...interface{}) (commandTag string, err error)

Execute acquires a connection, delegates the call to that connection, and releases the connection

func (*ConnectionPool) MaxConnectionCount 

func (p *ConnectionPool) MaxConnectionCount() int

func (*ConnectionPool) Release 

func (p *ConnectionPool) Release(conn *Connection)

Release gives up use of a connection.

func (*ConnectionPool) SelectFunc 

func (p *ConnectionPool) SelectFunc(sql string, onDataRow func(*DataRowReader) error, arguments ...interface{}) (err error)

SelectFunc acquires a connection, delegates the call to that connection, and releases the connection

func (*ConnectionPool) SelectRow 

func (p *ConnectionPool) SelectRow(sql string, arguments ...interface{}) (row map[string]interface{}, err error)

SelectRow acquires a connection, delegates the call to that connection, and releases the connection

func (*ConnectionPool) SelectRows 

func (p *ConnectionPool) SelectRows(sql string, arguments ...interface{}) (rows []map[string]interface{}, err error)

SelectRows acquires a connection, delegates the call to that connection, and releases the connection

func (*ConnectionPool) SelectValue 

func (p *ConnectionPool) SelectValue(sql string, arguments ...interface{}) (v interface{}, err error)

SelectValue acquires a connection, delegates the call to that connection, and releases the connection

func (*ConnectionPool) SelectValueTo 

func (p *ConnectionPool) SelectValueTo(w io.Writer, sql string, arguments ...interface{}) (err error)

SelectValueTo acquires a connection, delegates the call to that connection, and releases the connection

func (*ConnectionPool) SelectValues 

func (p *ConnectionPool) SelectValues(sql string, arguments ...interface{}) (values []interface{}, err error)

SelectValues acquires a connection, delegates the call to that connection, and releases the connection

func (*ConnectionPool) Stat 

func (p *ConnectionPool) Stat() (s ConnectionPoolStat)

func (*ConnectionPool) Transaction 

func (p *ConnectionPool) Transaction(f func(conn *Connection) bool) (committed bool, err error)

Transaction acquires a connection, delegates the call to that connection, and releases the connection. The call signature differs slightly from the underlying Transaction in that the callback function accepts a *Connection

func (*ConnectionPool) TransactionIso 

func (p *ConnectionPool) TransactionIso(isoLevel string, f func(conn *Connection) bool) (committed bool, err error)

TransactionIso acquires a connection, delegates the call to that connection, and releases the connection. The call signature differs slightly from the underlying TransactionIso in that the callback function accepts a *Connection

type ConnectionPoolOptions 

type ConnectionPoolOptions struct {
	MaxConnections int // max simultaneous connections to use
	AfterConnect   func(*Connection) error
	Logger         Logger
}

type ConnectionPoolStat 

type ConnectionPoolStat struct {
	MaxConnections       int // max simultaneous connections to use
	CurrentConnections   int // current live connections
	AvailableConnections int // unused live connections
}

type DataRowReader 

type DataRowReader struct {
	// contains filtered or unexported fields
}

DataRowReader is used by SelectFunc to process incoming rows.

func (*DataRowReader) ReadValue 

func (r *DataRowReader) ReadValue() interface{}

ReadValue returns the next value from the current row.

type FieldDescription 

type FieldDescription struct {
	Name            string
	Table           Oid
	AttributeNumber int16
	DataType        Oid
	DataTypeSize    int16
	Modifier        int32
	FormatCode      int16
}

type Logger 

type Logger interface {
	Error(msg string)
	Warning(msg string)
	Info(msg string)
	Debug(msg string)
}

type MessageReader 

type MessageReader struct {
	Err error
	// contains filtered or unexported fields
}

MessageReader is a helper that reads values from a PostgreSQL message. To avoid verbose error handling it internally records errors and no-ops any calls that occur after an error. At the end of a sequence of reads the Err field should be checked to see if any errors occurred.

func (*MessageReader) ReadByte 

func (r *MessageReader) ReadByte() (b byte)

func (*MessageReader) ReadCString 

func (r *MessageReader) ReadCString() (s string)

ReadString reads a null terminated string

func (*MessageReader) ReadInt16 

func (r *MessageReader) ReadInt16() (n int16)

func (*MessageReader) ReadInt32 

func (r *MessageReader) ReadInt32() (n int32)

func (*MessageReader) ReadInt64 

func (r *MessageReader) ReadInt64() (n int64)

func (*MessageReader) ReadOid 

func (r *MessageReader) ReadOid() (oid Oid)

func (*MessageReader) ReadString 

func (r *MessageReader) ReadString(count int32) (s string)

ReadString reads count bytes and returns as string

type MessageWriter 

type MessageWriter struct {
	Err error
	// contains filtered or unexported fields
}

MessageWriter is a helper for producing messages to send to PostgreSQL. To avoid verbose error handling it internally records errors and no-ops any calls that occur after an error. At the end of a sequence of writes the Err field should be checked to see if any errors occurred.

func (*MessageWriter) Write 

func (w *MessageWriter) Write(data interface{})

Write writes data in the network byte order. data can be an integer type, float type, or byte slice.

func (*MessageWriter) WriteByte 

func (w *MessageWriter) WriteByte(b byte)

func (*MessageWriter) WriteCString 

func (w *MessageWriter) WriteCString(s string)

WriteCString writes a null-terminated string.

func (*MessageWriter) WriteString 

func (w *MessageWriter) WriteString(s string)

WriteString writes a string without a null terminator.

type NotSingleRowError 

type NotSingleRowError struct {
	RowCount int64
}

NotSingleRowError is returned when exactly 1 row is expected, but 0 or more than 1 row is returned

func (NotSingleRowError) Error 

func (e NotSingleRowError) Error() string

type Notification 

type Notification struct {
	Pid     int32  // backend pid that sent the notification
	Channel string // channel from which notification was received
	Payload string
}

type Oid 

type Oid int32

type PgError 

type PgError struct {
	Severity string
	Code     string
	Message  string
}

func (PgError) Error 

func (self PgError) Error() string

type ProtocolError 

type ProtocolError string

func (ProtocolError) Error 

func (e ProtocolError) Error() string

type UnexpectedColumnCountError 

type UnexpectedColumnCountError struct {
	ExpectedCount int16
	ActualCount   int16
}

UnexpectedColumnCountError is returned when an unexpected number of columns is returned from a Select.

func (UnexpectedColumnCountError) Error 

func (e UnexpectedColumnCountError) Error() string

type ValueTranscoder 

type ValueTranscoder struct {
	// DecodeText decodes values returned from the server in text format
	DecodeText func(*MessageReader, int32) interface{}
	// DecodeBinary decodes values returned from the server in binary format
	DecodeBinary func(*MessageReader, int32) interface{}
	// EncodeTo encodes values to send to the server
	EncodeTo func(*MessageWriter, interface{})
	// EncodeFormat is the format values are encoded for transmission.
	// 0 = text
	// 1 = binary
	EncodeFormat int16
}

ValueTranscoder stores all the data necessary to encode and decode values from a PostgreSQL server

Source Files

View all Source files

Directories

Path Synopsis
examples
url_shortener command

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.