konnosDashboard
TableEntity.utils.test.ts208 lines · main
1import { describe, expect, it } from 'vitest'
2
3import { formatTableRowsToSQL, getTablePoliciesUrl } from './TableEntity.utils'
4import type { SupaTable } from '@/components/grid/types'
5import { ENTITY_TYPE } from '@/data/entity-types/entity-type-constants'
6
7describe('TableEntity.utils: formatTableRowsToSQL', () => {
8 it('should format rows into a single SQL INSERT statement', () => {
9 const table: SupaTable = {
10 id: 1,
11 type: ENTITY_TYPE.TABLE,
12 columns: [
13 { name: 'id', dataType: 'bigint', format: 'int8', position: 0 },
14 { name: 'name', dataType: 'text', format: 'text', position: 1 },
15 ],
16 name: 'people',
17 schema: 'public',
18 comment: undefined,
19 estimateRowCount: 1,
20 }
21 const rows = [
22 { id: 1, name: 'Person 1' },
23 { id: 2, name: 'Person 2' },
24 { id: 3, name: 'Person 3' },
25 ]
26
27 const result = formatTableRowsToSQL(table, rows)
28 const expected = `INSERT INTO "public"."people" ("id", "name") VALUES (1, 'Person 1'), (2, 'Person 2'), (3, 'Person 3');`
29 expect(result).toBe(expected)
30 })
31
32 it('should not stringify null values', () => {
33 const table: SupaTable = {
34 id: 1,
35 type: ENTITY_TYPE.TABLE,
36 columns: [
37 { name: 'id', dataType: 'bigint', format: 'int8', position: 0 },
38 { name: 'name', dataType: 'text', format: 'text', position: 1 },
39 ],
40 name: 'people',
41 schema: 'public',
42 comment: undefined,
43 estimateRowCount: 1,
44 }
45 const rows = [
46 { id: 1, name: 'Person 1' },
47 { id: 2, name: null },
48 { id: 3, name: 'Person 3' },
49 ]
50
51 const result = formatTableRowsToSQL(table, rows)
52 const expected = `INSERT INTO "public"."people" ("id", "name") VALUES (1, 'Person 1'), (2, null), (3, 'Person 3');`
53 expect(result).toBe(expected)
54 })
55
56 it('should handle PG JSON and array columns', () => {
57 const table: SupaTable = {
58 id: 1,
59 type: ENTITY_TYPE.TABLE,
60 columns: [
61 { name: 'id', dataType: 'bigint', format: 'int8', position: 0 },
62 { name: 'name', dataType: 'text', format: 'text', position: 1 },
63 { name: 'tags', dataType: 'ARRAY', format: '_text', position: 2 },
64 { name: 'metadata', dataType: 'jsonb', format: 'jsonb', position: 3 },
65 ],
66 name: 'demo',
67 schema: 'public',
68 comment: undefined,
69 estimateRowCount: 1,
70 }
71 const rows = [
72 {
73 idx: 1,
74 id: 2,
75 name: 'Person 1',
76 tags: ['tag-a', 'tag-c'],
77 metadata: '{"version": 1}',
78 },
79 {
80 idx: 2,
81 id: 3,
82 name: 'ONeil',
83 tags: ['tag-a'],
84 metadata: `{"version": 1, "name": "O'Neil"}`,
85 },
86 ]
87 const result = formatTableRowsToSQL(table, rows)
88 const expected = `INSERT INTO "public"."demo" ("id", "name", "tags", "metadata") VALUES (2, 'Person 1', ARRAY['tag-a','tag-c'], '{"version": 1}'), (3, 'ONeil', ARRAY['tag-a'], '{"version": 1, "name": "O''Neil"}');`
89 expect(result).toBe(expected)
90 })
91
92 it('should emit valid Postgres literals for booleans, numbers and text arrays', () => {
93 const table: SupaTable = {
94 id: 1,
95 type: ENTITY_TYPE.TABLE,
96 columns: [
97 { name: 'id', dataType: 'text', format: 'text', position: 0 },
98 { name: 'public', dataType: 'bool', format: 'bool', position: 1 },
99 { name: 'avif_autodetection', dataType: 'bool', format: 'bool', position: 2 },
100 { name: 'file_size_limit', dataType: 'int8', format: 'int8', position: 3 },
101 { name: 'allowed_mime_types', dataType: 'ARRAY', format: '_text', position: 4 },
102 ],
103 name: 'buckets',
104 schema: 'storage',
105 comment: undefined,
106 estimateRowCount: 1,
107 }
108 const rows = [
109 {
110 id: 'emails',
111 public: true,
112 avif_autodetection: false,
113 file_size_limit: 10485760,
114 allowed_mime_types: ['image/*', "image/o'neil"],
115 },
116 ]
117
118 const result = formatTableRowsToSQL(table, rows)
119 const expected = `INSERT INTO "storage"."buckets" ("id", "public", "avif_autodetection", "file_size_limit", "allowed_mime_types") VALUES ('emails', true, false, 10485760, ARRAY['image/*','image/o''neil']);`
120 expect(result).toBe(expected)
121 })
122
123 it('should escape fallback string formats outside text and varchar', () => {
124 const table: SupaTable = {
125 id: 1,
126 type: ENTITY_TYPE.TABLE,
127 columns: [{ name: 'email', dataType: 'USER-DEFINED', format: 'citext', position: 0 }],
128 name: 'users',
129 schema: 'public',
130 comment: undefined,
131 estimateRowCount: 1,
132 }
133 const rows = [{ email: "o'neil@example.com" }]
134
135 const result = formatTableRowsToSQL(table, rows)
136 const expected = `INSERT INTO "public"."users" ("email") VALUES ('o''neil@example.com');`
137 expect(result).toBe(expected)
138 })
139
140 it('should return an empty string for empty rows', () => {
141 const table: SupaTable = {
142 id: 1,
143 type: ENTITY_TYPE.TABLE,
144 columns: [
145 { name: 'id', dataType: 'bigint', format: 'int8', position: 0 },
146 { name: 'name', dataType: 'text', format: 'text', position: 1 },
147 ],
148 name: 'people',
149 schema: 'public',
150 comment: undefined,
151 estimateRowCount: 1,
152 }
153 const result = formatTableRowsToSQL(table, [])
154 expect(result).toBe('')
155 })
156
157 it('should remove the idx property', () => {
158 const table: SupaTable = {
159 id: 1,
160 type: ENTITY_TYPE.TABLE,
161 columns: [
162 { name: 'id', dataType: 'bigint', format: 'int8', position: 0 },
163 { name: 'name', dataType: 'text', format: 'text', position: 1 },
164 ],
165 name: 'people',
166 schema: 'public',
167 comment: undefined,
168 estimateRowCount: 1,
169 }
170 const rows = [
171 { idx: 0, id: 1, name: 'Person 1' },
172 { idx: 1, id: 2, name: 'Person 2' },
173 ]
174
175 const result = formatTableRowsToSQL(table, rows)
176 const expected = `INSERT INTO "public"."people" ("id", "name") VALUES (1, 'Person 1'), (2, 'Person 2');`
177 expect(result).toBe(expected)
178 })
179})
180
181describe('TableEntity.utils: getTablePoliciesUrl', () => {
182 it('builds the policies url for plain schema and name values', () => {
183 expect(getTablePoliciesUrl('abc', 'public', 'users')).toBe(
184 '/project/abc/auth/policies?search=users&schema=public'
185 )
186 })
187
188 it('preserves special characters in the table name', () => {
189 const url = getTablePoliciesUrl('abc', 'public', 'user_data&secret=1')
190 const parsed = new URL(url, 'http://example.com')
191 expect(parsed.searchParams.get('search')).toBe('user_data&secret=1')
192 expect(parsed.searchParams.get('schema')).toBe('public')
193 })
194
195 it('preserves special characters in the schema', () => {
196 const url = getTablePoliciesUrl('abc', 'my schema+x', 'users')
197 const parsed = new URL(url, 'http://example.com')
198 expect(parsed.searchParams.get('schema')).toBe('my schema+x')
199 expect(parsed.searchParams.get('search')).toBe('users')
200 })
201
202 it('encodes both the table name and schema together', () => {
203 const url = getTablePoliciesUrl('abc', 'a&b=c', 'd e+f')
204 const parsed = new URL(url, 'http://example.com')
205 expect(parsed.searchParams.get('search')).toBe('d e+f')
206 expect(parsed.searchParams.get('schema')).toBe('a&b=c')
207 })
208})