sqlc: Type-Safe Querying in Go
Leapcell: The Next-Gen Serverless Platform for Golang Hosting Introduction The interfaces provided by the database/sql standard library in the Go language are relatively low-level. This requires us to write a large amount of repetitive code. This substantial amount of boilerplate code is not only cumbersome to write but also error-prone. Sometimes, if you modify the field type, you may need to make changes in many places; if you add a new field, you also need to modify the places where the select * query statement was previously used. If there are any omissions, it may lead to a panic during runtime. Even if you use an ORM library, these issues cannot be completely resolved! That's where sqlc comes in! sqlc can generate type-safe and idiomatic Go interface code based on the SQL statements we write, and we just need to call these methods. Quick Start Installation First, install sqlc: $ go install github.com/sqlc-dev/sqlc/cmd/sqlc@latest Of course, you also need the corresponding database driver: $ go get github.com/lib/pq $ go get github.com/go-sql-driver/mysql Writing SQL Statements Write the table creation statement. Write the following content in the schema.sql file: CREATE TABLE users ( id BIGSERIAL PRIMARY KEY, name TEXT NOT NULL, bio TEXT ); Write the query statements. Write the following content in the query.sql file: -- name: GetUser :one SELECT * FROM users WHERE id = $1 LIMIT 1; -- name: ListUsers :many SELECT * FROM users ORDER BY name; -- name: CreateUser :exec INSERT INTO users ( name, bio ) VALUES ( $1, $2 ) RETURNING *; -- name: DeleteUser :exec DELETE FROM users WHERE id = $1; sqlc supports PostgreSQL. sqlc only requires a small configuration file sqlc.yaml: version: "1" packages: - name: "db" path: "./db" queries: "./query.sql" schema: "./schema.sql" Configuration Explanation: version: Version. packages: name: The generated package name. path: The path of the generated files. queries: The query SQL file. schema: The table creation SQL file. Generating Go Code Execute the following command to generate the corresponding Go code: sqlc generate sqlc will generate the database operation code in the same directory. The directory structure is as follows: db ├── db.go ├── models.go └── query.sql.go sqlc generates the model object structure according to schema.sql and query.sql: // models.go type User struct { ID int64 Name string Bio sql.NullString } And the operation interfaces: // query.sql.go func (q *Queries) CreateUser(ctx context.Context, arg CreateUserParams) (User, error) func (q *Queries) DeleteUser(ctx context.Context, id int64) error func (q *Queries) GetUser(ctx context.Context, id int64) (User, error) func (q *Queries) ListUsers(ctx context.Context) ([]User, error) Among them, Queries is a structure encapsulated by sqlc. Usage Example package main import ( "database/sql" "fmt" "log" _ "github.com/lib/pq" "golang.org/x/net/context" "github.com/leapcell/examples/sqlc" ) func main() { pq, err := sql.Open("postgres", "dbname=sqlc sslmode=disable") if err != nil { log.Fatal(err) } queries := db.New(pq) users, err := queries.ListUsers(context.Background()) if err != nil { log.Fatal("ListUsers error:", err) } fmt.Println(users) insertedUser, err := queries.CreateUser(context.Background(), db.CreateUserParams{ Name: "Rob Pike", Bio: sql.NullString{String: "Co-author of The Go Programming Language", Valid: true}, }) if err != nil { log.Fatal("CreateUser error:", err) } fmt.Println(insertedUser) fetchedUser, err := queries.GetUser(context.Background(), insertedUser.ID) if err != nil { log.Fatal("GetUser error:", err) } fmt.Println(fetchedUser) err = queries.DeleteUser(context.Background(), insertedUser.ID) if err != nil { log.Fatal("DeleteUser error:", err) } } The generated code is under the package db (specified by the packages.name option). First, call db.New() and pass in the return value sql.DB of sql.Open() as a parameter to obtain the Queries object. All our operations on the users table need to be completed through the methods of this object. Starting PostgreSQL and Creating the Database and Tables For the above program to run, you also need to start PostgreSQL and create the database and tables: $ createdb sqlc $ psql -f schema.sql -d sqlc The first command creates a database named sqlc, and the second command executes the statements in the schema.sql file in the sqlc database, that is, creates the table. Running the Program $ go run . Example of the running result: [] {1 Rob Pike {Co-author of The Go Programming Language true}} Code Generation In addition to the SQL statements themselves, sqlc requires us to provide some bas

