AliveMCP

Guide · ORM Integration

MCP Tools for GORM — connection pool tuning, Preload vs Joins, transaction error handling

Three GORM behaviours routinely trip MCP server authors: GORM accumulates errors on the *gorm.DB result value and does not panic or return early on query failure — calling db.First(&user, id) and then db.Delete(&user) without checking result.Error after the first call will delete the zero-value user struct if the First failed with record not found, silently modifying the wrong row or the entire table; GORM's default SetMaxOpenConns(0) means unlimited connections — a burst of parallel MCP tool calls each opening a connection can exhaust PostgreSQL's max_connections (default 100) before other services on the same database server notice; and db.Raw("SELECT ...").Rows() returns a *sql.Rows cursor that must be explicitly closed with defer rows.Close() — forgetting the defer holds the underlying connection in the pool for the lifetime of the tool call's goroutine (often until the GC runs), slowly starving the pool under concurrent requests.

TL;DR

Call sqlDB.SetMaxOpenConns(25), SetMaxIdleConns(5), SetConnMaxLifetime(time.Hour) on the underlying *sql.DB. Check result.Error after every GORM call. Use db.Preload("Author") for separate-query loading or db.Joins("Author") for JOIN loading. Always defer rows.Close() after db.Raw().Rows(). Wrap multi-step writes in db.Transaction(func(tx *gorm.DB) error { ... }). Health probe: sqlDB.Ping().

Connection pool configuration — sql.DB settings for concurrent tool calls

GORM wraps the standard library's database/sql pool. GORM's own constructor does not set connection limits — the underlying *sql.DB defaults to unlimited open connections and no idle connection pruning. For an MCP server receiving parallel tool calls from multiple agents, this creates unbounded database connections under burst load.

package main

import (
	"database/sql"
	"fmt"
	"log"
	"os"
	"time"

	"gorm.io/driver/postgres"
	"gorm.io/gorm"
	"gorm.io/gorm/logger"
)

var DB *gorm.DB

func InitDB() error {
	dsn := os.Getenv("DATABASE_URL")

	db, err := gorm.Open(postgres.Open(dsn), &gorm.Config{
		Logger: logger.Default.LogMode(logger.Warn), // Log slow queries and errors only
		// Don't use singular table names (GORM default is singular)
		NamingStrategy: schema.NamingStrategy{SingularTable: false},
		// Disable auto-create transactions for each Create/Update/Delete
		// When you run these inside db.Transaction(), wrapping in another transaction is wasteful
		SkipDefaultTransaction: true,
		// Prepare statements — caches prepared statements for reuse
		PrepareStmt: true,
	})
	if err != nil {
		return fmt.Errorf("gorm.Open: %w", err)
	}

	// Access the underlying *sql.DB to configure the pool
	sqlDB, err := db.DB()
	if err != nil {
		return fmt.Errorf("db.DB(): %w", err)
	}

	// Pool sizing for MCP server — tune to expected parallel tool call concurrency
	sqlDB.SetMaxOpenConns(25)              // Max concurrent database connections
	sqlDB.SetMaxIdleConns(5)               // Keep 5 warm connections in the pool
	sqlDB.SetConnMaxLifetime(time.Hour)    // Recycle connections after 1 hour
	sqlDB.SetConnMaxIdleTime(10 * time.Minute) // Close idle connections after 10 min

	// Verify connectivity before the MCP server starts
	if err := sqlDB.Ping(); err != nil {
		return fmt.Errorf("database ping failed: %w", err)
	}

	DB = db
	log.Println("GORM: database connection pool ready")
	return nil
}

// Graceful shutdown — close all pool connections
func CloseDB() {
	sqlDB, err := DB.DB()
	if err != nil {
		log.Printf("GORM: error getting sql.DB for close: %v", err)
		return
	}
	sqlDB.Close()
}

PrepareStmt: true caches the prepared statement for each unique query the server executes, reducing query planning overhead for repeated tool calls. The cache is stored in *gorm.DB's statement cache — safe for concurrent use. Be aware that prepared statements are per-connection in PostgreSQL; when using PgBouncer in transaction-pooling mode, prepared statements do not work across pool connections and you must disable this option.

