大多數系統的資料庫查詢操作比寫入多樣化且複雜，後端工程師要花比較多的心力撰寫查詢 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 支援
= != > >= < <=
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)
以查詢 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": "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 看完整程式碼
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 就等到我那天有需要用到再說...
