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 {
getCreateMigrationsTableSQL,
getInsertMigrationSQL,
getMigrationsSql,
} from '../../../src/sql/studio/database/migrations'
import { cleanupRoot, createTestDatabase } from '../../db/utils'
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(
'returns zero rows (not an error) when the migrations table does not exist',
async ({ executeQuery }) => {
// Regression test: this used to throw 42P01 (undefined_table), which surfaced
// as a 400 on every dashboard load for projects without migrations.
const result = await executeQuery(getMigrationsSql())
expect(result).toEqual([])
}
)
withTestDatabase(
'returns migrations ordered by version desc when the table exists',
async ({ executeQuery }) => {
await executeQuery(getCreateMigrationsTableSQL())
await executeQuery(
getInsertMigrationSQL({
version: '20240101000000',
name: 'create_users',
statements: JSON.stringify(['create table public.users (id int primary key)']),
})
)
await executeQuery(
getInsertMigrationSQL({
version: '20240202000000',
name: `special <chars> & "quotes" 'apostrophes'`,
statements: JSON.stringify([`select '<a>&</a>' as x`, 'select 2']),
})
)
const result = await executeQuery(getMigrationsSql())
expect(result).toEqual([
{
version: '20240202000000',
name: `special <chars> & "quotes" 'apostrophes'`,
statements: [`select '<a>&</a>' as x`, 'select 2'],
},
{
version: '20240101000000',
name: 'create_users',
statements: ['create table public.users (id int primary key)'],
},
])
}
)
withTestDatabase('handles null name and statements', async ({ executeQuery }) => {
await executeQuery(getCreateMigrationsTableSQL())
await executeQuery(
`insert into supabase_migrations.schema_migrations (version) values ('20240303000000')`
)
const result = await executeQuery(getMigrationsSql())
expect(result).toEqual([{ version: '20240303000000', name: null, statements: null }])
})
withTestDatabase(
'works as the single multi-statement string pg-meta sends (statement_timeout prefix included)',
async ({ executeQuery }) => {
// postgres-meta concatenates a statement_timeout prefix and sends the whole
// thing as one simple-protocol query (a single implicit transaction), which
// is what lets the transaction-local GUC set in the do-block survive to the
// trailing select:
// https://github.com/supabase/postgres-meta/blob/master/src/lib/db.ts
await executeQuery(getCreateMigrationsTableSQL())
await executeQuery(
getInsertMigrationSQL({
version: '20240101000000',
name: 'create_users',
statements: JSON.stringify(['create table public.users (id int primary key)']),
})
)
const result = await executeQuery(
`SET statement_timeout='10s'; SET idle_session_timeout='10s';${getMigrationsSql()}`
)
expect(result).toEqual([
{
version: '20240101000000',
name: 'create_users',
statements: ['create table public.users (id int primary key)'],
},
])
}
)
withTestDatabase(
'does not leak the migrations GUC into the (pooled) session',
async ({ executeQuery }) => {
await executeQuery(getCreateMigrationsTableSQL())
await executeQuery(
getInsertMigrationSQL({
version: '20240101000000',
name: 'create_users',
statements: JSON.stringify(['create table public.users (id int primary key)']),
})
)
await executeQuery(getMigrationsSql())
// The pool is capped at one connection, so this runs on the same session.
// set_config(..., true) is transaction-local: once the query's implicit
// transaction ends, the migration payload must be gone.
const [{ value }] = await executeQuery(
`select current_setting('supabase.studio_migrations', true) as value`
)
expect([null, '']).toContain(value)
}
)
withTestDatabase(
'tolerates legacy migrations tables with only a version column',
async ({ executeQuery }) => {
// Older CLI versions created schema_migrations with a single `version` column
await executeQuery(`
create schema if not exists supabase_migrations;
create table supabase_migrations.schema_migrations (version text not null primary key);
insert into supabase_migrations.schema_migrations (version) values ('20230101000000');
`)
const result = await executeQuery(getMigrationsSql())
expect(result).toEqual([{ version: '20230101000000', name: null, statements: null }])
}
)