Error handling — checking result.Error after every GORM operation

GORM chains operations on a *gorm.DB value and accumulates errors in result.Error. If you ignore an error from one call and use the result struct in a subsequent write, you may operate on a zero-value struct. gorm.ErrRecordNotFound is the most common unchecked error — db.First() sets it when no row matches, returning the struct unchanged.

package handlers

import (
	"errors"
	"fmt"
	"gorm.io/gorm"
	"your-module/models"
)

// BAD: unchecked error leads to operating on zero-value struct
func deleteUserBad(db *gorm.DB, userID string) error {
	var user models.User
	db.First(&user, "id = ?", userID) // If not found, user is zero-value — no error check!
	db.Delete(&user)                  // Deletes WHERE id = '' (zero value) — wrong row or all rows
	return nil
}

// GOOD: check every result
func deleteUserGood(db *gorm.DB, userID string) error {
	var user models.User
	result := db.First(&user, "id = ?", userID)
	if result.Error != nil {
		if errors.Is(result.Error, gorm.ErrRecordNotFound) {
			return fmt.Errorf("user %q not found", userID)
		}
		return fmt.Errorf("query user: %w", result.Error)
	}

	if result := db.Delete(&user); result.Error != nil {
		return fmt.Errorf("delete user: %w", result.Error)
	}
	return nil
}

// Helper: check RowsAffected for update/delete operations
// GORM does not return ErrRecordNotFound for Update/Delete — check RowsAffected
func updateUserBalance(db *gorm.DB, userID string, delta int64) error {
	result := db.Model(&models.User{}).
		Where("id = ?", userID).
		Update("balance", gorm.Expr("balance + ?", delta))

	if result.Error != nil {
		return fmt.Errorf("update balance: %w", result.Error)
	}
	if result.RowsAffected == 0 {
		return fmt.Errorf("user %q not found or balance update had no effect", userID)
	}
	return nil
}

// Rows cursor: always defer rows.Close() to prevent connection leak
func listActiveUserIDs(db *gorm.DB) ([]string, error) {
	rows, err := db.Model(&models.User{}).
		Where("is_active = ?", true).
		Select("id").
		Rows()
	if err != nil {
		return nil, fmt.Errorf("query active users: %w", err)
	}
	defer rows.Close() // CRITICAL: must close rows to return connection to pool

	var ids []string
	for rows.Next() {
		var id string
		if err := rows.Scan(&id); err != nil {
			return nil, fmt.Errorf("scan user id: %w", err)
		}
		ids = append(ids, id)
	}
	return ids, rows.Err()
}

GORM's Updates() method ignores zero-value fields (empty string, 0, false) by default — it uses the struct's non-zero fields to build the SET clause. To update a field to its zero value, use db.Model(&user).Select("field_name").Updates(&user) or use map[string]interface{} as the update argument instead of a struct.

Preload vs Joins — relationship loading strategies for MCP tool results

GORM's Preload issues a separate query for each preloaded association: it fetches the parent records first, collects their foreign keys, then issues one additional query per association using an IN clause. Joins adds a SQL JOIN to the primary query. Use Preload for has-many (avoids row multiplication); use Joins for belongs-to (avoids an extra round-trip).

package handlers

import (
	"gorm.io/gorm"
	"gorm.io/gorm/clause"
	"your-module/models"
)

// Preload: separate query per association — good for has-many
// SELECT * FROM posts LIMIT 50
// SELECT * FROM users WHERE id IN (authorId1, authorId2, ...)
// SELECT * FROM comments WHERE post_id IN (postId1, postId2, ...)
func listPostsWithPreload(db *gorm.DB) ([]models.Post, error) {
	var posts []models.Post
	result := db.
		Preload("Author", func(db *gorm.DB) *gorm.DB {
			return db.Select("id", "name", "email") // Limit Author columns
		}).
		Preload("Comments", func(db *gorm.DB) *gorm.DB {
			return db.Order("created_at DESC").Limit(5) // Latest 5 comments per post
		}).
		Preload("Comments.Author").  // Nested preload — load comment authors too
		Order("created_at DESC").
		Limit(50).
		Find(&posts)
	return posts, result.Error
}

