The Postgres development platform. Supabase gives you a dedicated Postgres database to build your web, mobile, and AI applications.
TypeScript aialternativeauthdatabasedenoembeddingsexamplefirebasenextjsoauth2pgvectorpostgispostgrespostgresqlpostgrestrealtimesupabasevectorswebsockets
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')
}
)