Why can you still choose GORM in a production environment

• API-friendly: GORM provides commonly used CRUD, associations, hooks, and transaction encapsulation, which can significantly reduce the amount of duplicate SQL writing.
• Flexibility: When you need to do complex queries, you can fall back to native SQL or use 'Clauses'/'Scopes' to assemble high-performance statements.
• Ecology and Documentation: Active community, rich information, and good compatibility with most database drivers.

Choosing ORM should be business cost-oriented: in small and medium-sized businesses, ORM can increase development speed; complex, ultra-low latency core paths can be optimized with handwritten SQL.

Consistent initialization and connection management (engineering basics)

The most common pitfalls are spreading DB connections among individual packages or repeatedly creating connections in a handler. Recommended practices:

• Read connection parameters (DSN, connection pool, timeout, etc.) in the config/environment variable.
• Create a single instance * gorm.DB during thecore`/startup phase and centrally configure connection pools: MaxOpenConns, MaxIdleConns, MaxConnMaxLifetime.
• Integrate GORM's logger with structured logging (such as zap) and adjust log levels online through environment variables or configurations.
• Elegantly close the connection pool of the underlying sql.DB after startup.

Example (pseudo-code):

//Pseudocode: initialization
db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{/*...*/})
sqlDB, _ := db.DB()
sqlDB.SetMaxOpenConns(cfg.MaxOpenConns)
//When exiting: sqlDB.Close()

Model design: Keep it simple, transferable, and readable

It is recommended to unify model conventions:

• Each model contains common fields such as primary key (self-increment or UUID), CreatedAt, UpdatedAt, DeletedAt (soft delete), etc.
• Explicit declaration of column types and indexes in tags ensures cross-database compatibility and query performance.
• Try to provide clear foreign key constraints on association relationships to avoid increased maintenance costs caused by implicit associations.

Don't use a lot of json:"-" or hidden fields to circumvent problems, but instead do field clipping at the serialization level (DTO).

Query and Performance: Avoiding Common Traps

• N+1 problem: Pay attention to the level and quantity when using Preload; for large amounts of data, you should split the query or use JOIN/sub-query to reduce the number of requests.
• Paging: Deep paging avoids using large OFFSET and prefers index-based cursor paging.
• Batch operations: Use CreateInBatches and batch update/delete statements to reduce round trips.
• Monitor slow query: Enable SQL printing and slow query thresholds in the development environment, and collect slow query indicators and alert the production environment.

Example (paging idea):

// Seek-based pagination
var items []Post
db.Where("id > ? ", lastID).Order("id").Limit(pageSize).Find(&items)

Transaction and Concurrency Control

• Transaction boundaries should be controlled by the service layer. Handler is only responsible for request parsing and response return, while service is responsible for transaction start and commit/rollback.
• Use db.Transaction(func(tx *gorm.DB) error {...}) Ensure automatic rollback when an error occurs.
• Avoid long-term blocking calls in transactions (such as remote HTTP requests), commit the transaction first and then invoke external services if necessary, or use reliable asynchronous mechanisms.

Migration strategy: AutoMigrate is not a panacea

GORM's AutoMigrate is useful for fast iteration, but the production environment recommends:

• Write schema changes into explicit migration scripts (such as using golang-migrate), and check and roll back the migration scripts in CI.
• Make non-destructive changes (add columns, create indexes) first within the release window, and adopt a multi-phase migration strategy for vulnerable changes (delete columns, modify column types).

Testing: A practical method for unit and integration testing

• Unit testing: When writing unit tests for the service layer, try to abstract the repository interface and mock * gorm.DB behavior, or replace it with lightweight memory.
• Integration testing: Use testcontainers or docker-compose to start the real database in CI, run migrations, load fixtures, perform tests, and clean up after completion.
• Use transaction rollback techniques to isolate use cases in integration testing (but be aware of limitations when testing concurrently).

Logging, monitoring and error handling

• Combine SQL logging with structured logging to record only slow queries or error-level SQL online.
• Export database-related indicators (number of connections, query delay, slow query count) and connect with monitoring systems such as Prometheus.
• Unify error mapping (for example, map the 'record not found' of GORM to the custom 'ErrNotFound') to facilitate consistent response by the upstream HTTP layer.

Security and sensitive information management (must pay attention)

• Never submit DSN, passwords, private keys, etc. in clear text to the repository. Use environment variables or secrets management tools (Vault, KMS, Cloud Provider's Secret Manager).
• Use secret variables to pass DB credentials in CI to avoid writing credentials to logs or temporary files.
• Regularly scan warehouse history with tools such as gitleaks to discover and clean up leaked keys.

Common anti-patterns (to avoid)

• Handle complex transactions in the handler or access DB in large quantities directly.
• Relying on AutoMigrate to update tables online without verification.
• Build complex SQL using ORM as a query builder, regardless of the performance advantages of native SQL or views.

Summary: Put reliability first and compromise on demand

GORM can significantly improve development efficiency, but engineering (initialization, connection pooling, logging, migration strategies, CI testing) is the key to bringing services to the production level. In practice, a trade-off should be made between "development efficiency" and "operational performance/reliability":

• General business gives priority to using GORM's convenient API;
• For core highly concurrent paths, use handwritten SQL or additional optimization (index, cache, database and table, etc.);
• Incorporate security, CI, and monitoring as "must-have items" into the development and release process.