// Joins: SQL JOIN — good for belongs-to (many-to-one), avoids round-trip
// SELECT posts.*, users.name AS author_name FROM posts
// LEFT JOIN users ON users.id = posts.author_id
// WHERE users.is_active = true
func listPostsByActiveAuthors(db *gorm.DB) ([]models.Post, error) {
	var posts []models.Post
	result := db.
		Joins("Author").              // LEFT JOIN users ON users.id = posts.author_id
		Where("Author.is_active = ?", true).  // Filter on joined table
		Order("posts.created_at DESC").
		Limit(50).
		Find(&posts)
	return posts, result.Error
}

// Preload all associations — use clause.Associations for all direct relationships
// Equivalent to Preload("Author").Preload("Tags").Preload("Category")
func getPostFull(db *gorm.DB, postID string) (*models.Post, error) {
	var post models.Post
	result := db.
		Preload(clause.Associations). // Loads all direct associations
		First(&post, "id = ?", postID)
	if result.Error != nil {
		return nil, result.Error
	}
	return &post, nil
}

// Conditional preload: load comments only if the client requested them
func getPost(db *gorm.DB, postID string, includeComments bool) (*models.Post, error) {
	var post models.Post
	query := db
	if includeComments {
		query = query.Preload("Comments", func(db *gorm.DB) *gorm.DB {
			return db.Order("created_at ASC").Limit(20)
		})
	}
	result := query.First(&post, "id = ?", postID)
	return &post, result.Error
}

For tool results that aggregate data (counts, sums, averages), prefer raw SQL via db.Raw() with Scan(&result) over loading full associations and computing in Go. A SELECT COUNT(*) FROM comments WHERE post_id = ? is always faster than loading all comments and calling len(post.Comments).

Transaction management in MCP tool handlers

GORM's db.Transaction() begins a transaction, passes a transactional *gorm.DB to the callback, and commits on nil return or rolls back on a non-nil error return. All operations inside the callback must use the tx parameter, not the outer db — using db inside a callback runs the query outside the transaction on a separate pool connection.

package handlers

import (
	"errors"
	"fmt"
	"gorm.io/gorm"
	"gorm.io/gorm/clause"
	"your-module/models"
)

// Atomic tool handler: transfer balance between two users
func TransferBalance(db *gorm.DB, fromID, toID string, amount int64) error {
	return db.Transaction(func(tx *gorm.DB) error {
		// Lock both rows to prevent concurrent double-spends
		var fromUser, toUser models.User

		if err := tx.Clauses(clause.Locking{Strength: "UPDATE"}).
			First(&fromUser, "id = ?", fromID).Error; err != nil {
			return fmt.Errorf("lock fromUser: %w", err)
		}
		if err := tx.Clauses(clause.Locking{Strength: "UPDATE"}).
			First(&toUser, "id = ?", toID).Error; err != nil {
			return fmt.Errorf("lock toUser: %w", err)
		}

		if fromUser.Balance < amount {
			return fmt.Errorf("insufficient balance: have %d, need %d", fromUser.Balance, amount)
		}

		// Both updates run in the same transaction
		if err := tx.Model(&fromUser).Update("balance", gorm.Expr("balance - ?", amount)).Error; err != nil {
			return fmt.Errorf("deduct fromUser: %w", err)
		}
		if err := tx.Model(&toUser).Update("balance", gorm.Expr("balance + ?", amount)).Error; err != nil {
			return fmt.Errorf("credit toUser: %w", err)
		}

		// Audit record — also in the transaction
		if err := tx.Create(&models.TransferAudit{
			FromUserID: fromID,
			ToUserID:   toID,
			Amount:     amount,
		}).Error; err != nil {
			return fmt.Errorf("audit log: %w", err)
		}

		return nil // tx.Transaction() commits here
		// Any non-nil return triggers automatic rollback
	})
}

