Migrate From lib pq to pgx Without Breaking Production

lib/pq was the PostgreSQL driver for Go for a decade, and in 2026 it is in maintenance mode while pgx is where development happens. Most migrations are not a rewrite. The two drivers speak the same database/sql interface, take the same connection URL, use the same $1 placeholders and report the same SQLSTATE codes, so the first step is a change of one string. What differs is at the edges: helper functions, a few Go types, error values and time zones, and those are what break production if you do not look. This article lists them from a side by side run on Go 1.25.6, lib/pq v1.10.9 and pgx v5.11.0 against PostgreSQL 18.6.

It belongs to the database cornerstone. The decision it follows from is in pgx or database/sql, and the native API it ends at is in the pgx tour.

The plan

Do it in two separate steps, with tests green between them.

Step 1: change the driver, keep database/sql. Replace the import _ "github.com/lib/pq" with _ "github.com/jackc/pgx/v5/stdlib", and sql.Open("postgres", dsn) with sql.Open("pgx", dsn). Fix the differences below. Deploy and watch.

Step 2: adopt native pgx where it pays, in the code that benefits (batches, COPY, LISTEN, hot reads), one repository at a time, as described in moving to sqlc one query at a time, whose seam and contract test technique applies unchanged.

Step 1 alone gets you a maintained driver, better type support and the statement cache. Step 2 is optional.

What differs

package main

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
	"log"
	"strings"
	"time"

	"github.com/jackc/pgx/v5/pgconn"
	"github.com/jackc/pgx/v5/pgtype"
	_ "github.com/jackc/pgx/v5/stdlib"
	"github.com/lib/pq"
)

const dsn = "postgres://postgres:[email protected]:55432/lab?sslmode=disable"

func must(err error) {
	if err != nil {
		log.Fatal(err)
	}
}

func main() {
	ctx := context.Background()
	drivers := []struct{ label, name string }{{"lib/pq", "postgres"}, {"pgx stdlib", "pgx"}}
	dbs := map[string]*sql.DB{}
	for _, d := range drivers {
		db, err := sql.Open(d.name, dsn)
		must(err)
		defer db.Close()
		dbs[d.name] = db
	}
	for _, q := range []string{
		"drop table if exists m683",
		"create table m683 (id int primary key, name text, price numeric(10,2), added timestamptz, tags text[], meta jsonb, raw bytea, flag boolean, born date)",
		"insert into m683 values (1, 'ada', 19.90, '2026-03-01 12:00:00+02', '{a,b}', '{\"k\": 1}', '\\xdeadbeef', true, '1815-12-10')",
	} {
		_, err := dbs["pgx"].ExecContext(ctx, q)
		must(err)
	}

	// 1. The Go type each driver hands back for each column, scanned into any.
	fmt.Println("1. scanning each column into any:")
	fmt.Printf("   %-8s %-12s %-12s\n", "column", "lib/pq", "pgx stdlib")
	cols := []string{"id", "name", "price", "added", "tags", "meta", "raw", "flag", "born"}
	got := map[string][]any{}
	for _, d := range drivers {
		vals := make([]any, len(cols))
		ptrs := make([]any, len(cols))
		for i := range vals {
			ptrs[i] = &vals[i]
		}
		must(dbs[d.name].QueryRowContext(ctx, "select "+strings.Join(cols, ", ")+" from m683 where id = 1").Scan(ptrs...))
		got[d.name] = vals
	}
	for i, c := range cols {
		fmt.Printf("   %-8s %-12T %-12T\n", c, got["postgres"][i], got["pgx"][i])
	}

	// 2. Arrays: the helper changes.
	var viaPQ []string
	must(dbs["postgres"].QueryRowContext(ctx, "select tags from m683 where id = 1").Scan(pq.Array(&viaPQ)))
	var viaPgx []string
	must(dbs["pgx"].QueryRowContext(ctx, "select tags from m683 where id = 1").Scan(pgtype.NewMap().SQLScanner(&viaPgx)))
	fmt.Println("2. text[] with pq.Array:", viaPQ, "| with a pgtype.Map scanner:", viaPgx)
	_, err := dbs["pgx"].ExecContext(ctx, "update m683 set tags = $1 where id = 1", []string{"x", "y"})
	fmt.Println("   passing a []string as an argument through pgx stdlib:", err)
	_, err = dbs["postgres"].ExecContext(ctx, "update m683 set tags = $1 where id = 1", []string{"x", "y"})
	fmt.Println("   the same through lib/pq without pq.Array:", short(err))

	// 3. Errors.
	for _, d := range drivers {
		_, err := dbs[d.name].ExecContext(ctx, "insert into m683 (id) values (1)")
		var pqe *pq.Error
		var pge *pgconn.PgError
		switch {
		case errors.As(err, &pqe):
			fmt.Printf("3. %-10s *pq.Error: Code %q, Constraint %q\n", d.label, string(pqe.Code), pqe.Constraint)
		case errors.As(err, &pge):
			fmt.Printf("3. %-10s *pgconn.PgError: Code %q, ConstraintName %q\n", d.label, pge.Code, pge.ConstraintName)
		}
	}

	// 4. Time zones: the same timestamptz as seen by each driver.
	var tPQ, tPgx time.Time
	must(dbs["postgres"].QueryRowContext(ctx, "select added from m683 where id = 1").Scan(&tPQ))
	must(dbs["pgx"].QueryRowContext(ctx, "select added from m683 where id = 1").Scan(&tPgx))
	fmt.Printf("4. timestamptz: same instant %v; locations lib/pq %q, pgx %q\n", tPQ.Equal(tPgx), tPQ.Location(), tPgx.Location())

	// 5. NULLs behave the same with the standard Null types.
	_, err = dbs["pgx"].ExecContext(ctx, "insert into m683 (id) values (2)")
	must(err)
	for _, d := range drivers {
		var n sql.NullString
		var p sql.NullFloat64
		var ts sql.NullTime
		must(dbs[d.name].QueryRowContext(ctx, "select name, price::float8, added from m683 where id = 2").Scan(&n, &p, &ts))
		fmt.Printf("5. %-10s NULLs into sql.Null*: name valid=%v price valid=%v time valid=%v\n", d.label, n.Valid, p.Valid, ts.Valid)
	}

	// 6. Placeholders are the same, and so is the connection string for the common settings.
	var name string
	for _, d := range drivers {
		must(dbs[d.name].QueryRowContext(ctx, "select name from m683 where id = $1", 1).Scan(&name))
	}
	fmt.Println("6. $1 placeholders and the URL connection string worked unchanged on both:", name)
}

