How database drivers work in Go

I wanted to understand what actually happens behind the database libraries I use in Go.

I knew how to call Query, but names such as database/sql, sqlx, pgx and pgxpool were starting to blur together. Which package owns the connections? Which one understands PostgreSQL? Where does MySQL behave differently? What keeps a transaction on the same connection?

The easiest way for me to understand it was to stop comparing package names and follow one query from my code to the database.

The 30-second mental model

There are four main parts. Each part has one job:

Part	Its job	The short version
Application	Supplies SQL, parameters and a context	asks
Connection pool	Finds a physical connection	lends
Driver	Converts Go calls into database-specific operations	translates
Database	Parses and executes SQL	executes

The sentence I want to remember is:

The app asks. The pool lends. The driver translates. The database executes.

Everything else in this journal is a deeper explanation of those four verbs.

Follow one query step by step

This diagram is the map for the rest of the journal. I read the solid arrows from left to right and then follow the dashed result arrow back.

Start with a normal query:

row := db.QueryRowContext(	ctx,	`SELECT name FROM users WHERE id = $1`,	userID,) var name stringif err := row.Scan(&name); err != nil {	return err}

When db is a *sql.DB, this is what happens:

The application asks. It passes the context, SQL text and userID to database/sql.
The pool lends. database/sql reuses an idle connection, opens one when allowed, or waits when the pool is full.
The driver translates. It converts userID into a database value and encodes database-specific protocol messages.
The database executes. It parses, plans and runs the statement.
The result travels back. The driver decodes the returned value and Scan assigns it to name.
The connection is released. Once the row is consumed, the physical connection can return to the pool.

The layers are separate even though one method call hides them. database/sql does not contain PostgreSQL, MySQL or SQL Server support. It defines a common lifecycle. A third-party driver implements the database-specific work.

The SQL is still database-specific too. PostgreSQL uses $1; the common MySQL driver uses ?; SQL Server supports named parameters. A shared Go API does not make SQL dialects identical.

An optional helper can sit above this flow:

sqlx / sqlc / ORM → application conveniencedatabase/sql      → common API and connection pooldriver            → database-specific translationdatabase          → SQL execution and storage

That distinction prevents my first common mistake: expecting sqlx or an ORM to replace the driver. They make application code easier, but the pool and driver still do their original jobs.

A quick package-name decoder

This is the table I wish I had when I first saw these names:

Name	What it is	What it is not
`database/sql`	Standard Go API and connection pool	A PostgreSQL or MySQL implementation
`pgx`	PostgreSQL-native driver and API	A multi-database abstraction
`pgxpool`	Pool for native pgx connections	A second database engine
`pgx/stdlib`	Adapter that puts pgx behind `database/sql`	The native pgx API
`sqlx`	Convenience wrapper above `database/sql`	A wire-protocol driver
`sqlc`	Generates typed Go code from SQL	A runtime pool or database

If I forget everything else, I ask two questions: who owns the connection pool, and which package speaks the database protocol? Those answers reveal the architecture.

What `database/sql` really provides

The name is slightly misleading because *sql.DB is not one database connection. It is a concurrency-safe handle around a pool containing zero or more connections.

*sql.DB  ├── idle connection 1  ├── busy connection 2  ├── busy connection 3  └── callers waiting when the configured limit is reached

sql.Open may only validate its arguments. It may not contact the database immediately. I use PingContext during startup or readiness when I need to verify that credentials, networking and the server are actually usable.

db, err := sql.Open("pgx", databaseURL)if err != nil {	return err} ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)defer cancel() if err := db.PingContext(ctx); err != nil {	return fmt.Errorf("connect to database: %w", err)}

The pool is managed with four important settings:

Setting	Question it answers
`SetMaxOpenConns`	How many physical connections may exist at once?
`SetMaxIdleConns`	How many completed connections should remain ready?
`SetConnMaxLifetime`	How long may a connection exist before retirement?
`SetConnMaxIdleTime`	How long may an unused connection remain idle?

These are not arbitrary performance knobs. MaxOpenConns limits concurrency against the database. Once reached, acquiring a connection behaves like waiting on a semaphore. Too high can overwhelm the database; too low can create long waits inside the application.

