Zenn
🕌

[sqlc]gRPC API Development Tutorial

2024/07/09に公開
Go
gRPC
sqlc
tech

[sqlc]gRPC API Development Tutorial

In this tutorial, we will walk through the steps to develop a simple gRPC API for a memo application using Go. Each step will be explained with its purpose and rationale, following best practices.

Prerequisites

Ensure that the following tools are installed:

  • Go
  • Protocol Buffers (protoc)
  • sqlc
  • direnv
  • PostgreSQL
  • evans

1. Project Setup

1.1 Create Project Directory

First, create the project directory and navigate into it.

mkdir grpc-memo-api
cd grpc-memo-api

1.2 Initialize Go Module

Initialize the Go module. This helps in managing dependencies.

go mod init grpc-memo-api

1.3 Create Necessary Directories

Create the project directory structure. This helps in organizing the code.

mkdir -p api/proto
mkdir -p internal/server
mkdir -p internal/db/generated
mkdir -p cmd

Directory structure explanation:

  • api/proto: Contains Protocol Buffers definition files
  • internal/server: Contains gRPC server implementation
  • internal/db/generated: Contains code generated from SQL queries
  • cmd: Contains the main entry point

1.4 Create api/proto/memo.proto

Create the Protocol Buffers definition file. This defines the gRPC interface.

syntax = "proto3"; // Specify the version of Protocol Buffers

package memo; // Specify the package name

option go_package = "grpc-memo-api/api/proto;proto"; // Specify the package name for the generated Go code

// Define the MemoService service
service MemoService {
  rpc ListMemos (ListMemosRequest) returns (ListMemosResponse); // RPC to list memos
  rpc GetMemo (GetMemoRequest) returns (GetMemoResponse); // RPC to get a specific memo
  rpc CreateMemo (CreateMemoRequest) returns (CreateMemoResponse); // RPC to create a new memo
}

// Define the ListMemosRequest message
message ListMemosRequest {
  string user_id = 1; // User ID
}

// Define the ListMemosResponse message
message ListMemosResponse {
  repeated Memo memos = 1; // List of memos
}

// Define the GetMemoRequest message
message GetMemoRequest {
  string user_id = 1; // User ID
  string memo_id = 2; // Memo ID
}

// Define the GetMemoResponse message
message GetMemoResponse {
  Memo memo = 1; // Memo
}

// Define the CreateMemoRequest message
message CreateMemoRequest {
  string user_id = 1; // User ID
  string content = 2; // Memo content
}

// Define the CreateMemoResponse message
message CreateMemoResponse {
  Memo memo = 1; // Created memo
}

// Define the Memo message
message Memo {
  string id = 1; // Memo ID
  string user_id = 2; // User ID
  string content = 3; // Memo content
  string created_at = 4; // Creation timestamp
}

1.5 Compile Protocol Buffers

Run the following command to generate Go code. This generates the gRPC server and client code.

protoc --go_out=. --go_opt=paths=source_relative --go-grpc_out=. --go-grpc_opt=paths=source_relative api/proto/memo.proto

This command results in the following folder structure:

.
├── api
│   └── proto
│       ├── memo.proto
│       ├── memo.pb.go
│       └── memo_grpc.pb.go
├── cmd
│   └── main.go
├── go.mod
├── internal
│   ├── db
│   │   ├── db.go
│   │   └── generated
│   └── server
│       └── memo_server.go
└── sqlc.yaml

2. Server Implementation

2.1 Create internal/server/memo_server.go

Implement the gRPC server. This defines the logic to handle client requests.

package server

import (
  "context"
  "grpc-memo-api/api/proto"
  "grpc-memo-api/internal/db/generated"
  "time"

  "github.com/google/uuid"
)

// MemoServer implements the MemoService.
type MemoServer struct {
  proto.UnimplementedMemoServiceServer // Embed to have forward-compatible implementations
  DB *generated.Queries // Field to execute database queries
}

// ListMemos lists memos for the specified user.
func (s *MemoServer) ListMemos(ctx context.Context, req *proto.ListMemosRequest) (*proto.ListMemosResponse, error) {
  // Retrieve memos from the database
  memos, err := s.DB.ListMemos(ctx, req.UserId)
  if err != nil {
    return nil, err
  }

  // Convert to Protocol Buffers format
  var protoMemos []*proto.Memo
  for _, memo := range memos {
    protoMemos = append(protoMemos, &proto.Memo{
      Id:        memo.ID.String(),
      UserId:    memo.UserID,
      Content:   memo.Content,
      CreatedAt: memo.CreatedAt.Format(time.RFC3339),
    })
  }

  // Return the response
  return &proto.ListMemosResponse{Memos: protoMemos}, nil
}

