1[](https://pkg.go.dev/github.com/jackc/pgx/v4)
2[](https://travis-ci.org/jackc/pgx)
3
4---
5
6This is the previous stable `v4` release. `v5` been released.
7
8---
9# pgx - PostgreSQL Driver and Toolkit
10
11pgx is a pure Go driver and toolkit for PostgreSQL.
12
13pgx aims to be low-level, fast, and performant, while also enabling PostgreSQL-specific features that the standard `database/sql` package does not allow for.
14
15The driver component of pgx can be used alongside the standard `database/sql` package.
16
17The toolkit component is a related set of packages that implement PostgreSQL functionality such as parsing the wire protocol
18and type mapping between PostgreSQL and Go. These underlying packages can be used to implement alternative drivers,
19proxies, load balancers, logical replication clients, etc.
20
21The current release of `pgx v4` requires Go modules. To use the previous version, checkout and vendor the `v3` branch.
22
23## Example Usage
24
25```go
26package main
27
28import (
29 "context"
30 "fmt"
31 "os"
32
33 "github.com/jackc/pgx/v4"
34)
35
36func main() {
37 // urlExample := "postgres://username:password@localhost:5432/database_name"
38 conn, err := pgx.Connect(context.Background(), os.Getenv("DATABASE_URL"))
39 if err != nil {
40 fmt.Fprintf(os.Stderr, "Unable to connect to database: %v\n", err)
41 os.Exit(1)
42 }
43 defer conn.Close(context.Background())
44
45 var name string
46 var weight int64
47 err = conn.QueryRow(context.Background(), "select name, weight from widgets where id=$1", 42).Scan(&name, &weight)
48 if err != nil {
49 fmt.Fprintf(os.Stderr, "QueryRow failed: %v\n", err)
50 os.Exit(1)
51 }
52
53 fmt.Println(name, weight)
54}
55```
56
57See the [getting started guide](https://github.com/jackc/pgx/wiki/Getting-started-with-pgx) for more information.
58
59## Choosing Between the pgx and database/sql Interfaces
60
61It is recommended to use the pgx interface if:
621. The application only targets PostgreSQL.
632. No other libraries that require `database/sql` are in use.
64
65The pgx interface is faster and exposes more features.
66
67The `database/sql` interface only allows the underlying driver to return or receive the following types: `int64`,
68`float64`, `bool`, `[]byte`, `string`, `time.Time`, or `nil`. Handling other types requires implementing the
69`database/sql.Scanner` and the `database/sql/driver/driver.Valuer` interfaces which require transmission of values in text format. The binary format can be substantially faster, which is what the pgx interface uses.
70
71## Features
72
73pgx supports many features beyond what is available through `database/sql`:
74
75* Support for approximately 70 different PostgreSQL types
76* Automatic statement preparation and caching
77* Batch queries
78* Single-round trip query mode
79* Full TLS connection control
80* Binary format support for custom types (allows for much quicker encoding/decoding)
81* COPY protocol support for faster bulk data loads
82* Extendable logging support including built-in support for `log15adapter`, [`logrus`](https://github.com/sirupsen/logrus), [`zap`](https://github.com/uber-go/zap), and [`zerolog`](https://github.com/rs/zerolog)
83* Connection pool with after-connect hook for arbitrary connection setup
84* Listen / notify
85* Conversion of PostgreSQL arrays to Go slice mappings for integers, floats, and strings
86* Hstore support
87* JSON and JSONB support
88* Maps `inet` and `cidr` PostgreSQL types to `net.IPNet` and `net.IP`
89* Large object support
90* NULL mapping to Null* struct or pointer to pointer
91* Supports `database/sql.Scanner` and `database/sql/driver.Valuer` interfaces for custom types
92* Notice response handling
93* Simulated nested transactions with savepoints
94
95## Performance
96
97There are three areas in particular where pgx can provide a significant performance advantage over the standard
98`database/sql` interface and other drivers:
99
1001. PostgreSQL specific types - Types such as arrays can be parsed much quicker because pgx uses the binary format.
1012. Automatic statement preparation and caching - pgx will prepare and cache statements by default. This can provide an
102 significant free improvement to code that does not explicitly use prepared statements. Under certain workloads, it can
103 perform nearly 3x the number of queries per second.
1043. Batched queries - Multiple queries can be batched together to minimize network round trips.
105
106## Testing
107
108pgx tests naturally require a PostgreSQL database. It will connect to the database specified in the `PGX_TEST_DATABASE` environment
109variable. The `PGX_TEST_DATABASE` environment variable can either be a URL or DSN. In addition, the standard `PG*` environment
110variables will be respected. Consider using [direnv](https://github.com/direnv/direnv) to simplify environment variable
111handling.
112
113### Example Test Environment
114
115Connect to your PostgreSQL server and run:
116
117```
118create database pgx_test;
119```
120
121Connect to the newly-created database and run:
122
123```
124create domain uint64 as numeric(20,0);
125```
126
127Now, you can run the tests:
128
129```
130PGX_TEST_DATABASE="host=/var/run/postgresql database=pgx_test" go test ./...
131```
132
133In addition, there are tests specific for PgBouncer that will be executed if `PGX_TEST_PGBOUNCER_CONN_STRING` is set.
134
135## Supported Go and PostgreSQL Versions
136
137pgx supports the same versions of Go and PostgreSQL that are supported by their respective teams. For [Go](https://golang.org/doc/devel/release.html#policy) that is the two most recent major releases and for [PostgreSQL](https://www.postgresql.org/support/versioning/) the major releases in the last 5 years. This means pgx supports Go 1.17 and higher and PostgreSQL 10 and higher. pgx also is tested against the latest version of [CockroachDB](https://www.cockroachlabs.com/product/).
138
139## Version Policy
140
141pgx follows semantic versioning for the documented public API on stable releases. `v4` is the latest stable major version.
142
143## PGX Family Libraries
144
145pgx is the head of a family of PostgreSQL libraries. Many of these can be used independently. Many can also be accessed
146from pgx for lower-level control.
147
148### [github.com/jackc/pgconn](https://github.com/jackc/pgconn)
149
150`pgconn` is a lower-level PostgreSQL database driver that operates at nearly the same level as the C library `libpq`.
151
152### [github.com/jackc/pgx/v4/pgxpool](https://github.com/jackc/pgx/tree/master/pgxpool)
153
154`pgxpool` is a connection pool for pgx. pgx is entirely decoupled from its default pool implementation. This means that pgx can be used with a different pool or without any pool at all.
155
156### [github.com/jackc/pgx/v4/stdlib](https://github.com/jackc/pgx/tree/master/stdlib)
157
158This is a `database/sql` compatibility layer for pgx. pgx can be used as a normal `database/sql` driver, but at any time, the native interface can be acquired for more performance or PostgreSQL specific functionality.
159
160### [github.com/jackc/pgtype](https://github.com/jackc/pgtype)
161
162Over 70 PostgreSQL types are supported including `uuid`, `hstore`, `json`, `bytea`, `numeric`, `interval`, `inet`, and arrays. These types support `database/sql` interfaces and are usable outside of pgx. They are fully tested in pgx and pq. They also support a higher performance interface when used with the pgx driver.
163
164### [github.com/jackc/pgproto3](https://github.com/jackc/pgproto3)
165
166pgproto3 provides standalone encoding and decoding of the PostgreSQL v3 wire protocol. This is useful for implementing very low level PostgreSQL tooling.
167
168### [github.com/jackc/pglogrepl](https://github.com/jackc/pglogrepl)
169
170pglogrepl provides functionality to act as a client for PostgreSQL logical replication.
171
172### [github.com/jackc/pgmock](https://github.com/jackc/pgmock)
173
174pgmock offers the ability to create a server that mocks the PostgreSQL wire protocol. This is used internally to test pgx by purposely inducing unusual errors. pgproto3 and pgmock together provide most of the foundational tooling required to implement a PostgreSQL proxy or MitM (such as for a custom connection pooler).
175
176### [github.com/jackc/tern](https://github.com/jackc/tern)
177
178tern is a stand-alone SQL migration system.
179
180### [github.com/jackc/pgerrcode](https://github.com/jackc/pgerrcode)
181
182pgerrcode contains constants for the PostgreSQL error codes.
183
184## 3rd Party Libraries with PGX Support
185
186### [github.com/georgysavva/scany](https://github.com/georgysavva/scany)
187
188Library for scanning data from a database into Go structs and more.
189
190### [https://github.com/otan/gopgkrb5](https://github.com/otan/gopgkrb5)
191
192Adds GSSAPI / Kerberos authentication support.
193
194### [https://github.com/vgarvardt/pgx-google-uuid](https://github.com/vgarvardt/pgx-google-uuid)
195
196Adds support for [`github.com/google/uuid`](https://github.com/google/uuid).
View as plain text