sqlc for Gophers

Why Developers Struggle Between ORMs and Raw SQL

As developers, we often find ourselves torn between using Object-Relational Mappers (ORMs) and writing raw SQL queries. Each approach has its advantages and trade-offs. sqlc, however, offers a unique solution by blending the flexibility and power of raw SQL with the safety and efficiency of Go's type system.

Introduction to sqlc

In traditional SQL approaches, Go developers write raw SQL queries and manually scan results into Go structs. While this provides full control, it can quickly become error-prone and cumbersome, especially with complex queries.

In contrast, ORMs like GORM abstract away the SQL entirely, mapping database tables directly to Go structs. While this can speed up development, it sacrifices performance and control, as ORMs often generate inefficient SQL queries behind the scenes. As we all know, too much abstraction can be detrimental to both performance and clarity.

Sqlc allows developers to write raw SQL directly while providing the benefits of type safety and compile-time error checking. It’s the best of both worlds, enabling you to maintain full control over your SQL queries while benefiting from Go’s strong type system.

Setup and Installation

Setting up sqlc is straightforward. Below are the installation instructions for different platforms:

For macOS:

brew install sqlc

For Ubuntu-Based Distros (Using Snap Store)

sudo snap install sqlc

For Go Install (Personal Favorite)

go install github.com/sqlc-dev/sqlc/cmd/sqlc@latest

Using Docker

For UNIX:

docker pull sqlc/sqlc
docker run --rm -v $(pwd):/src -w /src sqlc/sqlc generate

For Windows:

docker pull sqlc/sqlc
docker run --rm -v "%cd%:/src" -w /src sqlc/sqlc generate

Once installed, you’ll need to create a configuration file (sqlc.yaml) at the root of your project. Here’s an example configuration for a PostgreSQL database:

version: "2"
sql:
  - schema: "sql/schema"    # Directory for your schema files
    queries: "sql/queries"  # Directory for your SQL queries
    engine: "postgresql"    
    gen:
      go:
        out: "internal/database"  # Output directory for generated Go code

Core Concepts of sqlc

With sqlc, you write SQL queries in .sql files, and it generates the corresponding Go code. Each SQL query is mappe/d to a Go function, which returns results typed according to the query’s result structure.

Each query starts with a special comment containing:

• The name of the Go function (this is the method name in your generated Go code).

• The number of rows the query is expected to return (one, many, or none).

Example Query:

-- name: <FunctionName> :<Rowtype>
SELECT id, name, email FROM users WHERE id = $1;

Explanation:

• <FunctionName>: This is the name of the function that sqlc will generate in your Go code.

• <Rowtype>: This indicates that the row type that will be returned.

After running sqlc generate, a Go function named GetUserByID will be created in your internal/database directory. This function will execute the SQL query and return the result as a typed Go struct.

Example Generated Go Code

Given the SQL query:

-- name: GetUserByID :one
SELECT id, name, email FROM users WHERE id = $1;

sqlc will generate the following Go code:

type GetUserByIDRow struct {
    ID    int32  `json:"id"`
    Name  string `json:"name"`
    Email string `json:"email"`
}

func (q *Queries) GetUserByID(ctx context.Context, id int32) (GetUserByIDRow, error) {
    row := q.db.QueryRowContext(ctx, "SELECT id, name, email FROM users WHERE id = $1", id)
    var i GetUserByIDRow
    err := row.Scan(&i.ID, &i.Name, &i.Email)
    return i, err
}

Generated Functions

For each SQL query, sqlc generates a corresponding Go function:

• GetUserByID will execute the SELECT query and return a single result in the form of a GetUserByIDRow struct.

• Error handling is built into the generated code, ensuring that you can safely interact with the database.

Conclusion

In conclusion, sqlc offers Gophers a streamlined, type-safe approach to working with databases, providing the best of both worlds: the flexibility of raw SQL and the safety and efficiency of Go’s type system. By enabling developers to write SQL directly while generating type-safe Go code, sqlc eliminates many of the pitfalls associated with traditional SQL handling and ORM-based approaches.