1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
|
// src/lib/tasks/repository.ts
import db from "@/db/db";
import { tasks, type Task } from "@/db/schema/tasks";
import {
eq,
inArray,
not,
asc,
desc,
and,
ilike,
gte,
lte,
count,
gt,
} from "drizzle-orm";
import { PgTransaction } from "drizzle-orm/pg-core";
export type NewTask = typeof tasks.$inferInsert
/**
* 단건/복수 조회 시 공통으로 사용 가능한 SELECT 함수 예시
* - 트랜잭션(tx)을 받아서 사용하도록 구현
*/
export async function selectTasks(
tx: PgTransaction<any, any, any>,
params: {
where?: any; // drizzle-orm의 조건식 (and, eq...) 등
orderBy?: (ReturnType<typeof asc> | ReturnType<typeof desc>)[];
offset?: number;
limit?: number;
}
) {
const { where, orderBy, offset = 0, limit = 10 } = params;
return tx
.select()
.from(tasks)
.where(where)
.orderBy(...(orderBy ?? []))
.offset(offset)
.limit(limit);
}
/** 총 개수 count */
export async function countTasks(
tx: PgTransaction<any, any, any>,
where?: any
) {
const res = await tx.select({ count: count() }).from(tasks).where(where);
return res[0]?.count ?? 0;
}
/** 단건 Insert 예시 */
export async function insertTask(
tx: PgTransaction<any, any, any>,
data: NewTask // DB와 동일한 insert 가능한 타입
) {
// returning() 사용 시 배열로 돌아오므로 [0]만 리턴
return tx
.insert(tasks)
.values(data)
.returning({ id: tasks.id, createdAt: tasks.createdAt });
}
/** 복수 Insert 예시 */
export async function insertTasks(
tx: PgTransaction<any, any, any>,
data: Task[]
) {
return tx.insert(tasks).values(data).onConflictDoNothing();
}
/** (방금 생성된 Task를 제외한) 가장 오래된 Task 하나 조회 */
export async function selectOldestTaskExcept(
tx: PgTransaction<any, any, any>,
excludeId: string
) {
return tx
.select({ id: tasks.id, createdAt: tasks.createdAt })
.from(tasks)
.where(not(eq(tasks.id, excludeId)))
.orderBy(asc(tasks.createdAt))
.limit(1);
}
/** 단건 삭제 */
export async function deleteTaskById(
tx: PgTransaction<any, any, any>,
taskId: string
) {
return tx.delete(tasks).where(eq(tasks.id, taskId));
}
/** 복수 삭제 */
export async function deleteTasksByIds(
tx: PgTransaction<any, any, any>,
ids: string[]
) {
return tx.delete(tasks).where(inArray(tasks.id, ids));
}
/** 전체 삭제 */
export async function deleteAllTasks(
tx: PgTransaction<any, any, any>,
) {
return tx.delete(tasks);
}
/** 단건 업데이트 */
export async function updateTask(
tx: PgTransaction<any, any, any>,
taskId: string,
data: Partial<Task>
) {
return tx
.update(tasks)
.set(data)
.where(eq(tasks.id, taskId))
.returning({ status: tasks.status, priority: tasks.priority });
}
/** 복수 업데이트 */
export async function updateTasks(
tx: PgTransaction<any, any, any>,
ids: string[],
data: Partial<Task>
) {
return tx
.update(tasks)
.set(data)
.where(inArray(tasks.id, ids))
.returning({ status: tasks.status, priority: tasks.priority });
}
/** status 기준 groupBy */
export async function groupByStatus(
tx: PgTransaction<any, any, any>,
) {
return tx
.select({
status: tasks.status,
count: count(),
})
.from(tasks)
.groupBy(tasks.status)
.having(gt(count(), 0));
}
/** priority 기준 groupBy */
export async function groupByPriority(
tx: PgTransaction<any, any, any>,
) {
return tx
.select({
priority: tasks.priority,
count: count(),
})
.from(tasks)
.groupBy(tasks.priority)
.having(gt(count(), 0));
}
// 모든 task 조회
export const getAllTasks = async (): Promise<Task[]> => {
const users = await db.select().from(tasks).execute();
return users
};
|