// GetMemo retrieves a memo for the specified user and memo ID.
func (s *MemoServer) GetMemo(ctx context.Context, req *proto.GetMemoRequest) (*proto.GetMemoResponse, error) {
  // Retrieve memo from the database
  memo, err := s.DB.GetMemo(ctx, generated.GetMemoParams{
    UserID: req.UserId,
    ID:     uuid.MustParse(req.MemoId),
  })
  if err != nil {
    return nil, err
  }

  // Return the response
  return &proto.GetMemoResponse{Memo: &proto.Memo{
    Id:        memo.ID.String(),
    UserId:    memo.UserID,
    Content:   memo.Content,
    CreatedAt: memo.CreatedAt.Format(time.RFC3339),
  }}, nil
}

// CreateMemo creates a new memo.
func (s *MemoServer) CreateMemo(ctx context.Context, req *proto.CreateMemoRequest) (*proto.CreateMemoResponse, error) {
  // Generate new memo ID and creation timestamp
  memoID := uuid.New()
  createdAt := time.Now()

  // Insert memo into the database
  params := generated.CreateMemoParams{
    ID:        memoID,
    UserID:    req.UserId,
    Content:   req.Content,
    CreatedAt: createdAt,
  }

  err := s.DB.CreateMemo(ctx, params)
  if err != nil {
    return nil, err
  }

  // Return the response
  return &proto.CreateMemoResponse{Memo: &proto.Memo{
    Id:        memoID.String(),
    UserId:    req.UserId,
    Content:   req.Content,
    CreatedAt: createdAt.Format(time.RFC3339),
  }}, nil
}

3. Database Access Implementation

3.1 Create internal/db/db.go

Create a function to initialize the database connection. This establishes the connection to the database.

package db

import (
  "database/sql"
  _ "github.com/lib/pq" // Import PostgreSQL driver
)

// NewDB initializes the database connection.
func NewDB(dataSourceName string) (*sql.DB, error) {
  // Open the database connection
  conn, err := sql.Open("postgres", dataSourceName)
  if err != nil {
    return nil, err
  }
  return conn, nil
}

4. Main Function Implementation

4.1 Create cmd/main.go

Implement the main function. This starts the gRPC server and listens for client requests.

package main

import (
  "os"
  "log"
  "net"
  "grpc-memo-api/api/proto"
  "grpc-memo-api/internal/db"
  "grpc-memo-api/internal/db/generated"
  "grpc-memo-api/internal/server"
  "google.golang.org/grpc"
  "google.golang.org/grpc/reflection"
  "github.com/joho/godotenv"
)

func main() {
  // Load .env file
  err := godotenv.Load()
  if err != nil {
    log.Fatalf("Error loading .env file")
  }

  // Get database URL from environment variables
  databaseURL := os.Getenv("DATABASE_URL")
  if databaseURL == "" {
    log.Fatalf("DATABASE_URL is not set in the environment")
  }

  // Initialize database connection
  conn, err := db.NewDB(databaseURL)
  if err != nil {
    log.Fatalf("failed to connect to database: %v", err)
  }
  queries := generated.New(conn)

  // Initialize gRPC server
  grpcServer := grpc.NewServer()
  memoServer := &server.MemoServer{DB: queries}
  proto.RegisterMemoServiceServer(grpcServer, memoServer)
  reflection.Register(grpcServer)

  // Initialize TCP listener
  lis, err := net.Listen("tcp", ":50051")
  if err != nil {
    log.Fatalf("failed to listen: %v", err)
  }

  log.Println("gRPC server is running on port 50051")
  // Start gRPC server
  if err := grpcServer.Serve(lis); err != nil {
    log.Fatalf("failed to serve: %v", err)
  }
}

5. sqlc Configuration and Execution

5.1 Create sqlc.yaml

Create the sqlc configuration file. This generates Go code from SQL queries.

version: "2"
sql:
  - schema: "schema.sql" # Path to the schema file
    queries: "queries.sql" # Path to the query file
    engine: "postgresql" # Database engine to use
    gen:
      go:
        package: "generated" # Package name for the generated Go code
        out: "internal/db/generated" # Output directory for the generated Go code

5.2 Create schema.sql

Define the database schema. This creates the database tables.

CREATE TABLE memos (
  id UUID PRIMARY KEY, // Memo ID
  user_id TEXT NOT NULL, // User ID
  content TEXT NOT NULL, // Memo content
  created_at TIMESTAMP NOT NULL // Creation timestamp
);

