# Echo × database production design: choosing pgx / sqlc / GORM, connection pools, transaction boundaries, and context propagation > An implementation guide to designing the database layer of Go Echo (v5) at production quality. With real code it covers selecting pgx / sqlc / GORM, tuning the pgxpool connection pool, context propagation that threads c.Request().Context(), safe transaction boundaries (WithTx), the Repository pattern, SQL-injection countermeasures, migrations, and serverless connection exhaustion. - Published: 2026-06-28 - Author: 友田 陽大 - Tags: Go, Echo, PostgreSQL, 型安全, アーキテクチャ設計, セキュリティ - URL: https://tomodahinata.com/en/blog/go-echo-database-postgresql-pgx-sqlc-gorm-transaction-guide - Category: Go & Echo in production - Pillar guide: https://tomodahinata.com/en/blog/go-echo-framework-production-guide ## Key points - Pass the handler's c.Request().Context() to every query. This threads timeout, cancellation, and tracing all the way to the DB, so a disconnected process doesn't keep occupying the DB. - If type safety is the top priority, sqlc (generate Go from SQL); for low-level control, pgx; for development speed, GORM. Choose by the trade-offs of the three. - Work the connection pool's (pgxpool) MaxConns back from the DB's max_connections and the instance count. In serverless, prepare for connection exhaustion with RDS Proxy, etc. - Consolidate transactions into a WithTx helper with defer tx.Rollback (a no-op after commit), committing/rolling back in one place without swallowing. - Always assemble queries with placeholders ($1). String concatenation is SQL injection. Guarantee idempotency on the DB side with a unique constraint. --- 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](/blog/go-echo-framework-production-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. ```go 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](/blog/opentelemetry-observability-production-tracing-metrics-logs) becomes a single thread. ```go // 重いクエリには期限を付ける 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.** | Aspect | `database/sql` + **pgx** | **sqlc** | **GORM** | | --- | --- | --- | --- | | Abstraction | low (raw SQL) | low (raw SQL + type generation) | high (ORM) | | Type safety | manual mapping | **auto-generate Go from SQL (strongest)** | struct-tag dependent | | Learning cost | medium | medium | low–medium (custom DSL) | | Performance | ◎ | ◎ | ○ (reflection) | | Complex queries | ◎ (SQL as-is) | ◎ (SQL as-is) | △ (tends to retreat to raw SQL) | | Best scene | fine control, high performance | **both type safety and SQL** | CRUD-centric, development-speed priority | **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](/blog/ai-driven-development-quality-gates-ci-type-safety-test-security). - **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. ```go 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](/blog/go-echo-deployment-docker-distroless-ecs-cloud-run-graceful-shutdown-guide) 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. ```go 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.** ```go // 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. ```go 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](/blog/payment-double-charge-prevention-idempotency-procurement-guide). --- ## 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](/blog/go-echo-clean-architecture-dependency-injection-google-wire-guide). ```go // ドメイン層：インターフェース（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](/blog/go-echo-testing-strategy-httptest-echotest-testcontainers-guide) 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.**" ```go // ❌ 絶対にやらない：文字列連結（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.** ```go 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](/blog/go-echo-request-binding-validation-error-handling-guide), 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. ```go 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](#1-most-important-thread-context-from-the-handler-to-the-db). --- ## 8. Migrations: manage the schema as code too Do schema changes with version-controlled migrations, not manual SQL (`golang-migrate` / `goose` are standards). ```bash # 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](/blog/alembic-zero-downtime-migrations-sqlalchemy) (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)](/blog/aws-lambda-rds-aurora-connection-management-rds-proxy-vpc-guide). 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](/blog/go-echo-jwt-authentication-authorization-rbac-refresh-token-guide) that rides on top of this, and the [clean architecture](/blog/go-echo-clean-architecture-dependency-injection-google-wire-guide) that binds it all together.