konnosDashboard
SidePanelEditor.utils.tsx1255 lines · main
1import * as Sentry from '@sentry/nextjs'
2import pgMeta, {
3 getAddForeignKeySQL,
4 getAddPrimaryKeySQL,
5 getDropConstraintSQL,
6 getDuplicateIdentitySequenceSQL,
7 getDuplicateRowsSQL,
8 getDuplicateTableSQL,
9 getEnableRLSSQL,
10 getRemoveForeignKeySQL,
11 getUpdateIdentitySequenceSQL,
12 type ForeignKey,
13} from '@supabase/pg-meta'
14import type { PGTablePrimaryKey } from '@supabase/pg-meta'
15import { joinSqlFragments, safeSql, type SafeSqlFragment } from '@supabase/pg-meta/src/pg-format'
16import { Query } from '@supabase/pg-meta/src/query'
17import { chunk, find, isEmpty, isEqual } from 'lodash'
18import Papa from 'papaparse'
19import { toast } from 'sonner'
20
21import {
22 generateCreateColumnPayload,
23 generateUpdateColumnPayload,
24} from './ColumnEditor/ColumnEditor.utils'
25import type { ColumnField, CreateColumnPayload, UpdateColumnPayload } from './SidePanelEditor.types'
26import { checkIfRelationChanged } from './TableEditor/ForeignKeysManagement/ForeignKeysManagement.utils'
27import type { ImportContent } from './TableEditor/TableEditor.types'
28import type { SupaRow } from '@/components/grid/types'
29import { type AcceptedGeneratedPolicy } from '@/components/interfaces/Auth/Policies/Policies.utils'
30import SparkBar from '@/components/ui/SparkBar'
31import { createDatabaseColumn } from '@/data/database-columns/database-column-create-mutation'
32import { deleteDatabaseColumn } from '@/data/database-columns/database-column-delete-mutation'
33import { updateDatabaseColumn } from '@/data/database-columns/database-column-update-mutation'
34import { createDatabasePolicy } from '@/data/database-policies/database-policy-create-mutation'
35import type { Constraint } from '@/data/database/constraints-query'
36import { ForeignKeyConstraint } from '@/data/database/foreign-key-constraints-query'
37import { databaseKeys } from '@/data/database/keys'
38import { entityTypeKeys } from '@/data/entity-types/keys'
39import { lintKeys } from '@/data/lint/keys'
40import { prefetchEditorTablePage } from '@/data/prefetchers/project.$ref.editor.$id'
41import { getQueryClient } from '@/data/query-client'
42import { executeSql } from '@/data/sql/execute-sql-query'
43import { tableEditorKeys } from '@/data/table-editor/keys'
44import { prefetchTableEditor } from '@/data/table-editor/table-editor-query'
45import { tableRowKeys } from '@/data/table-rows/keys'
46import { executeWithRetry } from '@/data/table-rows/table-rows-query'
47import { tableKeys } from '@/data/tables/keys'
48import { getTable, getTableQuery, RetrieveTableResult } from '@/data/tables/table-retrieve-query'
49import {
50 UpdateTableBody,
51 updateTable as updateTableMutation,
52} from '@/data/tables/table-update-mutation'
53import { getTables } from '@/data/tables/tables-query'
54import { sendEvent } from '@/data/telemetry/send-event-mutation'
55import { isObject, isObjectContainingKeys, timeout, tryParseJson } from '@/lib/helpers'
56import type { SafePostgresColumn } from '@/lib/postgres-types'
57import type { DeepReadonly } from '@/lib/type-helpers'
58import type { SidePanel } from '@/state/table-editor'
59
60const BATCH_SIZE = 1000
61const CHUNK_SIZE = 1024 * 1024 * 0.1 // 0.1MB
62
63/**
64 * Extracts the row data from the current side panel state.
65 * Used when queuing cell edit operations to get the row being edited.
66 * Accepts both mutable and readonly (valtio snapshot) versions of SidePanel.
67 *
68 * @param sidePanel - The current side panel state (can be readonly from valtio snapshot)
69 * @returns The row data if available, undefined otherwise
70 */
71export function getRowFromSidePanel(
72 sidePanel: SidePanel | DeepReadonly<SidePanel> | undefined
73): SupaRow | undefined {
74 if (!sidePanel) return undefined
75
76 switch (sidePanel.type) {
77 case 'json':
78 return sidePanel.jsonValue.row as SupaRow | undefined
79 case 'cell':
80 return sidePanel.value?.row as SupaRow | undefined
81 case 'row':
82 return sidePanel.row as SupaRow | undefined
83 case 'foreign-row-selector':
84 return sidePanel.foreignKey.row as SupaRow | undefined
85 default:
86 return undefined
87 }
88}
89
90const addPrimaryKey = async (
91 projectRef: string,
92 connectionString: string | undefined | null,
93 schema: string,
94 table: string,
95 columns: string[]
96) => {
97 const query = getAddPrimaryKeySQL({ schema, table, columns })
98 return await executeSql({
99 projectRef: projectRef,
100 connectionString: connectionString,
101 sql: query,
102 queryKey: ['primary-keys'],
103 })
104}
105
106const dropConstraint = async (
107 projectRef: string,
108 connectionString: string | undefined | null,
109 schema: string,
110 table: string,
111 name: string
112) => {
113 const query = getDropConstraintSQL({ schema, table, name })
114 return await executeSql({
115 projectRef: projectRef,
116 connectionString: connectionString,
117 sql: query,
118 queryKey: ['drop-constraint'],
119 })
120}
121
122const addForeignKey = async ({
123 projectRef,
124 connectionString,
125 table,
126 foreignKeys,
127}: {
128 projectRef: string
129 connectionString?: string | null
130 table: { schema: string; name: string }
131 foreignKeys: ForeignKey[]
132}) => {
133 const query = getAddForeignKeySQL({ table, foreignKeys })
134 return await executeSql({
135 projectRef: projectRef,
136 connectionString: connectionString,
137 sql: query,
138 queryKey: ['foreign-keys'],
139 })
140}
141
142const removeForeignKey = async ({
143 projectRef,
144 connectionString,
145 table,
146 foreignKeys,
147}: {
148 projectRef: string
149 connectionString?: string | null
150 table: { schema: string; name: string }
151 foreignKeys: ForeignKey[]
152}) => {
153 const query = getRemoveForeignKeySQL({ table, foreignKeys })
154 return await executeSql({
155 projectRef: projectRef,
156 connectionString: connectionString,
157 sql: query,
158 queryKey: ['foreign-keys'],
159 })
160}
161
162const updateForeignKey = async ({
163 projectRef,
164 connectionString,
165 table,
166 foreignKeys,
167}: {
168 projectRef: string
169 connectionString?: string | null
170 table: { schema: string; name: string }
171 foreignKeys: ForeignKey[]
172}) => {
173 const query = safeSql`${getRemoveForeignKeySQL({ table, foreignKeys })} ${getAddForeignKeySQL({ table, foreignKeys })}`
174 return await executeSql({
175 projectRef: projectRef,
176 connectionString: connectionString,
177 sql: query,
178 queryKey: ['foreign-keys'],
179 })
180}
181
182/**
183 * The methods below involve several contexts due to the UI flow of the
184 * dashboard and hence do not sit within their own stores
185 */
186
187/** TODO: Refactor to do in a single transaction */
188export const createColumn = async ({
189 projectRef,
190 connectionString,
191 payload,
192 selectedTable,
193 primaryKey,
194 foreignKeyRelations = [],
195 skipSuccessMessage = false,
196 toastId: _toastId,
197}: {
198 projectRef: string
199 connectionString?: string | null
200 payload: CreateColumnPayload
201 selectedTable: RetrieveTableResult
202 primaryKey?: Constraint
203 foreignKeyRelations?: ForeignKey[]
204 skipSuccessMessage?: boolean
205 toastId?: string | number
206}) => {
207 const toastId = _toastId ?? toast.loading(`Creating column "${payload.name}"...`)
208 try {
209 // Once pg-meta supports composite keys, we can remove this logic
210 const { isPrimaryKey, ...formattedPayload } = payload
211 await createDatabaseColumn({
212 projectRef: projectRef,
213 connectionString: connectionString,
214 payload: formattedPayload,
215 })
216
217 // Firing createColumn in createTable will bypass this block
218 if (isPrimaryKey) {
219 toast.loading('Assigning primary key to column...', { id: toastId })
220 // Same logic in createTable: Remove any primary key constraints first (we'll add it back later)
221 const existingPrimaryKeys = selectedTable.primary_keys.map((x) => x.name)
222
223 if (existingPrimaryKeys.length > 0 && primaryKey !== undefined) {
224 await dropConstraint(
225 projectRef,
226 connectionString,
227 payload.schema,
228 payload.table,
229 primaryKey.name
230 )
231 }
232
233 const primaryKeyColumns = existingPrimaryKeys.concat([formattedPayload.name])
234 await addPrimaryKey(
235 projectRef,
236 connectionString,
237 payload.schema,
238 payload.table,
239 primaryKeyColumns
240 )
241 }
242
243 // Then add the foreign key constraints here
244 if (foreignKeyRelations.length > 0) {
245 await addForeignKey({
246 projectRef,
247 connectionString,
248 table: { schema: payload.schema, name: payload.table },
249 foreignKeys: foreignKeyRelations,
250 })
251 }
252
253 if (!skipSuccessMessage) {
254 toast.success(`Successfully created column "${formattedPayload.name}"`, { id: toastId })
255 }
256 return { error: undefined }
257 } catch (error) {
258 toast.error(`An error occurred while creating the column "${payload.name}"`, { id: toastId })
259 return { error }
260 }
261}
262
263/** TODO: Refactor to do in a single transaction */
264export const updateColumn = async ({
265 projectRef,
266 connectionString,
267 originalColumn,
268 payload,
269 selectedTable,
270 primaryKey,
271 foreignKeyRelations = [],
272 existingForeignKeyRelations = [],
273 skipPKCreation,
274 skipSuccessMessage = false,
275}: {
276 projectRef: string
277 connectionString?: string | null
278 originalColumn: DeepReadonly<SafePostgresColumn>
279 payload: UpdateColumnPayload
280 selectedTable: RetrieveTableResult
281 primaryKey?: Constraint
282 foreignKeyRelations?: ForeignKey[]
283 existingForeignKeyRelations?: ForeignKeyConstraint[]
284 skipPKCreation?: boolean
285 skipSuccessMessage?: boolean
286}) => {
287 try {
288 const { isPrimaryKey, ...formattedPayload } = payload
289 await updateDatabaseColumn({
290 projectRef,
291 connectionString,
292 originalColumn,
293 payload: formattedPayload,
294 })
295
296 if (!skipPKCreation && isPrimaryKey !== undefined) {
297 const existingPrimaryKeys = selectedTable.primary_keys.map((x) => x.name)
298
299 // Primary key is getting updated for the column
300 if (existingPrimaryKeys.length > 0 && primaryKey !== undefined) {
301 await dropConstraint(
302 projectRef,
303 connectionString,
304 originalColumn.schema,
305 originalColumn.table,
306 primaryKey.name
307 )
308 }
309
310 const columnName = formattedPayload.name ?? originalColumn.name
311 const primaryKeyColumns = isPrimaryKey
312 ? existingPrimaryKeys.concat([columnName])
313 : existingPrimaryKeys.filter((x) => x !== columnName)
314
315 if (primaryKeyColumns.length) {
316 await addPrimaryKey(
317 projectRef,
318 connectionString,
319 originalColumn.schema,
320 originalColumn.table,
321 primaryKeyColumns
322 )
323 }
324 }
325
326 // Then update foreign keys
327 if (foreignKeyRelations.length > 0) {
328 await updateForeignKeys({
329 projectRef,
330 connectionString,
331 table: { schema: originalColumn.schema, name: originalColumn.table },
332 foreignKeys: foreignKeyRelations,
333 existingForeignKeyRelations,
334 })
335 }
336
337 if (!skipSuccessMessage) toast.success(`Successfully updated column "${originalColumn.name}"`)
338 } catch (error: any) {
339 return { error }
340 }
341}
342
343/** TODO: Refactor to do in a single transaction */
344export const duplicateTable = async (
345 projectRef: string,
346 connectionString: string | undefined | null,
347 payload: { name: string; comment?: string | null },
348 metadata: {
349 duplicateTable: RetrieveTableResult
350 isRLSEnabled: boolean
351 isDuplicateRows: boolean
352 foreignKeyRelations: ForeignKey[]
353 }
354) => {
355 const queryClient = getQueryClient()
356 const { duplicateTable, isRLSEnabled, isDuplicateRows, foreignKeyRelations } = metadata
357 const { name: sourceTableName, schema: sourceTableSchema } = duplicateTable
358 const duplicatedTableName = payload.name
359
360 // The following query will copy the structure of the table along with indexes, constraints and
361 // triggers. However, foreign key constraints are not duplicated over - has to be done separately
362 await executeSql({
363 projectRef,
364 connectionString,
365 sql: getDuplicateTableSQL({
366 sourceTableName,
367 sourceTableSchema,
368 duplicatedTableName,
369 comment: payload.comment,
370 }),
371 })
372 await queryClient.invalidateQueries({ queryKey: tableKeys.list(projectRef, sourceTableSchema) })
373
374 // Duplicate foreign key constraints over
375 if (foreignKeyRelations.length > 0) {
376 await addForeignKey({
377 projectRef,
378 connectionString,
379 table: { ...duplicateTable, name: payload.name },
380 foreignKeys: foreignKeyRelations,
381 })
382 }
383
384 // Duplicate rows if needed
385 if (isDuplicateRows) {
386 await executeSql({
387 projectRef,
388 connectionString,
389 sql: getDuplicateRowsSQL({
390 sourceTableName,
391 sourceTableSchema,
392 duplicatedTableName,
393 }),
394 })
395
396 // Insert into does not copy over auto increment sequences, so we manually do it next if any
397 const columns = duplicateTable.columns ?? []
398 const identityColumns = columns.filter((column) => column.identity_generation !== null)
399 identityColumns.map(async (column) => {
400 await executeSql({
401 projectRef,
402 connectionString,
403 sql: getDuplicateIdentitySequenceSQL({
404 sourceTableName,
405 sourceTableSchema,
406 duplicatedTableName,
407 columnName: column.name,
408 }),
409 })
410 })
411 }
412
413 const tables = await queryClient.fetchQuery({
414 queryKey: tableKeys.list(projectRef, sourceTableSchema),
415 queryFn: ({ signal }) =>
416 getTables({ projectRef, connectionString, schema: sourceTableSchema }, signal),
417 })
418
419 const duplicatedTable = find(tables, { schema: sourceTableSchema, name: duplicatedTableName })!
420
421 if (isRLSEnabled) {
422 await updateTableMutation({
423 projectRef,
424 connectionString,
425 id: duplicatedTable?.id!,
426 name: duplicatedTable?.name!,
427 schema: duplicatedTable?.schema!,
428 payload: { rls_enabled: isRLSEnabled },
429 })
430 }
431
432 return duplicatedTable
433}
434
435export const createTable = async ({
436 projectRef,
437 connectionString,
438 toastId,
439 payload,
440 columns = [],
441 foreignKeyRelations,
442 isRLSEnabled,
443 importContent,
444 organizationSlug,
445 generatedPolicies = [],
446 onCreatePoliciesSuccess,
447}: {
448 projectRef: string
449 connectionString?: string | null
450 toastId: string | number
451 payload: {
452 name: string
453 schema: string
454 comment?: string | null
455 }
456 columns: ColumnField[]
457 foreignKeyRelations: ForeignKey[]
458 isRLSEnabled: boolean
459 importContent?: ImportContent
460 organizationSlug?: string
461 generatedPolicies?: AcceptedGeneratedPolicy[]
462 onCreatePoliciesSuccess?: () => void
463}) => {
464 const queryClient = getQueryClient()
465
466 // Build all SQL statements for table creation, columns, and constraints
467 // to execute in a single transaction for better performance and atomicity
468 const sqlStatements: Array<SafeSqlFragment> = []
469
470 // 1. Create table SQL
471 const { sql: createTableSql } = pgMeta.tables.create({ ...payload, no_transaction: true })
472 sqlStatements.push(createTableSql)
473
474 // 2. Enable RLS if configured
475 if (isRLSEnabled) {
476 const enableRLSSQL = getEnableRLSSQL({
477 schema: payload.schema,
478 table: payload.name,
479 })
480 sqlStatements.push(enableRLSSQL)
481 }
482
483 // 3. Add columns SQL (without primary keys - those are added as constraints)
484 for (const column of columns) {
485 const columnPayload = generateCreateColumnPayload(
486 { schema: payload.schema, name: payload.name } as RetrieveTableResult,
487 { ...column, isPrimaryKey: false }
488 )
489 const { sql: columnSQL } = pgMeta.columns.create({
490 schema: columnPayload.schema,
491 table: columnPayload.table,
492 name: columnPayload.name,
493 type: columnPayload.type,
494 default_value: columnPayload.defaultValue,
495 default_value_format: columnPayload.defaultValueFormat,
496 is_identity: columnPayload.isIdentity,
497 is_nullable: columnPayload.isNullable,
498 is_primary_key: columnPayload.isPrimaryKey,
499 is_unique: columnPayload.isUnique,
500 comment: columnPayload.comment,
501 check: columnPayload.check,
502 no_transaction: true,
503 })
504 sqlStatements.push(columnSQL)
505 }
506
507 // 4. Add primary key constraint (supports composite keys)
508 const primaryKeyColumns = columns
509 .filter((column) => column.isPrimaryKey)
510 .map((column) => column.name)
511 if (primaryKeyColumns.length > 0) {
512 const primaryKeySQL = getAddPrimaryKeySQL({
513 schema: payload.schema,
514 table: payload.name,
515 columns: primaryKeyColumns,
516 })
517 sqlStatements.push(primaryKeySQL)
518 }
519
520 // 5. Add foreign key constraints
521 if (foreignKeyRelations.length > 0) {
522 const fkSql = getAddForeignKeySQL({
523 table: { schema: payload.schema, name: payload.name },
524 foreignKeys: foreignKeyRelations,
525 })
526 const fkSqlWithoutTrailingSemicolon = fkSql.replace(/;+$/, '') as SafeSqlFragment
527 sqlStatements.push(fkSqlWithoutTrailingSemicolon)
528 }
529
530 // Execute all table creation SQL in a single transaction
531 toast.loading(`Creating table ${payload.name}...`, { id: toastId })
532
533 await Sentry.startSpan(
534 { name: 'create_table.execute_sql', op: 'db.sql.transaction' },
535 async (span) => {
536 span.setAttribute('sql.statement_count', sqlStatements.length)
537 await executeSql({
538 projectRef,
539 connectionString,
540 sql: safeSql`BEGIN; ${joinSqlFragments(sqlStatements, ';\n')}; COMMIT;`,
541 queryKey: ['table', 'create-with-columns'],
542 })
543 }
544 )
545
546 // 6. Create generated RLS policies if any
547 // [Joshen] Possible area for optimization to create all policies in a single query call
548 // Can be subsequently added to the table creation SQL as well for a single transaction
549
550 const failedPolicies: AcceptedGeneratedPolicy[] = []
551 if (generatedPolicies.length > 0 && isRLSEnabled) {
552 await Sentry.startSpan(
553 { name: 'create_table.create_policies', op: 'db.policies.create' },
554 async (span) => {
555 span.setAttribute('policies.count', generatedPolicies.length)
556 toast.loading(`Creating ${generatedPolicies.length} policies for table...`, { id: toastId })
557 await Promise.all(
558 generatedPolicies.map(async (policy) => {
559 try {
560 return await createDatabasePolicy({
561 projectRef,
562 connectionString,
563 payload: {
564 name: policy.name,
565 table: policy.table,
566 schema: policy.schema,
567 definition: policy.definition,
568 check: policy.check,
569 action: policy.action,
570 command: policy.command,
571 roles: policy.roles,
572 },
573 })
574 } catch (error: any) {
575 console.error('Failed to generate policy', error.message)
576 failedPolicies.push(policy)
577 }
578 })
579 )
580 span.setAttribute('policies.failed_count', failedPolicies.length)
581 onCreatePoliciesSuccess?.()
582 }
583 )
584 }
585
586 // Track table creation event (fire-and-forget to avoid blocking)
587 sendEvent({
588 event: {
589 action: 'table_created',
590 properties: {
591 method: 'table_editor',
592 schema_name: payload.schema,
593 table_name: payload.name,
594 has_generated_policies: generatedPolicies.length > 0 && isRLSEnabled,
595 },
596 groups: {
597 project: projectRef,
598 ...(organizationSlug && { organization: organizationSlug }),
599 },
600 },
601 }).catch((error) => {
602 console.error('Failed to track table creation event:', error)
603 })
604
605 // Track RLS enablement event if enabled (fire-and-forget)
606 if (isRLSEnabled) {
607 sendEvent({
608 event: {
609 action: 'table_rls_enabled',
610 properties: {
611 method: 'table_editor',
612 schema_name: payload.schema,
613 table_name: payload.name,
614 },
615 groups: {
616 project: projectRef,
617 ...(organizationSlug && { organization: organizationSlug }),
618 },
619 },
620 }).catch((error) => {
621 console.error('Failed to track RLS enablement event:', error)
622 })
623 }
624
625 // Fetch the created table
626 const table = await Sentry.startSpan(
627 { name: 'create_table.fetch_table', op: 'db.table.fetch' },
628 async () => {
629 return await getTableQuery({
630 projectRef,
631 connectionString,
632 name: payload.name,
633 schema: payload.schema,
634 })
635 }
636 )
637
638 // If the user is importing data via a spreadsheet
639 if (importContent !== undefined) {
640 await Sentry.startSpan(
641 { name: 'create_table.import_data', op: 'db.table.import' },
642 async (span) => {
643 const rowCount = importContent.file
644 ? importContent.rowCount
645 : (importContent.rows?.length ?? 0)
646 span.setAttribute('import.row_count', rowCount)
647 span.setAttribute('import.method', importContent.file ? 'csv' : 'paste')
648
649 if (importContent.file && importContent.rowCount > 0) {
650 // Via a CSV file
651 const { error } = await insertRowsViaSpreadsheet({
652 projectRef,
653 connectionString,
654 file: importContent.file,
655 table,
656 selectedHeaders: importContent.selectedHeaders,
657 onProgressUpdate: (progress: number) => {
658 toast.loading(
659 <div className="flex flex-col space-y-2" style={{ minWidth: '220px' }}>
660 <SparkBar
661 value={progress}
662 max={100}
663 type="horizontal"
664 barClass="bg-brand"
665 labelBottom={`Adding ${importContent.rowCount.toLocaleString()} rows to ${table.name}`}
666 labelBottomClass=""
667 labelTop={`${progress.toFixed(2)}%`}
668 labelTopClass="tabular-nums"
669 />
670 </div>,
671 { id: toastId }
672 )
673 },
674 emptyStringAsNullHeaders: importContent.emptyStringAsNullHeaders,
675 })
676
677 if (error !== undefined) {
678 span.setAttribute('import.error', 1)
679 toast.error('Do check your spreadsheet if there are any discrepancies.')
680 const message = isObjectContainingKeys(error, ['message'])
681 ? String(error.message)
682 : 'An unknown error occurred during data import.'
683 toast.error(message)
684 console.error('Error:', { error, message })
685 }
686 } else {
687 // Via text copy and paste
688 await insertTableRows({
689 projectRef,
690 connectionString,
691 table,
692 rows: importContent.rows,
693 selectedHeaders: importContent.selectedHeaders,
694 onProgressUpdate: (progress: number) => {
695 toast.loading(
696 <div className="flex flex-col space-y-2" style={{ minWidth: '220px' }}>
697 <SparkBar
698 value={progress}
699 max={100}
700 type="horizontal"
701 barClass="bg-brand"
702 labelBottom={`Adding ${importContent.rows.length.toLocaleString()} rows to ${table.name}`}
703 labelTop={`${progress.toFixed(2)}%`}
704 labelTopClass="tabular-nums"
705 />
706 </div>,
707 { id: toastId }
708 )
709 },
710 emptyStringAsNullHeaders: importContent.emptyStringAsNullHeaders,
711 })
712 }
713
714 // For identity columns, manually raise the sequences (batched for performance)
715 const identityColumns = columns.filter((column) => column.isIdentity)
716 if (identityColumns.length > 0) {
717 const updateSequenceSQL = joinSqlFragments(
718 identityColumns.map((column) =>
719 getUpdateIdentitySequenceSQL({
720 schema: table.schema,
721 table: table.name,
722 column: column.name,
723 })
724 ),
725 ';\n'
726 )
727 await executeSql({
728 projectRef,
729 connectionString,
730 sql: updateSequenceSQL,
731 queryKey: ['sequences', 'update-batch'],
732 })
733 }
734 }
735 )
736 }
737
738 await Sentry.startSpan(
739 { name: 'create_table.prefetch_editor', op: 'db.table.prefetch' },
740 async () => {
741 await prefetchEditorTablePage({
742 queryClient,
743 projectRef,
744 connectionString,
745 id: table.id,
746 })
747 }
748 )
749
750 // Finally, return the created table
751 return { table, failedPolicies }
752}
753
754/** TODO: Refactor to do in a single transaction */
755export const updateTable = async ({
756 projectRef,
757 connectionString,
758 toastId,
759 table,
760 payload,
761 columns,
762 foreignKeyRelations,
763 existingForeignKeyRelations,
764 primaryKey,
765 organizationSlug,
766}: {
767 projectRef: string
768 connectionString?: string | null
769 toastId: string | number
770 table: RetrieveTableResult
771 payload: UpdateTableBody
772 columns: ColumnField[]
773 foreignKeyRelations: ForeignKey[]
774 existingForeignKeyRelations: ForeignKeyConstraint[]
775 primaryKey?: Constraint
776 organizationSlug?: string
777}) => {
778 const queryClient = getQueryClient()
779
780 // Prepare a check to see if primary keys to the tables were updated or not
781 const primaryKeyColumns = columns
782 .filter((column) => column.isPrimaryKey)
783 .map((column) => column.name)
784 const existingPrimaryKeyColumns = table.primary_keys.map((pk: PGTablePrimaryKey) => pk.name)
785 const isPrimaryKeyUpdated = !isEqual(primaryKeyColumns, existingPrimaryKeyColumns)
786
787 if (isPrimaryKeyUpdated) {
788 // Remove any primary key constraints first (we'll add it back later)
789 // If we do it later, and if the user deleted a PK column, we'd need to do
790 // an additional check when removing PK if the column in the PK was removed
791 // So doing this one step earlier, lets us skip that additional check.
792 if (primaryKey !== undefined) {
793 await dropConstraint(projectRef, connectionString, table.schema, table.name, primaryKey.name)
794 }
795 }
796
797 if (Object.keys(payload).length > 0) {
798 await updateTableMutation({
799 projectRef,
800 connectionString,
801 id: table.id,
802 name: table.name,
803 schema: table.schema,
804 payload,
805 })
806 }
807
808 // Track RLS enablement if it's being turned on
809 if (payload.rls_enabled === true) {
810 try {
811 await sendEvent({
812 event: {
813 action: 'table_rls_enabled',
814 properties: {
815 method: 'table_editor',
816 schema_name: table.schema,
817 table_name: payload.name ?? table.name,
818 },
819 groups: {
820 project: projectRef,
821 ...(organizationSlug && { organization: organizationSlug }),
822 },
823 },
824 })
825 } catch (error) {
826 console.error('Failed to track RLS enablement event:', error)
827 }
828 }
829
830 const updatedTable = await queryClient.fetchQuery({
831 queryKey: tableKeys.retrieve(
832 projectRef,
833 payload.name ?? table.name,
834 payload.schema ?? table.schema
835 ),
836 queryFn: ({ signal }) =>
837 getTable(
838 {
839 projectRef,
840 connectionString,
841 name: payload.name ?? table.name,
842 schema: payload.schema ?? table.schema,
843 },
844 signal
845 ),
846 })
847
848 const originalColumns = updatedTable.columns ?? []
849 const columnIds = columns.map((column) => column.id)
850
851 // Delete any removed columns
852 const columnsToRemove = originalColumns.filter((column) => !columnIds.includes(column.id))
853 for (const column of columnsToRemove) {
854 toast.loading(`Removing column ${column.name} from ${updatedTable.name}`, { id: toastId })
855 await deleteDatabaseColumn({
856 projectRef,
857 connectionString,
858 column,
859 })
860 }
861
862 // Add any new columns / Update any existing columns
863 let hasError = false
864 for (const column of columns) {
865 if (!column.id.includes(table.id.toString())) {
866 toast.loading(`Adding column ${column.name} to ${updatedTable.name}`, { id: toastId })
867 // Ensure that columns do not created as primary key first, cause the primary key will
868 // be added later on further down in the code
869 const columnPayload = generateCreateColumnPayload(updatedTable, {
870 ...column,
871 isPrimaryKey: false,
872 })
873 const { error } = await createColumn({
874 projectRef: projectRef,
875 connectionString: connectionString,
876 payload: columnPayload,
877 selectedTable: updatedTable,
878 skipSuccessMessage: true,
879 toastId,
880 })
881 if (!!error) hasError = true
882 } else {
883 const originalColumn = find(table.columns, { id: column.id })
884 if (originalColumn) {
885 const columnPayload = generateUpdateColumnPayload(originalColumn, updatedTable, column)
886 if (!isEmpty(columnPayload)) {
887 toast.loading(`Updating column ${column.name} from ${updatedTable.name}`, { id: toastId })
888
889 const res = await updateColumn({
890 projectRef: projectRef,
891 connectionString: connectionString,
892 // Use the updated table name and schema since the table might have been renamed
893 originalColumn: {
894 ...originalColumn,
895 table: updatedTable.name,
896 schema: updatedTable.schema,
897 },
898 payload: columnPayload,
899 selectedTable: updatedTable,
900 skipPKCreation: true,
901 skipSuccessMessage: true,
902 })
903 if (res?.error) {
904 hasError = true
905 toast.error(`Failed to update column "${column.name}": ${res.error.message}`)
906 }
907 }
908 }
909 }
910 }
911
912 // Then add back the primary keys again
913 if (isPrimaryKeyUpdated && primaryKeyColumns.length > 0) {
914 await addPrimaryKey(
915 projectRef,
916 connectionString,
917 updatedTable.schema,
918 updatedTable.name,
919 primaryKeyColumns
920 )
921 }
922
923 // Foreign keys will get updated here accordingly
924 await updateForeignKeys({
925 projectRef,
926 connectionString,
927 table: updatedTable,
928 foreignKeys: foreignKeyRelations,
929 existingForeignKeyRelations,
930 })
931
932 await Promise.all([
933 queryClient.invalidateQueries({ queryKey: tableEditorKeys.tableEditor(projectRef, table.id) }),
934 queryClient.invalidateQueries({
935 queryKey: databaseKeys.foreignKeyConstraints(projectRef, table.schema),
936 }),
937 queryClient.invalidateQueries({ queryKey: databaseKeys.tableDefinition(projectRef, table.id) }),
938 queryClient.invalidateQueries({ queryKey: entityTypeKeys.list(projectRef) }),
939 queryClient.invalidateQueries({ queryKey: tableKeys.list(projectRef, table.schema, true) }),
940 queryClient.invalidateQueries({ queryKey: lintKeys.lint(projectRef) }),
941 ])
942
943 // We need to invalidate tableRowsAndCount after tableEditor
944 // to ensure the query sent is correct
945 await queryClient.invalidateQueries({
946 queryKey: tableRowKeys.tableRowsAndCount(projectRef, table.id),
947 })
948
949 return {
950 table: await prefetchTableEditor(queryClient, {
951 projectRef,
952 connectionString,
953 id: table.id,
954 }),
955 hasError,
956 }
957}
958
959/**
960 * Used in insertRowsViaSpreadsheet + insertTableRows
961 */
962export const formatRowsForInsert = ({
963 rows,
964 headers,
965 columns = [],
966 emptyStringAsNullHeaders = headers,
967}: {
968 rows: unknown[]
969 headers: string[]
970 columns?: RetrieveTableResult['columns']
971 emptyStringAsNullHeaders?: string[]
972}) => {
973 return rows.map((row) => {
974 const formattedRow: Record<string, unknown> = {}
975 if (!isObject(row)) {
976 return formattedRow
977 }
978
979 headers.forEach((header) => {
980 const column = columns?.find((c) => c.name === header)
981
982 const originalValue = row[header]
983
984 if ((column?.format ?? '').includes('json')) {
985 formattedRow[header] = tryParseJson(originalValue)
986 } else if ((column?.data_type ?? '') === 'ARRAY') {
987 if (
988 typeof originalValue === 'string' &&
989 originalValue.startsWith('{') &&
990 originalValue.endsWith('}')
991 ) {
992 const formattedPostgresArraytoJsonArray = `[${originalValue.slice(1, originalValue.length - 1)}]`
993 formattedRow[header] = tryParseJson(formattedPostgresArraytoJsonArray)
994 } else {
995 formattedRow[header] = tryParseJson(originalValue)
996 }
997 } else if (originalValue === '') {
998 formattedRow[header] =
999 column?.is_nullable && emptyStringAsNullHeaders.includes(header) ? null : ''
1000 } else {
1001 formattedRow[header] = originalValue
1002 }
1003 })
1004 return formattedRow
1005 })
1006}
1007
1008export async function insertRowsViaSpreadsheet({
1009 projectRef,
1010 connectionString,
1011 file,
1012 table,
1013 selectedHeaders,
1014 emptyStringAsNullHeaders = selectedHeaders,
1015 onProgressUpdate,
1016}: {
1017 projectRef: string
1018 connectionString: string | undefined | null
1019 file: File
1020 table: RetrieveTableResult
1021 selectedHeaders: string[]
1022 emptyStringAsNullHeaders?: string[]
1023 onProgressUpdate: (progress: number) => void
1024}): Promise<{ error: unknown }> {
1025 let chunkNumber = 0
1026 let insertError: unknown = undefined
1027 const t1 = new Date()
1028 return new Promise((resolve) => {
1029 Papa.parse(file, {
1030 header: true,
1031 // dynamicTyping has to be disabled so that "00001" doesn't get parsed as 1.
1032 dynamicTyping: false,
1033 skipEmptyLines: true,
1034 chunkSize: CHUNK_SIZE,
1035 quoteChar: file.type === 'text/tab-separated-values' ? '' : '"',
1036 chunk: async (results, parser) => {
1037 parser.pause()
1038
1039 const formattedData = formatRowsForInsert({
1040 rows: results.data,
1041 headers: selectedHeaders,
1042 columns: table.columns,
1043 emptyStringAsNullHeaders,
1044 })
1045
1046 const insertQuery = new Query().from(table.name, table.schema).insert(formattedData).toSql()
1047 try {
1048 await executeWithRetry(() =>
1049 executeSql({ projectRef, connectionString, sql: insertQuery })
1050 )
1051 } catch (error) {
1052 console.warn(error)
1053 insertError = error
1054 parser.abort()
1055 }
1056
1057 chunkNumber += 1
1058 const progress = (chunkNumber * CHUNK_SIZE) / file.size
1059 const progressPercentage = progress > 1 ? 100 : progress * 100
1060 onProgressUpdate(progressPercentage)
1061 parser.resume()
1062 },
1063 complete: () => {
1064 const t2 = new Date()
1065 console.log(
1066 `Total time taken for importing spreadsheet: ${(t2.getTime() - t1.getTime()) / 1000} seconds`
1067 )
1068 if (insertError === undefined) {
1069 const sequenceColumns = (table.columns ?? []).filter(
1070 (column) =>
1071 column.is_identity ||
1072 (typeof column.default_value === 'string' &&
1073 column.default_value.includes('nextval('))
1074 )
1075
1076 if (sequenceColumns.length === 0) {
1077 resolve({ error: insertError })
1078 return
1079 }
1080
1081 const updateSequenceSQL = joinSqlFragments(
1082 sequenceColumns.map((column) =>
1083 getUpdateIdentitySequenceSQL({
1084 schema: table.schema,
1085 table: table.name,
1086 column: column.name,
1087 })
1088 ),
1089 ';\n'
1090 )
1091
1092 executeSql({
1093 projectRef,
1094 connectionString,
1095 sql: updateSequenceSQL,
1096 queryKey: ['sequences', 'update-batch'],
1097 })
1098 .then(() => resolve({ error: insertError }))
1099 .catch((error) => resolve({ error }))
1100 return
1101 }
1102
1103 resolve({ error: insertError })
1104 },
1105 })
1106 })
1107}
1108
1109export async function insertTableRows({
1110 projectRef,
1111 connectionString,
1112 table,
1113 rows,
1114 selectedHeaders,
1115 emptyStringAsNullHeaders = selectedHeaders,
1116 onProgressUpdate,
1117}: {
1118 projectRef: string
1119 connectionString: string | undefined | null
1120 table: RetrieveTableResult
1121 rows: unknown[]
1122 selectedHeaders: string[]
1123 emptyStringAsNullHeaders?: string[]
1124 onProgressUpdate: (progress: number) => void
1125}): Promise<{ error: unknown }> {
1126 let insertError: unknown = undefined
1127 let insertProgress = 0
1128
1129 const formattedRows = formatRowsForInsert({
1130 rows,
1131 headers: selectedHeaders,
1132 columns: table.columns,
1133 emptyStringAsNullHeaders,
1134 })
1135
1136 const batches = chunk(formattedRows, BATCH_SIZE)
1137 const tasks = batches.map((batch) => {
1138 return () => {
1139 return Promise.race([
1140 new Promise(async (resolve, reject) => {
1141 const insertQuery = new Query().from(table.name, table.schema).insert(batch).toSql()
1142 try {
1143 await executeSql({ projectRef, connectionString, sql: insertQuery })
1144 } catch (error) {
1145 insertError = error
1146 reject(error)
1147 }
1148
1149 insertProgress = insertProgress + batch.length / rows.length
1150 resolve({})
1151 }),
1152 timeout(30_000),
1153 ])
1154 }
1155 })
1156
1157 const batchedPromises = chunk(tasks, 10)
1158 for (const batchedPromise of batchedPromises) {
1159 const res = await Promise.allSettled(batchedPromise.map((batch) => batch()))
1160 const failedBatch = res.find((result) => result.status === 'rejected')
1161 if (failedBatch?.status === 'rejected') {
1162 if (insertError === undefined) insertError = failedBatch.reason
1163 break
1164 }
1165 onProgressUpdate(insertProgress * 100)
1166 }
1167
1168 if (insertError !== undefined) {
1169 return { error: insertError }
1170 }
1171
1172 const sequenceColumns = (table.columns ?? []).filter(
1173 (column) =>
1174 column.is_identity ||
1175 (typeof column.default_value === 'string' && column.default_value.includes('nextval('))
1176 )
1177
1178 if (sequenceColumns.length === 0) {
1179 return { error: insertError }
1180 }
1181
1182 const updateSequenceSQL = joinSqlFragments(
1183 sequenceColumns.map((column) =>
1184 getUpdateIdentitySequenceSQL({
1185 schema: table.schema,
1186 table: table.name,
1187 column: column.name,
1188 })
1189 ),
1190 ';\n'
1191 )
1192
1193 try {
1194 await executeSql({
1195 projectRef,
1196 connectionString,
1197 sql: updateSequenceSQL,
1198 queryKey: ['sequences', 'update-batch'],
1199 })
1200 return { error: insertError }
1201 } catch (error) {
1202 return { error }
1203 }
1204}
1205
1206const updateForeignKeys = async ({
1207 projectRef,
1208 connectionString,
1209 table,
1210 foreignKeys,
1211 existingForeignKeyRelations,
1212}: {
1213 projectRef: string
1214 connectionString?: string | null
1215 table: { schema: string; name: string }
1216 foreignKeys: ForeignKey[]
1217 existingForeignKeyRelations: ForeignKeyConstraint[]
1218}) => {
1219 // Foreign keys will get updated here accordingly
1220 const relationsToAdd = foreignKeys.filter((x) => typeof x.id === 'string')
1221 if (relationsToAdd.length > 0) {
1222 await addForeignKey({
1223 projectRef,
1224 connectionString,
1225 table,
1226 foreignKeys: relationsToAdd,
1227 })
1228 }
1229
1230 const relationsToRemove = foreignKeys.filter((x) => x.toRemove)
1231 if (relationsToRemove.length > 0) {
1232 await removeForeignKey({
1233 projectRef,
1234 connectionString,
1235 table,
1236 foreignKeys: relationsToRemove,
1237 })
1238 }
1239
1240 const remainingRelations = foreignKeys.filter((x) => typeof x.id === 'number' && !x.toRemove)
1241 const relationsToUpdate = remainingRelations.filter((x) => {
1242 const existingRelation = existingForeignKeyRelations.find((y) => x.id === y.id)
1243 if (existingRelation !== undefined) {
1244 return checkIfRelationChanged(existingRelation as unknown as ForeignKeyConstraint, x)
1245 } else return false
1246 })
1247 if (relationsToUpdate.length > 0) {
1248 await updateForeignKey({
1249 projectRef,
1250 connectionString,
1251 table,
1252 foreignKeys: relationsToUpdate,
1253 })
1254 }
1255}