Christmas cookie ranking site
eldridge.cam
1import pg, { type PoolClient, type QueryResult, type QueryResultRow } from "pg";
2import sql, { type SQLQuery } from "pg-sql2";
3
4export class Database {
5 static async connect(pool: pg.Pool) {
6 return new Database(await pool.connect());
7 }
8
9 client: PoolClient;
10
11 constructor(client: PoolClient) {
12 this.client = client;
13 }
14
15 query(query: SQLQuery): Promise<QueryResult> {
16 const { text, values } = sql.compile(query);
17 return this.client.query(text, values);
18 }
19
20 async one<T extends QueryResultRow>(query: SQLQuery): Promise<T | null> {
21 const result = await this.query(query);
22 if (result.rowCount === null) {
23 throw new TypeError("Query does not return rows.");
24 }
25 if (result.rowCount > 1) {
26 throw new TypeError("Query returned more than one row.");
27 }
28 return (result.rows[0] ?? null) as T | null;
29 }
30
31 async many<T extends QueryResultRow>(query: SQLQuery): Promise<T[]> {
32 const result = await this.query(query);
33 return result.rows as T[];
34 }
35
36 async transaction<T>(cb: () => Promise<T>): Promise<T> {
37 await this.client.query("BEGIN");
38 try {
39 const result = await cb();
40 await this.client.query("COMMIT");
41 return result;
42 } catch (err) {
43 await this.client.query("ROLLBACK");
44 throw err;
45 }
46 }
47
48 release() {
49 this.client.release();
50 }
51}
52
53export interface Cookie {
54 id: string;
55 name: string;
56 description: string;
57 year: number;
58 image_url: string | null;
59 ordering: number;
60}
61
62export interface Account {
63 id: string;
64 created_at: Date;
65}
66
67export interface Session {
68 id: string;
69 account_id: string;
70 created_at: Date;
71 expires_at: Date;
72}
73
74export interface Review {
75 id: number;
76 account_id: string;
77 cookie_id: string;
78 year: number;
79 comment: string;
80 created_at: Date;
81}
82
83export interface Ranking {
84 id: number;
85 account_id: string;
86 cookie_id: string;
87 year: number;
88 ranking: number;
89 created_at: Date;
90}
91
92export interface Comment {
93 id: number;
94 account_id: string;
95 review_id: string;
96 comment: string;
97 created_at: Date;
98}