SQLC vs GORM - Two Approaches to Database Interaction in Go

Introduction

Interacting with databases is a cornerstone of almost any serious application, and Go, with its strong typing and performance characteristics, has become a popular choice for building such systems. When working with relational databases in Go, developers often find themselves choosing between two broad categories of tools: those that emphasize raw SQL and code generation, and those that offer an object-relational mapping (ORM) layer. This article dives into two prominent examples from these categories: SQLC and GORM. We will explore their distinct philosophies, practical implementations, and suitable use cases, providing a clear understanding of how each approaches the challenge of database interaction in Go.

Understanding the Philosophies: SQLC and GORM

Before delving into the specifics, let's establish a common understanding of the core concepts at play.

SQLC (SQL Code Generation): SQLC is a tool that generates Go code from raw SQL queries. Its philosophy is rooted in the belief that SQL's declarative nature is powerful and should be leveraged directly. By writing SQL queries, SQLC then automatically generates type-safe Go code for executing those queries and scanning the results into Go structs. This approach prioritizes explicit SQL, compile-time safety, and minimal abstraction over database operations.

GORM (Go ORM - Object-Relational Mapping): GORM, on the other hand, is a full-fledged ORM library for Go. Its philosophy centers around mapping database tables to Go structs and providing a high-level API to interact with the database using Go idioms, rather than raw SQL. GORM aims to abstract away the underlying SQL, allowing developers to manipulate data as Go objects. This approach prioritizes developer convenience, rapid development, and an object-oriented view of the database.

Let's illustrate these differences with practical examples.

SQLC: Embrace Explicit SQL and Code Generation

SQLC's workflow involves writing your SQL schema and queries in .sql files. SQLC then processes these files to generate Go code.

Example: Defining a Schema and Query with SQLC

First, define your SQL schema (e.g., schema.sql):

CREATE TABLE authors (
  id   BIGSERIAL PRIMARY KEY,
  name TEXT      NOT NULL,
  bio  TEXT
);

Next, define your SQL queries (e.g., query.sql):

-- name: GetAuthor :one
SELECT id, name, bio FROM authors WHERE id = $1 LIMIT 1;

-- name: ListAuthors :many
SELECT id, name, bio FROM authors ORDER BY name;

-- name: CreateAuthor :one
INSERT INTO authors (name, bio) VALUES ($1, $2) RETURNING id, name, bio;

-- name: UpdateAuthor :one
UPDATE authors SET name = $1, bio = $2 WHERE id = $3 RETURNING id, name, bio;

-- name: DeleteAuthor :exec
DELETE FROM authors WHERE id = $1;

After running sqlc generate, SQLC produces Go files (e.g., db.go, models.go, query.sql.go). Here's a snippet of the generated query.sql.go:

// Code generated by sqlc. DO NOT EDIT.
// versions:
//   sqlc v1.25.0
// source: query.sql

package db

import (
	"context"
)

const createAuthor = `-- name: CreateAuthor :one
INSERT INTO authors (name, bio) VALUES ($1, $2) RETURNING id, name, bio
`

type CreateAuthorParams struct {
	Name string `json:"name"`
	Bio  *string `json:"bio"`
}

func (q *Queries) CreateAuthor(ctx context.Context, arg CreateAuthorParams) (Author, error) {
	row := q.db.QueryRowContext(ctx, createAuthor, arg.Name, arg.Bio)
	var i Author
	err := row.Scan(
		&i.ID,
		&i.Name,
		&i.Bio,
	)
	return i, err
}

const getAuthor = `-- name: GetAuthor :one
SELECT id, name, bio FROM authors WHERE id = $1 LIMIT 1
`

func (q *Queries) GetAuthor(ctx context.Context, id int64) (Author, error) {
	row := q.db.QueryRowContext(ctx, getAuthor, id)
	var i Author
	err := row.Scan(
		&i.ID,
		&i.Name,
		&i.Bio,
	)
	return i, err
}

// ... other generated functions

And how you'd use it in your application:

package main

import (
	"context"
	"database/sql"
	"fmt"
	_ "github.com/lib/pq" // PostgreSQL driver
	"your-project/db"      // Assuming db package is where sqlc generates code
)

func main() {
	connStr := "user=postgres password=password dbname=sqlc_example sslmode=disable"
	conn, err := sql.Open("postgres", connStr)
	if err != nil {
		panic(err)
	}
	defer conn.Close()

	queries := db.New(conn)
	ctx := context.Background()

	// Create an author
	newAuthor, err := queries.CreateAuthor(ctx, db.CreateAuthorParams{
		Name: "Jane Doe",
		Bio:  sql.NullString{String: "A talented writer", Valid: true},
	})
	if err != nil {
		panic(err)
	}
	fmt.Printf("Created author: %+v\n", newAuthor)

	// Get an author
	author, err := queries.GetAuthor(ctx, newAuthor.ID)
	if err != nil {
		panic(err)
	}
	fmt.Printf("Fetched author: %+v\n", author)

	// List authors
	authors, err := queries.ListAuthors(ctx)
	if err != nil {
		panic(err)
	}
	fmt.Printf("All authors: %+v\n", authors)
}

