[Day 24] 自定义 REST QueryDSL 实现动态查询资料库

大多数系统的资料库查询操作比写入多样化且复杂，後端工程师要花比较多的心力撰写查询 API，以下列出常见的几个问题

• 同一张资料表的查询 API 会随着 Client 端类型 (Web或App) 而有所不同，例如 App 端考量效能要求而必须多加查询条件或减少回传资料栏位。如果为每一种查询案例撰写对应的 API，虽然实作简单，但 API 数量会爆增。相反地，如果都写在同一个 API，那麽程序码就容易失控变得复杂
• 如果查询规格有异动，例如想多增加一个栏位作为过滤条件，通常需要前後端配合修改程序码。如果经常发生异动的话，那麽维护 API 及前後端沟通成本会很高。
• 如果很多查询 API 都要求支援 「仅回传指定栏位资料」、「多个查询条件任意组合」、「分页」、「排序」、「仅查询笔数」…等功能，那麽开发时间会拉长，而且最怕的是这些 API 是在不同时间点或由不同工程师开发的，内部实作方式可能会不一致，难以维护。

综观以上问题，後端是否能对於每一张资料表都使用相同的实作方式，只需要写一个查询 API 就可以满足前端 「仅回传指定栏位资料」、「多个查询条件任意组合」、「分页」、「排序」、「仅查询笔数」的查询需求，而且这种实作方式要开发快速，只需要写少许程序码就能立即套用於任一张资料表。

实作目标

查询参数 querystring

• q_fields (string): 回传栏位
  • 可指定多个栏位，以逗号分隔。如果不指定则回传所有栏位。
  • 支援抓取多层资料 (DB Join)
    • q_fields=name,devices => 抓取使用者名称及多个 devices 物件
    • q_fields=name,devices.pushToken => 抓取使用者名称及多个 devices 物件的 pushToken 栏位
• q_filter (string): 查询条件 DSL
  • 以 '[' 字元开始， ']' 字元结束，每个运算式 expression 的格式是 field operator value ，中间以空白字元分隔，多个条件以 and 或 or 连接 (目前还不支援 nested condition)
  • operator 支援
    • = != > >= < <=
    • like
    • in, not_in
    • is_null, is_not_null
  • value 支援 typesafe 验证型态
    • 日期时间字串会自动转换为 LocalDate, LocalDateTime, ZonedDateTime 型态
    • 字串可转换 enum 型态验证是否为有效值
  • 范例: q_filter=[name = james and age >= 18 and enabled = true and role in (admin, member) and createdAt >= 2021-01-01T00:00:00Z]
• q_orderBy (string): 指定排序栏位
  • 可指定多个栏位，以逗号分隔, 例如 name,price- 代表先以 name 栏位 asc 排序，再以 price 栏位 desc 排序 (+ 号代表 asc 预设可省略, - 号代表 desc)
• 仅回传部分笔数
  • OFFSET-LIMIT
    • q_offset (integer)
    • q_limit (integer)
  • Pagination
    • q_pageIndex (integer)
    • q_itemsPerPage (integer)
• q_count (boolean): 仅回传资料笔数 (预设值 false)

查询范例

以查询 Club 子专案的 User 资料表为范例，因为我在 q_fields 有指定 devices，查询资料库时就会 join 另一张 infra_user_device 资料表，一并取得使用者的多个 device 资料

QueryDSL

GET http://localhost:8080/club/users?q_fields=name,devices&q_filter=[birthYear > 1970 and enabled = true and role in (Admin, Member)]&q_orderBy=createdAt-

底层转换为 SQL

SELECT club_user.id, infra_user_device.id, club_user."name", infra_user_device.enabled, infra_user_device.enabled_at, infra_user_device.os_version, infra_user_device.push_token, infra_user_device.source_type, infra_user_device.user_agent, infra_user_device.user_id FROM club_user LEFT JOIN infra_user_device ON club_user.id = infra_user_device.user_id WHERE (club_user.birth_year > 1970) AND (club_user.enabled = true) AND (club_user."role" IN ('Admin', 'Member')) ORDER BY club_user.created_at DESC

我习惯在 Swagger 操作比较清楚，因为有 API 文件说明如何撰写 QueryDSL

查询结果

