sqlh - SQL HTTP Handlers

The sqlh package exposes database entities as REST API endpoints. Built on top of sqlr and sqlc, it provides automatic CRUD handler generation, a transformer pattern for input/output mapping, and transaction middleware for wrapping HTTP requests in database transactions.

Getting Started

Add the dependency to your Go module:

go get github.com/gosoline-project/sqlh@v0.2.1

Then import the package in your Go code:

import "github.com/gosoline-project/sqlh"

Configuration

The sqlh package requires both an HTTP server and a database client. Configure them under the httpserver and sqlc keys respectively:

config.dist.yml
httpserver:
  default:
    port: 8080
    mode: release

sqlc:
  default:
    driver: mysql
    uri:
      host: 127.0.0.1
      port: 3306
      user: root
      password: gosoline
      database: blog
    migrations:
      enabled: true
      reset: true
      path: migrations

The HTTP server configuration is described in the httpserver documentation. The database configuration follows the same format described in the sqlc documentation.

CRUD Handlers

The WithCrudHandlers function generates a complete set of REST endpoints for an entity. It connects an sqlr repository with a transformer to handle input/output mapping.

Defining Entities

Entities use the same sqlr.Entity base struct described in the sqlr documentation:

main.go
type Author struct {
  sqlr.Entity[int64]
  Name  string `db:"name"`
  Email string `db:"email"`
}

Input Types

Define separate types for create and update input. These decouple the HTTP API from the database entity:

main.go
type AuthorCreateInput struct {
  Name  string `json:"name" binding:"required"`
  Email string `json:"email" binding:"required"`
}

type AuthorUpdateInput struct {
  Name string `json:"name" binding:"required"`
}

Using separate input types lets you:

Validate input with binding tags (e.g., binding:"required")
Exclude internal fields (like Id or CreatedAt) from create/update payloads

Implementing the Transformer

The Transformer[K, E, IC, IU] interface converts between input types and entity types, and controls how responses are rendered. Implement TransformCreateInput, TransformUpdateInput, RenderEntityResponse, and RenderQueryResponse:

main.go
type AuthorTransformer struct{}

func (t *AuthorTransformer) TransformCreateInput(_ context.Context, input *AuthorCreateInput) (*Author, error) {
  return &Author{
    Name:  input.Name,
    Email: input.Email,
  }, nil
}

func (t *AuthorTransformer) TransformUpdateInput(_ context.Context, entity *Author, input *AuthorUpdateInput) (*Author, error) {
  entity.Name = input.Name

  return entity, nil
}

func (t *AuthorTransformer) RenderEntityResponse(_ context.Context, entity *Author) (httpserver.Response, error) {
  return httpserver.NewJsonResponse(entity), nil
}

func (t *AuthorTransformer) RenderQueryResponse(_ context.Context, entities []Author) (httpserver.Response, error) {
  return httpserver.NewJsonResponse(entities), nil
}

The type parameters are:

Parameter	Description
`K`	Primary key type (e.g., `int64`)
`E`	Entity type (e.g., `Author`)
`IC`	Create input type (e.g., `AuthorCreateInput`)
`IU`	Update input type (e.g., `AuthorUpdateInput`)

The interface requires four methods:

Method	Description
`TransformCreateInput`	Converts a create input DTO into a new entity
`TransformUpdateInput`	Merges an update input DTO into an existing entity
`RenderEntityResponse`	Serialises a single entity into an `httpserver.Response`
`RenderQueryResponse`	Serialises a slice of entities into an `httpserver.Response`

In the simple case above, RenderEntityResponse and RenderQueryResponse return the entity directly as JSON. See Customizing Output Transformers for approaches when you need a separate output shape.

Registering CRUD Handlers

Use WithCrudHandlers to generate all endpoints and register them with the HTTP server:

main.go
func(ctx context.Context, config cfg.Config, logger log.Logger, router *httpserver.Router) error {
  router.HandleWith(sqlh.WithCrudHandlers(1, "author", sqlh.SimpleTransformer(&AuthorTransformer{})))

  return nil
},

The arguments are:

Argument	Description
`version`	API version number, used in the URL path (e.g., `1` produces `/v1/...`)
`entityName`	Singular entity name for the URL path (e.g., `"author"`)
`transformerFactory`	Factory that creates the transformer instance

Generated Endpoints

WithCrudHandlers registers five endpoints:

Method	Path	Handler	Description
`POST`	`/v{n}/{entity}`	`HandleCreate`	Creates an entity from `IC` input
`GET`	`/v{n}/{entity}/:id`	`HandleRead`	Reads a single entity by ID
`PUT`	`/v{n}/{entity}/:id`	`HandleUpdate`	Updates an entity from `IU` input
`DELETE`	`/v{n}/{entity}/:id`	`HandleDelete`	Deletes an entity by ID; returns `204 No Content`
`POST`	`/v{n}/{entities}`	`HandleQuery`	Queries entities with a JSON filter

