Skip to content

The Postgres development platform. Supabase gives you a dedicated Postgres database to build your web, mobile, and AI applications.

3.6 KB
Raw Blame
import { afterAll, expect, test } from 'vitest'

import { getTableEditorSql } from '../../../src'
import { cleanupRoot, createTestDatabase } from '../../db/utils'

afterAll(async () => {
  await cleanupRoot()
})

type Entity = {
  entity_type: string
  id: number
  schema: string
  name: string
  primary_keys: Array<{ table_id: number; schema: string; table_name: string; name: string }>
  unique_indexes: Array<{ table_id: number; schema: string; table_name: string; columns: string[] }>
  relationships: Array<{
    id: number
    constraint_name: string
    source_schema: string
    source_table_name: string
    source_column_name: string
    target_table_schema: string
    target_table_name: string
    target_column_name: string
  }>
  columns: Array<{
    name: string
    is_unique: boolean
    check: string | null
    comment: string | null
  }>
}

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(
  'scopes primary keys, unique indexes, relationships and columns to the target table',
  async ({ executeQuery }) => {
    await executeQuery(`
      create schema if not exists editor_scope;

      create table editor_scope.authors (
        id serial primary key
      );

      create table editor_scope.books (
        id serial primary key,
        isbn text not null unique,
        price numeric not null check (price > 0),
        author_id int not null references editor_scope.authors (id)
      );
      comment on column editor_scope.books.isbn is 'International Standard Book Number';

      create table editor_scope.reviews (
        id serial primary key,
        book_id int not null references editor_scope.books (id)
      );
    `)

    const [{ id: booksId }] = await executeQuery<{ id: number }[]>(
      `select 'editor_scope.books'::regclass::oid::int8 as id;`
    )

    const sql = getTableEditorSql({ id: booksId })
    const [{ entity }] = await executeQuery<{ entity: Entity }[]>(sql)

    // Basic identity.
    expect(entity.id).toBe(booksId)
    expect(entity.schema).toBe('editor_scope')
    expect(entity.name).toBe('books')

    // Primary key scoped to `books`.
    expect(entity.primary_keys).toEqual([
      { schema: 'editor_scope', table_name: 'books', table_id: booksId, name: 'id' },
    ])

    // Unique index scoped to `books`.
    expect(entity.unique_indexes).toHaveLength(1)
    expect(entity.unique_indexes[0]).toMatchObject({
      schema: 'editor_scope',
      table_name: 'books',
      table_id: booksId,
      columns: ['isbn'],
    })

    // Relationships include both the outgoing FK (books -> authors) and the
    // incoming FK (reviews -> books).
    expect(entity.relationships).toContainEqual(
      expect.objectContaining({
        source_table_name: 'books',
        source_column_name: 'author_id',
        target_table_name: 'authors',
        target_column_name: 'id',
      })
    )
    expect(entity.relationships).toContainEqual(
      expect.objectContaining({
        source_table_name: 'reviews',
        source_column_name: 'book_id',
        target_table_name: 'books',
        target_column_name: 'id',
      })
    )

    // Columns: unique flag, check constraint definition, and comment.
    const isbnCol = entity.columns.find((c) => c.name === 'isbn')!
    expect(isbnCol.is_unique).toBe(true)
    expect(isbnCol.comment).toBe('International Standard Book Number')

    const priceCol = entity.columns.find((c) => c.name === 'price')!
    expect(priceCol.check).toContain('price > 0')
  }
)