如何快速为资料库的某张资料表，加上 REST QueryDSL API

1. 使用自定义 dynamicQuery Route Function

继续以上面查询 Club 子专案的 User 资料表为范例，实作上，我们只要使用自定义的 dynamicQuery route function，然後指定 UserDTO 型态，只要 3 行程序码就可以快速为某张资料表，完成一个支援 REST QueryDSL 的 API

authorize(ClubAuth.Admin) {
    dynamicQuery<UserDTO>(ClubOpenApi.FindUsers) { dynamicQuery ->
        call.respond(dynamicQuery.queryDB<UserDTO>())
    }
} 

2. 定义 UserDTO 及 UserDeviceDTO

需要在 UserDTO 的 ResultRowDTOMapper 定义如何 join UserDeviceDTO 的 infra_user_device 资料表，这样转换 QueryDSL 为 SQL 时，才知道要怎麽 join。

@Serializable
data class UserDTO(@JvmField @Serializable(with = UUIDSerializer::class) val id: UUID) : EntityDTO<UUID> {

    var account: String? = null
    var enabled: Boolean? = null
    var role: ClubUserRole? = null

    var name: String? = null
    var gender: Gender? = null

    var birthYear: Int? = null

    var email: String? = null
    var mobile: String? = null
    var lang: Lang? = null

    @Transient
    var password: String? = null

    @Serializable(with = InstantSerializer::class)
    var createdAt: Instant? = null

    var devices: List<UserDeviceDTO>? = null

    override fun getId(): UUID = id

    companion object {
        val mapper: ResultRowDTOMapper<UserDTO> = ResultRowDTOMapper(
            UserDTO::class, ClubUserTable,
            joins = listOf(DynamicDBJoinPart(JoinType.LEFT, UserDeviceTable, ClubUserTable.id, UserDeviceTable.userId))
        )
    }
}

@Serializable
data class UserDeviceDTO(@JvmField @Serializable(with = UUIDSerializer::class) val id: UUID) : EntityDTO<UUID> {

    @Serializable(with = UUIDSerializer::class)
    var userId: UUID? = null
    var sourceType: PrincipalSourceType? = null
    var enabled: Boolean? = null
    var pushToken: String? = null
    var osVersion: String? = null
    var userAgent: String? = null

    @Serializable(with = InstantSerializer::class)
    var enabledAt: Instant? = null

    override fun getId(): UUID = id

    companion object {
        val mapper: ResultRowDTOMapper<UserDeviceDTO> = ResultRowDTOMapper(UserDeviceDTO::class, UserDeviceTable)
    }
}

实作 REST QueryDSL

自定义 DynamicQuery Route Function 简化呼叫程序码

考量需要为很多资料库资料表实作 REST QueryDSL API，所以我自定义 dynamicQuery route function 取代原始的 http get，隐藏实作细节，就可以减少 route 部分需要撰写的程序码

authorize(ClubAuth.Admin) {
    dynamicQuery<UserDTO>(ClubOpenApi.FindUsers) { dynamicQuery ->
        call.respond(dynamicQuery.queryDB<UserDTO>())
    }
} 

@ContextDsl
inline fun <reified RESPONSE : EntityDTO<*>> Route.dynamicQuery(
    operation: OpenApiOperation,
    noinline body: suspend PipelineContext<Unit, ApplicationCall>.(DynamicQuery) -> Unit
): Route {
    operation.bindRoute(
        this, null, HttpMethod.Get,
        typeOf<Unit>(), typeOf<RESPONSE>(), DynamicQueryLocation::class
    )
    return locationGet<DynamicQueryLocation> {
        it.validate()
        body(this, DynamicQuery.from(it))
    }
}

QueryDSL 支援多种输入方式，并统一转换为 DynamicQuery 物件

实作上是使用 Ktor Locations Plugin，先把 querystring 转换为 DynamicQueryLocation 物件，然後再转为 DynamicQuery 物件。因为我不限制只能透过 querystring 指定 QueryDSL，我也想透过 POST requestBody 的 DynamicQueryForm 物件，甚至是只要传入 DSL 字串即可。例如 [Day 28] 实作 Multi-Channel Notifications 文章中的 Ops 子专案实作後台汇出 Excel 报表寄送 Email 范例，就是在 request body 的 query 栏位撰写 QueryDSL