I watch db.Stats() rather than guessing. In particular, InUse, Idle, WaitCount and WaitDuration reveal whether the pool is saturated or constantly reconnecting.

stats := db.Stats() log.Printf(	"open=%d in_use=%d idle=%d waits=%d wait_time=%s",	stats.OpenConnections,	stats.InUse,	stats.Idle,	stats.WaitCount,	stats.WaitDuration,)

A pool is also a concurrency boundary

The pool does more than save the cost of reconnecting. For every request it has three choices, always in this order:

Reuse an idle connection → open a new one → wait.

The second diagram follows those decisions one at a time.

A query holds a connection while its result is active. With database/sql, I close Rows and check its final error:

rows, err := db.QueryContext(ctx, query, accountID)if err != nil {	return err}defer rows.Close() for rows.Next() {	var event Event	if err := rows.Scan(&event.ID, &event.Kind); err != nil {		return err	}} if err := rows.Err(); err != nil {	return err}

If rows are abandoned without being closed or fully consumed, their connection can remain unavailable. Under light testing that may look harmless; under load it becomes a pool-exhaustion incident.

A transaction is different again. An sql.Tx is pinned to one physical connection until Commit or Rollback. A dedicated sql.Conn also reserves one connection when connection-local state is required.

This explains an easy deadlock pattern: code starts a transaction, consumes the last permitted connection, and then calls db.QueryContext instead of tx.QueryContext. The second call waits for a free connection while the first connection cannot be returned until the transaction finishes.

What a Go SQL driver implements

The database/sql/driver package defines the interfaces between the standard library and a driver. Application code normally should not call these interfaces directly, but reading them makes the boundary concrete.

At the bottom are concepts such as:

type Driver interface {	Open(name string) (Conn, error)} type Connector interface {	Connect(context.Context) (Conn, error)	Driver() Driver}

Modern drivers can also implement optional capabilities including:

QueryerContext and ExecerContext to execute directly.
ConnPrepareContext and ConnBeginTx for context-aware preparation and transactions.
Pinger to verify a physical connection.
SessionResetter and Validator before a connection is reused.
NamedValueChecker for database-specific argument types.

If a connection does not implement a direct execution interface, database/sql can fall back to preparing a statement, executing it and closing it. This is one reason the concrete driver's implementation matters even when application code only imports database/sql types.

The special error driver.ErrBadConn is also part of this contract. It tells database/sql that a connection is unusable and that an operation may be retried on another connection. A driver must not return it when the server may already have performed the operation; blindly retrying an ambiguous write could duplicate it.

Who chooses the wire protocol?

The driver does.

database/sql does not force PostgreSQL's simple query protocol, extended query protocol, MySQL prepared statements or SQL Server's TDS message sequence. It asks the driver to execute an operation through an interface. The driver decides how to translate that operation.

For PostgreSQL, the extended protocol separates stages such as parse, bind and execute. Those are protocol messages, not automatically three network round trips. A driver can pipeline or combine work, and a cached prepared statement can avoid repeated preparation.

For MySQL, the common driver can use the query protocol or server-side prepared-statement protocol depending on the call and configuration. SQL Server uses TDS. SQLite is the useful counterexample: there is no database wire protocol at all because the engine runs inside the application process.

Parameters are values, not SQL structure

Every driver has a placeholder convention, but the security rule is the same: keep SQL text separate from untrusted values.

row := db.QueryRowContext(	ctx,	`SELECT id FROM users WHERE email = $1`,	email,)

The driver encodes email as a value. It is not pasted into the SQL grammar.

Parameters cannot safely stand in for identifiers or keywords. This does not work as a portable way to choose a sort column:

SELECT id, name FROM users ORDER BY $1;

For dynamic identifiers, I use an allowlist and select a trusted SQL fragment:

sortColumns := map[string]string{	"name":       "name",	"created_at": "created_at",} sortColumn, ok := sortColumns[requestedSort]if !ok {	return errors.New("unsupported sort column")} query := `SELECT id, name FROM users ORDER BY ` + sortColumn

