Skip to content

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

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

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

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

test('getTableDefinitionSql: escapes double quotes in schema, table, column and constraint names', async () => {
  const db = await createTestDatabase()
  try {
    await db.executeQuery(`
      create schema "pub""lic2";
      create table "pub""lic2"."te""st" (
        id bigint generated by default as identity,
        "we""ird" text not null,
        constraint "pk""test" primary key (id)
      );
    `)

    const [{ id }] = await db.executeQuery<{ id: number }[]>(
      `select '"pub""lic2"."te""st"'::regclass::oid::int8 as id;`
    )

    const sql = getTableDefinitionSql({ id })
    const [{ definition }] = await db.executeQuery<{ definition: string }[]>(sql)

    expect(definition).toContain('"pub""lic2"')
    expect(definition).toContain('"te""st"')
    expect(definition).toContain('"we""ird"')
    expect(definition).toContain('"pk""test"')

    // the generated DDL must itself be valid, executable SQL
    await db.executeQuery(`drop table "pub""lic2"."te""st";`)
    await db.executeQuery(definition)
  } finally {
    await db.cleanup()
  }
})

test('getTableDefinitionSql: quotes the parent schema for an inherited table', async () => {
  const db = await createTestDatabase()
  try {
    await db.executeQuery(`
      create schema "par""ent1";
      create table "par""ent1"."par""ent_tbl" (id bigint primary key);
      create schema "chi""ld1";
      create table "chi""ld1"."chi""ld_tbl" () inherits ("par""ent1"."par""ent_tbl");
    `)

    const [{ id }] = await db.executeQuery<{ id: number }[]>(
      `select '"chi""ld1"."chi""ld_tbl"'::regclass::oid::int8 as id;`
    )

    const sql = getTableDefinitionSql({ id })
    const [{ definition }] = await db.executeQuery<{ definition: string }[]>(sql)

    expect(definition).toContain('INHERITS ("par""ent1"."par""ent_tbl")')

    // the generated DDL must itself be valid, executable SQL
    await db.executeQuery(`drop table "chi""ld1"."chi""ld_tbl";`)
    await db.executeQuery(definition)
  } finally {
    await db.cleanup()
  }
})