iT邦幫忙

2021 iThome 鐵人賽

DAY 24
0
Modern Web

基於 Kotlin Ktor 建構支援模組化開發的 Web 框架系列 第 24

[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

需要在 UserDTOResultRowDTOMapper 定義如何 join UserDeviceDTOinfra_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": "admin@test.abc.com",
  "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 看完整程式碼

為什麼我不使用 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 就等到我那天有需要用到再說...

上一篇
[Day 23] 自定義 ColumnType, Operator, Expression 擴展 Exposed Query DSL API
下一篇
[Day 25] 實作 Redis Plugin 整合 Redis Coroutine Client
系列文
基於 Kotlin Ktor 建構支援模組化開發的 Web 框架30

尚未有邦友留言

立即登入留言