The concatenated value is safe here because it came from code, not directly from the request.

Convenience libraries live above the driver

sqlx, sqlc and ORMs solve a different problem from drivers.

`sqlx`

sqlx wraps database/sql and adds conveniences such as struct scanning, named parameters, Get and Select.

type User struct {	ID    int64  `db:"id"`	Name  string `db:"name"`	Email string `db:"email"`} var user Usererr := db.GetContext(	ctx,	&user,	`SELECT id, name, email FROM users WHERE id = $1`,	userID,)

It does not speak PostgreSQL and does not own an independent pool. Its DB wraps the database/sql handle underneath.

`sqlc`

sqlc reads SQL files and generates typed Go methods. It can generate code for a database/sql-style backend or native pgx, depending on configuration. It moves mapping work to code generation rather than runtime reflection, but the selected runtime package and driver still handle connections and protocol details.

ORMs

An ORM adds models, relationship loading, query construction and lifecycle hooks. It still needs a dialect and a driver underneath. Changing the ORM does not make PostgreSQL arrays behave like MySQL JSON or make transaction semantics identical.

The useful distinction is:

sqlx / sqlc / ORM  → application ergonomics and mappingdatabase/sql       → generic execution API and pooldriver             → database connection and protocol implementationdatabase           → SQL semantics, storage and concurrency control

`database/sql`, `sqlx` and `pgxpool` side by side

These names are often compared as if they are three versions of the same library. They are not at the same layer.

The following diagram shows three complete stacks. I read each column from top to bottom.

Stack 1: `database/sql` with a PostgreSQL driver

application → database/sql → lib/pq → PostgreSQL

The application uses standard types such as *sql.DB, *sql.Tx and *sql.Rows. database/sql owns the pool. lib/pq implements the PostgreSQL-specific connection and protocol behavior.

I can replace lib/pq with pgx/stdlib and keep the application-facing database/sql API. The outer code may look similar, but the driver implementation underneath has changed.

Stack 2: `sqlx` above `database/sql`

application → sqlx → database/sql → pgx/stdlib → PostgreSQL

sqlx adds named queries and convenient struct mapping. It does not create a second pool and does not speak PostgreSQL itself. Its DB wraps *sql.DB; the standard library still chooses connections and the selected driver still talks to PostgreSQL.

This stack is useful when I want sqlx ergonomics or another library requires *sql.DB.

Stack 3: native `pgxpool`

application → pgxpool → pgx → PostgreSQL

This path does not use database/sql. pgxpool owns a pool of native pgx connections, and the application uses pgx types and methods directly.

This is a strong default for a PostgreSQL-only service when no required library depends on *sql.DB. It makes PostgreSQL capabilities visible instead of squeezing every operation through a generic SQL interface.

Why native `pgxpool` can be a better fit

I would choose it for concrete reasons, not because one package universally “wins.”

1. PostgreSQL features are first-class

Native pgx exposes COPY, LISTEN/NOTIFY, batches, PostgreSQL error types, binary formats and a large PostgreSQL type system directly. The generic database/sql contract cannot describe every PostgreSQL feature.

pgx also provides helpers for collecting rows into Go values. For example:

type User struct {	ID    int64	Name  string	Email string} rows, err := pool.Query(	ctx,	`SELECT id, name, email FROM users ORDER BY id`,)if err != nil {	return err} users, err := pgx.CollectRows(rows, pgx.RowToStructByName[User])if err != nil {	return err}

This does not mean database/sql cannot carry PostgreSQL values. A capable driver can support many of them, and a program can access a raw driver connection when necessary. The native API is simply the clearer route when those features are normal parts of the application.

2. Query protocol behavior is explicit

pgx uses PostgreSQL's extended query protocol by default and automatically prepares and caches statements in its default execution mode.

The conceptual protocol stages are:

Parse   → send the SQL shape and learn its parameter/result typesBind    → attach this execution's parameter valuesExecute → run the bound statement and return results

These are protocol messages, not automatically three network round trips. pgx can combine work, cache statement information on each physical connection and execute a cached statement in one round trip.

