Drogon's Object-Relational Mapping (ORM) layer provides a type-safe, compile-time-checked interface for database interactions. Unlike traditional ORMs in higher-level languages, Drogon’s ORM is synchronous by design—intentionally decoupled from the framework’s asynchronous I/O model. This separation allows developers to choose between high-throughput async database clients (via DbClient) for latency-sensitive operations, or the convenience and safety of ORM for structured, read-heavy workflows.

Setting Up ORM Models

Before using the ORM, ensure your database schema is reflected in Drogon’s model configuration:

1. Define table structure and column mappings in model.json. Example snippet:

{
  "tables": [
    {
      "name": "admin",
      "class_name": "Admin",
      "columns": [
        { "name": "_id", "type": "int64", "is_primary_key": true, "is_auto_increment": true },
        { "name": "username", "type": "string" },
        { "name": "created_at", "type": "datetime" }
      ]
    }
  ]
}

2. Generate C++ model classes using the CLI tool:

drogon_ctl create model models

Confirm overwrites when prompted. Generated headers (e.g., Admin.h) reside under models/ and expose strongly-typed accessors and static column identifiers like Admin::Cols::_id.

Basic ORM Usage

The following handler demonstrates safe query composition using Mapper<T>:

#include "TestCtrl.h"
#include "../models/Admin.h"
#include <drogon/orm/Mapper.h>
#include <iostream>

using namespace drogon;
using namespace drogon_model::v2;
using namespace drogon::orm;

void TestCtrl::name(const HttpRequestPtr &req,
                    std::function<void(const HttpResponsePtr &)>&& callback) const
{
    try
    {
        auto db = app().getDbClient();
        Mapper<Admin> mapper(db);

        // Count total rows
        size_t total = mapper.count();
        std::cout << "Total admins: " << total << "\n";

        // Paginated fetch: LIMIT 5 OFFSET 5
        auto results = mapper
            .orderBy(Admin::Cols::_id)
            .limit(5)
            .offset(5)
            .findAll();

        std::cout << "Fetched " << results.size() << " records\n";

        Json::Value jsonResp;
        jsonResp["result"] = "success";
        jsonResp["count"] = static_cast<Json::Int>(results.size());
        callback(HttpResponse::newHttpJsonResponse(jsonResp));
    }
    catch (const DrogonDbException &ex)
    {
        std::cerr << "Database error: " << ex.base().what() << "\n";
        auto resp = HttpResponse::newNotFoundResponse();
        callback(resp);
    }
    catch (const std::exception &ex)
    {
        std::cerr << "Unexpected error: " << ex.what() << "\n";
        auto resp = HttpResponse::newInternalServerErrorResponse();
        callback(resp);
    }
}

Known Limitations and Workarounds

• Invalid datetime handling: Values like "0000-00-00 00:00:00" trigger undefined behavior in generated model code. Normalize such fields to NULL or valid ISO timestamps at the database level.
• SQL dialect variance: Drogon emits LIMIT N OFFSET M, not MySQL-style LIMIT M,N. Ensure your target database supports standard SQL pagination syntax.
• Encoding consistency: Enforce UTF-8 across layers by setting "client_encoding": "utf8" in both database.json and model.json.
• Debug visibility: Enable verbose SQL logging via "log_level": "TRACE" in drogon.json.

Server-Side HTML Generation with CSP

Drogon’s CSP (C++ Server Pages) is a compile-time templating system—not a runtime interpreter. Templates are transpiled into efficient C++ code during build, enabling zero-cost abstractions and full IDE support.

To scaffold a new view:

1. Create an empty views/Index.csp file encoded as UTF-8 with out BOM.
2. Run:

drogon_ctl create view Index.csp

This generates both the CSP file and its corresponding HttpView subclass.

Example CSP Template

Below is a minimal yet functional Index.csp that renders two news listings—one via typed model objects, another via raw query results:

<%inc
#include "../models/News.h"
#include <drogon/orm/Result.h>
#include <trantor/utils/Date.h>

using namespace drogon_model::v2;
using namespace drogon::orm;
%>

<html lang="zh-CN">
<%c++
    auto newsList = @@.get<std::vector<News>>("news1");
    auto rawResults = @@.get<Result>("news2");
%>
  <head>
    <meta charset="utf-8">
    <title>News Dashboard</title>
  </head>
  <body>
    <h2>Typed Model Rendering</h2>
    <table>
      <thead><tr><th>ID</th><th>Title</th><th>Created</th></tr></thead>
      <tbody>
        <%c++ for (const auto& item : newsList) { %>
          <tr>
            <td>{% item.getId() %}</td>
            <td><a href="/news_detail?news_id={% item.getId() %}">{% item.getTitle() %}</a></td>
            <td>{% trantor::Date(item.getCreateTime().value()).toDbStringLocal() %}</td>
          </tr>
        <%c++ } %>
      </tbody>
    </table>

    <h2>Raw Query Result Rendering</h2>
    <table>
      <thead><tr><th>ID</th><th>Title</th><th>Time</th></tr></thead>
      <tbody>
        <%c++ for (const auto& row : rawResults) { %>
          <tr>
            <td>{% row["id"].as<std::string>() %}</td>
            <td>{% row["title"].as<std::string>() %}</td>
            <td>{% row["create_time"].as<std::string>() %}</td>
          </tr>
        <%c++ } %>
      </tbody>
    </table>
  </body>
</html>

Note: CSP files must be placced under views/, and duplicate names will cause compilation conflicts since generated source files are written to the build directory. Always verify UTF-8 encoding and avoid BOM.