1 // Package pgx is a PostgreSQL database driver. 2 /* 3 pgx provides lower level access to PostgreSQL than the standard database/sql. It remains as similar to the database/sql 4 interface as possible while providing better speed and access to PostgreSQL specific features. Import 5 github.com/jackc/pgx/v4/stdlib to use pgx as a database/sql compatible driver. 6 7 Establishing a Connection 8 9 The primary way of establishing a connection is with `pgx.Connect`. 10 11 conn, err := pgx.Connect(context.Background(), os.Getenv("DATABASE_URL")) 12 13 The database connection string can be in URL or DSN format. Both PostgreSQL settings and pgx settings can be specified 14 here. In addition, a config struct can be created by `ParseConfig` and modified before establishing the connection with 15 `ConnectConfig`. 16 17 config, err := pgx.ParseConfig(os.Getenv("DATABASE_URL")) 18 if err != nil { 19 // ... 20 } 21 config.Logger = log15adapter.NewLogger(log.New("module", "pgx")) 22 23 conn, err := pgx.ConnectConfig(context.Background(), config) 24 25 Connection Pool 26 27 `*pgx.Conn` represents a single connection to the database and is not concurrency safe. Use sub-package pgxpool for a 28 concurrency safe connection pool. 29 30 Query Interface 31 32 pgx implements Query and Scan in the familiar database/sql style. 33 34 var sum int32 35 36 // Send the query to the server. The returned rows MUST be closed 37 // before conn can be used again. 38 rows, err := conn.Query(context.Background(), "select generate_series(1,$1)", 10) 39 if err != nil { 40 return err 41 } 42 43 // rows.Close is called by rows.Next when all rows are read 44 // or an error occurs in Next or Scan. So it may optionally be 45 // omitted if nothing in the rows.Next loop can panic. It is 46 // safe to close rows multiple times. 47 defer rows.Close() 48 49 // Iterate through the result set 50 for rows.Next() { 51 var n int32 52 err = rows.Scan(&n) 53 if err != nil { 54 return err 55 } 56 sum += n 57 } 58 59 // Any errors encountered by rows.Next or rows.Scan will be returned here 60 if rows.Err() != nil { 61 return rows.Err() 62 } 63 64 // No errors found - do something with sum 65 66 pgx also implements QueryRow in the same style as database/sql. 67 68 var name string 69 var weight int64 70 err := conn.QueryRow(context.Background(), "select name, weight from widgets where id=$1", 42).Scan(&name, &weight) 71 if err != nil { 72 return err 73 } 74 75 Use Exec to execute a query that does not return a result set. 76 77 commandTag, err := conn.Exec(context.Background(), "delete from widgets where id=$1", 42) 78 if err != nil { 79 return err 80 } 81 if commandTag.RowsAffected() != 1 { 82 return errors.New("No row found to delete") 83 } 84 85 QueryFunc can be used to execute a callback function for every row. This is often easier to use than Query. 86 87 var sum, n int32 88 _, err = conn.QueryFunc( 89 context.Background(), 90 "select generate_series(1,$1)", 91 []interface{}{10}, 92 []interface{}{&n}, 93 func(pgx.QueryFuncRow) error { 94 sum += n 95 return nil 96 }, 97 ) 98 if err != nil { 99 return err 100 } 101 102 Base Type Mapping 103 104 pgx maps between all common base types directly between Go and PostgreSQL. In particular: 105 106 Go PostgreSQL 107 ----------------------- 108 string varchar 109 text 110 111 // Integers are automatically be converted to any other integer type if 112 // it can be done without overflow or underflow. 113 int8 114 int16 smallint 115 int32 int 116 int64 bigint 117 int 118 uint8 119 uint16 120 uint32 121 uint64 122 uint 123 124 // Floats are strict and do not automatically convert like integers. 125 float32 float4 126 float64 float8 127 128 time.Time date 129 timestamp 130 timestamptz 131 132 []byte bytea 133 134 135 Null Mapping 136 137 pgx can map nulls in two ways. The first is package pgtype provides types that have a data field and a status field. 138 They work in a similar fashion to database/sql. The second is to use a pointer to a pointer. 139 140 var foo pgtype.Varchar 141 var bar *string 142 err := conn.QueryRow("select foo, bar from widgets where id=$1", 42).Scan(&foo, &bar) 143 if err != nil { 144 return err 145 } 146 147 Array Mapping 148 149 pgx maps between int16, int32, int64, float32, float64, and string Go slices and the equivalent PostgreSQL array type. 150 Go slices of native types do not support nulls, so if a PostgreSQL array that contains a null is read into a native Go 151 slice an error will occur. The pgtype package includes many more array types for PostgreSQL types that do not directly 152 map to native Go types. 153 154 JSON and JSONB Mapping 155 156 pgx includes built-in support to marshal and unmarshal between Go types and the PostgreSQL JSON and JSONB. 157 158 Inet and CIDR Mapping 159 160 pgx encodes from net.IPNet to and from inet and cidr PostgreSQL types. In addition, as a convenience pgx will encode 161 from a net.IP; it will assume a /32 netmask for IPv4 and a /128 for IPv6. 162 163 Custom Type Support 164 165 pgx includes support for the common data types like integers, floats, strings, dates, and times that have direct 166 mappings between Go and SQL. In addition, pgx uses the github.com/jackc/pgtype library to support more types. See 167 documention for that library for instructions on how to implement custom types. 168 169 See example_custom_type_test.go for an example of a custom type for the PostgreSQL point type. 170 171 pgx also includes support for custom types implementing the database/sql.Scanner and database/sql/driver.Valuer 172 interfaces. 173 174 If pgx does cannot natively encode a type and that type is a renamed type (e.g. type MyTime time.Time) pgx will attempt 175 to encode the underlying type. While this is usually desired behavior it can produce surprising behavior if one the 176 underlying type and the renamed type each implement database/sql interfaces and the other implements pgx interfaces. It 177 is recommended that this situation be avoided by implementing pgx interfaces on the renamed type. 178 179 Composite types and row values 180 181 Row values and composite types are represented as pgtype.Record (https://pkg.go.dev/github.com/jackc/pgtype?tab=doc#Record). 182 It is possible to get values of your custom type by implementing DecodeBinary interface. Decoding into 183 pgtype.Record first can simplify process by avoiding dealing with raw protocol directly. 184 185 For example: 186 187 type MyType struct { 188 a int // NULL will cause decoding error 189 b *string // there can be NULL in this position in SQL 190 } 191 192 func (t *MyType) DecodeBinary(ci *pgtype.ConnInfo, src []byte) error { 193 r := pgtype.Record{ 194 Fields: []pgtype.Value{&pgtype.Int4{}, &pgtype.Text{}}, 195 } 196 197 if err := r.DecodeBinary(ci, src); err != nil { 198 return err 199 } 200 201 if r.Status != pgtype.Present { 202 return errors.New("BUG: decoding should not be called on NULL value") 203 } 204 205 a := r.Fields[0].(*pgtype.Int4) 206 b := r.Fields[1].(*pgtype.Text) 207 208 // type compatibility is checked by AssignTo 209 // only lossless assignments will succeed 210 if err := a.AssignTo(&t.a); err != nil { 211 return err 212 } 213 214 // AssignTo also deals with null value handling 215 if err := b.AssignTo(&t.b); err != nil { 216 return err 217 } 218 return nil 219 } 220 221 result := MyType{} 222 err := conn.QueryRow(context.Background(), "select row(1, 'foo'::text)", pgx.QueryResultFormats{pgx.BinaryFormatCode}).Scan(&r) 223 224 Raw Bytes Mapping 225 226 []byte passed as arguments to Query, QueryRow, and Exec are passed unmodified to PostgreSQL. 227 228 Transactions 229 230 Transactions are started by calling Begin. 231 232 tx, err := conn.Begin(context.Background()) 233 if err != nil { 234 return err 235 } 236 // Rollback is safe to call even if the tx is already closed, so if 237 // the tx commits successfully, this is a no-op 238 defer tx.Rollback(context.Background()) 239 240 _, err = tx.Exec(context.Background(), "insert into foo(id) values (1)") 241 if err != nil { 242 return err 243 } 244 245 err = tx.Commit(context.Background()) 246 if err != nil { 247 return err 248 } 249 250 The Tx returned from Begin also implements the Begin method. This can be used to implement pseudo nested transactions. 251 These are internally implemented with savepoints. 252 253 Use BeginTx to control the transaction mode. 254 255 BeginFunc and BeginTxFunc are variants that begin a transaction, execute a function, and commit or rollback the 256 transaction depending on the return value of the function. These can be simpler and less error prone to use. 257 258 err = conn.BeginFunc(context.Background(), func(tx pgx.Tx) error { 259 _, err := tx.Exec(context.Background(), "insert into foo(id) values (1)") 260 return err 261 }) 262 if err != nil { 263 return err 264 } 265 266 Prepared Statements 267 268 Prepared statements can be manually created with the Prepare method. However, this is rarely necessary because pgx 269 includes an automatic statement cache by default. Queries run through the normal Query, QueryRow, and Exec functions are 270 automatically prepared on first execution and the prepared statement is reused on subsequent executions. See ParseConfig 271 for information on how to customize or disable the statement cache. 272 273 Copy Protocol 274 275 Use CopyFrom to efficiently insert multiple rows at a time using the PostgreSQL copy protocol. CopyFrom accepts a 276 CopyFromSource interface. If the data is already in a [][]interface{} use CopyFromRows to wrap it in a CopyFromSource 277 interface. Or implement CopyFromSource to avoid buffering the entire data set in memory. 278 279 rows := [][]interface{}{ 280 {"John", "Smith", int32(36)}, 281 {"Jane", "Doe", int32(29)}, 282 } 283 284 copyCount, err := conn.CopyFrom( 285 context.Background(), 286 pgx.Identifier{"people"}, 287 []string{"first_name", "last_name", "age"}, 288 pgx.CopyFromRows(rows), 289 ) 290 291 When you already have a typed array using CopyFromSlice can be more convenient. 292 293 rows := []User{ 294 {"John", "Smith", 36}, 295 {"Jane", "Doe", 29}, 296 } 297 298 copyCount, err := conn.CopyFrom( 299 context.Background(), 300 pgx.Identifier{"people"}, 301 []string{"first_name", "last_name", "age"}, 302 pgx.CopyFromSlice(len(rows), func(i int) ([]interface{}, error) { 303 return []interface{}{rows[i].FirstName, rows[i].LastName, rows[i].Age}, nil 304 }), 305 ) 306 307 CopyFrom can be faster than an insert with as few as 5 rows. 308 309 Listen and Notify 310 311 pgx can listen to the PostgreSQL notification system with the `Conn.WaitForNotification` method. It blocks until a 312 notification is received or the context is canceled. 313 314 _, err := conn.Exec(context.Background(), "listen channelname") 315 if err != nil { 316 return nil 317 } 318 319 if notification, err := conn.WaitForNotification(context.Background()); err != nil { 320 // do something with notification 321 } 322 323 324 Logging 325 326 pgx defines a simple logger interface. Connections optionally accept a logger that satisfies this interface. Set 327 LogLevel to control logging verbosity. Adapters for github.com/inconshreveable/log15, github.com/sirupsen/logrus, 328 go.uber.org/zap, github.com/rs/zerolog, and the testing log are provided in the log directory. 329 330 Lower Level PostgreSQL Functionality 331 332 pgx is implemented on top of github.com/jackc/pgconn a lower level PostgreSQL driver. The Conn.PgConn() method can be 333 used to access this lower layer. 334 335 PgBouncer 336 337 pgx is compatible with PgBouncer in two modes. One is when the connection has a statement cache in "describe" mode. The 338 other is when the connection is using the simple protocol. This can be set with the PreferSimpleProtocol config option. 339 */ 340 package pgx 341