This blogpost also posted in Medium as a part of Gravel Engineering blog.

Why SQLC?

For those who does not know what SQLC is, it is a Database tools to generate idiomatic type-safe go code that derived from our own SQL Queries.

Why using SQLC you may asked? because ORM only great if you have rather basic relationships between models OR you basically omit any modern relational DB feature for ease of use. Once you have the needs to modify models in the non-basic ways, you’re start to tangle the ORM code and may or may not impact performance due to unoptimized queries.

SQLC is here for people that comfortable using SQL Queries but want the type safety of Go, basically dealing with the uncomfortable part of actually using Database in any programming language, but still allowing us to make deep, optimized modification as any people with SQL know-how can relate.

Setting up SQLC

SQLC comes in various flavour, you can embed it directly via brew:

1brew install sqlc

Or via Go Instal (Go >=1.17):

1go install github.com/kyleconroy/sqlc/cmd/sqlc@latest

Or via Go Get:

1go get github.com/kyleconroy/sqlc/cmd/sqlc

It also has docker image for those that needed it in a pipeline somehow:

1docker pull kjconroy/sqlc

After you install SQLC in any way, you should then afterward has access to it in terminal via sqlc command.

Using SQLC in Go Project

After you Initiate a Golang project, you can start using SQLC by first creating it’s configuration file in sqlc.yaml:

 1version: "2"
 2sql:
 3  - engine: "postgresql"
 4    # queries refers to where our manually created queries located
 5    queries: "query.sql" # will refer a file
 6    # or
 7    queries: "misc/queries" # will refer a directory
 8    # schema refers to where our schema definitions located
 9    schema: "schema.sql" # will refer a file
10    # or
11    schema: "misc/migrations" # will refer a directory
12    gen:
13      go:
14        package: "db"
15        sql_package: "pgx/v5"
16        out: "db"

This YAML specifies several things:

• Engine -> What kind of DB you’re using. In this example we’re using postgresql
• Queries-> File/Directory for where you store your SQL Queries. These queries is the source target for SQLC to generate type-safe Go code.
• Schema -> FIle/Directory for where you store your schema logic. SQLC has support for go-migrate schema structure of up.sql and down.sql format. Else, you may want to just dump the schema in a single schema.sql file.
• Gen -> specify the auto-generated parts of the SQLC, in this example it will generate Go specific implementation:
  • Package -> specify the package name of the generated code.
  • Out -> specify the path where the generated code will be put on.
  • SQL_Package -> due to go/pq package is currently entering maintenance mode, we’re encouraged to use pgx driver instead. This will specify the pgx version that SQLC will be basing on.

Using SQLC to generate Type-Safe Models from schema

In this example, I will demonstrate how to use go-migrate as the companion tools. After setting up Go-Migrate, we can create new migration via:

1migrate create -ext sql -dir misc/migrations CreateAuthor

It will create xxxxxxx_CreateAuthor.up.sql and xxxxxxx_CreateAuthor.down.sql in misc/migrations (Remember that this is the path of schema in the sqlc.yaml).

There we can define the migration up and the migration down of our codes. Then I will create a schema for authors table:

1CREATE TABLE authors (
2	id BIGSERIAL PRIMARY KEY,
3	name text NOT NULL,
4	bio text
5);  
6
7CREATE INDEX IF NOT EXISTS "idx_authors_id" ON "authors" ("id");

This will do 2 things:

• tell go-migrate what migration needed to be executed
• tell sqlc what is the schema for table authors

Then after that, we can migrate our DB so our author table would be created. Migrate the authors table via :

1migrate -database <Put your DSN here> -path=misc/migrations up all

After that we can generate the model for authors via:

1sqlc generate

It will then create a new file called db/models.go that contains:

 1package db
 2
 3import (
 4	"github.com/jackc/pgx/v5/pgtype"
 5	typeext "github.com/muhwyndhamhp/material-go/utils/typeext"
 6)
 7
 8type Author struct {
 9	ID int64
10	Name string
11	Bio pgtype.Text
12}

