Three-tier rule

The rule

Every request flows through exactly three layers before it reaches the database:

Handler  →  Service  →  Query  →  SQLite

A Handler calls a Service. It never calls a Query.
A Service calls s.querier.* (or s.cache.* for cached reads). It never opens a DB connection or runs raw SQL.
A Query is the only layer that executes SQL. It is generated by sqlc — never hand-written.

No layer may skip the one below it. A Handler that calls s.querier.* directly is a bug, even if it “works”.

What each layer may and may not do

Layer	May	May not
Handler	Parse request, call Service, return response	Call Query, access DB, contain business logic
Service	Business logic, call `s.querier.` or `s.cache.`	`sql.Open`, `db.Exec`, raw SQL
Models	Domain struct (entity + DTO), used cross-layer as a boundary type	Business logic, DB access, import `app/queries`
Cache	In-memory session cache (`sync.RWMutex` + map), called via Service	Direct access from a Handler
Queries	The only layer that executes SQL	—

Why it exists

The rule is what keeps the codebase small and legible. When every SQL statement lives in queries/*.sql and every business decision lives in a Service:

SQL is auditable. grep the queries directory and you have the complete data access surface.
Services are testable. A Service takes a *queries.Querier backed by an in-memory SQLite DB — no mocks, real SQL, fast tests.
Handlers stay thin. A Handler that grows past “parse, call service, respond” is a smell that logic leaked down.

Worked example: update profile

The PUT /app/profile route touches all three layers. Trace it from the route down.

Route (routes/web.go) — just wiring:

protected.Put("/profile", appHandler.UpdateProfile)

Handler (app/handlers/app.go) — parse, call service, respond. No SQL, no business logic:

func (h *AppHandler) UpdateProfile(c *fiber.Ctx) error {
    userID := c.Locals("user_id")
    if userID == nil {
        return c.Status(fiber.StatusUnauthorized).JSON(fiber.Map{"error": "Not authenticated"})
    }

    var req models.UpdateProfileRequest
    if err := c.BodyParser(&req); err != nil {
        return c.Status(fiber.StatusBadRequest).JSON(fiber.Map{"error": "Invalid request body"})
    }

    user, err := h.userService.UpdateProfile(userID.(int64), req)
    if err != nil {
        return c.Status(fiber.StatusInternalServerError).JSON(fiber.Map{"error": "Failed to update profile"})
    }

    // Sync session with updated values — session is a Handler concern
    sess, _ := h.store.Get(c)
    if req.Name != "" { sess.Set("name", user.Name) }
    if req.Avatar != "" { sess.Set("avatar", user.Avatar) }
    sess.Save()

    return h.inertiaService.Render(c, "app/Profile", fiber.Map{"user": user, "success": "Profile updated successfully"})
}

Service (app/services/user.go) — business logic, reads and writes through the querier only:

func (s *UserService) UpdateProfile(userID int64, req models.UpdateProfileRequest) (*models.UserResponse, error) {
    user, err := s.querier.GetUserByID(context.Background(), userID)
    if err != nil {
        return nil, err
    }

    if req.Name != "" { user.Name = req.Name }
    if req.Avatar != "" { user.Avatar = req.Avatar }

    if err := s.querier.UpdateUser(context.Background(), user); err != nil {
        return nil, err
    }

    response := user.ToResponse()
    return &response, nil
}

Query (app/queries/user.sql.go) — generated by sqlc from queries/user.sql. The only place db.Exec / db.QueryRow appear.

The DTO models.UpdateProfileRequest is the boundary type the Handler and Service share — it lives in app/models/ and knows nothing about either layer.

The test exception

This keeps tests fast and honest: they seed real rows through the generated querier, exercise the Service under test, and assert against real rows — all against an in-memory SQLite database. No mocking framework, no test doubles.

// app/services/auth_test.go — tests call querier directly for setup/assertion
func TestLogin(t *testing.T) {
    querier := queries.NewQuerier(setupTestDB(t))
    // seed a user directly through the querier — allowed in tests
    // ...
    svc := services.NewAuthService(querier, services.AuthServiceConfig{ /* ... */ })
    // exercise the Service — the layer under test
}

Outside *_test.go, the rule is absolute.