Using sqlc for Database Management in Go: Real-world Performance and Absolute Type-safety – ITFROMZERO

Table of Contents

Choosing a Database Approach in Go: Raw SQL or ORM?

Whenever starting a new Go project, choosing a database library is always a headache for developers trying to escape spaghetti code. Usually, we face three popular choices:

Standard library (database/sql): The fastest performance but extremely labor-intensive. You have to manually map every column to a struct. A minor schema change can easily crash your code.
ORM (GORM, Ent): Very fast and convenient for development. However, heavy use of reflection significantly impacts performance. Sometimes, ORMs generate bloated queries with 5-6 levels of JOINs that are hard to control.
SQL Builders (Squirrel): Solves the problem of writing queries programmatically. However, they still lack the strict type-safety that backend developers crave.

After many projects, I’ve learned one thing: SQL is still the best language for querying data. Instead of trying to avoid SQL, we should focus on optimizing it. That’s why sqlc has become my top choice for sustainable architecture.

sqlc: When SQL Meets the Power of the Compiler

sqlc isn’t a typical ORM. In fact, it’s a powerful compiler. You just write raw SQL, and sqlc reads your schema to automatically generate the corresponding Go code.

The difference is that sqlc checks for SQL errors the moment you run the generate command. If you mistype a column name or use the wrong data type, the compiler throws an error immediately. You no longer have to worry about the application crashing unexpectedly at runtime due to a bad query, a concept further explored in Mastering Error Handling in Go.

Why should you use sqlc?

Superior Performance: The generated code is pure Go. It calls the database driver directly without any intermediate layers. In benchmarks, sqlc is 2-3 times faster than GORM.
Absolute Type-safety: Every Input and Output is defined as a clear struct. This takes full advantage of the Go compiler’s error-checking capabilities.
Freedom to Optimize SQL: You can use every specific feature of Postgres or MySQL. You are no longer limited by the abstract functions of an ORM.

Limitations to Consider:

Handling Dynamic SQL (e.g., queries with arbitrary filters) is quite complex.
The development process depends on a code generation step, requiring the team to agree on a unified workflow.

Real-world Experience from a Fintech Project

I once participated in refactoring a Fintech system with about 50,000 lines of code. Initially, the team used GORM for rapid development. When the record count reached millions, queries started slowing down noticeably.

When switching to sqlc, I discovered more than 15 latent bugs. Most were data type mismatches between int4 and int8 that GORM had ignored, which is a critical step in preventing data loss. After the migration, the latency of critical APIs dropped from 200ms to under 50ms.

5-Step Guide to Implementing sqlc

To get started, we will manage a simple authors table on PostgreSQL.

Step 1: Install the Tool

Installing sqlc is very straightforward via Homebrew or Go install:

# For macOS
brew install sqlc

# Or install directly using Go
go install github.com/sqlc-dev/sqlc/cmd/sqlc@latest

Step 2: Configure sqlc.yaml

Create a configuration file to specify where the SQL is located and where the Go code should be saved:

version: "2"
sql:
  - schema: "schema.sql"
    queries: "query.sql"
    engine: "postgresql"
    gen:
      go:
        package: "db"
        out: "db"

Step 3: Write Schema and Queries

Define the table structure in the schema.sql file:

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

Write the operation commands in query.sql. Note the -- name part to define the function name:

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

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

Step 4: Execute Generation

Run the following command in the terminal to generate the code:

sqlc generate

sqlc will automatically create a db folder containing all query logic. You should never edit these files manually.

Step 5: Integrate into the Go Project

Now, calling the database becomes very concise and safe:

func main() {
	ctx := context.Background()
	conn, _ := sql.Open("postgres", "user=postgres dbname=test sslmode=disable")

	queries := db.New(conn)

	// Create a new author with type-safety
	author, err := queries.CreateAuthor(ctx, db.CreateAuthorParams{
		Name: "John Doe",
		Bio:  sql.NullString{String: "Backend Engineer", Valid: true},
	})
	if err != nil {
		log.Fatal(err)
	}
	fmt.Println("Newly created ID:", author.ID)
}

Tips for Effective Usage

To optimize your workflow, you should apply a few techniques:

Use a Makefile: Include the sqlc generate command in your build process to ensure the code is always in sync with the SQL.
Combine with Migration Tools: sqlc does not manage database versions. Use golang-migrate or goose for more professional schema management.
Customize Data Types: If you don’t like sql.NullString, use the overrides feature in the config to switch to *string pointers.

If you need a solution that balances execution speed and safety, sqlc is the missing piece. Happy coding, and may your SQL writing in Go be smooth!