Exploring SQLC: Generating Type-Safe Go Code From SQL
Hey there fellow Gophers! In this article, we’ll explore how SQLC can make your life easier by generating Go code from your SQL queries to PostgreSQL. I’ll show you how to set it up in your project, define your database schema, write and execute SQL queries, and share some best practices for using SQLC in your code.
By the end of this article, you’ll have a decent understanding of how SQLC works and how it can help you write cleaner, more efficient database code in Go. Let’s dive in!
Introduction
SQLC is a popular open-source code generation tool for Go programming language that allows developers to write SQL queries in a separate file and generate corresponding Go code. The generated Go code is type-safe, which means it can be checked at compile-time, helping to prevent runtime errors that can occur with dynamic queries.
So why you should choose SQLC over other methods of interactions with RDMBs in Go? In coding we mostly need the balance of being productive while being a precise. For example ORMs like GORM provides a really nice productivity while not letting us have precise queries, on the other hand using built-in library database/sql allows us to be very precise, but not being productive as it is not type-safe. Here are some of the pros and cons for methods of interactions with RDBMs;
- Database/SQL (built-in library)
+ Works fast
- Have to manually map SQL fields to variables
- Error-prone as it is not type-safe - SQLX
+ Works fast and easy to use
+ Provides some type safety through the use of struct tags or explicit binding of parameters
- Errors will only occur at runtime - GORM
+ Queries are already written, takes very short code to execute
- Performance overhead
- Limited customization - SQLC
+ Allows you to be precise while not spending too much time
+ Almost as fast as built-in SQL library
+ Type-safe queries
- Limited database support, for now only supports PostgreSQL, mySQL and SQLite
Let’s get our hands dirty
If you go to documentation of SQLC, you will find out how it can be installed on your machine, I am not covering it up here since it is very easy to do so.
Here is how our project will look like from start. sqlc-tutorial is our main folder, which has a db folder responsible for all necessary database interactions. And most importantly sqlc.yaml file, which contains configuration information for SQLC. You may also use json format, but I will go with yaml.
sqlc-tutorial
├── db
│ ├── query
│ │ └── account.sql
│ ├── schema.sql
│ └── sqlc
└── sqlc.yamlInside sqlc.yaml,for now we have only the required parameters. This can be more populated with other various parameters that SQLC allows us.
version: "2"
sql:
- engine: "postgresql"
queries: "./db/query/"
schema: "./db/schema.sql"
gen:
go:
package: "db"
out: "./db/sqlc"Let’s go over the parameters we have added real quick.
— version declares what version of SQLC we are using,
— sql let’s us map in the sql collection,
— engine, what database engine will be used,
— queries, where our raw SQL queries will be found,
— schema, where our database schema templates will be found
— gen, what language will be generated, for now Go, Python and Kotlin is being supported,
— package the package name to use for the generated Go code,
— out, where generated code will be stored.
Next step is creating our database schema in schema.sql. Here we are writing our table creation SQL code.
CREATE TABLE "accounts" (
"id" bigserial PRIMARY KEY,
"owner" varchar NOT NULL,
"balance" bigint NOT NULL,
"created_at" timestamptz NOT NULL DEFAULT (now())
);Then, inside query folder, we are heading to account.sql file, where our queries will be generated from.
-- name: CreateAccount :one
INSERT INTO accounts (owner, balance)
VALUES ($1, $2) RETURNING *;
-- name: GetAccount :one
SELECT id, owner, balance, created_at
FROM accounts
WHERE id = $1 LIMIT 1;
-- name: ListAccounts :many
SELECT id, owner, balance, created_at
FROM accounts
ORDER BY id;
-- name: UpdateAccount :one
UPDATE accounts
set balance = $1
WHERE id = $2 RETURNING *;
-- name: DeleteAccount :exec
DELETE FROM accounts
WHERE id = $1;There are some important points that we need to cover here. As you can see, apart from basic SQL code there are comments, where SQLC actually gets some information to generate code from our queries. Basically, the comments use a specific syntax that tells SQLC how to generate the Go code. Here’s a breakdown of the syntax:
— name points to the name of the function that will be generated,
— one, many mean what will be the result of the function. For example, will it return just one data or more?
— exec is used to indicate that the query should be executed as a statement, and does not return any rows. This is often used for DELETE or UPDATE queries. (In our UPDATEquery, we are returning the updated data, so we use one instead)
Now that we have all the necessary configuration to generate our code, all we need to type sqlc generate in the terminal. Then you will find three different go files inside sqlc folder.
sqlc-tutorial
├── db
│ ├── query
│ │ └── account.sql
│ ├── schema.sql
│ └── sqlc
│ ├── account.sql.go
│ ├── db.go
│ └── models.go
└── sqlc.yamlLet’s go over what these files are responsible for;
models.go , as the name suggests, holds the models of our schema.
// Code generated by sqlc. DO NOT EDIT.
// versions:
// sqlc v1.16.0
package db
import (
"time"
)
type Account struct {
ID int64
Owner string
Balance int64
CreatedAt time.Time
}Inside db.go SQLC defines an interface DBTXthat specifies four methods for executing SQL queries: ExecContext, PrepareContext, QueryContext, and QueryRowContext. You may think these as helper functions that are used inside generated query functions.
It also defines a function called New that takes an object that implements the DBTXinterface and returns a pointer to a new instance of the Queriesstruct, which contains a single field of type DBTX. The New function is intended to be used as a constructor for Queries.Finally, the Queriesstruct has a method called WithTxthat takes a pointer to a sql.Txobject and returns a new instance of the Queriesstruct with its db field set to the txparameter. This method is intended to be used to create a new instance of Queriesthat operates within the context of a transaction. In this tutorial, we are not going over database transactions though, maybe in a different writing as it is a wide topic.
// Code generated by sqlc. DO NOT EDIT.
// versions:
// sqlc v1.16.0
package db
import (
"context"
"database/sql"
)
type DBTX interface {
ExecContext(context.Context, string, ...interface{}) (sql.Result, error)
PrepareContext(context.Context, string) (*sql.Stmt, error)
QueryContext(context.Context, string, ...interface{}) (*sql.Rows, error)
QueryRowContext(context.Context, string, ...interface{}) *sql.Row
}
func New(db DBTX) *Queries {
return &Queries{db: db}
}
type Queries struct {
db DBTX
}
func (q *Queries) WithTx(tx *sql.Tx) *Queries {
return &Queries{
db: tx,
}
}And lastly inside account.sql.go file, you will find all the generated query functions that you will be using. This file basically consists an SQL query variable, a struct that has required parameters for the query and a method of Queries struct, which is the actual query function itself for each query that we wrote previously inside account.sql file.
// Code generated by sqlc. DO NOT EDIT.
// versions:
// sqlc v1.16.0
// source: account.sql
package db
import (
"context"
)
const createAccount = `-- name: CreateAccount :one
INSERT INTO accounts (owner, balance)
VALUES ($1, $2) RETURNING id, owner, balance, created_at
`
type CreateAccountParams struct {
Owner string
Balance int64
}
func (q *Queries) CreateAccount(ctx context.Context, arg CreateAccountParams) (Account, error) {
row := q.db.QueryRowContext(ctx, createAccount, arg.Owner, arg.Balance)
var i Account
err := row.Scan(
&i.ID,
&i.Owner,
&i.Balance,
&i.CreatedAt,
)
return i, err
}
const deleteAccount = `-- name: DeleteAccount :exec
DELETE FROM accounts
WHERE id = $1
`
func (q *Queries) DeleteAccount(ctx context.Context, id int64) error {
_, err := q.db.ExecContext(ctx, deleteAccount, id)
return err
}
const getAccount = `-- name: GetAccount :one
SELECT id, owner, balance, created_at
FROM accounts
WHERE id = $1 LIMIT 1
`
func (q *Queries) GetAccount(ctx context.Context, id int64) (Account, error) {
row := q.db.QueryRowContext(ctx, getAccount, id)
var i Account
err := row.Scan(
&i.ID,
&i.Owner,
&i.Balance,
&i.CreatedAt,
)
return i, err
}
const listAccounts = `-- name: ListAccounts :many
SELECT id, owner, balance, created_at
FROM accounts
ORDER BY id
`
func (q *Queries) ListAccounts(ctx context.Context) ([]Account, error) {
rows, err := q.db.QueryContext(ctx, listAccounts)
if err != nil {
return nil, err
}
defer rows.Close()
var items []Account
for rows.Next() {
var i Account
if err := rows.Scan(
&i.ID,
&i.Owner,
&i.Balance,
&i.CreatedAt,
); err != nil {
return nil, err
}
items = append(items, i)
}
if err := rows.Close(); err != nil {
return nil, err
}
if err := rows.Err(); err != nil {
return nil, err
}
return items, nil
}
const updateAccount = `-- name: UpdateAccount :one
UPDATE accounts
set balance = $1
WHERE id = $2 RETURNING id, owner, balance, created_at
`
type UpdateAccountParams struct {
Balance int64
ID int64
}
func (q *Queries) UpdateAccount(ctx context.Context, arg UpdateAccountParams) (Account, error) {
row := q.db.QueryRowContext(ctx, updateAccount, arg.Balance, arg.ID)
var i Account
err := row.Scan(
&i.ID,
&i.Owner,
&i.Balance,
&i.CreatedAt,
)
return i, err
}And just easy as that, there you have all the needs for interacting with your database. You may use these queries in your services, handlers or wherever you want! Now that we have covered the basics, we will go over some best practices while using SQLC in your project.
Note: Don’t forget to import your database engine in your main.gofile. In my case I used github.com/lib/pq with a blank identifier.
Best Practices
- It is suggested to use queries inside separate files. This helps our project and code to be more concise and organized. For the sake of the simplicity our tutorial, I put them in the same file, but you may separate them. For example, our
account.sqlfile could be separated into five different files.
├── db
│ ├── query
│ │ ├── create-account.sql
│ │ ├── delete-account.sql
│ │ ├── get-account.sql
│ │ ├── list-accounts.sql
│ │ └── update-account.sql
│ ├── schema.sql
│ └── sqlc
│ ├── create-account.sql.go
│ ├── db.go
│ ├── delete-account.sql.go
│ ├── get-account.sql.go
│ ├── list-accounts.sql.go
│ ├── models.go
│ └── update-account.sql.go
└── sqlc.yaml2. Handle errors properly: SQLC generates code that returns errors, so it’s important to handle these properly. Check for errors and handle them appropriately in your code. For example, our generated GetAccount query function possibly returns different kind of errors.
account, err := db.GetAccount(ctx, id)
if err != nil {
if err == sql.ErrNoRows {
// handle "not found" error
} else {
// handle other errors
}
}
// process account3. SELECT * is an expensive query, avoid it: Even if you have a small database with a few columns, try not to use * in your SELECT queries. It results in tight coupling between your code and the database schema. This coupling can lead to code breaking if the schema is changed, especially in microservices architectures where each service has its own database. To prevent code breaking due to schema changes, it is recommended to explicitly list the columns needed in your query.
Conclusion
In conclusion, SQLC is a powerful open-source code generation tool for Go programming language that allows developers to generate Go code from SQL queries. It provides type safety and compile-time checks, preventing runtime errors that can occur with dynamic queries. SQLC strikes a balance between being productive and precise, making it an ideal choice for developers who want to write cleaner, more efficient database code in Go.
