SQLC is an amazing Go program that generates Go code from SQL queries. While it’s not technically an ORM, SQLC simplifies working with raw SQL, making it almost as easy as using an ORM.

Installation

If you have the Go toolchain installed, you can add sqlc with this command:

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

Run sqlc version to confirm that it’s installed correctly.

Now we’re ready to work with our database in powerful new ways!

Configuration for Your Project

Create a file named sqlc.yaml in the root of your project. For example:

version: "2"
sql:
  - schema: "sql/schema"
    queries: "sql/queries"
    engine: "postgresql"
    gen:
      go:
        out: "internal/database"

In this file, we’re instructing SQLC to:

• Look in the sql/schema directory for our schema structure (the same files that Goose uses, though sqlcautomatically ignores “down” migrations).
• Look in the sql/queries directory for our SQL queries.
• Generate Go code in the internal/database directory.

Writing Queries

In the sql/queries directory (as specified in the configuration), create a file called users.sql. Here’s an example query:

-- name: CreateUser :one
INSERT INTO users (id, created_at, updated_at, name)
VALUES ($1, $2, $3, $4)
RETURNING *;

The placeholders $1, $2, $3, and $4 are parameters that will be passed in from our Go code. The :one annotation after the query name tells SQLC to expect a single row as the result (the newly created user).

Generating Code

From the root of your project, run:

sqlc generate

This command generates Go code in the internal/database directory, creating a package with functions for each query you’ve defined.

Usage

You can now import the generated database package and use the CreateUser function directly in your code. Here’s a quick example of how to use it:

package main

import (
	"context"
	"fmt"
	"log"
	"time"
	"yourproject/internal/database" // adjust to match your project path

	_ "github.com/lib/pq" // Postgres driver
)

func main() {
	// Set up your database connection
	connStr := "postgres://username:password@localhost:5432/yourdb"
	db, err := database.Open("postgres", connStr)
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	// Create a new user
	ctx := context.Background()
	newUser, err := db.CreateUser(ctx, database.CreateUserParams{
		ID:        1,
		CreatedAt: time.Now(),
		UpdatedAt: time.Now(),
		Name:      "John Doe",
	})
	if err != nil {
		log.Fatal(err)
	}

	fmt.Printf("User created: %+v\n", newUser)
}

In this example, CreateUserParams is a struct automatically generated by SQLC based on the parameters required by your CreateUser query.

Conclusion

SQLC makes working with raw SQL in Go simple and efficient, offering the performance and flexibility of SQL without sacrificing developer productivity. With a well-organized setup, SQLC enables you to manage and interact with your database in a clean, type-safe way that integrates seamlessly with tools like Goose. This streamlined approach allows you to write queries once and let SQLC handle the heavy lifting, so you can focus on building your application with confidence in your database interactions.