The query endpoint uses the plural form of the entity name (e.g., "author" becomes "/v1/authors"). Pluralization is handled automatically.

Query with JSON Filter

The query endpoint accepts a JSON body with a filter field that maps to sqlc.JsonFilter:

{
  "filter": {
    "column": "name",
    "operator": "=",
    "value": "Alice"
  }
}

The filter is converted to an sqlc.Expression and applied as a WHERE condition on the query. See the sqlc JSON filter documentation for the full filter syntax.

Wiring into the Application

main.go
func main() {
  application.New(
    application.WithConfigBytes(config, "yml"),
    application.WithLoggerHandlersFromConfig,
    application.WithModuleFactory("http", httpserver.NewServer(
      "default",
      func(ctx context.Context, config cfg.Config, logger log.Logger, router *httpserver.Router) error {
        router.HandleWith(sqlh.WithCrudHandlers(1, "author", sqlh.SimpleTransformer(&AuthorTransformer{})))

        return nil
      },
    )),
  ).Run()
}

Customizing Output Transformers

The Transformer interface is the primary extension point for controlling how entities are serialised in API responses. Because it is a plain Go interface, a single implementation can be written once and reused across multiple entities — or shared as an internal library across services. This makes it straightforward to enforce consistent response shapes, pagination envelopes, or field-level access control in one place.

JsonResultsTransformer

JsonResultsTransformer[K, E, IC, IU] is a simplified variant of Transformer for the common case where you want to map entities to a dedicated output type and return them as JSON, without needing to construct httpserver.Response values manually. Implement three methods — TransformCreateInput, TransformUpdateInput, and a single TransformOutput that converts one entity to any JSON-serialisable value — and pass the implementation to NewJSONResultsTransformer. The wrapping of single and multi-entity responses into JSON is handled automatically:

user_crud.go
type (
  UserCreateInput struct {
    Name string `json:"name"`
  }
  UserUpdateInput struct {
    Name string `json:"name"`
  }
  User struct {
    sqlr.Entity[int]
    Name string
  }
  UserOutput struct {
    Id        int       `json:"id"`
    Name      string    `json:"name"`
    CreatedAt time.Time `json:"created_at"`
    UpdatedAt time.Time `json:"updated_at"`
  }
)

user_crud.go
type UserTransformer struct{}

func (t *UserTransformer) TransformCreateInput(ctx context.Context, input *UserCreateInput) (*User, error) {
  return &User{
    Name: input.Name,
  }, nil
}

func (t *UserTransformer) TransformUpdateInput(ctx context.Context, user *User, input *UserUpdateInput) (*User, error) {
  user.Name = input.Name

  return user, nil
}

func (t *UserTransformer) TransformOutput(ctx context.Context, user *User) (any, error) {
  return UserOutput{
    Id:        user.Id,
    Name:      user.Name,
    CreatedAt: user.CreatedAt,
    UpdatedAt: user.UpdatedAt,
  }, nil
}

user_crud.go
func NewUserCrud() httpserver.RegisterFactoryFunc {
  return sqlh.WithCrudHandlers(0, "user", sqlh.NewJsonResultsTransformer(&UserTransformer{}))
}

Because TransformOutput receives a single entity, the same function is used for both single-entity and list responses — there is no duplication. Use the full Transformer interface directly when you need control over HTTP status codes, headers, or non-JSON response bodies.

Wiring Transformers

Use sqlh.SimpleTransformer() to wrap an already-constructed transformer into a factory when it has no startup dependencies:

sqlh.SimpleTransformer[K, E, IC, IU](&MyTransformer{})

For transformers that require configuration or other dependencies at startup, implement TransformerFactory directly:

type TransformerFactory[K, E, IC, IU] func(ctx context.Context, config cfg.Config, logger log.Logger) (Transformer[K, E, IC, IU], error)

This follows the standard gosoline factory pattern and gives the transformer access to the application config and logger during initialisation.

Transaction Middleware

The WithTx function wraps a group of HTTP routes in a database transaction. Each request automatically begins a transaction before the handler runs, commits on success, and rolls back if any error occurs.

Setting Up WithTx

Create a handler struct with a factory function, then use WithTx to register routes:

main.go
type PostHandler struct {
}

func NewPostHandler() httpserver.HandlerFactory[PostHandler] {
  return func(ctx context.Context, config cfg.Config, logger log.Logger) (*PostHandler, error) {
    return &PostHandler{}, nil
  }
}

main.go
func(ctx context.Context, config cfg.Config, logger log.Logger, router *httpserver.Router) error {
  router.HandleWith(sqlh.WithTx(NewPostHandler(), func(router *httpserver.Router, handler *PostHandler) {
    router.POST("/v1/authors/:id/posts", sqlh.BindTx(handler.HandleCreatePost))
    router.GET("/v1/posts/:id", sqlh.BindTxN(handler.HandleReadPost))
  }))

  return nil
},

Notice that the handler struct does not create or hold a repository. Instead of manually setting up database access, each handler receives an active sqlc.Tx directly through the Bind* helpers — the transaction is started automatically before the handler runs and committed or rolled back afterward.

