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 an error.
• :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

• https://github.com/sqlc-dev/sqlc

Leapcell: The Next-Gen Serverless Platform for Golang Hosting

Finally, I recommend a platform that is most suitable for deploying Go services: Leapcell