// Savepoint pattern for bulk operations with partial success
func BulkCreateTags(db *gorm.DB, names []string) ([]Result, error) {
	results := make([]Result, 0, len(names))

	err := db.Transaction(func(tx *gorm.DB) error {
		for _, name := range names {
			// Nested transaction uses SAVEPOINTs automatically
			nestedErr := tx.Transaction(func(innerTx *gorm.DB) error {
				tag := models.Tag{Name: name}
				return innerTx.Create(&tag).Error
			})
			if nestedErr != nil {
				// SAVEPOINT rolled back — outer tx still open, continue loop
				results = append(results, Result{Name: name, Status: "skipped", Error: nestedErr.Error()})
			} else {
				results = append(results, Result{Name: name, Status: "created"})
			}
		}
		return nil // Commit outer transaction regardless of individual failures
	})

	return results, err
}

// Isolation level for read-then-write pattern
func GetAndReserveItem(db *gorm.DB, itemID, userID string) error {
	return db.Transaction(func(tx *gorm.DB) error {
		// Use REPEATABLE READ to prevent phantom reads between check and update
		tx.Exec("SET TRANSACTION ISOLATION LEVEL REPEATABLE READ")

		var item models.Item
		if err := tx.First(&item, "id = ?", itemID).Error; err != nil {
			if errors.Is(err, gorm.ErrRecordNotFound) {
				return fmt.Errorf("item %q not found", itemID)
			}
			return err
		}

		if item.ReservedBy != "" {
			return fmt.Errorf("item already reserved by %s", item.ReservedBy)
		}

		return tx.Model(&item).Update("reserved_by", userID).Error
	})
}

GORM's automatic nested transaction uses SAVEPOINTs when the outer transaction is already active. SAVEPOINT names are generated from a counter on the outer transaction — they are not stable across calls and should not be referenced manually. Rely on GORM's tx.Transaction() nesting rather than constructing SAVEPOINT SQL by hand.

Health probe — GORM database reachability from an MCP server

GORM wraps database/sql, so the standard sql.DB.Ping() method is the most direct health check. It verifies that the pool can acquire a connection and the database server responds.

package health

import (
	"context"
	"fmt"
	"time"
	"gorm.io/gorm"
)

type HealthResult struct {
	Healthy   bool   \`json:"healthy"\`
	LatencyMs int64  \`json:"latency_ms,omitempty"\`
	Error     string \`json:"error,omitempty"\`
	PoolStats PoolStats \`json:"pool_stats"\`
}

type PoolStats struct {
	MaxOpenConns  int \`json:"max_open_conns"\`
	OpenConns     int \`json:"open_conns"\`
	InUseConns    int \`json:"in_use_conns"\`
	IdleConns     int \`json:"idle_conns"\`
	WaitCount     int64 \`json:"wait_count"\`
}

func CheckGORMHealth(db *gorm.DB) HealthResult {
	sqlDB, err := db.DB()
	if err != nil {
		return HealthResult{Healthy: false, Error: fmt.Sprintf("db.DB(): %v", err)}
	}

	ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
	defer cancel()

	start := time.Now()
	if err := sqlDB.PingContext(ctx); err != nil {
		return HealthResult{Healthy: false, Error: fmt.Sprintf("ping: %v", err)}
	}
	latencyMs := time.Since(start).Milliseconds()

	stats := sqlDB.Stats()
	return HealthResult{
		Healthy:   true,
		LatencyMs: latencyMs,
		PoolStats: PoolStats{
			MaxOpenConns: stats.MaxOpenConnections,
			OpenConns:    stats.OpenConnections,
			InUseConns:   stats.InUse,
			IdleConns:    stats.Idle,
			WaitCount:    stats.WaitCount,
		},
	}
}

// Register as MCP tool for agent-driven diagnostics
// server.RegisterTool("db_health", func(ctx context.Context, req ToolRequest) (any, error) {
//   return CheckGORMHealth(DB), nil
// })

The WaitCount field in sql.DBStats is a running total of the number of times a goroutine waited for a connection from the pool since the process started. If this number is increasing rapidly, the pool MaxOpenConns is too small for the current tool call concurrency. AliveMCP monitors your MCP server endpoints on a 60-second probe cycle and alerts when response latency spikes — giving you early warning of pool saturation before it causes tool call timeouts.

Related guides