3 Approaches to Working with Databases in Go
Working with databases in Go has always sparked heated debates. One side prizes raw performance above all, while the other prioritizes developer velocity and rapid feature delivery. Generally, the Go ecosystem splits into three main approaches:
- Standard library
database/sql(Raw SQL): You write raw SQL queries as strings likeSELECT id, name FROM users WHERE id = $1. This gives you complete control over every byte sent across the wire. In exchange, you have to handle all data mapping manually. - Code generation tools or Query Builders (sqlc, sqlx): You write SQL files, and sqlc compiles them into type-safe Go code. Performance is nearly on par with Raw SQL. However, handling dynamic filtering with 5–10 optional conditions can become quite cumbersome in sqlc.
- Full-featured ORM (GORM): Every table and record is represented as a Go
struct. GORM automatically translates Go methods into the appropriate SQL dialect for your database engine. This allows you to focus on business logic instead of tediously concatenating SQL strings.
Comparing Performance and Developer Experience
Every approach comes with trade-offs. Let’s weigh them carefully before choosing an architecture for your project.
1. Pure Raw SQL (database/sql)
- Pros: Maximum speed. Extremely low CPU and memory footprint since there is no intermediate abstraction layer.
- Cons: High boilerplate. Adding a single column to a table means manually updating everything from the SQL string to the
rows.Scan(&id, &name, &email)call. If variable order or types mismatch, the compiler cannot catch it, leading to runtime errors or panics.
2. Hybrid Solutions (sqlc / sqlx)
- Pros: Catches SQL errors at compile time. Since it avoids reflection, processing hundreds of thousands of records remains blistering fast.
- Cons: Struggles with complex dynamic filtering. Managing schema migrations also requires external tools like golang-migrate or goose.
3. GORM
- Pros: Outstanding productivity. GORM ships with built-in Auto Migration, Hooks/Callbacks, Soft Delete, and complex relationship support. Code is concise, readable, and minimizes SQL typos.
- Cons: GORM relies heavily on Go reflection for data mapping. Consequently, throughput is typically 10–25% lower than Raw SQL on high-throughput workloads with tens of thousands of concurrent rows. Furthermore, improper querying patterns can easily trigger the dreaded N+1 query problem.
When Should You Choose Which?
Choose Raw SQL or sqlc when building payment systems, crypto exchanges, or ad-tech real-time bidding engines—environments where handling 50,000 to 100,000 requests per second is mandatory and every millisecond of latency translates directly into financial loss.
Conversely, confidently reach for GORM for the other 90% of use cases: REST/gRPC APIs, internal microservices, SaaS platforms, CRMs, or e-commerce applications. In these domains, Time-to-Market matters far more than shaving off a few CPU cycles.
GORM’s abstraction layer also shines in real-world scenarios. For example, when migrating a 120GB database from MySQL 5.7 to PostgreSQL 16, our team only had to swap the driver and update the connection string. Over 80 models and hundreds of CRUD queries remained completely untouched, allowing us to deliver the project two weeks ahead of schedule.
Production-Ready GORM Setup with PostgreSQL
Here is a 5-step guide to building a production-grade database connection module, complete with connection pooling and relational model definitions.
Step 1: Install Dependencies
Run the following commands in your Go project root:
go get -u gorm.io/gorm
go get -u gorm.io/driver/postgres
Step 2: Define Models and Relationships (One-to-Many)
Create the schema for a blogging platform: One User has many Posts.
package models
import (
"time"
"gorm.io/gorm"
)
type User struct {
ID uint `gorm:"primaryKey" json:"id"`
Name string `gorm:"type:varchar(100);not null" json:"name"`
Email string `gorm:"type:varchar(150);uniqueIndex;not null" json:"email"`
Posts []Post `gorm:"foreignKey:UserID" json:"posts,omitempty"`
CreatedAt time.Time `json:"created_at"`
UpdatedAt time.Time `json:"updated_at"`
DeletedAt gorm.DeletedAt `gorm:"index" json:"-"` // Enables Soft Delete
}
type Post struct {
ID uint `gorm:"primaryKey" json:"id"`
Title string `gorm:"type:varchar(255);not null" json:"title"`
Content string `gorm:"type:text" json:"content"`
UserID uint `gorm:"not null;index" json:"user_id"`
CreatedAt time.Time `json:"created_at"`
UpdatedAt time.Time `json:"updated_at"`
DeletedAt gorm.DeletedAt `gorm:"index" json:"-"`
}
Step 3: Initialize and Configure Connection Pooling
By default, PostgreSQL caps max_connections = 100. Without connection limits configured on the Go side, sudden traffic spikes can easily overwhelm and crash your database.
package database
import (
"log"
"time"
"gorm.io/driver/postgres"
"gorm.io/gorm"
"gorm.io/gorm/logger"
)
func InitDB() *gorm.DB {
dsn := "host=localhost user=postgres password=secret dbname=blog_prod port=5432 sslmode=disable TimeZone=Asia/Ho_Chi_Minh"
db, err := gorm.Open(postgres.Open(dsn), &gorm.Config{
// In production, only log slow queries and execution errors
Logger: logger.Default.LogMode(logger.Warn),
})
if err != nil {
log.Fatalf("Database connection error: %v", err)
}
// Retrieve generic sql.DB instance to configure connection pool
sqlDB, err := db.DB()
if err != nil {
log.Fatalf("Error extracting sql.DB: %v", err)
}
sqlDB.SetMaxIdleConns(10) // Maintain 10 idle connections in pool
sqlDB.SetMaxOpenConns(100) // Maximum of 100 open connections
sqlDB.SetConnMaxLifetime(time.Hour) // Recycle connections after 60 minutes
sqlDB.SetConnMaxIdleTime(15 * time.Minute) // Close connection if idle for more than 15 minutes
return db
}
Step 4: Run Safe Auto Migrations
GORM’s AutoMigrate automatically creates tables, adds missing columns, and builds indexes. This mechanism is safe because GORM never drops existing columns or alters existing column types.
package main
import (
"your_project/database"
"your_project/models"
)
func main() {
db := database.InitDB()
// Sync schema for User and Post
if err := db.AutoMigrate(&models.User{}, &models.Post{}); err != nil {
panic("Migration failed: " + err.Error())
}
}
Step 5: Querying Data and Eliminating the N+1 Problem
Suppose you need to fetch a single User along with their 20 Posts. Never query the User first and then loop with a for statement to fetch each Post individually. That pattern triggers 21 separate queries, causing instant I/O bottlenecks on your database.
The standard solution is using Preload:
// Fetch user ID = 1 along with all related posts
var user models.User
if err := db.Preload("Posts").First(&user, 1).Error; err != nil {
// Handle record not found error
return
}
// Create a new post for this user
newPost := models.Post{
Title: "Optimizing GORM in Production",
Content: "Details on configuring connection pooling and Preload...",
UserID: user.ID,
}
db.Create(&newPost)
Under the hood, GORM executes exactly two SQL queries:
SELECT * FROM "users" WHERE "id" = 1 AND "deleted_at" IS NULL ORDER BY "id" LIMIT 1;
SELECT * FROM "posts" WHERE "user_id" = 1 AND "deleted_at" IS NULL;
Response times drop from 45ms down to under 4ms. By harnessing GORM effectively, you keep your development velocity high while maintaining a stable, responsive production system.