The cache is connection-local:

pool  ├── connection A → query already prepared  ├── connection B → query already prepared  └── connection C → first use still needs preparation

Parameters are kept separate from SQL structure in the extended protocol, which gives both safe value handling and efficient binary encoding. I still avoid absolute claims such as “SQL injection is impossible”: parameters only protect values. Dynamic table names, columns and keywords still require an allowlist.

3. Context behavior reaches the PostgreSQL layer

Both native pgx and database/sql accept context.Context. The important boundary is the driver—not the spelling of the outer API.

database/sql passes cancellation to context-aware driver interfaces. If the driver does not support cancellation, the call may not return until the query finishes. A capable PostgreSQL driver can perform PostgreSQL-specific cleanup or cancellation underneath database/sql.

With pgx, potentially blocking operations take a context. Its default cancellation behavior returns promptly and will often close an interrupted physical connection so uncertain protocol state is not reused. pgx can also be configured with a context watcher that sends PostgreSQL's separate Cancel Request and uses a socket deadline as a fallback. A PostgreSQL cancel request is best-effort; successful delivery does not prove that the server canceled the intended query.

The memory line is:

The context carries the deadline; the driver decides what happens on the wire.

4. The native path can be faster—but I still measure

pgx documents its native interface as faster than its database/sql path. It avoids the compatibility adapter and can use native encoders, decoders and PostgreSQL-specific operations.

That does not mean database/sql is automatically a bottleneck or that it performs all struct mapping through heavy reflection. database/sql scans columns into destinations; helpers such as sqlx, ORMs or pgx row collectors decide how structs are mapped. For many applications, query design, indexes, network time and database work dominate the API-layer difference.

I treat performance as something to benchmark with my actual queries. Feature access and a clean architecture are usually the easier reasons to decide first.

Five shortcuts I do not want to memorize

Tempting shortcut	More accurate model
“`database/sql` is a PostgreSQL driver.”	It is a generic API and pool that needs a driver.
“`database/sql` always uses the simple protocol.”	The selected driver chooses PostgreSQL protocol behavior.
“Parse, Bind and Execute mean three round trips.”	They are protocol stages; batching and caching determine round trips.
“Canceling `database/sql` only stops Go.”	The context reaches a capable driver; the driver determines server and connection behavior.
“`pgxpool` is always the winner.”	It is an excellent PostgreSQL-native choice, but `*sql.DB` compatibility or multi-database requirements can justify another path.

Looking more closely at the PostgreSQL paths

PostgreSQL makes the layering especially visible because pgx supports both a native API and a database/sql adapter.

Native pgx with pgxpool

A *pgx.Conn represents one connection and should not be shared for concurrent query execution. A server normally uses pgxpool.Pool, which owns multiple pgx connections.

config, err := pgxpool.Parse(os.Getenv("DATABASE_URL"))if err != nil {	return err} config.MaxConns = 20config.MinConns = 2config.MaxConnLifetime = 30 * time.Minuteconfig.MaxConnIdleTime = 5 * time.Minute pool, err := pgxpool.NewWithConfig(ctx, config)if err != nil {	return err}defer pool.Close() if err := pool.Ping(ctx); err != nil {	return fmt.Errorf("ping postgres: %w", err)}

The native API is attractive when PostgreSQL is an intentional dependency. It exposes PostgreSQL features including COPY, LISTEN/NOTIFY, rich type codecs, binary formats, batching and query execution modes.

For bulk ingestion, CopyFrom expresses a PostgreSQL-native operation rather than constructing thousands of individual inserts:

count, err := pool.CopyFrom(	ctx,	pgx.Identifier{"events"},	[]string{"block_number", "transaction_hash", "payload"},	pgx.CopyFromSlice(len(events), func(i int) ([]any, error) {		return []any{			events[i].BlockNumber,			events[i].TransactionHash,			events[i].Payload,		}, nil	}),)

Native pgx also has direct error types:

var pgErr *pgconn.PgErrorif errors.As(err, &pgErr) {	switch pgErr.Code {	case "23505":		return ErrAlreadyExists	case "23503":		return ErrInvalidReference	}}

