konnosDashboard
DatabaseSettings.utils.ts388 lines · main
1type ConnectionStrings = {
2 psql: string
3 uri: string
4 golang: string
5 jdbc: string
6 dotnet: string
7 nodejs: string
8 php: string
9 python: string
10 sqlalchemy: string
11}
12
13export const getConnectionStrings = ({
14 connectionInfo,
15 poolingInfo,
16 metadata,
17}: {
18 connectionInfo: {
19 db_user: string
20 db_port: number
21 db_host: string
22 db_name: string
23 }
24 poolingInfo?: {
25 connectionString: string
26 db_user: string
27 db_port: number
28 db_host: string
29 db_name: string
30 }
31 metadata: {
32 projectRef?: string
33 pgVersion?: string
34 }
35}): {
36 direct: ConnectionStrings
37 pooler: ConnectionStrings
38} => {
39 const isMd5 = poolingInfo?.connectionString.includes('options=reference')
40 const { projectRef } = metadata
41 const password = '[YOUR-PASSWORD]'
42
43 // Direct connection variables
44 const directUser = connectionInfo.db_user
45 const directPort = connectionInfo.db_port
46 const directHost = connectionInfo.db_host
47 const directName = connectionInfo.db_name
48
49 // Pooler connection variables
50 const poolerUser = poolingInfo?.db_user
51 const poolerPort = poolingInfo?.db_port
52 const poolerHost = poolingInfo?.db_host
53 const poolerName = poolingInfo?.db_name
54
55 // Direct connection strings
56 const directPsqlString = isMd5
57 ? `psql "postgresql://${directUser}:${password}@${directHost}:${directPort}/${directName}"`
58 : `psql -h ${directHost} -p ${directPort} -d ${directName} -U ${directUser}`
59
60 const directUriString = `postgresql://${directUser}:${password}@${directHost}:${directPort}/${directName}`
61
62 const directGolangString = `DATABASE_URL=${directUriString}`
63
64 const directJdbcString = `jdbc:postgresql://${directHost}:${directPort}/${directName}?user=${directUser}&password=${password}`
65
66 // User Id=${directUser};Password=${password};Server=${directHost};Port=${directPort};Database=${directName}`
67 const directDotNetString = `{
68 "ConnectionStrings": {
69 "DefaultConnection": "Host=${directHost};Database=${directName};Username=${directUser};Password=${password};SSL Mode=Require;Trust Server Certificate=true"
70 }
71}`
72
73 // `User Id=${poolerUser};Password=${password};Server=${poolerHost};Port=${poolerPort};Database=${poolerName}${isMd5 ? `;Options='reference=${projectRef}'` : ''}`
74 const poolerDotNetString = `{
75 "ConnectionStrings": {
76 "DefaultConnection": "User Id=${poolerUser};Password=${password};Server=${poolerHost};Port=${poolerPort};Database=${poolerName}${isMd5 ? `;Options='reference=${projectRef}'` : ''}"
77 }
78}`
79
80 const directNodejsString = `DATABASE_URL=${directUriString}`
81
82 // Pooler connection strings
83 const poolerPsqlString = isMd5
84 ? `psql "postgresql://${poolerUser}:${password}@${poolerHost}:${poolerPort}/${poolerName}?options=reference%3D${projectRef}"`
85 : `psql -h ${poolerHost} -p ${poolerPort} -d ${poolerName} -U ${poolerUser}`
86
87 const poolerUriString = poolingInfo?.connectionString ?? ''
88
89 const nodejsPoolerUriString = `DATABASE_URL=${poolingInfo?.connectionString}`
90
91 const poolerGolangString = `user=${poolerUser}
92password=${password}
93host=${poolerHost}
94port=${poolerPort}
95dbname=${poolerName}${isMd5 ? `options=reference=${projectRef}` : ''}`
96
97 const poolerJdbcString = `jdbc:postgresql://${poolerHost}:${poolerPort}/${poolerName}?user=${poolerUser}${isMd5 ? `&options=reference%3D${projectRef}` : ''}&password=${password}`
98
99 const sqlalchemyString = `user=${directUser}
100password=${password}
101host=${directHost}
102port=${directPort}
103dbname=${directName}`
104
105 const poolerSqlalchemyString = `user=${poolerUser}
106password=${password}
107host=${poolerHost}
108port=${poolerPort}
109dbname=${poolerName}`
110
111 return {
112 direct: {
113 psql: directPsqlString,
114 uri: directUriString,
115 golang: directGolangString,
116 jdbc: directJdbcString,
117 dotnet: directDotNetString,
118 nodejs: directNodejsString,
119 php: directGolangString,
120 python: directGolangString,
121 sqlalchemy: sqlalchemyString,
122 },
123 pooler: {
124 psql: poolerPsqlString,
125 uri: poolerUriString,
126 golang: poolerGolangString,
127 jdbc: poolerJdbcString,
128 dotnet: poolerDotNetString,
129 nodejs: nodejsPoolerUriString,
130 php: poolerGolangString,
131 python: poolerGolangString,
132 sqlalchemy: poolerSqlalchemyString,
133 },
134 }
135}
136
137const DB_USER_DESC = 'Database user (e.g postgres)'
138const DB_PASS_DESC = 'Database password'
139const DB_NAME_DESC = 'Database name (e.g postgres)'
140const PROJECT_REF_DESC = "Project's reference ID"
141const PORT_NUMBER_DESC = 'Port number (Use 5432 if using prepared statements)'
142
143// [Joshen] This is to the best of interpreting the syntax from the API response
144// // There's different format for PG13 (depending on authentication method being md5) and PG14
145export const constructConnStringSyntax = (
146 connString: string,
147 {
148 selectedTab,
149 usePoolerConnection,
150 ref,
151 cloudProvider,
152 region,
153 tld,
154 portNumber,
155 }: {
156 selectedTab: 'uri' | 'psql' | 'golang' | 'jdbc' | 'dotnet' | 'nodejs' | 'php' | 'python'
157 usePoolerConnection: boolean
158 ref: string
159 cloudProvider: string
160 region: string
161 tld: string
162 portNumber: string
163 }
164) => {
165 const isMd5 = connString.includes('options=reference')
166 const poolerHostDetails = [
167 { value: cloudProvider.toLocaleLowerCase(), tooltip: 'Cloud provider' },
168 { value: '-0-', tooltip: undefined },
169 { value: region, tooltip: "Project's region" },
170 { value: `.pooler.briven.${tld}`, tooltip: undefined },
171 ]
172 const dbHostDetails = [
173 { value: 'db.', tooltip: undefined },
174 { value: ref, tooltip: PROJECT_REF_DESC },
175 { value: `.briven.${tld}`, tooltip: undefined },
176 ]
177
178 if (selectedTab === 'uri' || selectedTab === 'nodejs') {
179 if (isMd5) {
180 return [
181 { value: 'postgresql://', tooltip: undefined },
182 { value: '[user]', tooltip: DB_USER_DESC },
183 { value: ':', tooltip: undefined },
184 { value: '[password]', tooltip: DB_PASS_DESC },
185 { value: '@', tooltip: undefined },
186 ...(usePoolerConnection ? poolerHostDetails : dbHostDetails),
187 { value: ':', tooltip: undefined },
188 { value: portNumber, tooltip: PORT_NUMBER_DESC },
189 { value: '/', tooltip: undefined },
190 { value: '[db-name]', tooltip: DB_NAME_DESC },
191 ...(usePoolerConnection
192 ? [
193 { value: `?options=reference%3D`, tooltip: undefined },
194 { value: ref, tooltip: PROJECT_REF_DESC },
195 ]
196 : []),
197 ]
198 } else {
199 return [
200 { value: 'postgresql://', tooltip: undefined },
201 { value: '[user]', tooltip: DB_USER_DESC },
202 ...(usePoolerConnection
203 ? [
204 { value: '.', tooltip: undefined },
205 { value: ref, tooltip: PROJECT_REF_DESC },
206 ]
207 : []),
208 { value: ':', tooltip: undefined },
209 { value: '[password]', tooltip: DB_PASS_DESC },
210 { value: '@', tooltip: undefined },
211 ...(usePoolerConnection ? poolerHostDetails : dbHostDetails),
212 { value: ':', tooltip: undefined },
213 { value: portNumber, tooltip: PORT_NUMBER_DESC },
214 { value: '/', tooltip: undefined },
215 { value: '[db-name]', tooltip: DB_NAME_DESC },
216 ]
217 }
218 }
219
220 if (selectedTab === 'psql') {
221 if (isMd5) {
222 return [
223 { value: 'psql "postgresql://', tooltip: undefined },
224 { value: '[user]', tooltip: DB_USER_DESC },
225 { value: ':', tooltip: undefined },
226 { value: '[password]', tooltip: DB_PASS_DESC },
227 { value: '@', tooltip: undefined },
228 ...(usePoolerConnection ? poolerHostDetails : dbHostDetails),
229 { value: ':', tooltip: undefined },
230 { value: portNumber, tooltip: PORT_NUMBER_DESC },
231 { value: '/', tooltip: undefined },
232 { value: '[db-name]', tooltip: DB_NAME_DESC },
233 ...(usePoolerConnection
234 ? [
235 { value: '?options=reference%3D', tooltip: undefined },
236 { value: ref, tooltip: PROJECT_REF_DESC },
237 ]
238 : []),
239 ]
240 } else {
241 return [
242 { value: 'psql -h ', tooltip: undefined },
243 ...(usePoolerConnection ? poolerHostDetails : dbHostDetails),
244 { value: ' -p ', tooltip: undefined },
245 { value: portNumber, tooltip: PORT_NUMBER_DESC },
246 { value: ' -d ', tooltip: undefined },
247 { value: '[db-name]', tooltip: DB_NAME_DESC },
248 { value: ' -U ', tooltip: undefined },
249 { value: '[user]', tooltip: DB_USER_DESC },
250 ...(usePoolerConnection
251 ? [
252 { value: '.', tooltip: undefined },
253 { value: ref, tooltip: PROJECT_REF_DESC },
254 ]
255 : []),
256 ]
257 }
258 }
259
260 if (selectedTab === 'golang' || selectedTab === 'php' || selectedTab === 'python') {
261 if (isMd5) {
262 return [
263 { value: 'user=', tooltip: undefined },
264 { value: '[user]', tooltip: DB_USER_DESC },
265 { value: ' password=', tooltip: undefined },
266 { value: '[password]', tooltip: DB_PASS_DESC },
267 { value: ' host=', tooltip: undefined },
268 ...(usePoolerConnection ? poolerHostDetails : dbHostDetails),
269 { value: ' port=', tooltip: undefined },
270 { value: portNumber, tooltip: PORT_NUMBER_DESC },
271 { value: ' dbname=', tooltip: undefined },
272 { value: '[db-name]', tooltip: DB_NAME_DESC },
273 ...(usePoolerConnection
274 ? [
275 { value: ' options=reference=', tooltip: undefined },
276 { value: ref, tooltip: PROJECT_REF_DESC },
277 ]
278 : []),
279 ]
280 } else {
281 return [
282 { value: 'user=', tooltip: undefined },
283 { value: '[user]', tooltip: DB_USER_DESC },
284 ...(usePoolerConnection
285 ? [
286 { value: '.', tooltip: undefined },
287 { value: ref, tooltip: PROJECT_REF_DESC },
288 ]
289 : []),
290 { value: ' password=', tooltip: undefined },
291 { value: '[password]', tooltip: DB_PASS_DESC },
292 { value: ' host=', tooltip: undefined },
293 ...(usePoolerConnection ? poolerHostDetails : dbHostDetails),
294 { value: ' port=', tooltip: undefined },
295 { value: portNumber, tooltip: PORT_NUMBER_DESC },
296 { value: ' dbname=', tooltip: undefined },
297 { value: '[db-name]', tooltip: DB_NAME_DESC },
298 ]
299 }
300 }
301
302 if (selectedTab === 'jdbc') {
303 if (isMd5) {
304 return [
305 { value: 'jdbc:postgresql://', tooltip: undefined },
306 ...(usePoolerConnection ? poolerHostDetails : dbHostDetails),
307 { value: ':', tooltip: undefined },
308 { value: portNumber, tooltip: PORT_NUMBER_DESC },
309 { value: '/', tooltip: undefined },
310 { value: '[db-name]', tooltip: DB_NAME_DESC },
311 { value: '?user=', tooltip: undefined },
312 { value: '[user]', tooltip: DB_USER_DESC },
313 { value: '&password=', tooltip: undefined },
314 { value: '[password]', tooltip: DB_PASS_DESC },
315 ...(usePoolerConnection
316 ? [
317 { value: '&options=reference%3D', tooltip: undefined },
318 { value: ref, tooltip: PROJECT_REF_DESC },
319 ]
320 : []),
321 ]
322 } else {
323 return [
324 { value: 'jdbc:postgresql://', tooltip: undefined },
325 ...(usePoolerConnection ? poolerHostDetails : dbHostDetails),
326 { value: `:`, tooltip: undefined },
327 { value: portNumber, tooltip: PORT_NUMBER_DESC },
328 { value: '/', tooltip: undefined },
329 { value: '[db-name]', tooltip: DB_NAME_DESC },
330 { value: '?user=', tooltip: undefined },
331 { value: '[user]', tooltip: DB_USER_DESC },
332 ...(usePoolerConnection
333 ? [
334 { value: '.', tooltip: undefined },
335 { value: ref, tooltip: PROJECT_REF_DESC },
336 ]
337 : []),
338 { value: '&password=', tooltip: undefined },
339 { value: '[password]', tooltip: DB_PASS_DESC },
340 ]
341 }
342 }
343
344 if (selectedTab === 'dotnet') {
345 if (isMd5) {
346 return [
347 { value: 'User Id=', tooltip: undefined },
348 { value: '[user]', tooltip: DB_USER_DESC },
349 { value: ';Password=', tooltip: undefined },
350 { value: '[password]', tooltip: DB_PASS_DESC },
351 { value: ';Server=', tooltip: undefined },
352 ...(usePoolerConnection ? poolerHostDetails : dbHostDetails),
353 { value: ';Port=', tooltip: undefined },
354 { value: portNumber, tooltip: PORT_NUMBER_DESC },
355 { value: ';Database=', tooltip: undefined },
356 { value: '[db-name]', tooltip: DB_NAME_DESC },
357 ...(usePoolerConnection
358 ? [
359 { value: ";Options='reference=", tooltip: undefined },
360 { value: ref, tooltip: PROJECT_REF_DESC },
361 { value: "'", tooltip: undefined },
362 ]
363 : []),
364 ]
365 } else {
366 return [
367 { value: 'User Id=', tooltip: undefined },
368 { value: '[user]', tooltip: DB_USER_DESC },
369 ...(usePoolerConnection
370 ? [
371 { value: '.', tooltip: undefined },
372 { value: ref, tooltip: PROJECT_REF_DESC },
373 ]
374 : []),
375 { value: ';Password=', tooltip: undefined },
376 { value: '[password]', tooltip: DB_PASS_DESC },
377 { value: ';Server=', tooltip: undefined },
378 ...(usePoolerConnection ? poolerHostDetails : dbHostDetails),
379 { value: ';Port=', tooltip: undefined },
380 { value: portNumber, tooltip: PORT_NUMBER_DESC },
381 { value: ';Database=', tooltip: undefined },
382 { value: '[db-name]', tooltip: DB_NAME_DESC },
383 ]
384 }
385 }
386
387 return []
388}