func short(err error) string {
	if err == nil {
		return "ok"
	}
	s := err.Error()
	if i := strings.Index(s, "\n"); i > 0 {
		s = s[:i]
	}
	return s
}

1. scanning each column into any:
   column   lib/pq       pgx stdlib  
   id       int64        int64       
   name     string       string      
   price    []uint8      string      
   added    time.Time    time.Time   
   tags     []uint8      string      
   meta     []uint8      []uint8     
   raw      []uint8      []uint8     
   flag     bool         bool        
   born     time.Time    time.Time   
2. text[] with pq.Array: [a b] | with a pgtype.Map scanner: [a b]
   passing a []string as an argument through pgx stdlib: <nil>
   the same through lib/pq without pq.Array: sql: converting argument $1 type: unsupported type []string, a slice of string
3. lib/pq     *pq.Error: Code "23505", Constraint "m683_pkey"
3. pgx stdlib *pgconn.PgError: Code "23505", ConstraintName "m683_pkey"
4. timestamptz: same instant true; locations lib/pq "UTC", pgx "Local"
5. lib/pq     NULLs into sql.Null*: name valid=false price valid=false time valid=false
5. pgx stdlib NULLs into sql.Null*: name valid=false price valid=false time valid=false
6. $1 placeholders and the URL connection string worked unchanged on both: ada

The lab runs the same statements through both drivers against one table with nine column types.

Types scanned into any

Code that scans into interface{}, or into a map, or through a generic helper, sees different dynamic types:

Two columns changed shape. numeric and arrays are bytes of text with lib/pq and strings with pgx. For code that scans into concrete types (float64, string, a struct) this is mostly invisible, because database/sql converts. It bites code that type switches on any, JSON encoders of generic rows, and tests that compare raw values. Search for interface{} scans and []byte assertions before you switch.

Arrays