The SQLSTATE code is stable machine-readable information. Matching an English error message is not.

pgx through `database/sql`

pgx/stdlib adapts pgx to the standard interface:

config, err := pgx.ParseConfig(os.Getenv("DATABASE_URL"))if err != nil {	return err} db := stdlib.OpenDB(*config)db.SetMaxOpenConns(20)db.SetMaxIdleConns(10)db.SetConnMaxLifetime(30 * time.Minute)

This is useful when a library requires *sql.DB, when the codebase standardizes on the generic interface, or when PostgreSQL-specific features are not central.

The stack is:

application → sqlx (optional) → database/sql → pgx/stdlib → PostgreSQL

Using pgx as the driver does not mean the application is using the full native pgx API.

Another PostgreSQL driver

lib/pq also implements the database/sql contract. Code written against *sql.DB can look almost identical regardless of whether the registered PostgreSQL driver is pgx/stdlib or lib/pq. Protocol behaviour, supported types, errors and maintenance direction still belong to the driver.

pgx query execution modes and prepared statements

pgx uses PostgreSQL's extended protocol by default and can automatically prepare and cache statements. The important detail is that prepared statements belong to physical connections.

pgxpool  ├── connection 1 → cached statement A  ├── connection 2 → cached statement A  └── connection 3 → no statement A yet

The pool does not create one magical server-side statement shared by every connection. Each connection encounters and caches query shapes independently.

pgx exposes several execution modes. The exact choice matters when a proxy such as PgBouncer operates in transaction-pooling mode. Current PgBouncer versions can support protocol-level prepared statements when configured appropriately; otherwise pgx's exec, describe-cache or simple-protocol modes may be required. I prefer configuration that matches the deployed proxy rather than changing modes based on a benchmark run against a direct local connection.

With database/sql, a Stmt prepared from a DB is safe for concurrent use and the package arranges for it across pooled connections. A statement prepared from a transaction or dedicated connection remains bound to that connection. Again, the apparent statement object and the server's physical prepared statement are not the same layer.

MySQL: the same generic API, a different driver

go-sql-driver/mysql is a pure-Go implementation of the MySQL protocol for database/sql. The pool still comes from the standard library; the driver handles MySQL authentication, packets, prepared statements, values and server errors.

I prefer building a structured configuration instead of assembling a DSN by hand:

config := mysql.NewConfig()config.User = os.Getenv("MYSQL_USER")config.Passwd = os.Getenv("MYSQL_PASSWORD")config.Net = "tcp"config.Addr = "mysql.internal:3306"config.DBName = "orders"config.ParseTime = true connector, err := mysql.NewConnector(config)if err != nil {	return err} db := sql.OpenDB(connector)db.SetMaxOpenConns(20)db.SetMaxIdleConns(20)db.SetConnMaxLifetime(3 * time.Minute)

The connection lifetime should usually be shorter than server, proxy or load-balancer connection limits. Otherwise the application repeatedly discovers dead connections at query time.

MySQL placeholders use ?:

row := db.QueryRowContext(	ctx,	`SELECT status FROM orders WHERE id = ?`,	orderID,)

The driver has an interpolateParams option that can encode parameters into one query string on the client and avoid the prepare/execute/close flow for ordinary parameterized calls. That is a driver-specific trade-off, not a feature of database/sql. It is disabled by default, and the driver rejects combinations with certain multibyte encodings because unsafe interpolation would risk SQL injection.

MySQL also exposes database-specific choices through its DSN: parseTime, time location, read/write timeouts, TLS configuration, packet limits and whether multiple statements are allowed. These details are exactly why a common API does not eliminate driver knowledge.

Error handling is database-specific too:

var mysqlErr *mysql.MySQLErrorif errors.As(err, &mysqlErr) && mysqlErr.Number == 1062 {	return ErrAlreadyExists}

PostgreSQL's unique-violation SQLSTATE is 23505; MySQL commonly reports error number 1062. My repository layer can translate both into the same domain error, but the adapters must understand their own database.

SQLite: a driver without a database server

