konnosDashboard
sql-editor-v2.ts525 lines · main
1import { untrustedSql } from '@supabase/pg-meta'
2import { debounce, memoize } from 'lodash'
3import { useMemo } from 'react'
4import { toast } from 'sonner'
5import { proxy, ref, snapshot, subscribe, useSnapshot } from 'valtio'
6import { devtools, proxyMap } from 'valtio/utils'
7
8import type { QueryPlanRow } from '@/components/interfaces/ExplainVisualizer/ExplainVisualizer.types'
9import { DiffType } from '@/components/interfaces/SQLEditor/SQLEditor.types'
10import { upsertContent, UpsertContentPayload } from '@/data/content/content-upsert-mutation'
11import { contentKeys } from '@/data/content/keys'
12import { createSQLSnippetFolder } from '@/data/content/sql-folder-create-mutation'
13import { updateSQLSnippetFolder } from '@/data/content/sql-folder-update-mutation'
14import { Snippet, SnippetFolder } from '@/data/content/sql-folders-query'
15import { getQueryClient } from '@/data/query-client'
16import type { SqlSnippets } from '@/types'
17
18type StateSnippetFolder = {
19 projectRef: string
20 folder: SnippetFolder
21 status?: 'editing' | 'saving' | 'idle'
22}
23
24type StateSnippet = {
25 projectRef: string
26 splitSizes: number[]
27 snippet: SnippetWithContent
28}
29
30// [Joshen] API codegen is somehow missing the content property
31export interface SnippetWithContent extends Snippet {
32 content?: SqlSnippets.Content
33 isNotSavedInDatabaseYet?: boolean
34}
35
36const NEW_FOLDER_ID = 'new-folder'
37
38export const sqlEditorState = proxy({
39 // ========================================================================
40 // ## Data properties within the store
41 // ========================================================================
42
43 /**
44 * Currently limitations include supporting up to one level of folders from root, and only private snippets
45 */
46 folders: {} as {
47 [folderId: string]: StateSnippetFolder
48 },
49
50 /**
51 * Private and shared snippets only, favorite snippets are derivatives of them by the `favorite` property
52 */
53 snippets: {} as {
54 [snippetId: string]: StateSnippet
55 },
56
57 /**
58 * Query results, if any, for a snippet. Set as an array per snippetId as we were previously experimenting
59 * with having a Jupyter notebook like UI but it never took off. Nonetheless kept this data structure as
60 * we'd also want to support returning multiple results from a single query (e.g From a query that contains
61 * multiple select statements), and this will allow us to do quite easily.
62 */
63 results: {} as {
64 [snippetId: string]: {
65 rows: any[]
66 error?: any
67 autoLimit?: number
68 }[]
69 },
70
71 /**
72 * Explain results, if any, for a snippet
73 */
74 explainResults: {} as {
75 [snippetId: string]: {
76 rows: QueryPlanRow[]
77 error?: { message: string; formattedError?: string }
78 }
79 },
80 /**
81 * Synchronous saving of folders and snippets (debounce behavior). Key is the snippet id, value is shouldInvalidate
82 */
83 needsSaving: proxyMap<string, boolean>([]),
84 /**
85 * Stores the state of each snippet
86 */
87 savingStates: {} as {
88 [snippetId: string]: 'IDLE' | 'UPDATING' | 'UPDATING_FAILED'
89 },
90 /**
91 * UI-imposed limit for the number of results a query can return (applied to the SQL query being run if applicable).
92 * Acts as a safeguard to prevent accidentally taking down the database from a really large SELECT query.
93 * Related to `autoLimit` in `results`. Refer to `checkIfAppendLimitRequired` and `suffixWithLimit` for usage.
94 */
95 limit: 100,
96
97 /**
98 * Used for error handling after optimistical rendering from renaming a folder
99 */
100 lastUpdatedFolderName: '',
101
102 /**
103 * For Assistant to render diffing into the editor
104 */
105 diffContent: undefined as undefined | { sql: string; diffType: DiffType },
106
107 get allFolderNames() {
108 return Object.values(sqlEditorState.folders).map((x) => x.folder.name)
109 },
110
111 // ========================================================================
112 // ## Methods to interact the store with
113 // ========================================================================
114
115 setDiffContent: (sql: string, diffType: DiffType) =>
116 (sqlEditorState.diffContent = { sql, diffType }),
117
118 /**
119 * Load snippet into SQL Editor Valtio store
120 */
121 addSnippet: ({ projectRef, snippet }: { projectRef: string; snippet: SnippetWithContent }) => {
122 if (sqlEditorState.snippets[snippet.id]) return
123
124 sqlEditorState.snippets[snippet.id] = { projectRef, splitSizes: [50, 50], snippet }
125 sqlEditorState.results[snippet.id] = []
126 sqlEditorState.explainResults[snippet.id] = { rows: [] }
127 sqlEditorState.savingStates[snippet.id] = 'IDLE'
128 },
129
130 /**
131 * Update snippet data (e.g name, visibility, chart) and queue for sync saving
132 */
133 updateSnippet: ({
134 id,
135 snippet,
136 skipSave = false,
137 }: {
138 id: string
139 snippet: Partial<Snippet>
140 skipSave?: boolean
141 }) => {
142 if (sqlEditorState.snippets[id]) {
143 sqlEditorState.snippets[id].snippet = {
144 ...sqlEditorState.snippets[id].snippet,
145 ...snippet,
146 }
147 if (!skipSave) sqlEditorState.needsSaving.set(id, true)
148 }
149 },
150
151 /**
152 * Load snippet content into the snippet within the Valtio store.
153 * Snippets fetched from the GET /content or /folders endpoints do not have the content loaded initially
154 * to reduce the response size from the API. Hence content for each snippet has to be loaded on demand
155 */
156 setSnippet: (projectRef: string, snippet: SnippetWithContent) => {
157 let storedSnippet = sqlEditorState.snippets[snippet.id]
158 if (storedSnippet) {
159 if (!storedSnippet.snippet.content) {
160 storedSnippet.snippet.content = snippet.content
161 }
162 } else {
163 sqlEditorState.addSnippet({ projectRef: projectRef, snippet })
164 }
165 },
166
167 /**
168 * Update the snippet content of a snippet and queue for sync saving
169 * Possibly can consolidate with `updateSnippet` to simplify
170 */
171 setSql: ({
172 id,
173 sql,
174 shouldInvalidate = false,
175 }: {
176 id: string
177 sql: string
178 shouldInvalidate?: boolean
179 }) => {
180 let snippet = sqlEditorState.snippets[id]?.snippet
181 if (snippet?.content) {
182 snippet.content.unchecked_sql = untrustedSql(sql)
183 sqlEditorState.needsSaving.set(id, shouldInvalidate)
184 }
185 },
186
187 /**
188 * Update snippet in Valtio store after renaming
189 * Renaming a snippet follows an async saving and hence doesnt require queuing for sync saving here
190 * Refer to `RenameQueryModal.tsx` for more details
191 */
192 renameSnippet: ({
193 id,
194 name,
195 description,
196 }: {
197 id: string
198 name: string
199 description?: string
200 }) => {
201 let snippet = sqlEditorState.snippets[id]?.snippet
202 if (snippet) {
203 snippet.name = name
204 snippet.description = description
205 }
206 },
207
208 /**
209 * Remove snippet from the Valtio store, and optionally remove snippet from the sync saving queue
210 */
211 removeSnippet: (id: string, skipSave: boolean = false) => {
212 const { [id]: snippet, ...otherSnippets } = sqlEditorState.snippets
213 sqlEditorState.snippets = otherSnippets
214
215 const { [id]: result, ...otherResults } = sqlEditorState.results
216 sqlEditorState.results = otherResults
217
218 const { [id]: explainResult, ...otherExplainResults } = sqlEditorState.explainResults
219 sqlEditorState.explainResults = otherExplainResults
220
221 if (!skipSave) sqlEditorState.needsSaving.delete(id)
222 },
223
224 /**
225 * Load folder into SQL Editor Valtio store
226 */
227 addFolder: ({ projectRef, folder }: { projectRef: string; folder: SnippetFolder }) => {
228 if (sqlEditorState.folders[folder.id]) return
229 sqlEditorState.folders[folder.id] = { projectRef, folder }
230 },
231
232 /**
233 * Adds a new folder placeholder for the UI to render
234 */
235 addNewFolder: ({ projectRef }: { projectRef: string }) => {
236 // [Joshen] Use this to identify new folders that have yet to be saved
237 const id = NEW_FOLDER_ID
238 sqlEditorState.folders[id] = {
239 projectRef,
240 status: 'editing',
241 folder: {
242 id,
243 name: '',
244 owner_id: -1,
245 project_id: -1,
246 parent_id: null,
247 },
248 }
249 },
250
251 editFolder: (id: string) => {
252 sqlEditorState.folders[id].status = 'editing'
253 },
254
255 /**
256 * For renaming a folder, queue for sync saving if pass all validations
257 */
258 saveFolder: ({ id, name }: { id: string; name: string }) => {
259 let storeFolder = sqlEditorState.folders[id]
260 const isNewFolder = id === 'new-folder'
261 const hasChanges = storeFolder.folder.name !== name
262
263 if (isNewFolder && sqlEditorState.allFolderNames.includes(name)) {
264 sqlEditorState.removeFolder(id)
265 return toast.error('Unable to create new folder: This folder name already exists')
266 } else if (hasChanges && sqlEditorState.allFolderNames.includes(name)) {
267 storeFolder.status = 'idle'
268 return toast.error('Unable to update folder: This folder name already exists')
269 }
270
271 const originalFolderName = storeFolder.folder.name.slice()
272
273 storeFolder.status = hasChanges ? 'saving' : 'idle'
274 storeFolder.folder.id = id
275 storeFolder.folder.name = name
276
277 if (hasChanges) {
278 sqlEditorState.lastUpdatedFolderName = originalFolderName
279 sqlEditorState.needsSaving.set(id, true)
280 }
281 },
282
283 /**
284 * Remove folder from the Valtio store
285 * Deleting a folder follows an async saving and hence doesnt require queuing for sync saving here
286 * Refer to `SQLEditorNav` for more details (ConfirmationModal for deleting a folder)
287 */
288 removeFolder: (id: string) => {
289 const { [id]: folder, ...otherFolders } = sqlEditorState.folders
290 sqlEditorState.folders = otherFolders
291 },
292
293 /**
294 * Set the value for the auto limit for SELECT based SQL queries
295 */
296 setLimit: (value: number) => (sqlEditorState.limit = value),
297
298 addNeedsSaving: (id: string) => sqlEditorState.needsSaving.set(id, true),
299
300 addFavorite: (id: string) => {
301 const storeSnippet = sqlEditorState.snippets[id]
302 if (storeSnippet) {
303 storeSnippet.snippet.favorite = true
304 sqlEditorState.needsSaving.set(id, true)
305 }
306 },
307
308 removeFavorite: (id: string) => {
309 const storeSnippet = sqlEditorState.snippets[id]
310 if (storeSnippet.snippet) {
311 storeSnippet.snippet.favorite = false
312 sqlEditorState.needsSaving.set(id, true)
313 }
314 },
315
316 addResult: (id: string, results: any[], autoLimit?: number) => {
317 if (sqlEditorState.results[id]) {
318 // Use ref() to prevent Valtio from creating proxies for each row object.
319 // This is critical for large result sets - without ref(), Valtio wraps every
320 // row and nested property in a Proxy, causing massive memory overhead.
321 // Alright to use ref() in this case as the data is meant to be read-only and we
322 // don't need to track changes to the underlying data
323 sqlEditorState.results[id] = [{ rows: ref(results), autoLimit }]
324 }
325 },
326
327 addResultError: (id: string, error: any, autoLimit?: number) => {
328 if (sqlEditorState.results[id]) {
329 sqlEditorState.results[id] = [{ rows: ref([]), error, autoLimit }]
330 }
331 },
332
333 resetResult: (id: string) => {
334 if (sqlEditorState.results[id]) {
335 sqlEditorState.results[id] = []
336 }
337 },
338
339 addExplainResult: (id: string, results: QueryPlanRow[]) => {
340 // Use ref() to prevent Valtio from creating proxies for each row object
341 sqlEditorState.explainResults[id] = { rows: ref(results) }
342 },
343
344 addExplainResultError: (id: string, error: { message: string; formattedError?: string }) => {
345 sqlEditorState.explainResults[id] = { rows: ref([]), error }
346 },
347
348 resetExplainResult: (id: string) => {
349 sqlEditorState.explainResults[id] = { rows: [] }
350 },
351
352 resetResults: (id: string) => {
353 sqlEditorState.resetResult(id)
354 sqlEditorState.resetExplainResult(id)
355 },
356})
357
358// ========================================================================
359// ## Expose entry points into this Valtio store
360// ========================================================================
361
362export const getSqlEditorV2StateSnapshot = () => snapshot(sqlEditorState)
363
364export const useSqlEditorV2StateSnapshot = (options?: Parameters<typeof useSnapshot>[1]) =>
365 useSnapshot(sqlEditorState, options)
366
367export const useSnippetFolders = (projectRef: string) => {
368 const snapshot = useSqlEditorV2StateSnapshot()
369
370 return useMemo(
371 () =>
372 Object.values(snapshot.folders)
373 .filter((x) => x.projectRef === projectRef)
374 .map((x) => x.folder)
375 // folders don't have created_at or inserted_at, so we always sort by name
376 .sort((a, b) => a.name.localeCompare(b.name)),
377 [projectRef, snapshot.folders]
378 )
379}
380
381/**
382 * Get ALL snippets for a project
383 */
384export const useSnippets = (projectRef: string) => {
385 const snapshot = useSqlEditorV2StateSnapshot()
386
387 return useMemo(
388 () =>
389 Object.values(snapshot.snippets)
390 .filter((storeSnippet) => storeSnippet.projectRef === projectRef)
391 .map((storeSnippet) => storeSnippet.snippet),
392 [projectRef, snapshot.snippets]
393 )
394}
395
396// ========================================================================
397// ## Below are all the asynchronous saving logic for the SQL Editor
398// ========================================================================
399
400async function upsertSnippet(
401 id: string,
402 projectRef: string,
403 payload: UpsertContentPayload,
404 shouldInvalidate = false
405) {
406 try {
407 sqlEditorState.savingStates[id] = 'UPDATING'
408 await upsertContent({ projectRef, payload })
409
410 if (shouldInvalidate) {
411 const queryClient = getQueryClient()
412 await Promise.all([
413 queryClient.invalidateQueries({ queryKey: contentKeys.count(projectRef, 'sql') }),
414 queryClient.invalidateQueries({ queryKey: contentKeys.sqlSnippets(projectRef) }),
415 queryClient.invalidateQueries({ queryKey: contentKeys.folders(projectRef) }),
416 ])
417 }
418
419 let snippet = sqlEditorState.snippets[id]?.snippet
420 if (snippet?.content && 'isNotSavedInDatabaseYet' in snippet) {
421 snippet.isNotSavedInDatabaseYet = false
422 }
423 sqlEditorState.savingStates[id] = 'IDLE'
424 } catch (error) {
425 sqlEditorState.savingStates[id] = 'UPDATING_FAILED'
426 }
427}
428
429const memoizedUpsertSnippet = memoize((_id: string) => debounce(upsertSnippet, 1000))
430
431const debouncedUpdateSnippet = (
432 id: string,
433 projectRef: string,
434 payload: UpsertContentPayload,
435 shouldInvalidate = false
436) => memoizedUpsertSnippet(id)(id, projectRef, payload, shouldInvalidate)
437
438async function upsertFolder(id: string, projectRef: string, name: string) {
439 try {
440 if (id === NEW_FOLDER_ID) {
441 const res = await createSQLSnippetFolder({ projectRef, name })
442 toast.success('Successfully created folder')
443 sqlEditorState.removeFolder(NEW_FOLDER_ID)
444 sqlEditorState.folders[res.id] = { projectRef, status: 'idle', folder: res }
445 } else {
446 await updateSQLSnippetFolder({ projectRef, id, name })
447 toast.success('Successfully updated folder')
448 sqlEditorState.folders[id].status = 'idle'
449 }
450 } catch (error: any) {
451 toast.error(`Failed to save folder: ${error.message}`)
452 if (error.message.includes('create')) {
453 sqlEditorState.removeFolder(id)
454 } else if (
455 error.message.includes('update') &&
456 sqlEditorState.lastUpdatedFolderName.length > 0
457 ) {
458 let storeFolder = sqlEditorState.folders[id]
459
460 storeFolder.status = 'idle'
461 storeFolder.folder.name = sqlEditorState.lastUpdatedFolderName
462 }
463 } finally {
464 sqlEditorState.lastUpdatedFolderName = ''
465 }
466}
467
468if (typeof window !== 'undefined') {
469 devtools(sqlEditorState, {
470 name: 'sqlEditorStateV2',
471 // [Joshen] So that jest unit tests can ignore this
472 enabled: process.env.NEXT_PUBLIC_ENVIRONMENT !== undefined,
473 })
474
475 subscribe(sqlEditorState.needsSaving, () => {
476 const state = getSqlEditorV2StateSnapshot()
477
478 state.needsSaving.forEach((shouldInvalidate, id) => {
479 const snippet = state.snippets[id]
480 const folder = state.folders[id]
481
482 if (snippet) {
483 const {
484 name,
485 description,
486 visibility,
487 project_id,
488 owner_id,
489 folder_id,
490 content,
491 favorite,
492 } = snippet.snippet
493
494 if (visibility === 'project' && !!folder_id) {
495 toast.error('Shared snippet cannot be within a folder')
496 } else {
497 debouncedUpdateSnippet(
498 id,
499 snippet.projectRef,
500 {
501 id,
502 type: 'sql',
503 name: name ?? 'Untitled',
504 description: description ?? '',
505 visibility: visibility ?? 'user',
506 project_id: project_id ?? 0,
507 owner_id: owner_id,
508 folder_id: folder_id ?? undefined,
509 favorite: favorite ?? false,
510 content: {
511 ...content!,
512 content_id: id,
513 },
514 },
515 shouldInvalidate
516 )
517 sqlEditorState.needsSaving.delete(id)
518 }
519 } else if (folder) {
520 upsertFolder(id, folder.projectRef, folder.folder.name)
521 sqlEditorState.needsSaving.delete(id)
522 }
523 })
524 })
525}