sqlc-grpc

Create a gRPC (and HTTP/JSON) Server from the generated code by the awesome sqlc project. If you’re searching for a SQLC plugin, use sqlc-gen-go-server.

Requirements

• Go 1.23 or superior
• sqlc
• buf

go install github.com/sqlc-dev/sqlc/cmd/sqlc@latest
go install github.com/bufbuild/buf/cmd/buf@latest

Installation

go install github.com/walterwanderley/sqlc-grpc@latest

Example

1. Create a queries.sql file:

--queries.sql

CREATE TABLE authors (
  id   BIGSERIAL PRIMARY KEY,
  name text      NOT NULL,
  bio  text,
  created_at TIMESTAMP
);

-- name: GetAuthor :one
-- http: GET /authors/{id}
SELECT * FROM authors
WHERE id = $1 LIMIT 1;

-- name: ListAuthors :many
-- http: GET /authors
SELECT * FROM authors
ORDER BY name;

-- name: CreateAuthor :one
-- http: POST /authors
INSERT INTO authors (
  name, bio, created_at
) VALUES (
  $1, $2, $3
)
RETURNING *;

-- name: DeleteAuthor :exec
-- http: DELETE /authors/{id}
DELETE FROM authors
WHERE id = $1;

-- name: UpdateAuthorBio :exec
-- http: PATCH /authors/{id}/bio
UPDATE authors
SET bio = $1
WHERE id = $2;

2. Create a sqlc.yaml file

version: "2"
sql:
- schema: "./queries.sql"
  queries: "./queries.sql"
  engine: "postgresql"
  gen:
    go:
      out: "internal/author"

3. Execute sqlc

sqlc generate

4. Execute sqlc-grpc

sqlc-grpc -m "mymodule"

5. Run the generated server

go run . -db [Database Connection URL] -dev

6. Enjoy!

• Swagger UI http://localhost:5000/swagger

SQLite with LiteFS

Use the -litefs command line parameter to replicate SQLite with LiteFS as a library. Example: https://github.com/walterwanderley/sqlc-grpc/tree/main/_examples/authors/sqlite

Customizing HTTP endpoints

You can customize the HTTP endpoints generated by grpc-gateway by adding comments to the queries.

-- http: Method Path :Roles

Here’s an example of a queries file that has a custom HTTP endpoint:

-- name: ListAuthors :many
-- http: GET /authors
SELECT * FROM authors
ORDER BY name;

-- name: UpdateAuthorBio :exec
-- http: PATCH /authors/{id}/bio
UPDATE authors
SET bio = $1
WHERE id = $2;

-- name: CreateAuthor :one
-- http: POST /authors :admin,editor
INSERT INTO authors (
  name, bio, created_at
) VALUES (
  $1, $2, $3
)
RETURNING *;

Editing the generated code

• It's safe to edit any generated code that doesn't have the DO NOT EDIT indication at the very first line.

• After modify a SQL file, execute these commands below:

sqlc generate
go generate

• After modify a *.proto file, execute buf generate.

Customize grpc using sqlc-grpc.yaml

ignore_services:
  - HealthService
  - DebugService

remove_services:
  - DeprecatedUserService

add_services:
  - name: AuthorService
    method: CreateAuthor
    path: /v1/authors
    req_fields:
      - name: name
        type: string
      - name: bio
        type: string
    res_fields:
      - name: id
        type: int64
      - name: created_at
        type: string

remove_fields:
  - "*.bio"         # Remove 'bio' field from all messages
  - "Author.email"  # Remove 'email' field only from Author message

add_fields:
  - msg: CreateAuthorRequest
    fields:
      - name: age
        type: int32
      - name: email
        type: string

roles_filter:
  admin:
    add_fields:
      - msg: Author
        fields:
          - name: is_verified
            type: bool
          - name: internal_notes
            type: string
    remove_fields:
      - "*.debug_info"

  viewer:
    remove_fields:
      - "*.email"
      - "*.phone"

packages:
  authors:
    ignore_services:
      - LegacyAuthorService

    add_fields:
      - msg: Author
        fields:
          - name: country
            type: string

    roles_filter:
      viewer:
        remove_fields:
          - "*.bio"
          - "Author.email"
      editor:
        add_fields:
          - msg: Author
            fields:
              - name: updated_by
                type: string

Similar Projects

• xo-grpc