gRPC Database Integration

Most gRPC services read and write data in a database. A clean connection between the two keeps calls fast, errors meaningful, and data consistent. The repository layer handles storage details, while the handler speaks gRPC. Neither layer needs to know the other's internals.

The Bank Teller Analogy

You ask a bank teller for your balance. The teller opens the vault system, reads the record, and tells you the number. You never see the vault, and you never learn how records are stored. The repository plays the teller. The handler plays the customer service desk that talks to you.

gRPC request
    │
    ▼
Handler ──▶ Service ──▶ Repository ──▶ Database
    ▲                                       │
    └──────────── status code ◀─────────────┘

Use Connection Pools

Opening a database connection for each call is slow. A pool keeps a set of ready connections and lends one per query. Create the pool once at startup and share it across handlers.

db, err := sql.Open("postgres", cfg.DatabaseURL)
if err != nil {
  log.Fatal(err)
}
db.SetMaxOpenConns(25)
db.SetMaxIdleConns(10)
db.SetConnMaxLifetime(30 * time.Minute)

Tune the pool size to the database limits and the number of server instances. Ten servers with 25 connections each open 250 connections in total.

Pass the Context

Every query takes the call context. A cancelled call or an expired deadline stops the query and frees the connection.

func (r *OrderRepo) Find(ctx context.Context, id string) (*Order, error) {
  var o Order
  err := r.db.QueryRowContext(ctx,
    "SELECT id, owner_id, total_cents FROM orders WHERE id = $1", id,
  ).Scan(&o.ID, &o.OwnerID, &o.TotalCents)
  if err == sql.ErrNoRows {
    return nil, ErrNotFound
  }
  return &o, err
}

Translate Database Errors

Clients understand gRPC status codes, not database messages. Convert storage errors in one helper function.

Database Situation	gRPC Code
No row found	NOT_FOUND
Duplicate key	ALREADY_EXISTS
Foreign key violation	FAILED_PRECONDITION
Context deadline reached	DEADLINE_EXCEEDED
Connection failure	UNAVAILABLE
Anything unexpected	INTERNAL

func toStatus(err error) error {
  switch {
  case errors.Is(err, ErrNotFound):
    return status.Error(codes.NotFound, "record not found")
  case errors.Is(err, ErrDuplicate):
    return status.Error(codes.AlreadyExists, "record already exists")
  case errors.Is(err, context.DeadlineExceeded):
    return status.Error(codes.DeadlineExceeded, "operation timed out")
  default:
    return status.Error(codes.Internal, "internal error")
  }
}

Use Transactions for Related Writes

A transaction groups several writes so that all succeed or all fail. Placing an order, for example, creates the order and reduces stock together.

tx, err := db.BeginTx(ctx, nil)
if err != nil {
  return err
}
defer tx.Rollback()

if _, err := tx.ExecContext(ctx, "INSERT INTO orders ...", args...); err != nil {
  return err
}
if _, err := tx.ExecContext(ctx, "UPDATE stock SET qty = qty - $1 WHERE id = $2", q, id); err != nil {
  return err
}
return tx.Commit()

The deferred rollback does nothing after a successful commit. It protects every early return.

Avoid the N+1 Problem

A list method that loads 50 orders and then runs one query per order issues 51 queries. Fetch related rows in one joined query or one query with an IN clause. Fewer round trips cut latency sharply.

Map Between Layers

Keep database models and proto messages separate. Convert between them in small mapping functions. A column rename then never forces a contract change.

Manage Schema Changes

Use migration tools such as golang-migrate or Flyway. Store migrations in version control, run them in order, and write backward compatible changes so old and new server versions can run against the same database during a rollout.

Test with Real Databases

Run repository tests against a throwaway database in a container. Real engines catch problems that mocks hide, such as constraint errors and query syntax mistakes.

Summary

Pool connections, pass the call context into every query, convert database errors to status codes, wrap related writes in transactions, and avoid N+1 queries. Separate storage models from proto messages to keep each layer free to change.