Echo × database production design: choosing pgx / sqlc / GORM, connection pools, transaction boundaries, and context propagation

An API's reliability is ultimately decided by the design of the database layer. Mistake the connection pool and it exhausts under high load; mistake the transaction boundary and inconsistency remains; forget to pass context and the client disconnects but the DB keeps processing — such accidents are invisible in "a working demo" and only bare their fangs at production peak.

This article is the database chapter of the Go Echo production-operations guide. Because Echo's role is a thin HTTP layer, most of the DB design is the manners of Go's standard library and each driver. This article implements "from the Echo handler to the DB, what to connect how," from the viewpoints of type safety, resilience, and security.

Rules for this article: Echo's API is based on the official documentation (v5, as of June 2026). DB libraries (pgx v5 / sqlc / GORM v2 / golang-migrate) are actively updated, so confirm the latest API in each official doc before production rollout. Connection info (DSN, credentials) assumes environment variables / a secrets manager and is not baked into code or images.

1. Most important: thread context from the handler to the DB

When calling the DB from an Echo handler, always pass c.Request().Context(). This is the one "Echo-specific" point in this article, and the most effective.

func (h *UserHandler) Get(c *echo.Context) error {
	ctx := c.Request().Context() // ← リクエストの context を取り出す

	user, err := h.repo.FindByID(ctx, c.Param("id")) // ← DB まで渡す
	if err != nil {
		return err
	}
	return c.JSON(http.StatusOK, user)
}

Why it matters. This ctx carries the request's lifecycle.

• Client disconnect: when the browser leaves, ctx is canceled and the in-progress query is also aborted. It prevents the accident of a disconnected process keeping a DB connection occupied.
• Timeout: wire in context.WithTimeout and you can cut off a slow query at a deadline (now that middleware.Timeout was removed in v5, express timeouts with context).
• Trace propagation: OpenTelemetry's trace ID passes through ctx to the DB span, and observability becomes a single thread.

// 重いクエリには期限を付ける
ctx, cancel := context.WithTimeout(c.Request().Context(), 3*time.Second)
defer cancel()
rows, err := pool.Query(ctx, "SELECT ...")

Using context.Background() for a DB call is an antipattern. The moment you discard the request-derived ctx, you lose all the benefits above.

2. Library selection: choose pgx / sqlc / GORM by trade-offs

Starting with "GORM for now" and regretting it is a common story. Choose among the three options by axis.

Decision guide:

• If you most value type safety, use sqlc. From a query written in a .sql file, Go code is generated with typed arguments and return values. You get both "the freedom to write SQL" and "compile-time type guarantees," the choice most compatible with this site's discipline of type safety.
• If you need low-level control and top performance, use pgx (a PostgreSQL-only driver).
• If CRUD-centric and development-speed priority, use GORM. But if you end up fleeing to raw SQL for complex queries, sqlc/pgx is more consistent from the start.

3. pgx: configure the connection pool correctly

pgxpool is a connection pool essential in production. Mistake the pool's upper-limit design and the service goes down with too many connections under high load.

import "github.com/jackc/pgx/v5/pgxpool"

func NewPool(ctx context.Context, dsn string) (*pgxpool.Pool, error) {
	cfg, err := pgxpool.ParseConfig(dsn) // DSN は os.Getenv("DATABASE_URL")
	if err != nil {
		return nil, err
	}
	cfg.MaxConns = 10                       // このインスタンスが握る最大接続数
	cfg.MinConns = 2                        // 常時温めておく最小接続数
	cfg.MaxConnLifetime = time.Hour         // 接続を作り直す周期（DB側の制限対策）
	cfg.MaxConnIdleTime = 30 * time.Minute  // アイドル接続の回収
	cfg.HealthCheckPeriod = time.Minute     // 死んだ接続の検出

	return pgxpool.NewWithConfig(ctx, cfg)
}

How to decide MaxConns: the upper limit is the DB's max_connections divided by the total number of app instances, minus margin for admin connections. Example: max_connections=100, 4 app instances, 20 reserved → the ceiling is (100 - 20) / 4 = 20. Set this "big for now" and autoscaling raises connections all at once and the DB dies first.

Create the pool only once at app startup and share it across Echo handlers. Do the lifespan-equivalent initialization in main and close gracefully with defer pool.Close().

3.1 Near-type-safe row mapping (pgx v5)

pgx v5 has helpers to stuff rows into a struct. You can avoid the hand-written rows.Scan hell.

type User struct {
	ID    string `db:"id"`
	Name  string `db:"name"`
	Email string `db:"email"`
}

func (r *UserRepo) FindByID(ctx context.Context, id string) (*User, error) {
	rows, err := r.pool.Query(ctx, `SELECT id, name, email FROM users WHERE id = $1`, id)
	if err != nil {
		return nil, err
	}
	user, err := pgx.CollectExactlyOneRow(rows, pgx.RowToStructByName[User])
	if errors.Is(err, pgx.ErrNoRows) {
		return nil, ErrUserNotFound // ドメインエラーへ変換（後述）
	}
	return &user, err
}

4. Transaction boundary: consolidate into a WithTx helper

A transaction is a mechanism to make "multiple writes all-or-nothing." Hand-writing Begin / Commit / Rollback everywhere leaks connections through forgetting to write the rollback. Consolidate into one helper.

// fn の中の処理を1トランザクションで実行する。エラー/panic なら rollback
func (r *Store) WithTx(ctx context.Context, fn func(tx pgx.Tx) error) error {
	tx, err := r.pool.Begin(ctx)
	if err != nil {
		return err
	}
	defer tx.Rollback(ctx) // commit 済みなら no-op。panic 時も確実に巻き戻る

	if err := fn(tx); err != nil {
		return err // defer の Rollback が走る
	}
	return tx.Commit(ctx)
}

