mirror of
https://github.com/supabase/supabase.git
synced 2026-06-02 19:02:06 +08:00
d79a276824
## I have read the [CONTRIBUTING.md](https://github.com/supabase/supabase/blob/master/CONTRIBUTING.md) file. YES ## What kind of change does this PR introduce? Refactor + bug fixes (part of the SafeSql migration stack — PR 2 of 7, stacks on top of #45897). ## What is the current behavior? - `pgMeta.columns.create` and the table-editor SQL builder take column type as a string with array suffix and schema baked in (e.g. `'private.test_enum'`, `'int4[]'`). - The studio table-editor SQL emits the legacy schema-embedded `format` string for enums in non-public schemas, while the pg-meta columns SQL already returns the new shape (bare `format` + separate `format_schema`). The two queries disagree on how to represent the same column, surfacing as a false-positive type mismatch in the FK selector when both ends are an enum from a non-public schema. - The FK selector compares column types by `format` alone — same-named enums in different schemas appear equal, and arrays vs. scalars of the same base type pass the family check. - `displayColumnType` renders arrays as the raw `_typname` pg-meta emits (e.g. `_int4` instead of `int4[]`). ## What is the new behavior? **pg-meta** - Introduce `ColumnTypeRef` (`{ schema?, name, isArray? }`) for column type input, replacing the legacy string-with-array-suffix format. `pgMeta.columns.create` and the table-editor SQL builder consume the new shape. - Add `format_schema` to the column zod schema; pg-meta SQL emits the type's schema for the table editor's ColumnType dropdown. - `pgMeta.columns.create` returns a `SafeSqlFragment`. - Studio table-editor SQL now emits bare `format` + `format_schema`, matching pg-meta's columns SQL. **Studio** - `SafePostgresColumn`/`SafePostgresTable` extend the new `PG*` types (master dropped postgres-meta). - Pipe `ColumnTypeRef` through `SidePanelEditor` → `ColumnEditor` → `TableEditor`, along with the column-create mutation, table retrieve/list queries, and the `TableList`/`ColumnList` surfaces. - `displayColumnType` helper renders arrays as `type[]` (or `schema.type[]`) and handles non-implicit schemas. - FK selector now carries `sourceIsArray`/`targetIsArray` and compares the full `(format, format_schema, isArray)` triple. Family checks for numeric/text/uuid skip when either side is an array (FKs across array boundaries are never compatible). - Type-mismatch and type-notice alerts pass `isArray` to the display helper. - Bundle `Policies.utils` + `Policies.types` + `sql-policy-mutation`, `PolicyEditorModal`, and `SchemaGraph` here because `SidePanelEditor` consumes `acceptGeneratedPolicy`/`AcceptedGeneratedPolicy` — splitting requires temporary overloads with no architectural payoff. ## Additional context Part of the SafeSql migration stack. Stacks on top of #45897. ### Manual test checklist Surfaces touched by this PR — please exercise each: **Table editor** - [x] Create a new table with a mix of column types (scalar, array, enum, foreign key) - [x] Add a column to an existing table; verify the type dropdown lists scalars + arrays separately and shows schema-qualified names for non-public enums - [x] Edit an existing column's type (scalar ↔ array, switch between enums in different schemas) and save - [x] Verify enum types from a non-public schema (e.g. `private.my_enum`) display as `private.my_enum` in the column list **Foreign key selector** - [x] Open the FK selector for a column and pick a target column with a matching type — no mismatch warning - [x] Pick a target column whose type differs only by schema (two same-named enums in different schemas) — should show a type-mismatch alert - [x] Pick a target column where one side is an array and the other is a scalar of the same base type — should show a type-mismatch alert (no auto-cast across array boundary) - [x] When FK target sets the column type, verify `format_schema` and `isArray` are preserved on the source column - [x] Type-mismatch and type-notice alert messages render array types as `type[]` (not `_type`) **Column list / table list** - [x] Schema-qualified type names display correctly for columns whose type lives in a non-public schema - [x] Array columns display as `type[]` (or `schema.type[]`) **Policies (bundled due to import dependency)** - [x] Open the Policies page; create/edit/delete a row-level policy via the modal - [x] Generate a policy via the AI assistant and accept it through `SidePanelEditor` — verify the accepted policy lands in the editor correctly **Schema visualizer** - [x] Open the Schemas → Schema Visualizer page; verify it renders without type errors and shows tables/relationships <!-- This is an auto-generated comment: release notes by coderabbit.ai --> ## Summary by CodeRabbit * **Improvements** * Support for column types in non-public schemas and richer column type presentation (includes schema and array info). * Stronger SQL safety around policies and constraints; draft policy SQL is now promoted explicitly on save. * Improved foreign-key type validation and compatibility checks using enhanced type metadata. * **Tests** * Updated snapshots and tests to reflect new column metadata and SQL fragment handling. <!-- review_stack_entry_start --> [](https://app.coderabbit.ai/change-stack/supabase/supabase/pull/45903) <!-- review_stack_entry_end --> <!-- end of auto-generated comment: release notes by coderabbit.ai -->
314 lines
8.7 KiB
TypeScript
314 lines
8.7 KiB
TypeScript
import { afterAll, beforeAll, expect, test } from 'vitest'
|
|
|
|
import pgMeta from '../src/index'
|
|
import { cleanupRoot, createTestDatabase } from './db/utils'
|
|
|
|
beforeAll(async () => {
|
|
// Any global setup if needed
|
|
})
|
|
|
|
afterAll(async () => {
|
|
await cleanupRoot()
|
|
})
|
|
|
|
const withTestDatabase = (
|
|
name: string,
|
|
fn: (db: Awaited<ReturnType<typeof createTestDatabase>>) => Promise<void>
|
|
) => {
|
|
test(name, async () => {
|
|
const db = await createTestDatabase()
|
|
try {
|
|
await fn(db)
|
|
} finally {
|
|
await db.cleanup()
|
|
}
|
|
})
|
|
}
|
|
|
|
withTestDatabase('list foreign tables', async ({ executeQuery }) => {
|
|
const { sql: listSql, zod: listZod } = pgMeta.foreignTables.list()
|
|
const tables = listZod.parse(await executeQuery(listSql))
|
|
const foreignTable = tables.find(({ name }) => name === 'foreign_table')
|
|
expect(foreignTable).toMatchInlineSnapshot(
|
|
{ id: expect.any(Number) },
|
|
`
|
|
{
|
|
"columns": [
|
|
{
|
|
"check": null,
|
|
"comment": null,
|
|
"data_type": "bigint",
|
|
"default_value": null,
|
|
"enums": [],
|
|
"format": "int8",
|
|
"format_schema": "pg_catalog",
|
|
"id": "16451.1",
|
|
"identity_generation": null,
|
|
"is_generated": false,
|
|
"is_identity": false,
|
|
"is_nullable": false,
|
|
"is_unique": false,
|
|
"is_updatable": true,
|
|
"name": "id",
|
|
"ordinal_position": 1,
|
|
"schema": "public",
|
|
"table": "foreign_table",
|
|
"table_id": 16451,
|
|
},
|
|
{
|
|
"check": null,
|
|
"comment": null,
|
|
"data_type": "text",
|
|
"default_value": null,
|
|
"enums": [],
|
|
"format": "text",
|
|
"format_schema": "pg_catalog",
|
|
"id": "16451.2",
|
|
"identity_generation": null,
|
|
"is_generated": false,
|
|
"is_identity": false,
|
|
"is_nullable": true,
|
|
"is_unique": false,
|
|
"is_updatable": true,
|
|
"name": "name",
|
|
"ordinal_position": 2,
|
|
"schema": "public",
|
|
"table": "foreign_table",
|
|
"table_id": 16451,
|
|
},
|
|
{
|
|
"check": null,
|
|
"comment": null,
|
|
"data_type": "USER-DEFINED",
|
|
"default_value": null,
|
|
"enums": [
|
|
"ACTIVE",
|
|
"INACTIVE",
|
|
],
|
|
"format": "user_status",
|
|
"format_schema": "public",
|
|
"id": "16451.3",
|
|
"identity_generation": null,
|
|
"is_generated": false,
|
|
"is_identity": false,
|
|
"is_nullable": true,
|
|
"is_unique": false,
|
|
"is_updatable": true,
|
|
"name": "status",
|
|
"ordinal_position": 3,
|
|
"schema": "public",
|
|
"table": "foreign_table",
|
|
"table_id": 16451,
|
|
},
|
|
],
|
|
"comment": null,
|
|
"foreign_data_wrapper_handler": "postgres_fdw_handler",
|
|
"foreign_data_wrapper_name": "postgres_fdw",
|
|
"foreign_server_name": "foreign_server",
|
|
"id": Any<Number>,
|
|
"name": "foreign_table",
|
|
"schema": "public",
|
|
}
|
|
`
|
|
)
|
|
})
|
|
|
|
withTestDatabase('list foreign tables without columns', async ({ executeQuery }) => {
|
|
const { sql: listSql, zod: listZod } = pgMeta.foreignTables.list({ includeColumns: false })
|
|
const tables = listZod.parse(await executeQuery(listSql))
|
|
const foreignTable = tables.find(({ name }) => name === 'foreign_table')
|
|
expect(foreignTable).toMatchInlineSnapshot(
|
|
{ id: expect.any(Number) },
|
|
`
|
|
{
|
|
"comment": null,
|
|
"foreign_data_wrapper_handler": "postgres_fdw_handler",
|
|
"foreign_data_wrapper_name": "postgres_fdw",
|
|
"foreign_server_name": "foreign_server",
|
|
"id": Any<Number>,
|
|
"name": "foreign_table",
|
|
"schema": "public",
|
|
}
|
|
`
|
|
)
|
|
})
|
|
|
|
withTestDatabase('retrieve foreign table by name', async ({ executeQuery }) => {
|
|
const { sql: retrieveSql, zod: retrieveZod } = pgMeta.foreignTables.retrieve({
|
|
name: 'foreign_table',
|
|
schema: 'public',
|
|
})
|
|
const table = retrieveZod.parse((await executeQuery(retrieveSql))[0])
|
|
expect(table).toMatchInlineSnapshot(
|
|
{ id: expect.any(Number) },
|
|
`
|
|
{
|
|
"columns": [
|
|
{
|
|
"check": null,
|
|
"comment": null,
|
|
"data_type": "bigint",
|
|
"default_value": null,
|
|
"enums": [],
|
|
"format": "int8",
|
|
"format_schema": "pg_catalog",
|
|
"id": "16451.1",
|
|
"identity_generation": null,
|
|
"is_generated": false,
|
|
"is_identity": false,
|
|
"is_nullable": false,
|
|
"is_unique": false,
|
|
"is_updatable": true,
|
|
"name": "id",
|
|
"ordinal_position": 1,
|
|
"schema": "public",
|
|
"table": "foreign_table",
|
|
"table_id": 16451,
|
|
},
|
|
{
|
|
"check": null,
|
|
"comment": null,
|
|
"data_type": "text",
|
|
"default_value": null,
|
|
"enums": [],
|
|
"format": "text",
|
|
"format_schema": "pg_catalog",
|
|
"id": "16451.2",
|
|
"identity_generation": null,
|
|
"is_generated": false,
|
|
"is_identity": false,
|
|
"is_nullable": true,
|
|
"is_unique": false,
|
|
"is_updatable": true,
|
|
"name": "name",
|
|
"ordinal_position": 2,
|
|
"schema": "public",
|
|
"table": "foreign_table",
|
|
"table_id": 16451,
|
|
},
|
|
{
|
|
"check": null,
|
|
"comment": null,
|
|
"data_type": "USER-DEFINED",
|
|
"default_value": null,
|
|
"enums": [
|
|
"ACTIVE",
|
|
"INACTIVE",
|
|
],
|
|
"format": "user_status",
|
|
"format_schema": "public",
|
|
"id": "16451.3",
|
|
"identity_generation": null,
|
|
"is_generated": false,
|
|
"is_identity": false,
|
|
"is_nullable": true,
|
|
"is_unique": false,
|
|
"is_updatable": true,
|
|
"name": "status",
|
|
"ordinal_position": 3,
|
|
"schema": "public",
|
|
"table": "foreign_table",
|
|
"table_id": 16451,
|
|
},
|
|
],
|
|
"comment": null,
|
|
"foreign_data_wrapper_handler": "postgres_fdw_handler",
|
|
"foreign_data_wrapper_name": "postgres_fdw",
|
|
"foreign_server_name": "foreign_server",
|
|
"id": Any<Number>,
|
|
"name": "foreign_table",
|
|
"schema": "public",
|
|
}
|
|
`
|
|
)
|
|
})
|
|
|
|
withTestDatabase('retrieve foreign table by id', async ({ executeQuery }) => {
|
|
const { sql: retrieveSql, zod: retrieveZod } = pgMeta.foreignTables.retrieve({
|
|
id: 16451,
|
|
})
|
|
const table = retrieveZod.parse((await executeQuery(retrieveSql))[0])
|
|
expect(table).toMatchInlineSnapshot(
|
|
{ id: expect.any(Number) },
|
|
`
|
|
{
|
|
"columns": [
|
|
{
|
|
"check": null,
|
|
"comment": null,
|
|
"data_type": "bigint",
|
|
"default_value": null,
|
|
"enums": [],
|
|
"format": "int8",
|
|
"format_schema": "pg_catalog",
|
|
"id": "16451.1",
|
|
"identity_generation": null,
|
|
"is_generated": false,
|
|
"is_identity": false,
|
|
"is_nullable": false,
|
|
"is_unique": false,
|
|
"is_updatable": true,
|
|
"name": "id",
|
|
"ordinal_position": 1,
|
|
"schema": "public",
|
|
"table": "foreign_table",
|
|
"table_id": 16451,
|
|
},
|
|
{
|
|
"check": null,
|
|
"comment": null,
|
|
"data_type": "text",
|
|
"default_value": null,
|
|
"enums": [],
|
|
"format": "text",
|
|
"format_schema": "pg_catalog",
|
|
"id": "16451.2",
|
|
"identity_generation": null,
|
|
"is_generated": false,
|
|
"is_identity": false,
|
|
"is_nullable": true,
|
|
"is_unique": false,
|
|
"is_updatable": true,
|
|
"name": "name",
|
|
"ordinal_position": 2,
|
|
"schema": "public",
|
|
"table": "foreign_table",
|
|
"table_id": 16451,
|
|
},
|
|
{
|
|
"check": null,
|
|
"comment": null,
|
|
"data_type": "USER-DEFINED",
|
|
"default_value": null,
|
|
"enums": [
|
|
"ACTIVE",
|
|
"INACTIVE",
|
|
],
|
|
"format": "user_status",
|
|
"format_schema": "public",
|
|
"id": "16451.3",
|
|
"identity_generation": null,
|
|
"is_generated": false,
|
|
"is_identity": false,
|
|
"is_nullable": true,
|
|
"is_unique": false,
|
|
"is_updatable": true,
|
|
"name": "status",
|
|
"ordinal_position": 3,
|
|
"schema": "public",
|
|
"table": "foreign_table",
|
|
"table_id": 16451,
|
|
},
|
|
],
|
|
"comment": null,
|
|
"foreign_data_wrapper_handler": "postgres_fdw_handler",
|
|
"foreign_data_wrapper_name": "postgres_fdw",
|
|
"foreign_server_name": "foreign_server",
|
|
"id": Any<Number>,
|
|
"name": "foreign_table",
|
|
"schema": "public",
|
|
}
|
|
`
|
|
)
|
|
})
|