lib/pq needs pq.Array for arguments and results: rows.Scan(pq.Array(&tags)) and db.Exec(q, pq.Array(tags)). pgx's adapter reads arrays through a different helper: a scanner from pgtype.NewMap().SQLScanner(&tags), which returned [a b] in the lab just as pq.Array did on the other side. For arguments, the adapter accepts a plain []string directly (the lab's update succeeded with no helper), while lib/pq without pq.Array failed with sql: converting argument $1 type: unsupported type []string, a slice of string. So arguments get simpler and scans need the pgx scanner; code that uses pq.Array for both must change each use, and leaving pq.Array in place keeps the lib/pq import (and its init), so remove it or accept both drivers in the binary.

Sending a []int64 for where id = any($1) also needs no helper through pgx. Writing a small scanArray wrapper in your own package, with the pgx scanner inside, makes the swap one function.

Errors

Both drivers report the SQLSTATE. With lib/pq the error is *pq.Error with a Code of type pq.ErrorCode (convert to string) and fields Constraint, Table, Column, Detail. With pgx it is *pgconn.PgError with Code as a string and ConstraintName, TableName, ColumnName, Detail. The lab's duplicate insert printed Code "23505", Constraint "m683_pkey" for lib/pq and Code "23505", ConstraintName "m683_pkey" for pgx. Replace every errors.As(err, &pqErr) and every if e, ok := err.(*pq.Error) with the pgconn type, and centralise the mapping, as database errors recommends, so the change is one file. A type assertion without errors.As fails when the driver wraps the error; use errors.As.

Time zones

The same timestamptz value was the same instant from both drivers, and the location differed: lib/pq returned it in UTC and pgx in Local, the machine's zone. Instants are equal, so comparisons and arithmetic are unaffected. Formatting is not: t.Format(...) and t.String() print the wall clock in the value's location, so a service that printed UTC timestamps now prints local ones, and one that stored the formatted string compares unequal to old data. Normalise explicitly with t.UTC() where you format, and fix the zone of the server's process in containers (set TZ=UTC). Also check timestamp (without zone) columns, which have no instant and are interpreted in the session's zone.

Things that do not change

NULL handling with sql.NullString, sql.NullInt64, sql.NullTime works the same: the lab scanned a row of NULLs through both and all three were Valid=false. Placeholders are $1, $2 on both and the URL form of the connection string with sslmode worked unchanged. database/sql pool settings apply to both. Rows, Tx, contexts and ErrNoRows behave identically, since they belong to the standard package.

Other differences to check

• LISTEN: lib/pq has pq.NewListener, which reconnects for you. pgx has no equivalent in the adapter; use a native connection and the loop from pgx LISTEN and NOTIFY.
• COPY: pq.CopyIn becomes pgx.CopyFrom on a native or raw connection.
• Connection parameters: most keys match (sslmode, connect_timeout, application_name); pgx adds pool_* for pgxpool and default_query_exec_mode. lib/pq's fallback_application_name and some environment variable behaviours differ; read pgx's list.
• Prepared statements: pgx caches them per connection by default, which interacts with poolers; see pgx with PgBouncer and the statement cache.
• database/sql retries and driver.ErrBadConn: both participate; bad connection errors describes what you will see.
• Numeric precision: keep numeric as strings or a decimal type, never floats, on both sides.

Why bother

If lib/pq works for you, the migration is optional, and the reasons to do it are practical. pgx receives the fixes and the new PostgreSQL features first (new types, protocol improvements, better error detail), it is faster on large results and for batches, and it gives you tracing and logging hooks that lib/pq lacks. Staying also has a cost that grows: libraries you adopt later, from ORMs to migration tools, increasingly assume pgx, and a binary that links both drivers carries two sets of init registrations and two ways to fail. Make the move when you touch the data layer anyway, and not as a standalone project with no other benefit.

A rollout that limits risk

1. Add a contract test around the data layer if there is not one, and make it pass on lib/pq.
2. Switch the driver in a branch and run the whole test suite against a real database, as testing with pgx describes.
3. Grep for pq., []byte assertions on scanned values, Format( on times from the database and interface{} scans, and review each.
4. Deploy to one instance, compare error rates and latency, then roll out.
5. Remove lib/pq from go.mod once nothing imports it, so a stray sql.Open("postgres") fails loudly.

The driver is documented in the pgx stdlib package and the project's status notes are in the lib/pq repository, which points new users to pgx.