The caller can write only the intent of the transaction.

func (uc *OrderUseCase) Place(ctx context.Context, order Order) error {
	return uc.store.WithTx(ctx, func(tx pgx.Tx) error {
		if err := insertOrder(ctx, tx, order); err != nil {
			return err
		}
		return decrementStock(ctx, tx, order.Items) // どちらか失敗すれば両方巻き戻る
	})
}

Guarantee idempotency on the DB: against retries and double submissions, a unique constraint (unique index) is more robust than app-side flag management. Express "the same operation only once" as a DB constraint with INSERT ... ON CONFLICT DO NOTHING or an idempotency-key column. Idempotency design in areas requiring strictness like payments is dug into in payment reliability with zero double charges.

5. Repository pattern: hide the DB behind an interface

If the UseCase layer directly knows pgx or GORM, you can't swap the DB and tests need a real DB. Define the Repository as an interface and hide the implementation behind it. This is also the core of clean architecture + DI.

// ドメイン層：インターフェース（DB 技術を知らない）
type UserRepository interface {
	FindByID(ctx context.Context, id string) (*User, error)
	Create(ctx context.Context, u *User) error
}

// インフラ層：pgx 実装（差し替え可能）
type pgUserRepository struct {
	pool *pgxpool.Pool
}

func NewUserRepository(pool *pgxpool.Pool) UserRepository {
	return &pgUserRepository{pool: pool}
}

Now the UseCase depends only on UserRepository, and the loose coupling holds: swap to a mock in tests, inject the pgx implementation in production.

6. Security: structurally crush SQL injection

You must not string-concatenate external input into SQL. Always use placeholders ($1, $2…). The correct answer is not "be careful" but "make it a structure where you can only write it that way."

// ❌ 絶対にやらない：文字列連結（SQLインジェクション）
q := "SELECT * FROM users WHERE email = '" + email + "'" // 攻撃者が ' OR '1'='1 を注入できる

// ✅ プレースホルダ（ドライバが値を安全にエスケープ／バインド）
rows, err := pool.Query(ctx, "SELECT * FROM users WHERE email = $1", email)

For places where a placeholder can't be used, like a column name in ORDER BY, don't embed user input directly; receive it with an allowlist.

allowed := map[string]string{"created_at": "created_at", "name": "name"}
col, ok := allowed[c.QueryParam("sort")]
if !ok {
	col = "created_at" // 既定値。未知の入力は SQL に渡さない
}
query := fmt.Sprintf("SELECT ... ORDER BY %s", col) // col は allowlist 由来なので安全

Make input validation bite at the boundary with binding & validation, and at the SQL layer doubly protect the state where "only trustworthy values come now."

7. Production notes when you chose GORM

If you use GORM, grasp at least these two.

import (
	"gorm.io/driver/postgres"
	"gorm.io/gorm"
)

db, err := gorm.Open(postgres.Open(dsn), &gorm.Config{})

// ① プールは内部の *sql.DB で設定する（GORM 自体には無い）
sqlDB, _ := db.DB()
sqlDB.SetMaxOpenConns(10)
sqlDB.SetMaxIdleConns(2)
sqlDB.SetConnMaxLifetime(time.Hour)

// ② context を必ず渡す（WithContext）。N+1 は Preload で潰す
var users []User
err = db.WithContext(ctx).Preload("Orders").Find(&users).Error // 関連を1回でまとめて取得

GORM's biggest trap is the N+1 problem (after fetching a list, fetching relations one at a time so N additional queries fly). Fetch in bulk with Preload / Joins. The same point: forget WithContext(ctx) and you lose the benefits of chapter 1.

8. Migrations: manage the schema as code too

Do schema changes with version-controlled migrations, not manual SQL (golang-migrate / goose are standards).

# golang-migrate：up/down のペアでバージョンを進める
migrate -database "$DATABASE_URL" -path ./migrations up

Verify migration application and consistency in CI, and make it an operation that flows before production deploy. Zero-downtime schema evolution (backward-compatible staged changes) is systematized in the design of zero-downtime migrations (a Python example, but the principles are common).

9. Connection exhaustion in serverless / autoscale

In environments where instances are numerous and short-lived, like Lambda or Cloud Run, each instance holding a pool makes connection counts explode. It immediately eats up max_connections and the DB goes down.

• Resident servers (ECS/Cloud Run min>0): sharing a pool with pgxpool works straightforwardly.
• Functions like Lambda: put a connection proxy (RDS Proxy, etc.) in front and have the function side connect to the proxy. Leave connection multiplexing to the proxy.

This trap and its remedy are detailed in Lambda × RDS/Aurora connection management (RDS Proxy). If you run Echo resident on ECS/Fargate, pgxpool is usually enough, which is also the advantage of running Echo as a "resident server" rather than a "function."

Conclusion: 7 principles to make the DB layer production-quality

1. Thread c.Request().Context() through every query. Don't use context.Background() for the DB.
2. For type-safety priority, sqlc; for control, pgx; for speed, GORM — choose by trade-offs.
3. Work pgxpool's MaxConns back from the DB limit ÷ instance count. A too-large setting kills the DB.
4. Consolidate transactions into WithTx. Structure the rollback with defer tx.Rollback.
5. Hide the Repository behind an interface. Both DB swappability and testability.
6. Always placeholders. Concatenation forbidden. Allowlist where placeholders can't be used.
7. Guarantee idempotency on the DB with a unique constraint, and prevent exhaustion with a connection proxy in serverless.

The DB layer is the area with the largest gap between "it works" and "it endures in production." Echo's thinness is also the flip side of being able to build this layer robustly with Go's standard manners. Next, on to the authentication / authorization that rides on top of this, and the clean architecture that binds it all together.