Use SQLC to generate Go code from SQL queries

Introduction

SQLC is a tool that generates type-safe Go code from SQL queries. It bridges the gap between SQL and Go by allowing developers to write raw SQL queries, and automatically generating the corresponding Go code.

The go code generated by the SQL queries is validated at compile time, reducing runtime errors and should improving code maintainability.

SQL Files and Migrations

Before we start with SQLC, we’ll first need to set up the SQL files and migrations.

With migration files, you can manage your database schema changes and versioning. And SQLC is compatible with several migration tools.

Fell free to choose the one you prefer. As for my particular case I chose dbmate because it’s written in Go, it’s simple to set up with a useful CLI, and has a simple one file per migration system.

Installation

To install dbmate, you can use the following command:

go install github.com/amacneil/dbmate@latest

To create a migration use:

dbmate new create_authors_table

Then you can run the migration on a test database running in the background. Make sure you have set the DATABASE_URL environment variable for the tool to connect to it and apply the migration. Then run the migration in the root directory where the migration folder in db/migrations is located:

dbmate up

This will run all migrations that have not been run yet.

Example Migration File with `dbmate`

Create a migration file in the db/migrations directory:

-- db/migrations/202504020000_create_authors_table.sql
-- migrate:up
CREATE TABLE IF NOT EXISTS authors (
   id   BIGSERIAL PRIMARY KEY,
   name text NOT NULL,
   bio  text
);

-- migrate:down
DROP TABLE IF EXISTS authors;

Example Query

Create a SQL query file in the db directory called query.sql to fetch an author by ID:

-- db/query.sql
-- name: GetAuthor :one
SELECT *
FROM authors
WHERE id = $1;

This will be used to generate the Go code. We could also have a folder with queries organized by entity or feature in different files.

Using SQLC

Installation

To get started with SQLC, you need to install it. You can do this using the following command:

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

I am basing this article on the official documentation for SQLC with PostgreSQL.

Configuration with `sqlc.yaml`

Create a sqlc.yaml file in the root of your project to configure SQLC. This file specifies the database schema, queries, and the output directory for the generated Go code.

version: "2"
sql:
  - engine: "postgresql"
    queries: "../db/query.sql"
    schema: "../db/migrations" # Or use 
    gen:
      go:
        package: "sqlc"
        out: "../db/sqlc"
        sql_package: "pgx/v5"
        output_batch_file_name: "batch.gen.go"
        output_db_file_name: "db.gen.go"
        output_models_file_name: "models.gen.go"

We are using the migrations file to build the database schema, but you can also test it out with a single file schema: "../db/schema.sql" with all the schema definitions. If you are planning to go into production, I’d advise using migrations to keep track of the changes.

Using the tools pattern

The tools.go file is a common pattern in Go projects to manage dependencies and tools. You can have multiple generate command and tool dependency in the same file.

//go:build tools
package tools

import (
	_ "github.com/sqlc-dev/sqlc/cmd/sqlc"
)

// SQL code generation
//go:generate sqlc generate

In our case it will ensure that the sqlc module is installed and available for code generation. Now you can:

go generate ./...
# Or directly sqlc generate

The script will generate the Go code.

Generated code

Assuming you have this folder structure, that should match what is specified in the sqlc.yaml file:

.
├── db/
│   ├── migrations/
│   │   └── 202504020000_create_authors_table.sql
│   └── query.sql
├── internal/
│   └── ...
├── tools.go
└── sqlc.yaml

The generated Go code should be in the db/sqlc directory with the .gen.go extension. I use that extension to differentiate the generated code from the handwritten code.

db/sqlc/
    ├── db.gen.go           # High level interface and DB functions
    ├── models.gen.go       # Database models
    └── queries.gen.go      # Go code for the queries

The generated code from the sql queries is already fully typed and tested to match your query. This is far more reliable than using AI to build the go code, you just need to use it to write the SQL queries. 🙃 Just make sure that queries does what you expect.

Generated Code

Connect to the Database

Let’s dive into the code that gets generated by SQLC. Firs in db.gen.go, you will find the DBTX interface with the basic db commands, and the Queries struct which will hold all the queries as go functions:

// db.gen.go
package sqlc

type DBTX interface {
	Exec(context.Context, string, ...interface{}) (pgconn.CommandTag, error)
	Query(context.Context, string, ...interface{}) (pgx.Rows, error)
	QueryRow(context.Context, string, ...interface{}) pgx.Row
}

func New(db DBTX) *Queries {
	return &Queries{db: db}
}

To use it with your database connection, you can do the following in a simple script to test it out. We are using pgx as the PostgresSQL database driver, it was specified in the sqlc.yaml file as well.

package main

import (
	"context"
	"app/db/sqlc"
	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgtype"
)

func main() {
    ctx := context.Background()
    conn, err := pgx.Connect(ctx, "postgres://username:password@localhost:5432/dbname")
    if err != nil {
        return err
    }
    defer conn.Close(ctx)

    queries := sqlc.New(conn)
}

Execute a Query

Now with the queries object, you can call the generated functions to interact with the database. Let’s look at the generated code for the GetAuthor query:

// queries.gen.go
const getAuthor = `-- name: GetAuthor :one
SELECT id, name, bio, uuid FROM authors WHERE id = $1`

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

It keeps in a variable the actual SQL query and the function to call it and return an object of the type Author.

// models.gen.go
type Author struct {
    ID   int64
    Name string
    Bio  pgtype.Text
    Uuid pgtype.UUID
}

The generated object is using the pgtype package to handle the Postgres specific types. The type can be overridden to a certain level in the sqlc.yaml file.

But it’s recommended to convert those type to a “business logic” type, so that you don’t propagate the database types in your code. Let’s see how it can be used with our previously created queries object:

author, err := queries.GetAuthor(ctx, 1)

This will return the author with the ID 1 from the database, or an error saying no row was found if the author doesn’t exist.

That’s it for the basics with SQLC, hopefully that’s enough to get you started with it.

Introduction

SQL Files and Migrations

Installation

Example Migration File with dbmate

Example Query

Using SQLC

Installation

Configuration with sqlc.yaml

Using the tools pattern

Generated code

Generated Code

Connect to the Database

Execute a Query

Example Migration File with `dbmate`

Configuration with `sqlc.yaml`