SQLite makes the word “driver” easier to understand because it removes the network.

modernc.org/sqlite implements database/sql using a CGo-free Go port of SQLite. mattn/go-sqlite3 is the widely used CGo-based alternative. Both can present the standard Go API, but the execution path is different from PostgreSQL or MySQL:

application → database/sql → SQLite driver → SQLite library → database file

There is no remote server and no PostgreSQL/MySQL wire protocol. A “connection” is a handle to an in-process engine and database file.

import (	"database/sql" 	_ "modernc.org/sqlite") db, err := sql.Open("sqlite", "file:app.db")if err != nil {	return err} // A conservative starting point for a small write-heavy application.db.SetMaxOpenConns(1)

Pooling still matters, but the constraints change:

A plain :memory: database can be private to one connection. Opening another pooled connection may reveal an apparently empty database.
SQLite allows many readers but has constrained write concurrency. WAL mode and a busy timeout can improve behaviour, but they do not turn it into a client/server database.
Settings such as foreign-key enforcement can be connection-local. They must be applied consistently through driver configuration or connection hooks, not once on an arbitrary pooled connection.
Increasing MaxOpenConns can increase lock contention rather than throughput.

This is a good example of why copying PostgreSQL pool settings to every SQL database is unsafe.

SQL Server: `database/sql` over TDS

Microsoft's go-mssqldb is a pure-Go database/sql driver that speaks TDS, the Tabular Data Stream protocol used by SQL Server and Azure SQL.

connector, err := mssql.NewConnector(	"sqlserver://user:password@sql.internal:1433?database=payments",)if err != nil {	return err} db := sql.OpenDB(connector) row := db.QueryRowContext(	ctx,	`SELECT status FROM payments WHERE id = @id`,	sql.Named("id", paymentID),)

The outer shape—*sql.DB, contexts, transactions and pool settings—is familiar. Authentication options, parameter rules, SQL types, TDS behaviour and error codes come from the SQL Server driver and server.

Same interface does not mean portable application

database/sql provides useful mechanical portability, but a real application can still depend on its database in many ways.

Concern	PostgreSQL	MySQL	SQLite	SQL Server
Common placeholder	`$1`	`?`	`?` or named forms	`@name` / named value
Connection path	Network PG protocol	Network MySQL protocol	In-process library	Network TDS
Generated IDs	Often `RETURNING`	Often `LastInsertId`	Often `LastInsertId`	Commonly `OUTPUT`
Upsert style	`ON CONFLICT`	`ON DUPLICATE KEY`	`ON CONFLICT`	Database-specific patterns
Type concerns	arrays, JSONB, UUID, ranges	unsigned integers, decimal, datetime, JSON	dynamic typing and affinity	uniqueidentifier, datetimeoffset, decimal
Concurrency model	MVCC server	storage-engine dependent server	file locks, constrained writer concurrency	lock/versioning options

Even transaction isolation names can hide different defaults and behaviour. Schema migrations, locking, generated keys, bulk loading, error classification and retry rules remain adapter responsibilities.

The portability I value is therefore at my own boundary:

type UserStore interface {	Find(ctx context.Context, id UserID) (User, error)	Create(ctx context.Context, user User) error	Transfer(ctx context.Context, from, to UserID, amount Money) error}

PostgreSQL and MySQL implementations can satisfy the same domain interface while using the SQL and error handling appropriate to each database. This is more honest than pretending every query is portable.

Context cancellation: API guarantee, driver mechanism

Both database/sql and native pgx accept contexts. It is incorrect to say that database/sql cannot cancel database work.

ctx, cancel := context.WithTimeout(request.Context(), 2*time.Second)defer cancel() rows, err := db.QueryContext(ctx, reportQuery)

Context-aware driver interfaces are required to return when the context is cancelled. The exact mechanism remains database-specific. A driver may send a cancellation message, interrupt an in-process engine, close a socket or mark a connection unusable when it cannot safely recover protocol state.

Cancellation also applies while waiting to acquire a pool connection. This matters because a request can spend its entire deadline in the pool queue without ever reaching the database.

I therefore distinguish at least three timings:

pool wait     → waiting for a connectionquery time    → database execution and row transfertotal request → application work around the query

Measuring only server query duration can miss saturation in the application pool.

Transactions pin connections

The transaction pattern is similar across drivers because database/sql owns the outer lifecycle:

func transfer(	ctx context.Context,	db *sql.DB,	fromID int64,	toID int64,	amount int64,) error {	tx, err := db.BeginTx(ctx, &sql.TxOptions{		Isolation: sql.LevelSerializable,	})	if err != nil {		return err	}	defer func() { _ = tx.Rollback() }() 	if _, err := tx.ExecContext(		ctx,		`UPDATE accounts SET balance = balance - $1 WHERE id = $2`,		amount,		fromID,	); err != nil {		return err	} 	if _, err := tx.ExecContext(		ctx,		`UPDATE accounts SET balance = balance + $1 WHERE id = $2`,		amount,		toID,	); err != nil {		return err	} 	return tx.Commit()}

Inside the function, every operation goes through tx. Calling db.ExecContext would use an independently acquired connection outside the transaction.

The example uses PostgreSQL placeholders. A MySQL adapter would use ?, and the databases may report serialization conflicts differently. Retrying requires rerunning the complete transaction from a clean state. I also keep network calls, message publication and other irreversible side effects outside a retryable transaction body.

Choosing the stack intentionally

Situation	A reasonable starting point
PostgreSQL-only service using COPY, LISTEN/NOTIFY or rich PostgreSQL types	Native `pgx` + `pgxpool`
PostgreSQL service whose libraries expect `*sql.DB`	`database/sql` + `pgx/stdlib`
Existing `database/sql` code that wants easier struct scanning	`sqlx` above the chosen driver
Typed SQL without runtime mapping	`sqlc` generating for `database/sql` or pgx
MySQL or MariaDB service	`database/sql` + `go-sql-driver/mysql`
Embedded local database	`database/sql` + a deliberate SQLite driver
SQL Server or Azure SQL	`database/sql` + `go-mssqldb`
One application genuinely supporting several engines	Domain interface + database-specific adapters

I would not choose native pgx only because “native must be faster,” and I would not choose database/sql only because “generic must be portable.” The correct choice follows the features, libraries, operational environment and database commitment of the application.

What I want to remember

The path

App asks → pool lends → driver translates → database executes → result returns.

*sql.DB is the pool, not one connection.
sqlx, sqlc and ORMs help the application; they do not speak the database protocol themselves.
The driver owns database-specific encoding, protocol operations, cancellation details and error translation.
The database owns SQL parsing, planning, storage and concurrency control.

The pool's three choices

Idle → open → wait.

Reuse an idle connection when one exists.
Otherwise open a connection when below MaxOpenConns.
Otherwise wait until a connection returns or the context expires.

Open rows keep a connection checked out. A transaction pins one connection until Commit or Rollback. I close rows, check Rows.Err, and finish every transaction.

Which PostgreSQL path?

Native pgx + pgxpool: choose this when PostgreSQL features such as COPY, rich types, batching or LISTEN/NOTIFY are part of the design.
database/sql + pgx/stdlib: choose this when code or libraries expect *sql.DB.

Neither path is automatically correct. I choose from the features and interfaces the application actually needs.

The databases in one line each

PostgreSQL: network server, PG protocol, $1 placeholders, strong pgx-native option.
MySQL: network server, MySQL protocol, ? placeholders, driver-specific DSN behavior.
SQLite: in-process engine and local file; more connections do not automatically mean more write throughput.
SQL Server: network server using TDS, commonly with named parameters.

The rules that prevent most mistakes

sql.Open may be lazy; use PingContext when startup must prove connectivity.
Pool limits create backpressure. Inspect WaitCount and WaitDuration instead of guessing.
Parameters protect values, not table names, column names or SQL keywords. Allowlist SQL structure.
Prepared statements ultimately belong to physical connections, even when a pool hides that detail.
A common Go API does not erase SQL dialects, types, isolation behavior or error codes.
For real portability, keep a small domain interface and write honest database-specific adapters underneath.