The Postgres development platform. Supabase gives you a dedicated Postgres database to build your web, mobile, and AI applications.
TypeScript aialternativeauthdatabasedenoembeddingsexamplefirebasenextjsoauth2pgvectorpostgispostgrespostgresqlpostgrestrealtimesupabasevectorswebsockets
import { describe, expect, it } from 'vitest'
import { QueryPerformanceSort } from './QueryPerformance.types'
import { generateQueryPerformanceSql } from './useQueryPerformanceQuery'
describe('generateQueryPerformanceSql', () => {
it('generates sql with no filters', () => {
const result = generateQueryPerformanceSql({ preset: 'mostFrequentlyInvoked' })
expect(result.sql).toBeDefined()
expect(result.whereSql).toBe('')
expect(result.orderBySql).toBeUndefined()
})
it('generates ORDER BY clause', () => {
const result = generateQueryPerformanceSql({
preset: 'mostFrequentlyInvoked',
orderBy: { column: 'calls', order: 'desc' },
})
expect(result.orderBySql).toBe('ORDER BY calls desc')
})
it('filters by roles', () => {
const result = generateQueryPerformanceSql({
preset: 'mostFrequentlyInvoked',
roles: ['postgres', 'anon'],
})
expect(result.whereSql).toContain("auth.rolname in ('postgres', 'anon')")
})
it('filters by search query', () => {
const result = generateQueryPerformanceSql({
preset: 'mostFrequentlyInvoked',
searchQuery: 'SELECT',
})
expect(result.whereSql).toContain("statements.query ~* 'SELECT'")
})
it('filters by dashboard source only', () => {
const result = generateQueryPerformanceSql({
preset: 'mostFrequentlyInvoked',
sources: ['dashboard'],
})
expect(result.whereSql).toContain("statements.query ~* 'source: dashboard'")
})
it('filters by non-dashboard source only', () => {
const result = generateQueryPerformanceSql({
preset: 'mostFrequentlyInvoked',
sources: ['non-dashboard'],
})
expect(result.whereSql).toContain("statements.query !~* 'source: dashboard'")
})
it('does not add source filter when both sources are selected', () => {
const result = generateQueryPerformanceSql({
preset: 'mostFrequentlyInvoked',
sources: ['dashboard', 'non-dashboard'],
})
expect(result.whereSql).not.toContain('source: dashboard')
})
it('filters by minimum calls', () => {
const result = generateQueryPerformanceSql({
preset: 'mostFrequentlyInvoked',
minCalls: 10,
})
expect(result.whereSql).toContain('statements.calls >= 10')
})
it('does not filter by minCalls when 0', () => {
const result = generateQueryPerformanceSql({
preset: 'mostFrequentlyInvoked',
minCalls: 0,
})
expect(result.whereSql).not.toContain('statements.calls')
})
it('filters by minimum total time', () => {
const result = generateQueryPerformanceSql({
preset: 'mostFrequentlyInvoked',
minTotalTime: 500,
})
expect(result.whereSql).toContain(
'(statements.total_exec_time + statements.total_plan_time) >= 500'
)
})
it('combines multiple WHERE conditions with AND', () => {
const result = generateQueryPerformanceSql({
preset: 'mostFrequentlyInvoked',
roles: ['postgres'],
searchQuery: 'SELECT',
minCalls: 5,
})
expect(result.whereSql).toContain("auth.rolname in ('postgres')")
expect(result.whereSql).toContain("statements.query ~* 'SELECT'")
expect(result.whereSql).toContain('statements.calls >= 5')
expect(result.whereSql.split(' AND ').length).toBe(3)
})
it('passes WHERE clause to base sql function', () => {
const result = generateQueryPerformanceSql({
preset: 'mostFrequentlyInvoked',
roles: ['postgres'],
})
expect(result.sql).toContain('WHERE')
})
it.each([
'mostFrequentlyInvoked',
'mostTimeConsuming',
'slowestExecutionTime',
'queryHitRate',
'unified',
] as const)('generates sql for the %s preset', (preset) => {
expect(generateQueryPerformanceSql({ preset }).sql.length).toBeGreaterThan(0)
})
it.each([0, -5])('clamps page=%s to page=1 (no negative offset)', (page) => {
const result = generateQueryPerformanceSql({ preset: 'unified', page, pageSize: 20 })
expect(result.sql).toContain('offset 0')
expect(result.sql).not.toMatch(/offset -\d/)
})
it('clamps pageSize above 100 to 100', () => {
const result = generateQueryPerformanceSql({ preset: 'unified', page: 1, pageSize: 9999 })
expect(result.sql).toContain('limit 100')
expect(result.sql).not.toContain('limit 9999')
})
it('applies LIMIT and OFFSET for page 2', () => {
const result = generateQueryPerformanceSql({ preset: 'unified', page: 2, pageSize: 20 })
expect(result.sql).toContain('limit 20 offset 20')
})
it('does not produce NaN in SQL when page is NaN', () => {
const result = generateQueryPerformanceSql({ preset: 'unified', page: NaN, pageSize: 20 })
expect(result.sql).not.toContain('NaN')
expect(result.sql).toContain('offset 0')
})
it('does not produce NaN in SQL when pageSize is NaN', () => {
const result = generateQueryPerformanceSql({ preset: 'unified', page: 1, pageSize: NaN })
expect(result.sql).not.toContain('NaN')
expect(result.sql).toContain('limit 20')
})
})
describe('generateQueryPerformanceSql - ORDER BY column validation', () => {
it('rejects SQL injection in orderBy column', () => {
const result = generateQueryPerformanceSql({
preset: 'mostFrequentlyInvoked',
orderBy: { column: 'calls; DROP TABLE users--' as any, order: 'asc' },
})
expect(result.orderBySql).toBeUndefined()
expect(result.sql).not.toContain('DROP TABLE')
})
it('rejects an orderBy column containing a colon (old URL format) and falls back to the default', () => {
const result = generateQueryPerformanceSql({
preset: 'mostFrequentlyInvoked',
orderBy: { column: 'created_at:asc' as any, order: 'asc' },
})
expect(result.orderBySql).toBeUndefined()
expect(result.sql).toContain('order by statements.calls desc')
})
it('accepts all valid sort columns', () => {
const columns: QueryPerformanceSort['column'][] = [
'query',
'rolname',
'total_time',
'prop_total_time',
'calls',
'avg_rows',
'max_time',
'mean_time',
'min_time',
]
for (const column of columns) {
const result = generateQueryPerformanceSql({
preset: 'mostFrequentlyInvoked',
orderBy: { column, order: 'asc' },
})
expect(result.orderBySql).toBe(`ORDER BY ${column} asc`)
}
})
})