konnosDashboard
ExportAllRows.tsx457 lines · main
1import { useQueryClient, type QueryClient } from '@tanstack/react-query'
2import { IS_PLATFORM } from 'common'
3import saveAs from 'file-saver'
4import Papa from 'papaparse'
5import { useCallback, useState, type ReactNode } from 'react'
6import { ConfirmationModal } from 'ui-patterns/Dialogs/ConfirmationModal'
7
8import {
9 BlobCreationError,
10 DownloadSaveError,
11 FetchRowsError,
12 NoConnectionStringError,
13 NoRowsToExportError,
14 NoTableError,
15 OutputConversionError,
16 TableDetailsFetchError,
17 TableTooLargeError,
18 type ExportAllRowsErrorFamily,
19} from './ExportAllRows.errors'
20import { useProgressToasts } from './ExportAllRows.progress'
21import { parseSupaTable } from '@/components/grid/BrivenGrid.utils'
22import type { Filter, Sort, SupaTable } from '@/components/grid/types'
23import { formatTableRowsToSQL } from '@/components/interfaces/TableGridEditor/TableEntity.utils'
24import { InlineLink } from '@/components/ui/InlineLink'
25import { ENTITY_TYPE } from '@/data/entity-types/entity-type-constants'
26import type { Entity } from '@/data/entity-types/entity-types-infinite-query'
27import { tableEditorKeys } from '@/data/table-editor/keys'
28import { getTableEditor, type TableEditorData } from '@/data/table-editor/table-editor-query'
29import { isTableLike } from '@/data/table-editor/table-editor-types'
30import { fetchAllTableRows } from '@/data/table-rows/table-rows-query'
31import { useStaticEffectEvent } from '@/hooks/useStaticEffectEvent'
32import { DOCS_URL } from '@/lib/constants'
33import type { RoleImpersonationState } from '@/lib/role-impersonation'
34
35// [Joshen] CSV exports require this guard as a fail-safe if the table is
36// just too large for a browser to keep all the rows in memory before
37// exporting. Either that or export as multiple CSV sheets with max n rows each
38const MAX_EXPORT_ROW_COUNT = 500000
39const MAX_EXPORT_ROW_COUNT_MESSAGE = (
40 <p>
41 Sorry! We're unable to support exporting row counts larger than{' '}
42 {MAX_EXPORT_ROW_COUNT.toLocaleString('en-US')} at the moment. Alternatively, you may consider
43 using <InlineLink href={`${DOCS_URL}/reference/cli/briven-db-dump`}>pg_dump</InlineLink> via
44 our CLI instead.
45 </p>
46)
47
48type OutputCallbacks = {
49 convertToOutputFormat: (formattedRows: Record<string, unknown>[], table: SupaTable) => string
50 convertToBlob: (str: string) => Blob
51 save: (blob: Blob, table: SupaTable) => void
52}
53
54type FetchAllRowsParams = {
55 queryClient: QueryClient
56 projectRef: string
57 connectionString: string | null
58 entity: Pick<Entity, 'id' | 'name' | 'type'>
59 bypassConfirmation: boolean
60 filters?: Filter[]
61 sorts?: Sort[]
62 roleImpersonationState?: RoleImpersonationState
63 totalRows?: number
64 startCallback?: () => void
65 progressCallback?: (progress: number) => void
66} & OutputCallbacks
67
68type FetchAllRowsReturn =
69 | { status: 'require_confirmation'; reason: string }
70 | { status: 'error'; error: ExportAllRowsErrorFamily }
71 | { status: 'success'; rowsExported: number }
72
73const fetchAllRows = async ({
74 queryClient,
75 projectRef,
76 connectionString,
77 entity,
78 bypassConfirmation,
79 filters,
80 sorts,
81 roleImpersonationState,
82 totalRows,
83 startCallback,
84 progressCallback,
85 convertToOutputFormat,
86 convertToBlob,
87 save,
88}: FetchAllRowsParams): Promise<FetchAllRowsReturn> => {
89 if (IS_PLATFORM && !connectionString) {
90 return { status: 'error', error: new NoConnectionStringError() }
91 }
92
93 let table: TableEditorData | undefined
94 try {
95 table = await queryClient.ensureQueryData({
96 // Query is the same even if connectionString changes
97 // eslint-disable-next-line @tanstack/query/exhaustive-deps
98 queryKey: tableEditorKeys.tableEditor(projectRef, entity.id),
99 queryFn: ({ signal }) =>
100 getTableEditor({ projectRef, connectionString, id: entity.id }, signal),
101 })
102 } catch (error: unknown) {
103 return { status: 'error', error: new TableDetailsFetchError(entity.name, error) }
104 }
105
106 if (!table) {
107 return { status: 'error', error: new NoTableError(entity.name) }
108 }
109
110 const type = table.entity_type
111 if (type === ENTITY_TYPE.VIEW && !bypassConfirmation) {
112 return {
113 status: 'require_confirmation',
114 reason: `Exporting a view may cause consistency issues or performance issues on very large views. If possible, we recommend exporting the underlying table instead.`,
115 }
116 } else if (type === ENTITY_TYPE.MATERIALIZED_VIEW && !bypassConfirmation) {
117 return {
118 status: 'require_confirmation',
119 reason: `Exporting a materialized view may cause performance issues on very large views. If possible, we recommend exporting the underlying table instead.`,
120 }
121 } else if (type === ENTITY_TYPE.FOREIGN_TABLE && !bypassConfirmation) {
122 return {
123 status: 'require_confirmation',
124 reason: `Exporting a foreign table may cause consistency issues or performance issues on very large tables.`,
125 }
126 }
127
128 if (totalRows !== undefined) {
129 if (totalRows > MAX_EXPORT_ROW_COUNT) {
130 return {
131 status: 'error',
132 error: new TableTooLargeError(table.name, totalRows, MAX_EXPORT_ROW_COUNT),
133 }
134 }
135 } else if (isTableLike(table) && table.live_rows_estimate > MAX_EXPORT_ROW_COUNT) {
136 return {
137 status: 'error',
138 error: new TableTooLargeError(table.name, table.live_rows_estimate, MAX_EXPORT_ROW_COUNT),
139 }
140 }
141
142 const supaTable = parseSupaTable(table)
143
144 const primaryKey = supaTable.primaryKey
145 if (!primaryKey && !bypassConfirmation) {
146 return {
147 status: 'require_confirmation',
148 reason: `This table does not have a primary key defined, which may cause performance issues when exporting very large tables.`,
149 }
150 }
151
152 startCallback?.()
153
154 let rows: Record<string, unknown>[]
155 try {
156 rows = await fetchAllTableRows({
157 projectRef,
158 connectionString,
159 table: supaTable,
160 filters,
161 sorts,
162 roleImpersonationState,
163 progressCallback,
164 })
165 } catch (error: unknown) {
166 return { status: 'error', error: new FetchRowsError(supaTable.name, error) }
167 }
168
169 if (rows.length === 0) {
170 return { status: 'error', error: new NoRowsToExportError(entity.name) }
171 }
172 const formattedRows = formatRowsForExport(rows, supaTable)
173
174 return convertAndDownload(formattedRows, supaTable, {
175 convertToOutputFormat,
176 convertToBlob,
177 save,
178 })
179}
180
181const formatRowsForExport = (rows: Record<string, unknown>[], table: SupaTable) => {
182 return rows.map((row) => {
183 const formattedRow = { ...row }
184 Object.keys(row).map((column) => {
185 if (column === 'idx' && !table.columns.some((col) => col.name === 'idx')) {
186 // When we fetch this data from the database, we automatically add an
187 // 'idx' column if none exists. We shouldn't export this column since
188 // it's not actually part of the user's table.
189 delete formattedRow[column]
190 return
191 }
192
193 if (typeof row[column] === 'object' && row[column] !== null)
194 formattedRow[column] = JSON.stringify(formattedRow[column])
195 })
196 return formattedRow
197 })
198}
199
200const convertAndDownload = (
201 formattedRows: Record<string, unknown>[],
202 table: SupaTable,
203 callbacks: OutputCallbacks
204):
205 | { status: 'error'; error: ExportAllRowsErrorFamily }
206 | { status: 'success'; rowsExported: number } => {
207 let output: string
208 try {
209 output = callbacks.convertToOutputFormat(formattedRows, table)
210 } catch (error: unknown) {
211 return { status: 'error', error: new OutputConversionError(error) }
212 }
213 let data: Blob
214 try {
215 data = callbacks.convertToBlob(output)
216 } catch (error: unknown) {
217 return { status: 'error', error: new BlobCreationError(error) }
218 }
219 try {
220 callbacks.save(data, table)
221 } catch (error: unknown) {
222 return { status: 'error', error: new DownloadSaveError(error) }
223 }
224
225 return {
226 status: 'success',
227 rowsExported: formattedRows.length,
228 }
229}
230
231type UseExportAllRowsParams =
232 | { enabled: false }
233 | ({
234 enabled: true
235 projectRef: string
236 connectionString: string | null
237 entity: Pick<Entity, 'id' | 'name' | 'type'>
238 /**
239 * If known, the total number of rows that will be exported.
240 * This is used to show progress percentage during export.
241 */
242 totalRows?: number
243 } & (
244 | {
245 /**
246 * Rows need to be fetched from the database.
247 */
248 type: 'fetch_all'
249 filters?: Filter[]
250 sorts?: Sort[]
251 roleImpersonationState?: RoleImpersonationState
252 }
253 | {
254 /**
255 * Rows are already available and provided directly.
256 */
257 type: 'provided_rows'
258 table: SupaTable
259 rows: Record<string, unknown>[]
260 }
261 ))
262
263type UseExportAllRowsReturn = {
264 exportInDesiredFormat: () => Promise<void>
265 confirmationModal: ReactNode | null
266}
267
268export const useExportAllRowsGeneric = (
269 params: UseExportAllRowsParams & OutputCallbacks
270): UseExportAllRowsReturn => {
271 const queryClient = useQueryClient()
272 const {
273 startProgressTracker,
274 trackPercentageProgress,
275 stopTrackerWithError,
276 dismissTrackerSilently,
277 markTrackerComplete,
278 } = useProgressToasts()
279
280 const { convertToOutputFormat, convertToBlob, save } = params
281
282 const [confirmationMessage, setConfirmationMessage] = useState<string | null>(null)
283
284 const exportInternal = useStaticEffectEvent(
285 async ({ bypassConfirmation }: { bypassConfirmation: boolean }): Promise<void> => {
286 if (!params.enabled) return
287
288 const { projectRef, connectionString, entity, totalRows } = params
289
290 const exportResult =
291 params.type === 'provided_rows'
292 ? convertAndDownload(formatRowsForExport(params.rows, params.table), params.table, {
293 convertToOutputFormat,
294 convertToBlob,
295 save,
296 })
297 : await fetchAllRows({
298 queryClient,
299 projectRef: projectRef,
300 connectionString: connectionString,
301 entity: entity,
302 bypassConfirmation,
303 filters: params.filters,
304 sorts: params.sorts,
305 roleImpersonationState: params.roleImpersonationState,
306 totalRows: params.totalRows,
307 startCallback: () => {
308 startProgressTracker({
309 id: entity.id,
310 name: entity.name,
311 trackPercentage: totalRows !== undefined,
312 })
313 },
314 progressCallback: totalRows
315 ? (value: number) =>
316 trackPercentageProgress({
317 id: entity.id,
318 name: entity.name,
319 totalRows: totalRows,
320 value,
321 })
322 : undefined,
323 convertToOutputFormat,
324 convertToBlob,
325 save,
326 })
327
328 if (exportResult.status === 'error') {
329 const error = exportResult.error
330 if (error instanceof NoRowsToExportError) {
331 return stopTrackerWithError(
332 entity.id,
333 entity.name,
334 `The table ${entity.name} has no rows to export.`
335 )
336 }
337 if (error instanceof TableTooLargeError) {
338 return stopTrackerWithError(entity.id, entity.name, MAX_EXPORT_ROW_COUNT_MESSAGE)
339 }
340 console.error(
341 `Export All Rows > Error: %s%s%s`,
342 error.message,
343 error.cause?.message ? `\n${error.cause.message}` : '',
344 error.cause?.stack ? `:\n${error.cause.stack}` : ''
345 )
346 return stopTrackerWithError(entity.id, entity.name)
347 }
348
349 if (exportResult.status === 'require_confirmation') {
350 return setConfirmationMessage(exportResult.reason)
351 }
352
353 markTrackerComplete(entity.id, exportResult.rowsExported)
354 }
355 )
356
357 const exportInDesiredFormat = useCallback(
358 () => exportInternal({ bypassConfirmation: false }),
359 [exportInternal]
360 )
361
362 const onConfirmExport = () => {
363 exportInternal({
364 bypassConfirmation: true,
365 })
366 setConfirmationMessage(null)
367 }
368 const onCancelExport = () => {
369 if (!params.enabled) return
370
371 dismissTrackerSilently(params.entity.id)
372 setConfirmationMessage(null)
373 }
374
375 return {
376 exportInDesiredFormat,
377 confirmationModal: confirmationMessage ? (
378 <ConfirmationModal
379 title="Confirm to export data"
380 visible={true}
381 onCancel={onCancelExport}
382 onConfirm={onConfirmExport}
383 alert={{
384 base: { className: '[&>div>div>h5]:font-normal border-x-0 border-t-0 rounded-none mb-0' },
385 title: confirmationMessage,
386 }}
387 />
388 ) : null,
389 }
390}
391
392type UseExportAllRowsAsCsvReturn = {
393 exportCsv: () => Promise<void>
394 confirmationModal: ReactNode | null
395}
396
397export const useExportAllRowsAsCsv = (
398 params: UseExportAllRowsParams
399): UseExportAllRowsAsCsvReturn => {
400 const { exportInDesiredFormat: exportCsv, confirmationModal } = useExportAllRowsGeneric({
401 ...params,
402 convertToOutputFormat: (formattedRows, table) =>
403 Papa.unparse(formattedRows, {
404 columns: table.columns.map((col) => col.name),
405 }),
406 convertToBlob: (csv) => new Blob([csv], { type: 'text/csv;charset=utf-8;' }),
407 save: (csvData, table) => saveAs(csvData, `${table.name}_rows.csv`),
408 })
409
410 return {
411 exportCsv,
412 confirmationModal,
413 }
414}
415
416type UseExportAllRowsAsSqlReturn = {
417 exportSql: () => Promise<void>
418 confirmationModal: ReactNode | null
419}
420
421export const useExportAllRowsAsSql = (
422 params: UseExportAllRowsParams
423): UseExportAllRowsAsSqlReturn => {
424 const { exportInDesiredFormat: exportSql, confirmationModal } = useExportAllRowsGeneric({
425 ...params,
426 convertToOutputFormat: (formattedRows, table) => formatTableRowsToSQL(table, formattedRows),
427 convertToBlob: (sqlStatements) =>
428 new Blob([sqlStatements], { type: 'text/sql;charset=utf-8;' }),
429 save: (sqlData, table) => saveAs(sqlData, `${table.name}_rows.sql`),
430 })
431
432 return {
433 exportSql,
434 confirmationModal,
435 }
436}
437
438type UseExportAllRowsAsJsonReturn = {
439 exportJson: () => Promise<void>
440 confirmationModal: ReactNode | null
441}
442
443export const useExportAllRowsAsJson = (
444 params: UseExportAllRowsParams
445): UseExportAllRowsAsJsonReturn => {
446 const { exportInDesiredFormat: exportJson, confirmationModal } = useExportAllRowsGeneric({
447 ...params,
448 convertToOutputFormat: (formattedRows) => JSON.stringify(formattedRows),
449 convertToBlob: (jsonStr) => new Blob([jsonStr], { type: 'application/json;charset=utf-8;' }),
450 save: (jsonData, table) => saveAs(jsonData, `${table.name}_rows.json`),
451 })
452
453 return {
454 exportJson,
455 confirmationModal,
456 }
457}