Leapcell: The Next-Gen Serverless Platform for Golang Hosting
Introduction
The interfaces provided by the database/sql
standard library in the Go language are relatively low-level. This requires us to write a large amount of repetitive code. This substantial amount of boilerplate code is not only cumbersome to write but also error-prone. Sometimes, if you modify the field type, you may need to make changes in many places; if you add a new field, you also need to modify the places where the select *
query statement was previously used. If there are any omissions, it may lead to a panic during runtime. Even if you use an ORM library, these issues cannot be completely resolved! That's where sqlc comes in! sqlc can generate type-safe and idiomatic Go interface code based on the SQL statements we write, and we just need to call these methods.
Quick Start
Installation
First, install sqlc:
$ go install github.com/sqlc-dev/sqlc/cmd/sqlc@latest
Of course, you also need the corresponding database driver:
$ go get github.com/lib/pq
$ go get github.com/go-sql-driver/mysql
Writing SQL Statements
Write the table creation statement. Write the following content in the schema.sql
file:
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
name TEXT NOT NULL,
bio TEXT
);
Write the query statements. Write the following content in the query.sql
file:
-- name: GetUser :one
SELECT * FROM users
WHERE id = $1 LIMIT 1;
-- name: ListUsers :many
SELECT * FROM users
ORDER BY name;
-- name: CreateUser :exec
INSERT INTO users (
name, bio
) VALUES (
$1, $2
)
RETURNING *;
-- name: DeleteUser :exec
DELETE FROM users
WHERE id = $1;
sqlc supports PostgreSQL. sqlc only requires a small configuration file sqlc.yaml
:
version: "1"
packages:
- name: "db"
path: "./db"
queries: "./query.sql"
schema: "./schema.sql"
Configuration Explanation:
-
version
: Version. -
packages
:-
name
: The generated package name. -
path
: The path of the generated files. -
queries
: The query SQL file. -
schema
: The table creation SQL file.
-
Generating Go Code
Execute the following command to generate the corresponding Go code:
sqlc generate
sqlc will generate the database operation code in the same directory. The directory structure is as follows:
db
├── db.go
├── models.go
└── query.sql.go
sqlc generates the model object structure according to schema.sql
and query.sql
:
// models.go
type User struct {
ID int64
Name string
Bio sql.NullString
}
And the operation interfaces:
// query.sql.go
func (q *Queries) CreateUser(ctx context.Context, arg CreateUserParams) (User, error)
func (q *Queries) DeleteUser(ctx context.Context, id int64) error
func (q *Queries) GetUser(ctx context.Context, id int64) (User, error)
func (q *Queries) ListUsers(ctx context.Context) ([]User, error)
Among them, Queries
is a structure encapsulated by sqlc.
Usage Example
package main
import (
"database/sql"
"fmt"
"log"
_ "github.com/lib/pq"
"golang.org/x/net/context"
"github.com/leapcell/examples/sqlc"
)
func main() {
pq, err := sql.Open("postgres", "dbname=sqlc sslmode=disable")
if err != nil {
log.Fatal(err)
}
queries := db.New(pq)
users, err := queries.ListUsers(context.Background())
if err != nil {
log.Fatal("ListUsers error:", err)
}
fmt.Println(users)
insertedUser, err := queries.CreateUser(context.Background(), db.CreateUserParams{
Name: "Rob Pike",
Bio: sql.NullString{String: "Co-author of The Go Programming Language", Valid: true},
})
if err != nil {
log.Fatal("CreateUser error:", err)
}
fmt.Println(insertedUser)
fetchedUser, err := queries.GetUser(context.Background(), insertedUser.ID)
if err != nil {
log.Fatal("GetUser error:", err)
}
fmt.Println(fetchedUser)
err = queries.DeleteUser(context.Background(), insertedUser.ID)
if err != nil {
log.Fatal("DeleteUser error:", err)
}
}
The generated code is under the package db
(specified by the packages.name
option). First, call db.New()
and pass in the return value sql.DB
of sql.Open()
as a parameter to obtain the Queries
object. All our operations on the users
table need to be completed through the methods of this object.
Starting PostgreSQL and Creating the Database and Tables
For the above program to run, you also need to start PostgreSQL and create the database and tables:
$ createdb sqlc
$ psql -f schema.sql -d sqlc
The first command creates a database named sqlc
, and the second command executes the statements in the schema.sql
file in the sqlc
database, that is, creates the table.
Running the Program
$ go run .
Example of the running result:
[]
{1 Rob Pike {Co-author of The Go Programming Language true}}
Code Generation
In addition to the SQL statements themselves, sqlc requires us to provide some basic information for the generated program in the form of comments when writing SQL statements. The syntax is as follows:
-- name:
name
is the name of the generated method, such as CreateUser
, ListUsers
, GetUser
, DeleteUser
, etc. above. cmd
can have the following values:
-
:one
: Indicates that the SQL statement returns one object, and the return value of the generated method is(object type, error)
, and the object type can be derived from the table name. -
:many
: Indicates that the SQL statement will return multiple objects, and the return value of the generated method is([]object type, error)
. -
:exec
: Indicates that the SQL statement does not return an object and only returns anerror
. -
:execrows
: Indicates that the SQL statement needs to return the number of affected rows.
:one
Example
-- name: GetUser :one
SELECT id, name, bio FROM users
WHERE id = $1 LIMIT 1
The --name
in the comment instructs to generate the method GetUser
. Derived from the table name, the basic type of the return value is User
. :one
indicates that only one object is returned. Therefore, the final return value is (User, error)
:
// db/query.sql.go
const getUser = `-- name: GetUser :one
SELECT id, name, bio FROM users
WHERE id = $1 LIMIT 1
`
func (q *Queries) GetUser(ctx context.Context, id int64) (User, error) {
row := q.db.QueryRowContext(ctx, getUser, id)
var i User
err := row.Scan(&i.ID, &i.Name, &i.Bio)
return i, err
}
:many
Example
-- name: ListUsers :many
SELECT * FROM users
ORDER BY name;
The --name
in the comment instructs to generate the method ListUsers
. Derived from the table name users
, the basic type of the return value is User
. :many
indicates that a slice of objects is returned. Therefore, the final return value is ([]User, error)
:
// db/query.sql.go
const listUsers = `-- name: ListUsers :many
SELECT id, name, bio FROM users
ORDER BY name
`
func (q *Queries) ListUsers(ctx context.Context) ([]User, error) {
rows, err := q.db.QueryContext(ctx, listUsers)
if err != nil {
return nil, err
}
defer rows.Close()
var items []User
for rows.Next() {
var i User
if err := rows.Scan(&i.ID, &i.Name, &i.Bio); 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
}
Here is a detail to note. Even if we use select *
, the SQL statement in the generated code will be rewritten to specific fields:
SELECT id, name, bio FROM users
ORDER BY name
In this way, if we need to add or delete fields later, as long as we execute the sqlc
command, this SQL statement and the ListUsers()
method can be kept consistent, which is very convenient!
:exec
Example
-- name: DeleteUser :exec
DELETE FROM users
WHERE id = $1
The --name
in the comment instructs to generate the method DeleteUser
. Derived from the table name users
, the basic type of the return value is User
. :exec
indicates that no object is returned. Therefore, the final return value is error
:
// db/query.sql.go
const deleteUser = `-- name: DeleteUser :exec
DELETE FROM users
WHERE id = $1
`
func (q *Queries) DeleteUser(ctx context.Context, id int64) error {
_, err := q.db.ExecContext(ctx, deleteUser, id)
return err
}
:execrows
Example
-- name: DeleteUserN :execrows
DELETE FROM users
WHERE id = $1
The --name
in the comment instructs to generate the method DeleteUserN
. Derived from the table name users
, the basic type of the return value is User
. :exec
indicates that the number of affected rows (that is, how many rows are deleted) is returned. Therefore, the final return value is (int64, error)
:
// db/query.sql.go
const deleteUserN = `-- name: DeleteUserN :execrows
DELETE FROM users
WHERE id = $1
`
func (q *Queries) DeleteUserN(ctx context.Context, id int64) (int64, error) {
result, err := q.db.ExecContext(ctx, deleteUserN, id)
if err != nil {
return 0, err
}
return result.RowsAffected()
}
No matter how complex the written SQL is, it follows the above rules. We just need to add an extra line of comment when writing SQL statements, and sqlc can generate idiomatic SQL operation methods for us. The generated code is no different from what we write by hand, the error handling is also very complete, and it also avoids the trouble and errors of writing by hand.
Model Objects
sqlc will generate the corresponding model structure for all table creation statements. The structure name is the singular form of the table name, and the first letter is capitalized. For example:
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name text NOT NULL
);
It will generate the corresponding structure:
type User struct {
ID int
Name string
}
Moreover, sqlc can parse the ALTER TABLE
statement, and it will generate the structure of the model object according to the final table structure. For example:
CREATE TABLE users (
id SERIAL PRIMARY KEY,
birth_year int NOT NULL
);
ALTER TABLE users ADD COLUMN bio text NOT NULL;
ALTER TABLE users DROP COLUMN birth_year;
ALTER TABLE users RENAME TO writers;
In the above SQL statements, there are two columns id
and birth_year
when the table is created. The first ALTER TABLE
statement adds a column bio
, the second deletes the birth_year
column, and the third changes the table name from users
to writers
. sqlc generates the code according to the final table name writers
and the columns id
and bio
in the table:
package db
type Writer struct {
ID int
Bio string
}
Configuration Fields
Other configuration fields can also be set in the sqlc.yaml
file.
emit_json_tags
The default value is false
. Setting this field to true
can add JSON tags to the generated model object structure. For example:
CREATE TABLE users (
id SERIAL PRIMARY KEY,
created_at timestamp NOT NULL
);
It will generate:
package db
import (
"time"
)
type User struct {
ID int `json:"id"`
CreatedAt time.Time `json:"created_at"`
}
emit_prepared_queries
The default value is false
. Setting this field to true
will generate the corresponding prepared statement for the SQL. For example, if you set this option in the quick start example, the final generated structure Queries
will add all the prepared statement objects corresponding to the SQL:
type Queries struct {
db DBTX
tx *sql.Tx
createUserStmt *sql.Stmt
deleteUserStmt *sql.Stmt
getUserStmt *sql.Stmt
listUsersStmt *sql.Stmt
}
And a Prepare()
method:
func Prepare(ctx context.Context, db DBTX) (*Queries, error) {
q := Queries{db: db}
var err error
if q.createUserStmt, err = db.PrepareContext(ctx, createUser); err != nil {
return nil, fmt.Errorf("error preparing query CreateUser: %w", err)
}
if q.deleteUserStmt, err = db.PrepareContext(ctx, deleteUser); err != nil {
return nil, fmt.Errorf("error preparing query DeleteUser: %w", err)
}
if q.getUserStmt, err = db.PrepareContext(ctx, getUser); err != nil {
return nil, fmt.Errorf("error preparing query GetUser: %w", err)
}
if q.listUsersStmt, err = db.PrepareContext(ctx, listUsers); err != nil {
return nil, fmt.Errorf("error preparing query ListUsers: %w", err)
}
return &q, nil
}
The other generated methods all use these objects instead of directly using SQL statements:
func (q *Queries) CreateUser(ctx context.Context, arg CreateUserParams) (User, error) {
row := q.queryRow(ctx, q.createUserStmt, createUser, arg.Name, arg.Bio)
var i User
err := row.Scan(&i.ID, &i.Name, &i.Bio)
return i, err
}
We need to call this Prepare()
method during program initialization.
emit_interface
The default value is false
. Setting this field to true
will generate an interface for the query structure. For example, if you set this option in the quick start example, the final generated code will have an additional file querier.go
:
// db/querier.go
type Querier interface {
CreateUser(ctx context.Context, arg CreateUserParams) (User, error)
DeleteUser(ctx context.Context, id int64) error
DeleteUserN(ctx context.Context, id int64) (int64, error)
GetUser(ctx context.Context, id int64) (User, error)
ListUsers(ctx context.Context) ([]User, error)
}
Conclusion
Although sqlc still not perfect, it can indeed greatly simplify the complexity of writing database code in Go, improve our coding efficiency, and reduce the probability of errors. For those who use PostgreSQL, it is highly recommended to give it a try!
References
Leapcell: The Next-Gen Serverless Platform for Golang Hosting
Finally, I recommend a platform that is most suitable for deploying Go services: Leapcell
1. Multi-Language Support
- Develop with JavaScript, Python, Go, or Rust.
2. Deploy unlimited projects for free
- pay only for usage — no requests, no charges.
3. Unbeatable Cost Efficiency
- Pay-as-you-go with no idle charges.
- Example: $25 supports 6.94M requests at a 60ms average response time.
4. Streamlined Developer Experience
- Intuitive UI for effortless setup.
- Fully automated CI/CD pipelines and GitOps integration.
- Real-time metrics and logging for actionable insights.
5. Effortless Scalability and High Performance
- Auto-scaling to handle high concurrency with ease.
- Zero operational overhead — just focus on building.
Explore more in the documentation!
Leapcell Twitter: https://x.com/LeapcellHQ