5.3 Create queries.sql

Define the SQL queries. This simplifies database operations.

-- name: ListMemos :many
SELECT id, user_id, content, created_at FROM memos WHERE user_id = $1;

-- name: GetMemo :one
SELECT id, user_id, content, created_at FROM memos WHERE user_id = $1 AND id = $2;

-- name: CreateMemo :exec
INSERT INTO memos (id, user_id, content, created_at) VALUES ($1, $2, $3, $4);

5.4 Run sqlc

Run the following command to generate Go code from the SQL queries.

sqlc generate

This command generates the following files:

internal/db/generated/
├── db.go
├── models.go
├── querier.go
├── queries.sql.go
  • db.go: Contains code for initializing the database connection and managing transactions.
  • models.go: Defines Go structs corresponding to the database tables.
  • querier.go: Defines the query interface.
  • queries.sql.go: Contains Go functions corresponding to the SQL queries defined in queries.sql.

These files are used to simplify database access. Specifically, they are used in internal/server/memo_server.go to perform database operations.

6. Database Setup

6.1 Install PostgreSQL

Install PostgreSQL and start the service.

brew install postgresql
brew services start postgresql@14

6.2 Create Database

Create the database.

createdb memo

6.3 Create Database User and Set Password

Enter the PostgreSQL prompt and create a user and set a password.

psql -d memo

Once in the PostgreSQL prompt, run the following commands:

CREATE USER myuser WITH PASSWORD 'mypassword';
GRANT ALL PRIVILEGES ON DATABASE memo TO myuser;
ALTER DATABASE memo SET timezone TO 'UTC';

6.4 Execute schema.sql

Run the schema file to create the tables.

psql -d memo -U myuser -f schema.sql

7. API Verification

7.1 Start the Server

Start the gRPC server.

go run cmd/main.go

7.2 Install and Use evans

Install evans and connect to the gRPC server in REPL mode.

brew install evans
evans --host localhost --port 50051 -r repl

7.3 Connect to gRPC Server Using evans

Run the following command to start evans in REPL mode and connect to the gRPC server.

evans --host localhost --port 50051 -r repl

7.4 Select Package

In the evans prompt, run the package command to select the package.

package memo

7.5 Select Service

In the evans prompt, run the show service command to display available services.

show service

Select the MemoService from the displayed services.

service MemoService

7.6 Select Method and Send Request

MemoService has the following methods:

  • ListMemos
  • GetMemo
  • CreateMemo

Select each method and send a request.

CreateMemo

Select the CreateMemo method and send a request.

call CreateMemo

When prompted, enter the request data as follows:

{
  "user_id": "b7e72808-d71a-4502-a289-35cbafd1352c",
  "content": "test"
}

GetMemo

Select the GetMemo method and send a request.

call GetMemo

When prompted, enter the request data as follows:

{
  "user_id": "b7e72808-d71a-4502-a289-35cbafd1352c",
  "memo_id": "fdf4fd15-3056-4853-a6ce-ef113db86f5c"
}

ListMemos

Select the ListMemos method and send a request.

call ListMemos

When prompted, enter the request data as follows:

{
  "user_id": "b7e72808-d71a-4502-a289-35cbafd1352c"
}

7.7 Verify Results

After sending requests for each method, the server's response will be displayed. This confirms that the gRPC server is functioning correctly.

Summary

In this tutorial, we walked through the steps to develop a simple gRPC API for a memo application using Go. We covered the following steps to build a gRPC server and integrate it with a database:

  1. Project setup
  2. gRPC server implementation
  3. Database access implementation
  4. Main function implementation
  5. sqlc configuration and execution
  6. Database setup
  7. API verification

By following best practices and clearly explaining the purpose and rationale for each step, we can efficiently and effectively develop a gRPC API. This provides a solid foundation for building scalable and maintainable applications.

Final Directory Structure

.
├── api
│   └── proto
│       ├── memo.proto          # Protocol Buffers definition file
│       ├── memo.pb.go          # Generated Go code
│       └── memo_grpc.pb.go     # Generated gRPC server and client code
├── cmd
│   └── main.go                 # Main entry point
├── go.mod                      # Go module configuration file
├── internal
│   ├── db
│   │   ├── db.go               # Database connection initialization
│   │   └── generated           # Code generated from SQL queries
│   │       ├── db.go
│   │       ├── models.go
│   │       ├── querier.go
│   │       └── queries.sql.go
│   └── server
│       └── memo_server.go      # gRPC server implementation
└── sqlc.yaml                   # sqlc configuration file

Following this directory structure helps in organizing the code and makes the project easier to maintain.

Discussion