Queries
Bob のクエリ生成機能は、手書きの SQL クエリから型安全な Go コードを生成します。
基本的なクエリ生成
SQL ファイルの作成
queries/users.sql ファイルを作成：
-- name: GetUser :one
SELECT id, name, email, created_at
FROM users
WHERE id = $1;
-- name: ListUsers :many
SELECT id, name, email, created_at
FROM users
ORDER BY created_at DESC;
-- name: CreateUser :one
INSERT INTO users (name, email)
VALUES ($1, $2)
RETURNING id, name, email, created_at;
-- name: UpdateUser :exec
UPDATE users
SET name = $1, email = $2
WHERE id = $3;
-- name: DeleteUser :exec
DELETE FROM users
WHERE id = $1;生成されるコード
// User represents a user from the database
type User struct {
ID int `db:"id"`
Name string `db:"name"`
Email string `db:"email"`
CreatedAt time.Time `db:"created_at"`
}
// GetUserParams contains the parameters for the GetUser query
type GetUserParams struct {
ID int `db:"id"`
}
// GetUser retrieves a single user by ID
func (q *Queries) GetUser(ctx context.Context, id int) (*User, error) {
query := `SELECT id, name, email, created_at FROM users WHERE id = $1`
var user User
err := q.db.QueryRowContext(ctx, query, id).Scan(
&user.ID,
&user.Name,
&user.Email,
&user.CreatedAt,
)
if err != nil {
return nil, err
}
return &user, nil
}
// ListUsers retrieves all users
func (q *Queries) ListUsers(ctx context.Context) ([]User, error) {
query := `SELECT id, name, email, created_at FROM users ORDER BY created_at DESC`
rows, err := q.db.QueryContext(ctx, query)
if err != nil {
return nil, err
}
defer rows.Close()
var users []User
for rows.Next() {
var user User
err := rows.Scan(
&user.ID,
&user.Name,
&user.Email,
&user.CreatedAt,
)
if err != nil {
return nil, err
}
users = append(users, user)
}
return users, nil
}
// CreateUserParams contains the parameters for the CreateUser query
type CreateUserParams struct {
Name string `db:"name"`
Email string `db:"email"`
}
// CreateUser creates a new user
func (q *Queries) CreateUser(ctx context.Context, params CreateUserParams) (*User, error) {
query := `INSERT INTO users (name, email) VALUES ($1, $2) RETURNING id, name, email, created_at`
var user User
err := q.db.QueryRowContext(ctx, query, params.Name, params.Email).Scan(
&user.ID,
&user.Name,
&user.Email,
&user.CreatedAt,
)
if err != nil {
return nil, err
}
return &user, nil
}複雑なクエリ
JOIN クエリ
-- name: GetUserWithPosts :many
SELECT
u.id as user_id,
u.name as user_name,
u.email as user_email,
p.id as post_id,
p.title as post_title,
p.content as post_content,
p.published as post_published
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
WHERE u.id = $1;生成されるコード：
type GetUserWithPostsRow struct {
UserID int `db:"user_id"`
UserName string `db:"user_name"`
UserEmail string `db:"user_email"`
PostID sql.NullInt64 `db:"post_id"`
PostTitle sql.NullString `db:"post_title"`
PostContent sql.NullString `db:"post_content"`
PostPublished sql.NullBool `db:"post_published"`
}
func (q *Queries) GetUserWithPosts(ctx context.Context, userID int) ([]GetUserWithPostsRow, error) {
query := `
SELECT
u.id as user_id,
u.name as user_name,
u.email as user_email,
p.id as post_id,
p.title as post_title,
p.content as post_content,
p.published as post_published
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
WHERE u.id = $1
`
rows, err := q.db.QueryContext(ctx, query, userID)
if err != nil {
return nil, err
}
defer rows.Close()
var result []GetUserWithPostsRow
for rows.Next() {
var row GetUserWithPostsRow
err := rows.Scan(
&row.UserID,
&row.UserName,
&row.UserEmail,
&row.PostID,
&row.PostTitle,
&row.PostContent,
&row.PostPublished,
)
if err != nil {
return nil, err
}
result = append(result, row)
}
return result, nil
}集計クエリ
-- name: GetUserStats :one
SELECT
COUNT(*) as total_users,
COUNT(CASE WHEN created_at > NOW() - INTERVAL '30 days' THEN 1 END) as recent_users,
AVG(EXTRACT(YEAR FROM AGE(created_at))) as avg_age_years
FROM users;生成されるコード：
type GetUserStatsRow struct {
TotalUsers int64 `db:"total_users"`
RecentUsers int64 `db:"recent_users"`
AvgAgeYears sql.NullFloat64 `db:"avg_age_years"`
}
func (q *Queries) GetUserStats(ctx context.Context) (*GetUserStatsRow, error) {
query := `
SELECT
COUNT(*) as total_users,
COUNT(CASE WHEN created_at > NOW() - INTERVAL '30 days' THEN 1 END) as recent_users,
AVG(EXTRACT(YEAR FROM AGE(created_at))) as avg_age_years
FROM users
`
var stats GetUserStatsRow
err := q.db.QueryRowContext(ctx, query).Scan(
&stats.TotalUsers,
&stats.RecentUsers,
&stats.AvgAgeYears,
)
if err != nil {
return nil, err
}
return &stats, nil
}動的クエリ
条件付きクエリ
-- name: SearchUsers :many
SELECT id, name, email, created_at
FROM users
WHERE
($1::text IS NULL OR name ILIKE '%' || $1 || '%')
AND ($2::text IS NULL OR email ILIKE '%' || $2 || '%')
AND ($3::timestamp IS NULL OR created_at > $3)
ORDER BY created_at DESC
LIMIT $4 OFFSET $5;生成されるコード：
type SearchUsersParams struct {
NameFilter sql.NullString `db:"name_filter"`
EmailFilter sql.NullString `db:"email_filter"`
CreatedAfter sql.NullTime `db:"created_after"`
Limit int `db:"limit"`
Offset int `db:"offset"`
}
func (q *Queries) SearchUsers(ctx context.Context, params SearchUsersParams) ([]User, error) {
query := `
SELECT id, name, email, created_at
FROM users
WHERE
($1::text IS NULL OR name ILIKE '%' || $1 || '%')
AND ($2::text IS NULL OR email ILIKE '%' || $2 || '%')
AND ($3::timestamp IS NULL OR created_at > $3)
ORDER BY created_at DESC
LIMIT $4 OFFSET $5
`
rows, err := q.db.QueryContext(ctx, query,
params.NameFilter,
params.EmailFilter,
params.CreatedAfter,
params.Limit,
params.Offset,
)
if err != nil {
return nil, err
}
defer rows.Close()
var users []User
for rows.Next() {
var user User
err := rows.Scan(
&user.ID,
&user.Name,
&user.Email,
&user.CreatedAt,
)
if err != nil {
return nil, err
}
users = append(users, user)
}
return users, nil
}使用例
func main() {
db, err := sql.Open("postgres", "...")
if err != nil {
log.Fatal(err)
}
defer db.Close()
queries := New(db)
ctx := context.Background()
// ユーザーの作成
user, err := queries.CreateUser(ctx, CreateUserParams{
Name: "John Doe",
Email: "john@example.com",
})
if err != nil {
log.Fatal(err)
}
// ユーザーの取得
retrievedUser, err := queries.GetUser(ctx, user.ID)
if err != nil {
log.Fatal(err)
}
// 全ユーザーの取得
allUsers, err := queries.ListUsers(ctx)
if err != nil {
log.Fatal(err)
}
// 検索
searchResults, err := queries.SearchUsers(ctx, SearchUsersParams{
NameFilter: sql.NullString{String: "John", Valid: true},
EmailFilter: sql.NullString{},
CreatedAfter: sql.NullTime{},
Limit: 10,
Offset: 0,
})
if err != nil {
log.Fatal(err)
}
fmt.Printf("Found %d users\n", len(searchResults))
}設定オプション
queries:
enabled: true
package: "queries"
output: "./db/queries"
sql_files:
- "queries/*.sql"
overrides:
- column: "users.id"
go_type: "int64"
- column: "users.metadata"
go_type: "json.RawMessage"クエリタイプ
:one- 単一の結果を返す:many- 複数の結果を返す:exec- 実行結果のみを返す:execrows- 実行結果と影響を受けた行数を返す:execlastid- 実行結果と最後の挿入IDを返す
Bob のクエリ生成機能により、手書きの SQL を型安全な Go コードに変換し、パフォーマンスと安全性を両立できます。