SQLc: The Perfect Tool for Those Who Don't Like ORMs

When it comes to working with databases, I believe there's two types of software developers: those that like to use ORMs and those who prefer to write SQL. As for myself, I'm definitely that kind of developer that likes to get his hands dirty writing raw queries - but I also make sure to be safe, which means I use protection.

The Repository Design Pattern: My Go-To Approach

That protection typically comes in the form of abstraction, wrapping the raw queries in a type-safe construct using the repository design pattern. By doing so, it provides a number of benefits:

Full control over SQL queries - You get to write your own SQL, meaning you have complete control over the queries being performed against your database
Centralized database operations - By abstracting SQL queries into a central repository type, you can constrain database operations to a single location, preventing SQL queries from being sprinkled across your codebase
Type safety - Queries wrapped inside functions become type-safe due to concrete types for both inputs and outputs
Decoupled architecture - This approach decouples database implementation from application logic
Reusable queries - Makes it easy to reuse queries across your code

All of this makes the repository design pattern my preferred approach for working with databases in my application code. But as with all design patterns, there is a trade-off.

The Problem: Time-Consuming Boilerplate

In this case, that trade-off is time. You typically have to write this repository implementation by hand, which can be somewhat tedious as most of the code ends up just being boilerplate. For those developers who aren't AI-adverse, this may not be too much of an issue, but for myself, I'd much rather just write these SQL queries by hand and have the actual repository boilerplate be generated.

Enter SQLc: The Perfect Solution

Fortunately, it seems I'm not the only one who thinks this way. Some rather clever individuals have created what I think is the perfect solution: SQLc, which was actually recommended to me by a couple of members of my Discord channel. After having used it for about a month now, it's dramatically reduced the amount of time it takes for me to write code that works with SQL queries - so much so that I don't think I'll ever go back.

How SQLc Works

The way SQLc works is actually rather intuitive:

Define your SQL queries in a .sql file using annotations to define the query name and type
Run the sqlc generate command to generate code in your chosen language
Get type-safe code that matches the repository design pattern

If the term "generate code" gives you some pause, I get it. I've been known to have my own strong reaction whenever somebody mentions code gen to me, so when I first heard how SQLc worked, I was initially skeptical. However, after having used it a few times, I think the code generation has been implemented in one of the best ways I've ever seen.

Key Features of SQLc

Multi-language support: Supports TypeScript, Kotlin, Python, and Go natively, with plugin support for additional languages
Multiple SQL engines: Works with PostgreSQL, MySQL, and SQLite
Zero dependencies: Makes use of popular packages for each respective language
Highly customizable: Configure which types and packages are used by the generated code
Clean generated code: Looks similar to hand-written code

Getting Started with SQLc

Let's walk through a simple example. Here's how to set up SQLc for a Go project using PostgreSQL:

1. Create the Configuration File

First, create a sqlc.yaml file:

yaml

version: "2"
sql:
  - engine: "postgresql"
    queries: "query.sql"
    schema: "migrations/"
    gen:
      go:
        package: "repository"
        out: "internal/repository"

2. Define Your Schema

You can define your schema using database migrations (recommended) or by writing it directly in SQL. If you're using a migration tool like golang-migrate, just point SQLc to your migrations directory.

3. Write Your Queries

Create a query.sql file with your SQL queries:

sql

-- name: FindAllPlayers :many
SELECT * FROM player;

-- name: InsertItem :one
INSERT INTO item (id, name, value)
VALUES (uuid_generate_v4(), $1, $2)
RETURNING *;

-- name: FindItemByID :one
SELECT * FROM item WHERE id = $1;

4. Install SQLc

SQLc is available through various package managers:

bash

# macOS
brew install sqlc

# Arch Linux
pacman -S sqlc

# Or use Nix, Docker, etc.

5. Generate Code

Run the generation command:

bash

sqlc generate

This creates three files:

db.go - Constructor for accessing database queries
models.go - Generated data types based on your schema
query.sql.go - Implementation of your database queries

6. Use the Generated Code

// Create repository instance
repo := repository.New(db)

// Use generated methods
players, err := repo.FindAllPlayers(ctx)
if err != nil {
    return err
}

// Insert with type-safe parameters
item, err := repo.InsertItem(ctx, repository.InsertItemParams{
    Name:  "Ruby",
    Value: 300,
})

Advanced Features

Type Overrides

SQLc allows you to override default type mappings. For example, to use Google's UUID package instead of pgtype.UUID:

yaml

overrides:
  - db_type: "uuid"
    go_type:
      import: "github.com/google/uuid"
      type: "UUID"
  - db_type: "timestamptz"
    go_type:
      import: "time"
      type: "Time"

JSON Tags

Enable JSON struct tags for easy serialization:

yaml

gen:
  go:
    emit_json_tags: true

Embedded Structs

Use the sqlc.embed() macro to reuse existing types in complex queries:

sql

-- name: GetPlayerInventory :many
SELECT 
    sqlc.embed(player),
    sqlc.embed(item)
FROM inventory
LEFT JOIN player ON player.id = inventory.player_id
LEFT JOIN item ON item.id = inventory.item_id;

Prepared Statements

When using Go with PGX v5, prepared statements are handled automatically. For other drivers, enable them in your config:

yaml

gen:
  go:
    emit_prepared_queries: true

Transaction Support

SQLc makes transactions seamless through interface compatibility:

tx, err := db.Begin(ctx)
if err != nil {
    return err
}
defer tx.Rollback(ctx)

// Pass transaction to repository
repo := repository.New(tx)

// Perform operations
err = repo.DeleteInventoryItem(ctx, params)
if err != nil {
    return err
}

// Commit transaction
return tx.Commit(ctx)

Query Organization

For larger projects, organize queries into multiple files:

yaml

sql:
  - engine: "postgresql"
    queries: "queries/"  # Directory instead of single file
    schema: "migrations/"

Then structure your queries:

queries/player.sql - Player-related queries
queries/item.sql - Item-related queries
queries/inventory.sql - Inventory-related queries

Conclusion

SQLc has become a permanent fixture in my Go development stack. It saves tremendous time while still allowing me to write raw SQL queries. The generated code is clean, type-safe, and looks similar to what I would write by hand. If you prefer SQL over ORMs but want to eliminate boilerplate, SQLc is the perfect tool.

Resources

SQLc Documentation: https://sqlc.dev/
Example Code: https://github.com/dreamsofcode-io/sqlc
Repository Design Pattern: https://medium.com/@pererikbergman/repository-design-pattern-e28c0f3e4a30
Boot.dev (Video Sponsor): Use code DREAMSOFCODE for 25% off at https://sponsr.is/bootdev_dreamsofcode

This article is based on a video by Dreams of Code. Check out their YouTube channel and Discord community for more great content.