1 // Package pgx is a PostgreSQL database driver. 2 /* 3 pgx provides a native PostgreSQL driver and can act as a database/sql driver. The native PostgreSQL interface is similar 4 to the database/sql interface while providing better speed and access to PostgreSQL specific features. Use 5 github.com/jackc/pgx/v5/stdlib to use pgx as a database/sql compatible driver. See that package's documentation for 6 details. 7 8 Establishing a Connection 9 10 The primary way of establishing a connection is with [pgx.Connect]: 11 12 conn, err := pgx.Connect(context.Background(), os.Getenv("DATABASE_URL")) 13 14 The database connection string can be in URL or DSN format. Both PostgreSQL settings and pgx settings can be specified 15 here. In addition, a config struct can be created by [ParseConfig] and modified before establishing the connection with 16 [ConnectConfig] to configure settings such as tracing that cannot be configured with a connection string. 17 18 Connection Pool 19 20 [*pgx.Conn] represents a single connection to the database and is not concurrency safe. Use package 21 github.com/jackc/pgx/v5/pgxpool for a concurrency safe connection pool. 22 23 Query Interface 24 25 pgx implements Query in the familiar database/sql style. However, pgx provides generic functions such as CollectRows and 26 ForEachRow that are a simpler and safer way of processing rows than manually calling rows.Next(), rows.Scan, and 27 rows.Err(). 28 29 CollectRows can be used collect all returned rows into a slice. 30 31 rows, _ := conn.Query(context.Background(), "select generate_series(1,$1)", 5) 32 numbers, err := pgx.CollectRows(rows, pgx.RowTo[int32]) 33 if err != nil { 34 return err 35 } 36 // numbers => [1 2 3 4 5] 37 38 ForEachRow can be used to execute a callback function for every row. This is often easier than iterating over rows 39 directly. 40 41 var sum, n int32 42 rows, _ := conn.Query(context.Background(), "select generate_series(1,$1)", 10) 43 _, err := pgx.ForEachRow(rows, []any{&n}, func() error { 44 sum += n 45 return nil 46 }) 47 if err != nil { 48 return err 49 } 50 51 pgx also implements QueryRow in the same style as database/sql. 52 53 var name string 54 var weight int64 55 err := conn.QueryRow(context.Background(), "select name, weight from widgets where id=$1", 42).Scan(&name, &weight) 56 if err != nil { 57 return err 58 } 59 60 Use Exec to execute a query that does not return a result set. 61 62 commandTag, err := conn.Exec(context.Background(), "delete from widgets where id=$1", 42) 63 if err != nil { 64 return err 65 } 66 if commandTag.RowsAffected() != 1 { 67 return errors.New("No row found to delete") 68 } 69 70 PostgreSQL Data Types 71 72 pgx uses the pgtype package to converting Go values to and from PostgreSQL values. It supports many PostgreSQL types 73 directly and is customizable and extendable. User defined data types such as enums, domains, and composite types may 74 require type registration. See that package's documentation for details. 75 76 Transactions 77 78 Transactions are started by calling Begin. 79 80 tx, err := conn.Begin(context.Background()) 81 if err != nil { 82 return err 83 } 84 // Rollback is safe to call even if the tx is already closed, so if 85 // the tx commits successfully, this is a no-op 86 defer tx.Rollback(context.Background()) 87 88 _, err = tx.Exec(context.Background(), "insert into foo(id) values (1)") 89 if err != nil { 90 return err 91 } 92 93 err = tx.Commit(context.Background()) 94 if err != nil { 95 return err 96 } 97 98 The Tx returned from Begin also implements the Begin method. This can be used to implement pseudo nested transactions. 99 These are internally implemented with savepoints. 100 101 Use BeginTx to control the transaction mode. BeginTx also can be used to ensure a new transaction is created instead of 102 a pseudo nested transaction. 103 104 BeginFunc and BeginTxFunc are functions that begin a transaction, execute a function, and commit or rollback the 105 transaction depending on the return value of the function. These can be simpler and less error prone to use. 106 107 err = pgx.BeginFunc(context.Background(), conn, func(tx pgx.Tx) error { 108 _, err := tx.Exec(context.Background(), "insert into foo(id) values (1)") 109 return err 110 }) 111 if err != nil { 112 return err 113 } 114 115 Prepared Statements 116 117 Prepared statements can be manually created with the Prepare method. However, this is rarely necessary because pgx 118 includes an automatic statement cache by default. Queries run through the normal Query, QueryRow, and Exec functions are 119 automatically prepared on first execution and the prepared statement is reused on subsequent executions. See ParseConfig 120 for information on how to customize or disable the statement cache. 121 122 Copy Protocol 123 124 Use CopyFrom to efficiently insert multiple rows at a time using the PostgreSQL copy protocol. CopyFrom accepts a 125 CopyFromSource interface. If the data is already in a [][]any use CopyFromRows to wrap it in a CopyFromSource interface. 126 Or implement CopyFromSource to avoid buffering the entire data set in memory. 127 128 rows := [][]any{ 129 {"John", "Smith", int32(36)}, 130 {"Jane", "Doe", int32(29)}, 131 } 132 133 copyCount, err := conn.CopyFrom( 134 context.Background(), 135 pgx.Identifier{"people"}, 136 []string{"first_name", "last_name", "age"}, 137 pgx.CopyFromRows(rows), 138 ) 139 140 When you already have a typed array using CopyFromSlice can be more convenient. 141 142 rows := []User{ 143 {"John", "Smith", 36}, 144 {"Jane", "Doe", 29}, 145 } 146 147 copyCount, err := conn.CopyFrom( 148 context.Background(), 149 pgx.Identifier{"people"}, 150 []string{"first_name", "last_name", "age"}, 151 pgx.CopyFromSlice(len(rows), func(i int) ([]any, error) { 152 return []any{rows[i].FirstName, rows[i].LastName, rows[i].Age}, nil 153 }), 154 ) 155 156 CopyFrom can be faster than an insert with as few as 5 rows. 157 158 Listen and Notify 159 160 pgx can listen to the PostgreSQL notification system with the `Conn.WaitForNotification` method. It blocks until a 161 notification is received or the context is canceled. 162 163 _, err := conn.Exec(context.Background(), "listen channelname") 164 if err != nil { 165 return err 166 } 167 168 notification, err := conn.WaitForNotification(context.Background()) 169 if err != nil { 170 return err 171 } 172 // do something with notification 173 174 175 Tracing and Logging 176 177 pgx supports tracing by setting ConnConfig.Tracer. 178 179 In addition, the tracelog package provides the TraceLog type which lets a traditional logger act as a Tracer. 180 181 For debug tracing of the actual PostgreSQL wire protocol messages see github.com/jackc/pgx/v5/pgproto3. 182 183 Lower Level PostgreSQL Functionality 184 185 github.com/jackc/pgx/v5/pgconn contains a lower level PostgreSQL driver roughly at the level of libpq. pgx.Conn in 186 implemented on top of pgconn. The Conn.PgConn() method can be used to access this lower layer. 187 188 PgBouncer 189 190 By default pgx automatically uses prepared statements. Prepared statements are incompatible with PgBouncer. This can be 191 disabled by setting a different QueryExecMode in ConnConfig.DefaultQueryExecMode. 192 */ 193 package pgx 194