Introduction⌗

sqlc is an open-source code generator developed in Go that generates CRUD code for Go projects based on DDL. Before getting started, you need to write Schema and Query SQL.

If you have an existing database and want to quickly generate a Schema, you can use atlas to export your Schema:

atlas schema inspect -u "mysql://root:pass@localhost:3306/example" --format '{{ sql . }}' > schema.sql

Installation⌗

Installation method on macOS:

brew install sqlc

For other platforms, please refer to the official documentation.

Configuration⌗

First, you need to create a sqlc.yaml for your project:

version: "2"
sql:
  - engine: "mysql"
    queries: "query.sql"
    schema: "schema.sql"
    gen:
      go:
        out: "tutorial" # Directory for generated code
        package: "tutorial" # Package name in Go, can be customized as needed

File extensions can be yaml, yml, or json.

Define Schema⌗

Create a schema.sql file with the following definition (or use atlas to export an existing database structure):

-- Create "users" table
CREATE TABLE `users` (
    `id` bigint unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(255) NOT NULL,
    `email` varchar(255) NOT NULL,
    `password` varchar(255) NULL,
    `created_at` timestamp NULL,
    `updated_at` timestamp NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `users_email_unique` (`email`)
) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;

Define Query⌗

Create a query.sql file and define the related DML logic:

-- name: GetUser :one
SELECT * FROM users
WHERE id = ? LIMIT 1;

-- name: ListUsers :many
SELECT * FROM users
ORDER BY id DESC;

-- name: CreateUser :execresult
INSERT INTO users (
  name, email, password
) VALUES (
  ?, ?, ?
);

-- name: DeleteUser :exec
DELETE FROM users
WHERE id = ?;

Note that -- name: {NAME} :{} is used to declare the method name and return type.

Generate Code⌗

sqlc generate

After generation, the directory structure is as follows:

.
├── query.sql
├── schema.sql
├── sqlc.yaml
└── tutorial
    ├── db.go
    ├── models.go
    └── query.sql.go

2 directories, 6 files

Contents of the db.go file:

// Code generated by sqlc. DO NOT EDIT.
// versions:
//   sqlc v1.26.0

package tutorial

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,
	}
}

Code in the models.go file:

// Code generated by sqlc. DO NOT EDIT.
// versions:
//   sqlc v1.26.0

package tutorial

import (
	"database/sql"
)

type User struct {
	ID        uint64
	Name      string
	Email     string
	Password  sql.NullString
	CreatedAt sql.NullTime
	UpdatedAt sql.NullTime
}

Code in the query.sql.go file:

// Code generated by sqlc. DO NOT EDIT.
// versions:
//   sqlc v1.26.0
// source: query.sql

package tutorial

import (
	"context"
	"database/sql"
)

const createUser = `-- name: CreateUser :execresult
INSERT INTO users (
  name, email, password
) VALUES (
  ?, ?, ?
)
`

type CreateUserParams struct {
	Name     string
	Email    string
	Password sql.NullString
}

func (q *Queries) CreateUser(ctx context.Context, arg CreateUserParams) (sql.Result, error) {
	return q.db.ExecContext(ctx, createUser, arg.Name, arg.Email, arg.Password)
}

const deleteUser = `-- name: DeleteUser :exec
DELETE FROM users
WHERE id = ?
`

func (q *Queries) DeleteUser(ctx context.Context, id uint64) error {
	_, err := q.db.ExecContext(ctx, deleteUser, id)
	return err
}

const getUser = `-- name: GetUser :one
SELECT id, name, email, password, created_at, updated_at FROM users
WHERE id = ? LIMIT 1
`

func (q *Queries) GetUser(ctx context.Context, id uint64) (User, error) {
	row := q.db.QueryRowContext(ctx, getUser, id)
	var i User
	err := row.Scan(
		&i.ID,
		&i.Name,
		&i.Email,
		&i.Password,
		&i.CreatedAt,
		&i.UpdatedAt,
	)
	return i, err
}

const listUsers = `-- name: ListUsers :many
SELECT id, name, email, password, created_at, updated_at FROM users
ORDER BY id DESC
`

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.Email,
			&i.Password,
			&i.CreatedAt,
			&i.UpdatedAt,
		); 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
}

Summary⌗

sqlc currently only supports code generation for Go projects, although it also provides Kotlin, Python, and TypeScript, but these are currently in Beta stage. However, sqlc provides the capability for plugins in other languages, and it can be foreseen that the ecosystem will gradually improve in the future.

Additionally, atlas is also a good DDL management tool.

I hope this is helpful, Happy hacking…