konnosDashboard
pg-meta-extensions.ts117 lines · main
1import { z } from 'zod'
2
3import { ident, literal, safeSql, type SafeSqlFragment } from './pg-format'
4import { EXTENSIONS_SQL } from './sql/extensions'
5
6const pgExtensionZod = z.object({
7 name: z.string(),
8 schema: z.string().nullable(),
9 default_version: z.string(),
10 installed_version: z.string().nullable(),
11 comment: z.string(),
12})
13
14const pgExtensionArrayZod = z.array(pgExtensionZod)
15const pgExtensionOptionalZod = z.optional(pgExtensionZod)
16
17export type PGExtension = z.infer<typeof pgExtensionZod>
18
19function list({
20 limit,
21 offset,
22}: {
23 limit?: number
24 offset?: number
25} = {}): {
26 sql: SafeSqlFragment
27 zod: typeof pgExtensionArrayZod
28} {
29 let sql = EXTENSIONS_SQL
30 if (limit) {
31 sql = safeSql`${sql} LIMIT ${literal(limit)}`
32 }
33 if (offset) {
34 sql = safeSql`${sql} OFFSET ${literal(offset)}`
35 }
36 return {
37 sql,
38 zod: pgExtensionArrayZod,
39 }
40}
41
42function retrieve({ name }: { name: string }): {
43 sql: SafeSqlFragment
44 zod: typeof pgExtensionOptionalZod
45} {
46 const sql = safeSql`${EXTENSIONS_SQL} WHERE name = ${literal(name)};`
47 return {
48 sql,
49 zod: pgExtensionOptionalZod,
50 }
51}
52
53type ExtensionCreateParams = {
54 name: string
55 schema?: string
56 version?: string
57 cascade?: boolean
58}
59
60function create({ name, schema, version, cascade = false }: ExtensionCreateParams): {
61 sql: SafeSqlFragment
62} {
63 const sql = safeSql`
64CREATE EXTENSION ${ident(name)}
65 ${schema === undefined ? safeSql`` : safeSql`SCHEMA ${ident(schema)}`}
66 ${version === undefined ? safeSql`` : safeSql`VERSION ${literal(version)}`}
67 ${cascade ? safeSql`CASCADE` : safeSql``};`
68 return { sql }
69}
70
71type ExtensionUpdateParams = {
72 update?: boolean
73 version?: string
74 schema?: string
75}
76
77function update(
78 name: string,
79 { update = false, version, schema }: ExtensionUpdateParams
80): { sql: SafeSqlFragment } {
81 let updateSql = safeSql``
82 if (update) {
83 updateSql = safeSql`ALTER EXTENSION ${ident(name)} UPDATE ${
84 version === undefined ? safeSql`` : safeSql`TO ${literal(version)}`
85 };`
86 }
87 const schemaSql =
88 schema === undefined
89 ? safeSql``
90 : safeSql`ALTER EXTENSION ${ident(name)} SET SCHEMA ${ident(schema)};`
91
92 const sql = safeSql`BEGIN; ${updateSql} ${schemaSql} COMMIT;`
93 return { sql }
94}
95
96type ExtensionRemoveParams = {
97 cascade?: boolean
98}
99
100function remove(
101 name: string,
102 { cascade = false }: ExtensionRemoveParams = {}
103): {
104 sql: SafeSqlFragment
105} {
106 const sql = safeSql`DROP EXTENSION ${ident(name)} ${cascade ? safeSql`CASCADE` : safeSql`RESTRICT`};`
107 return { sql }
108}
109
110export default {
111 list,
112 retrieve,
113 create,
114 update,
115 remove,
116 zod: pgExtensionZod,
117}