/
utils.ts
97 lines (87 loc) · 2.91 KB
/
utils.ts
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
import { pgp, SupabaseDirectClient } from './init'
import { DataFor, Tables, TableName, Column } from 'common/supabase/utils'
export async function getIds<T extends TableName>(
db: SupabaseDirectClient,
table: T
) {
return db.map('select id from $1~', [table], (r) => r.id as string)
}
export async function getAll<T extends TableName>(
db: SupabaseDirectClient,
table: T
) {
return db.map('select data from $1~', [table], (r) => r.data as DataFor<T>)
}
export async function bulkInsert<
T extends TableName,
ColumnValues extends Tables[T]['Insert']
>(db: SupabaseDirectClient, table: T, values: ColumnValues[]) {
if (values.length) {
const columnNames = Object.keys(values[0])
const cs = new pgp.helpers.ColumnSet(columnNames, { table })
const query = pgp.helpers.insert(values, cs)
// Hack to properly cast jsonb values.
const q = query.replace(/::jsonb'/g, "'::jsonb")
await db.none(q)
}
}
export async function bulkUpdate<
T extends TableName,
ColumnValues extends Tables[T]['Update'],
Row extends Tables[T]['Row']
>(
db: SupabaseDirectClient,
table: T,
idFields: (string & keyof Row)[],
values: ColumnValues[]
) {
if (values.length) {
const columnNames = Object.keys(values[0])
const cs = new pgp.helpers.ColumnSet(columnNames, { table })
const clause = idFields.map((f) => `v.${f} = t.${f}`).join(' and ')
const query = pgp.helpers.update(values, cs) + ` WHERE ${clause}`
// Hack to properly cast jsonb values.
const q = query.replace(/::jsonb'/g, "'::jsonb")
await db.none(q)
}
}
export async function bulkUpsert<
T extends TableName,
ColumnValues extends Tables[T]['Insert'],
Col extends Column<T>
>(
db: SupabaseDirectClient,
table: T,
idField: Col | Col[],
values: ColumnValues[],
onConflict?: string
) {
if (!values.length) return
const columnNames = Object.keys(values[0])
const cs = new pgp.helpers.ColumnSet(columnNames, { table })
const baseQuery = pgp.helpers.insert(values, cs)
// Hack to properly cast jsonb values.
const baseQueryReplaced = baseQuery.replace(/::jsonb'/g, "'::jsonb")
const primaryKey = Array.isArray(idField) ? idField.join(', ') : idField
const upsertAssigns = cs.assignColumns({ from: 'excluded', skip: idField })
const query =
`${baseQueryReplaced} on ` +
(onConflict ? onConflict : `conflict(${primaryKey})`) +
' ' +
(upsertAssigns ? `do update set ${upsertAssigns}` : `do nothing`)
await db.none(query)
}
// Replacement for firebase updateDoc. Updates just the data field (what firebase would've replicated to)
export async function updateData<T extends TableName>(
db: SupabaseDirectClient,
table: T,
idField: Column<T>,
data: Partial<DataFor<T>>
) {
const { [idField]: id, ...rest } = data
if (!id) throw new Error(`Missing id field ${idField} in data`)
await db.none(
`update ${table} set data = data || $1 where ${idField} = '${id}'`,
[JSON.stringify(rest)]
)
}