When developing web applications in Go, we often encounter N+1 queries, incorrect connection pool settings, and errors when working with pgBouncer. For example, in an e-commerce project with 50 tables and a load of 10,000 RPS, each extra query multiplied the response time by 10 — the catalog page loaded in 2 seconds instead of 200 ms. GORM v2 is a powerful ORM, but its proper configuration requires understanding the details: from PostgreSQL driver configuration to versioned migrations. Over 5 years of Go experience and 30+ successful projects, we've developed an optimal approach that guarantees stability. In this article, we'll tell you how to configure GORM to avoid typical problems: N+1 queries, data loss during migrations, and incompatibility with pgBouncer. We'll provide specific code examples and configurations ready for production. Get a consultation on GORM configuration today.
Installation and configuration with pgBouncer
Install GORM and the PostgreSQL driver, as well as a migration utility:
go get gorm.io/gorm
go get gorm.io/driver/postgres
# For versioned migrations later, install golang-migrate
go install -tags 'postgres' github.com/golang-migrate/migrate/v4/cmd/migrate@latest
Initialize the connection with logging and connection pool (file internal/db/db.go):
package db
import (
"fmt"
"log"
"os"
"time"
"gorm.io/driver/postgres"
"gorm.io/gorm"
"gorm.io/gorm/logger"
)
func New(dsn string) (*gorm.DB, error) {
newLogger := logger.New(
log.New(os.Stdout, "\r\n", log.LstdFlags),
logger.Config{
SlowThreshold: 200 * time.Millisecond,
LogLevel: logger.Warn,
IgnoreRecordNotFoundError: true,
Colorful: false,
},
)
db, err := gorm.Open(postgres.New(postgres.Config{
DSN: dsn,
PreferSimpleProtocol: true,
}), &gorm.Config{
Logger: newLogger,
NowFunc: func() time.Time { return time.Now().UTC() },
PrepareStmt: false,
DisableForeignKeyConstraintWhenMigrating: false,
})
if err != nil {
return nil, fmt.Errorf("gorm.Open: %w", err)
}
sqlDB, err := db.DB()
if err != nil {
return nil, fmt.Errorf("db.DB(): %w", err)
}
sqlDB.SetMaxOpenConns(25)
sqlDB.SetMaxIdleConns(10)
sqlDB.SetConnMaxLifetime(5 * time.Minute)
sqlDB.SetConnMaxIdleTime(2 * time.Minute)
return db, nil
}
Recommended connection pool parameters
| Parameter | Value | Explanation |
|---|---|---|
| SetMaxOpenConns | 25 | Maximum open connections |
| SetMaxIdleConns | 10 | Maximum idle connections |
| SetConnMaxLifetime | 5 minutes | Connection lifetime |
| SetConnMaxIdleTime | 2 minutes | Idle time before close |
Official GORM documentation confirms the need for PreferSimpleProtocol: true and PrepareStmt: false when working with pgBouncer. This disables prepared statements, which are incompatible with transaction pooling. Without this setting, you will get the error "prepared statement \"\" already exists".
How to configure GORM in 5 steps
-
Install GORM and the driver. Run
go get gorm.io/gormandgo get gorm.io/driver/postgres. -
Configure the connection with PreferSimpleProtocol. In the driver configuration, set
PreferSimpleProtocol: true, and in GORM setPrepareStmt: false. -
Configure the connection pool. After opening the connection, get
*sql.DBand set limits: 25 max open, 10 max idle, 5 minutes lifetime, 2 minutes idle. - Create models with hooks. Use
BeforeCreateandBeforeUpdatehooks to automatically fill slug and updateupdated_at. - Use Preload for relations. In repositories, use
Preloadto avoid N+1 queries.
How GORM solves the N+1 query problem?
Preload replaces N+1 queries with a single SELECT with an IN condition, reducing the number of queries by 10 times. In a project with 50 products and 3 related entities (category, tags, images), without Preload 151 queries were executed: 1 for products + 50 for categories + 50 for tags + 50 for images. With Preload — only 4 queries. Page load time decreased from 2 seconds to 200 ms. Preload works 10 times faster than a regular loop with N+1 queries. Savings from proper configuration can amount to up to 500,000 rubles per year.
Models, repositories, and query optimization
Example Product model
package models
import (
"time"
"gorm.io/gorm"
)
type ProductStatus string
const (
StatusDraft ProductStatus = "draft"
StatusPublished ProductStatus = "published"
StatusArchived ProductStatus = "archived"
)
type Product struct {
ID uint `gorm:"primarykey"`
CreatedAt time.Time
UpdatedAt time.Time
DeletedAt gorm.DeletedAt `gorm:"index"`
Title string `gorm:"type:varchar(500);not null"`
Slug string `gorm:"type:varchar(520);uniqueIndex;not null"`
Price float64 `gorm:"type:decimal(12,2);not null"`
Status ProductStatus `gorm:"type:varchar(20);default:draft;not null"`
CategoryID uint `gorm:"not null;index"`
Category Category `gorm:"foreignKey:CategoryID;constraint:OnDelete:RESTRICT"`
Tags []Tag `gorm:"many2many:product_tags;"`
Images []ProductImage `gorm:"foreignKey:ProductID;constraint:OnDelete:CASCADE"`
}
func (Product) TableName() string {
return "products"
}
Repository with Preload, transactions, and GORM hooks
package repository
import (
"context"
"myapp/internal/models"
"gorm.io/gorm"
)
type ProductRepository struct {
db *gorm.DB
}
func NewProductRepository(db *gorm.DB) *ProductRepository {
return &ProductRepository{db: db}
}
func (r *ProductRepository) GetPublished(ctx context.Context, categoryID uint, limit, offset int) ([]models.Product, error) {
var products []models.Product
err := r.db.WithContext(ctx).Preload("Category").Preload("Tags").
Where("category_id = ? AND status = ?", categoryID, models.StatusPublished).
Order("created_at DESC").Limit(limit).Offset(offset).Find(&products).Error
return products, err
}
func (r *ProductRepository) CreateWithTags(ctx context.Context, product *models.Product, tagIDs []uint) error {
return r.db.WithContext(ctx).Transaction(func(tx *gorm.DB) error {
if err := tx.Create(product).Error; err != nil {
return err
}
var tags []models.Tag
if err := tx.Find(&tags, tagIDs).Error; err != nil {
return err
}
return tx.Model(product).Association("Tags").Append(tags)
})
}
// GORM hooks: BeforeCreate and BeforeUpdate
func (p *Product) BeforeCreate(tx *gorm.DB) error {
if p.Slug == "" {
p.Slug = slug.Make(p.Title)
}
return nil
}
func (p *Product) BeforeUpdate(tx *gorm.DB) error {
tx.Statement.SetColumn("UpdatedAt", time.Now().UTC())
return nil
}
Why AutoMigrate is dangerous in production?
AutoMigrate cannot accumulate changes without risk of data loss — it can drop columns or tables. In one project, we lost several columns due to an unintentional call to AutoMigrate after changing the model. Versioned migrations give full control and the ability to rollback. For production, we use golang-migrate with SQL migrations.
Example SQL migration
-- migrations/000002_create_products.up.sql
CREATE TABLE products (
id BIGSERIAL PRIMARY KEY,
title VARCHAR(500) NOT NULL,
slug VARCHAR(520) NOT NULL UNIQUE,
price DECIMAL(12, 2) NOT NULL,
status VARCHAR(20) NOT NULL DEFAULT 'draft',
category_id BIGINT NOT NULL REFERENCES categories(id) ON DELETE RESTRICT,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
deleted_at TIMESTAMPTZ
);
CREATE INDEX idx_products_status_created ON products (status, created_at DESC);
CREATE INDEX idx_products_category ON products (category_id, status);
CREATE INDEX idx_products_deleted_at ON products (deleted_at);
Comparison of AutoMigrate and versioned migrations
| Criterion | AutoMigrate | golang-migrate |
|---|---|---|
| Change control | No | Full |
| Risk of data loss | High | Low |
| Rollback | No | Yes |
| Suitable for production | No | Yes |
What's included in GORM setup
We provide:
- Configured database connection with connection pool (25 open, 10 idle, 5 min lifetime)
- Repositories with Preload and transactions
- Versioned SQL migrations with rollback capability
- Tests with testcontainers-go
- Documentation on the stack and configuration
- Support for 2 weeks after delivery
Timeframes
Initial GORM setup with migrations for a new Go project takes from 1 day to a week, depending on the number of models and business logic complexity. The cost is calculated individually. Savings from optimization can amount to up to 500,000 rubles per year.
Contact us to find out the exact cost and timelines. Our experienced engineers with 5 years of experience guarantee stable operation of your application.