Using SQLC to generate Type-Safe Functions that execute DB Transactions

Now that we have the model, we also want to create various transactions. SQLC doesn’t generate transaction for us, instead, we’re supposed to create our own transaction.

First we should create new file called authors.sql inside misc/queries (Remember this is the queries path we define in the sqlc.yaml up above):

 1-- name: GetAuthor :one
 2SELECT * FROM authors
 3WHERE id = $1 LIMIT 1;
 4
 5-- name: ListAuthors :many
 6SELECT * FROM authors
 7ORDER BY name;
 8
 9-- name: CreateAuthor :one
10INSERT INTO authors (
11	name, bio
12) VALUES (
13	$1, $2
14)
15RETURNING *;
16
17-- name: DeleteAuthor :exec
18DELETE FROM authors
19WHERE id = $1;
20
21-- name: UpdateAuthor :one
22UPDATE authors
23SET 
24	name = $2,
25	bio = $3
26WHERE id = $1
27RETURNING *;

Here, we’re defining several queries that refers to various operations. sqlc will read the comment structure above the queries to define what the function is and what kind of return format it wants.

1-- name: CreateAuthor :one

This comments means:

• Name of the function is CreateAuthor
• It should return exactly 1 schema of authors.

Other than :one, it also support :many for returning more than 1 schema in a list, and :exec which means it won’t return any schema (only executing the SQL).

After that we can exec:

1sqlc generate

again to both generate the model and the function. It will then create file db/authors.sql.go:

 1package db
 2
 3import (
 4	"context"
 5	"github.com/jackc/pgx/v5/pgtype"
 6)
 7
 8const createAuthor = `-- name: CreateAuthor :one
 9INSERT INTO authors (
10	name, bio
11) VALUES (
12	$1, $2
13)
14RETURNING id, name, bio
15`
16
17type CreateAuthorParams struct {
18	Name string
19	Bio pgtype.Text
20}
21
22func (q *Queries) CreateAuthor(ctx context.Context, arg CreateAuthorParams) (Author, error) {
23	row := q.db.QueryRow(ctx, createAuthor, arg.Name, arg.Bio)
24	var i Author
25	err := row.Scan(&i.ID, &i.Name, &i.Bio)
26	return i, err
27}
28
29// And many more below as you define your queries...

Using SQLC generated function in a program

One more thing that I haven’t mentioned is that other than generating models.go and authors.go inside db path (or any path that you specify in sqlc.yaml), it will also generate db.go file which functions as the entry points for our queries.

Before you can call queries tho, we need to do two things:

1. Create new DB instance

You can put this anywhere you first time initiate your program. Or if you have some sort of dependency injection, anywhere you initiate your DB instance:

 1db, err := pgxpool.New(ctx, 
 2						  `user=postgres 
 3						  dbname=postgres_db
 4						  sslmode=disable 
 5						  host=localhost 
 6						  port=5678`
 7						  )
 8						  
 9if err != nil {
10	return err
11}

2. Initiate Query entry point

After that, we should use the db instance up above to initiate entry points for our queries:

1queries := db.New(db)

3. Use our Query!

Then you can immediately use queries to access our predefined queries above. For example:

 1insertedAuthor, err := queries.CreateAuthor(ctx, db.CreateAuthorParams{
 2	Name: "Brian Kernighan",
 3	Bio: pgtype.Text{
 4		String: "Co-author of The C Programming Language and The Go Programming Language", 
 5		Valid: true,
 6	},
 7})
 8
 9if err != nil {
10	return err
11}
12
13log.Println(insertedAuthor)

That’s it! 🎉

Further Notes

This conclude the basic setups for SQLC in a Go project. But I suppose there is more than that to a project. Further readings can be found below:

1. Using custom struct for JSONB Type in SQLC (Work in Progress)