WithTx takes two arguments:

Argument	Description
`handlerFactory`	Factory that creates the handler struct (with access to `config` and `logger`)
`register`	Function that registers routes on the router, receiving the handler instance

The middleware:

Begins a transaction via sqlClient.BeginTx() before each request
Stores the transaction in the gin context
Calls the next handler
Commits if no errors occurred
Rolls back if any handler in the chain added an error to the gin context

Transaction Binding Helpers

Inside a WithTx-wrapped route group, use the BindTx family of functions to extract the transaction and bind request input:

BindTx — With Input

Use BindTx when the handler needs both the transaction and a parsed request body:

main.go
func (h *PostHandler) HandleCreatePost(cttx sqlc.Tx, input *PostCreateInput) (httpserver.Response, error) {
  // The transaction is automatically managed — commit on success, rollback on error.
  // Use cttx.Q() to execute queries within the transaction scope.

  post := &Post{
    Title:  input.Title,
    Body:   input.Body,
    Status: "draft",
  }

  _, err := cttx.Q().Into("posts").Records(post).Exec(cttx)
  if err != nil {
    return nil, fmt.Errorf("failed to create post: %w", err)
  }

  return httpserver.NewJsonResponse(post), nil
}

BindTx automatically:

Binds the request body to the input type I
Extracts the sqlc.Tx from the gin context
Calls the handler with both
Writes the response

BindTxN — No Input

Use BindTxN when the handler only needs the transaction (no request body):

main.go
func (h *PostHandler) HandleReadPost(cttx sqlc.Tx) (httpserver.Response, error) {
  // BindTxN is used when no request body input is needed.
  // The transaction is still available for database operations.

  var posts []Post
  err := cttx.Q().From("posts").Where(sqlc.Col("status").Eq("draft")).Select(cttx, &posts)
  if err != nil {
    return nil, fmt.Errorf("failed to query posts: %w", err)
  }

  return httpserver.NewJsonResponse(posts), nil
}

Since sqlc.Tx implements Querier, you can pass it to WithClient() on query builders to execute queries within the transaction.

BindTxR / BindTxNR — With Raw Request

For handlers that need access to the raw *http.Request (e.g., to read headers or query parameters), use the R variants:

// With input + raw request
sqlh.BindTxR(func(cttx sqlc.Tx, req *http.Request, input *MyInput) (httpserver.Response, error) {
    userAgent := req.Header.Get("User-Agent")
    // ...
})

// No input + raw request
sqlh.BindTxNR(func(cttx sqlc.Tx, req *http.Request) (httpserver.Response, error) {
    // ...
})

Wiring into the Application

main.go
func main() {
  application.New(
    application.WithConfigBytes(config, "yml"),
    application.WithLoggerHandlersFromConfig,
    application.WithModuleFactory("http", httpserver.NewServer(
      "default",
      func(ctx context.Context, config cfg.Config, logger log.Logger, router *httpserver.Router) error {
        router.HandleWith(sqlh.WithTx(NewPostHandler(), func(router *httpserver.Router, handler *PostHandler) {
          router.POST("/v1/authors/:id/posts", sqlh.BindTx(handler.HandleCreatePost))
          router.GET("/v1/posts/:id", sqlh.BindTxN(handler.HandleReadPost))
        }))

        return nil
      },
    )),
  ).Run()
}

Summary of Binding Functions

Function	Input	Raw Request	Signature
`BindTx`	Yes	No	`func(cttx sqlc.Tx, input *I) (Response, error)`
`BindTxR`	Yes	Yes	`func(cttx sqlc.Tx, req http.Request, input I) (Response, error)`
`BindTxN`	No	No	`func(cttx sqlc.Tx) (Response, error)`
`BindTxNR`	No	Yes	`func(cttx sqlc.Tx, req *http.Request) (Response, error)`

Getting Started​

Configuration​

CRUD Handlers​

Defining Entities​

Input Types​

Implementing the Transformer​

Registering CRUD Handlers​

Generated Endpoints​

Query with JSON Filter​

Wiring into the Application​

Customizing Output Transformers​

JsonResultsTransformer​

Wiring Transformers​

Transaction Middleware​

Setting Up WithTx​

Transaction Binding Helpers​

BindTx — With Input​

BindTxN — No Input​

BindTxR / BindTxNR — With Raw Request​

Wiring into the Application​

Summary of Binding Functions​

Getting Started

Configuration

CRUD Handlers

Defining Entities

Input Types

Implementing the Transformer

Registering CRUD Handlers

Generated Endpoints

Query with JSON Filter

Wiring into the Application

Customizing Output Transformers

JsonResultsTransformer

Wiring Transformers

Transaction Middleware

Setting Up WithTx

Transaction Binding Helpers

BindTx — With Input

BindTxN — No Input

BindTxR / BindTxNR — With Raw Request

Wiring into the Application

Summary of Binding Functions