The key benefits of SQLC include:

• Compile-time safety: SQL errors are caught at compilation, not runtime.
• No runtime reflection: Generated code is plain Go, leading to excellent performance.
• Direct SQL control: Full power of SQL is available, including complex joins, window functions, etc.
• Reduced boilerplate: Scannig results into structs is handled automatically.

However, it requires an extra generate step and can feel more verbose for simple CRUD operations.

GORM: Object-Relational Mapping for Go

GORM takes a different approach, allowing you to define Go structs that map directly to database tables.

Example: Defining a Model and Operations with GORM

First, define your Go struct (model):

package main

import (
	"gorm.io/gorm"
)

type Author struct {
	gorm.Model // Provides ID, CreatedAt, UpdatedAt, DeletedAt
	Name       string `gorm:"not null"`
	Bio        *string
}

Then, use GORM's API to interact with the database:

package main

import (
	"fmt"
	"gorm.io/driver/postgres"
	"gorm.io/gorm"
	"gorm.io/gorm/logger" // Import logger for better output
	"log"
	"os"
	"time"
)

func main() {
	dsn := "host=localhost user=postgres password=password dbname=gorm_example port=5432 sslmode=disable TimeZone=Asia/Shanghai"
	
	// Configure GORM logger
	newLogger := logger.New(
		log.New(os.Stdout, "\r\n", log.LstdFlags), // io writer
		logger.Config{
			SlowThreshold:              time.Second, // Slow SQL threshold
			LogLevel:                   logger.Info, // Log level
			IgnoreRecordNotFoundError: false,       // Ignore ErrRecordNotFound error for logger
			Colorful:                  true,        // Disable color
		},
	)

	db, err := gorm.Open(postgres.Open(dsn), &gorm.Config{
		Logger: newLogger, // Apply the logger
	})
	if err != nil {
		panic("failed to connect database")
	}

	// Migrate the schema (creates authors table if it doesn't exist)
	db.AutoMigrate(&Author{})

	// Create an author
	bio := "A talented writer"
	author := Author{Name: "John Doe", Bio: &bio}
	result := db.Create(&author) // pass pointer of data to Create
	if result.Error != nil {
		panic(result.Error)
	}
	fmt.Printf("Created author: %+v\n", author)

	// Get an author by its primary key
	var fetchedAuthor Author
	db.First(&fetchedAuthor, author.ID) // find author with id 1
	fmt.Printf("Fetched author: %+v\n", fetchedAuthor)

	// Update an author
	db.Model(&fetchedAuthor).Update("Name", "Jonathan Doe")
	fmt.Printf("Updated author: %+v\n", fetchedAuthor)

	// Delete an author (soft delete by default with gorm.Model)
	// db.Delete(&fetchedAuthor, fetchedAuthor.ID) // This would soft delete
}

GORM provides:

• Convenience and rapidity: Abstracting SQL reduces boilerplate for common operations.
• Object-oriented interaction: Work with Go structs directly, making code more idiomatic for Go developers.
• Advanced features: Built-in hooks, associations, eager loading, and transaction management.
• Database abstraction: Easily switch between different SQL databases with minimal code changes.

However, GORM relies on runtime reflection, which can introduce performance overhead (though often negligible for typical applications). It also abstracts SQL, which can sometimes make debugging complex queries harder and limit access to database-specific features.

When to choose which

Choose SQLC if:

• You prioritize full control over SQL, performance, and compile-time guarantees.
• Your project involves complex, hand-optimized SQL queries.
• You want to avoid runtime reflection and its potential performance implications.
• You prefer a "SQL-first" development approach.
• You need to integrate with existing database schemas directly.

Choose GORM if:

• You prioritize rapid development, convenience, and a higher level of abstraction.
• Your project involves mostly standard CRUD operations.
• You prefer working with Go structs rather than raw SQL.
• You need built-in features like associations, hooks, and migrations.
• You might need to switch between different SQL databases.

Conclusion

SQLC and GORM represent two distinct, yet equally valid, philosophies for interacting with databases in Go. SQLC champions explicit SQL and compile-time safety through code generation, offering unparalleled control and performance for applications deeply reliant on hand-tuned queries. GORM, on the other hand, embraces the convenience and abstraction of Object-Relational Mapping, accelerating development with its idiomatic Go API and robust feature set. The choice between them ultimately hinges on project requirements, team preferences, and the desired balance between SQL explicitness and Go-level abstraction. Both tools are excellent in their respective domains, providing powerful ways to manage data in your Go applications.