How to set timeout for long running queries in gorm

When using GORM for database operations, it is crucial to ensure that long-running queries do not indefinitely consume resources. To avoid this, we can implement timeouts. In GORM, there are several approaches to manage query timeouts:

1. Using the Database's Native Timeout Mechanism

Most modern database management systems (such as PostgreSQL, MySQL, etc.) support statement-level timeouts via SQL statements. For example, in PostgreSQL, you can set statement_timeout to define a timeout for individual queries.

Example - PostgreSQL

go
db := gorm.Open(postgres.New(postgres.Config{
  DSN: "host=myhost user=myuser dbname=mydb sslmode=disable",
}))

// Set timeout to 5000 milliseconds
db.Exec("SET statement_timeout TO 5000")

// Execute a query that may take a long time
result := db.Find(&users)
if result.Error != nil {
  fmt.Println("Query timeout or other error: ", result.Error)
}

2. Using Context-Based Timeout

GORM supports controlling request timeouts through the context package. This method propagates timeout information across the entire request pipeline, extending beyond database queries.

Example - Using Context Timeout

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

func fetchUsersWithTimeout(db *gorm.DB) ([]User, error) {
  // Create a context with a 5-second timeout
  ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
  defer cancel()

  var users []User
  result := db.WithContext(ctx).Find(&users) // Pass the context to GORM operations
  if result.Error != nil {
    return nil, result.Error
  }

  return users, nil
}

// Usage in caller
db := gorm.Open(sqlite.Open("test.db"), &gorm.Config{})
users, err := fetchUsersWithTimeout(db)
if err != nil {
  fmt.Println("Error occurred: ", err)
}

3. Database Driver Timeout Configuration

Some database drivers allow configuring timeout parameters during connection setup, which affect all database operations.

Example - Configuring Database Connection

go
import (
  "gorm.io/driver/mysql"
  "gorm.io/gorm"
)

func main() {
  // Set connection timeout via parameters
  dsn := "user:password@tcp(localhost:3306)/dbname?timeout=5s"
  db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
  if err != nil {
    panic("Database connection failed")
  }

  // Use database operations
}

Conclusion

Implementing timeouts is a critical best practice to ensure application robustness and responsiveness. In GORM, select the most appropriate method based on specific application scenarios and database types. Demonstrating your understanding of these methods and their applicability in interviews can showcase your professional competence and attention to detail.