{
  "dataType": "OpsUser",
  "email": "[email protected]",
  "query": "q_fields=account,name&q_filter=[role = AppTeam and enabled = true]&q_orderBy=createdAt"
}

查询 SQL => SELECT ops_user.id, ops_user.account, ops_user."name" FROM ops_user WHERE (ops_user."role" = 'AppTeam') AND (ops_user.enabled = true) ORDER BY ops_user.created_at ASC

@Serializable
class DynamicQuery(
    val fields: List<String>? = null,
    val filter: Predicate? = null,
    val orderByList: List<OrderBy>? = null,
    val offsetLimit: OffsetLimit? = null,
    val count: Boolean? = false,
    private val paramMap: MutableMap<String, String>? = null
) {
    fun from(form: DynamicQueryForm): DynamicQuery {...}

    fun from(request: ApplicationRequest): DynamicQuery{...}

    fun from(text: String): DynamicQuery{...}
}

@io.ktor.locations.Location("")
data class DynamicQueryLocation(
    val q_fields: String? = null,
    val q_filter: String? = null,
    val q_orderBy: String? = null,
    val q_offset: Long? = null,
    val q_limit: Int? = null,
    val q_pageIndex: Long? = null,
    val q_itemsPerPage: Int? = null,
    val q_count: Boolean? = null
) : Location()

class DynamicQueryForm(
    val fields: List<String>? = null,
    val filter: String? = null,
    val orderBy: String? = null,
    val offset: Long? = null,
    val limit: Int? = null,
    val pageIndex: Long? = null,
    val itemsPerPage: Int? = null,
    val count: Boolean?,
    val paramMap: MutableMap<String, String>? = null
) : Form<DynamicQueryForm>()

DynamicQuery 转换为 DynamicDBQuery

虽然我现在底层是查询 RMDB 资料库，但 QueryDSL 的概念也可以套用在其它资料库，说不定以後会需要查询 MongoDB。所以实作上，DynamicQuery 我是放在 infra.base.query package，然後在 infra.database.util package 实作 DynamicDBQuery，透过 kotlin extension function 在 DynamicQuery 类别增加 queryDB() 方法，让两边的程序码完全切开

inline fun <reified T : EntityDTO<*>> DynamicQuery.queryDB(): ResponseDTO {
    return transaction {
        if (offsetLimit != null && offsetLimit.isPaging) {
            val dbCountQuery = toDBCountQuery<T>()
            val total = dbCountQuery.count()
            val items = if (total > 0) {
                val dbQuery = toDBQuery<T>()
                dbQuery.toList<T>()
            } else listOf()
            PagingDataResponseDTO.dtoList(offsetLimit, total, items)
        } else {
            if (count == true) {
                val dbCountQuery = toDBCountQuery<T>()
                val total = dbCountQuery.count()
                DataResponseDTO(JsonObject(mapOf("total" to JsonPrimitive(total))))
            } else {
                val dbQuery = toDBQuery<T>()
                DataResponseDTO(dbQuery.toList<T>())
            }
        }
    }
}

DynamicDBQuery 内部使用 Exposed ORM Query 进行查询

这部分的实作内容过於细节且复杂，所以就不再此解说，有兴趣的读者可以到 Github 看完整程序码

• DynamicQuery
• DynamicDBQuery
• ResultRowMapper

为什麽我不使用 GraphQL ?

GraphQL 的确功能非常强大，可以应付多样复杂的查询需求，但是我没有使用是因为

• 现阶段 side project 的重点在於实作系统架构及基本功能，还没有什麽商业逻辑，所以我自己实作的 REST QueryDSL 已经可以满足我的查询需求，再导入 GraphQL 的效益很低
• 我没有实际使用过 GraphQL 的经验，必须要先花时间研究 Best Practice，否则随着不断加入新的查询需求，後端容易写出 anti-pattern 难以维护或是效能低落的程序码
• 未来 side project 要让别人串接时，要先说服别人学习 GraphQL …XD
• GraphQL 是使用 POST，然而 REST QueryDSL 只要使用 querystring 即可，呼叫 API 比较方便
• 总之我个人比较喜欢 RESTful 风格的 API，所以 GraphQL 就等到我那天有需要用到再说...