The Postgres development platform. Supabase gives you a dedicated Postgres database to build your web, mobile, and AI applications.
TypeScript aialternativeauthdatabasedenoembeddingsexamplefirebasenextjsoauth2pgvectorpostgispostgrespostgresqlpostgrestrealtimesupabasevectorswebsockets
import { safeSql, untrustedSql } from '@supabase/pg-meta'
import { stripIndent } from 'common-tags'
import { describe, expect, it, test } from 'vitest'
import { sqlAiDisclaimerComment, untitledSnippetTitle } from './SQLEditor.constants'
import { DiffType, type IStandaloneCodeEditor } from './SQLEditor.types'
import {
analyzeQueryIssues,
appendEnableRLSStatements,
assembleCompletionDiff,
buildCompletionRequestBody,
buildDebugChatArgs,
buildDebugPromptText,
buildExecuteParams,
checkAlterDatabaseConnection,
checkDestructiveQuery,
computeErrorHighlightLine,
createSqlSnippetSkeletonV2,
deriveSnippetIdentity,
extractDebugContext,
filterTablesCoveredByEnsureRLSTrigger,
getCreateTablesMissingRLS,
getEditorSql,
hasActiveEnsureRLSTrigger,
hasBlockingIssues,
isUpdateWithoutWhere,
planDiffRequestApplication,
resolveConnectionString,
resolveDiffKeyAction,
shouldAutoGenerateTitle,
sqlSourceToDialect,
} from './SQLEditor.utils'
import type { DatabaseEventTrigger } from '@/data/database-event-triggers/database-event-triggers-query'
import type { Database } from '@/data/read-replicas/replicas-query'
import { createRoleImpersonationState } from '@/state/role-impersonation-state'
const buildTrigger = (overrides: Partial<DatabaseEventTrigger> = {}): DatabaseEventTrigger => ({
oid: 1,
name: 'ensure_rls',
event: 'ddl_command_end',
enabled_mode: 'ORIGIN',
tags: ['CREATE TABLE'],
function_name: 'rls_auto_enable',
function_schema: 'public',
owner: 'postgres',
function_definition: null,
...overrides,
})
describe('SQLEditor.utils.ts:shouldAutoGenerateTitle', () => {
const base = {
aiOptInLevel: 'default',
snippetName: `${untitledSnippetTitle} 1`,
isPlatform: true,
} as const
test.each([
['AI is enabled, the name is still the placeholder, and on platform', {}, true],
['AI opt-in is disabled', { aiOptInLevel: 'disabled' }, false],
['the snippet already has a custom name', { snippetName: 'My saved query' }, false],
['not running on the hosted platform', { isPlatform: false }, false],
['the snippet name is undefined', { snippetName: undefined }, false],
] as const)('when %s -> %s', (_, overrides, expected) => {
expect(shouldAutoGenerateTitle({ ...base, ...overrides })).toBe(expected)
})
})
describe('SQLEditor.utils.ts:buildExecuteParams', () => {
const impersonatedRoleState = createRoleImpersonationState('default', {
current: async () => ({}),
})
const base = {
limit: 100,
connectionString: 'postgresql://example',
projectRef: 'default',
impersonatedRoleState,
}
test('applies the auto-limit suffix when appropriate', () => {
const params = buildExecuteParams({ ...base, sql: safeSql`select * from countries` })
expect(params.sql).toBe('select * from countries limit 100;')
expect(params.autoLimit).toBe(100)
})
test('leaves autoLimit undefined when a limit is already present', () => {
const params = buildExecuteParams({ ...base, sql: safeSql`select * from countries limit 10;` })
expect(params.sql).toBe('select * from countries limit 10;')
expect(params.autoLimit).toBeUndefined()
})
test('reflects isRoleImpersonationEnabled from the impersonation state', () => {
const params = buildExecuteParams({ ...base, limit: 0, sql: safeSql`select * from countries` })
expect(params.isRoleImpersonationEnabled).toBe(false)
expect(params.isStatementTimeoutDisabled).toBe(true)
expect(params.contextualInvalidation).toBe(true)
})
})
type SnippetsMap = Record<string, { snippet: { content?: unknown } }>
describe('SQLEditor.utils.ts:deriveSnippetIdentity', () => {
const loaded = { snippet: { content: { some: 'content' } } }
const noContent = { snippet: { content: undefined } }
test.each<
[string, string | undefined, SnippetsMap | undefined, { id: string; isLoading: boolean }]
>([
['no url id, generated id not in map', undefined, {}, { id: 'generated-id', isLoading: true }],
[
'no url id, generated id in map',
undefined,
{ 'generated-id': loaded },
{ id: 'generated-id', isLoading: false },
],
[
'url id "new" is never loading',
'new',
{ 'generated-id': noContent },
{ id: 'generated-id', isLoading: false },
],
['url id not yet in the store', 'existing-id', {}, { id: 'existing-id', isLoading: true }],
[
'url id with no content yet',
'existing-id',
{ 'existing-id': noContent },
{ id: 'existing-id', isLoading: true },
],
[
'url id with content',
'existing-id',
{ 'existing-id': loaded },
{ id: 'existing-id', isLoading: false },
],
['snippets map missing', 'existing-id', undefined, { id: 'existing-id', isLoading: true }],
[
'entry has no snippet',
'existing-id',
{ 'existing-id': {} as SnippetsMap[string] },
{ id: 'existing-id', isLoading: true },
],
])('%s', (_, urlId, snippets, expected) => {
expect(
deriveSnippetIdentity({
urlId,
generatedId: 'generated-id',
snippets: snippets as SnippetsMap,
})
).toEqual(expected)
})
})
const buildDebugSnippet = (uncheckedSql: string) => ({
snippet: { content: { unchecked_sql: untrustedSql(uncheckedSql) } },
})
describe('SQLEditor.utils.ts:extractDebugContext', () => {
test('strips the AI disclaimer comment and trims the sql', () => {
const snippet = buildDebugSnippet(`${sqlAiDisclaimerComment}\n\nselect 1;`)
const result = { error: { message: 'relation does not exist' } }
expect(extractDebugContext(snippet, result)).toEqual({
sql: 'select 1;',
errorMessage: 'relation does not exist',
})
})
test.each([
['the snippet is undefined', undefined],
['the snippet has no content yet', { snippet: { content: undefined } }],
])('falls back to an empty sql when %s', (_, snippet) => {
expect(extractDebugContext(snippet, { error: { message: 'boom' } })).toEqual({
sql: '',
errorMessage: 'boom',
})
})
test.each([
['the result is undefined', undefined],
['the result has no error message', {}],
])('falls back to "Unknown error" when %s', (_, result) => {
expect(extractDebugContext(buildDebugSnippet('select 1;'), result)).toEqual({
sql: 'select 1;',
errorMessage: 'Unknown error',
})
})
})
describe('SQLEditor.utils.ts:buildDebugChatArgs', () => {
test('builds the newChat payload from the snippet sql and error message', () => {
const snippet = buildDebugSnippet('select 1;')
const result = { error: { message: 'relation does not exist' } }
expect(buildDebugChatArgs(snippet, result, 'database')).toEqual({
name: 'Debug SQL snippet',
sqlSnippets: [{ label: 'Current Query', content: 'select 1;', source: 'database' }],
initialInput:
'Help me to debug the attached sql snippet which gives the following error: \n\nrelation does not exist',
})
})
// The attachment is what puts sqlSource on the message the user then submits, so
// the debug flow has to attach a sourced snippet, not a bare string.
test('attaches the query with its source and names the dialect', () => {
const snippet = buildDebugSnippet('select count(*) from logs;')
const result = { error: { message: 'Unknown expression identifier' } }
const args = buildDebugChatArgs(snippet, result, 'logs')
expect(args.sqlSnippets).toEqual([
{ label: 'Current Query', content: 'select count(*) from logs;', source: 'logs' },
])
expect(args.initialInput).toContain('Unknown expression identifier')
expect(args.initialInput).toContain('ClickHouse')
expect(args.initialInput).toContain('not Postgres')
})
})
describe('SQLEditor.utils.ts:buildCompletionRequestBody', () => {
const base = { projectRef: 'default', connectionString: 'postgresql://example', orgSlug: 'acme' }
test('builds the base request body and merges options on top', () => {
expect(buildCompletionRequestBody(base)).toEqual({ ...base, language: 'sql' })
expect(
buildCompletionRequestBody({
...base,
options: { completionMetadata: { prompt: 'add a where clause' } },
})
).toEqual({
...base,
language: 'sql',
completionMetadata: { prompt: 'add a where clause' },
})
})
test('omits dialect when not provided, so the route keeps its Postgres default', () => {
expect(buildCompletionRequestBody(base)).not.toHaveProperty('dialect')
expect(buildCompletionRequestBody({ ...base, dialect: 'clickhouse' }).dialect).toBe(
'clickhouse'
)
})
})
describe('SQLEditor.utils.ts:sqlSourceToDialect', () => {
test('logs snippets get ClickHouse, database snippets get Postgres', () => {
expect(sqlSourceToDialect('logs')).toBe('clickhouse')
expect(sqlSourceToDialect('database')).toBe('postgres')
})
})
describe('SQLEditor.utils.ts:createSqlSnippetSkeletonV2', () => {
const baseArgs = { name: 'Untitled', sql: 'select 1', owner_id: 1, project_id: 1 }
test.each([
['defaults to a database (sql) snippet', undefined, 'sql'],
['builds a database snippet for source "database"', 'database', 'sql'],
['builds a logs (log_sql) snippet for source "logs"', 'logs', 'log_sql'],
] as const)('%s', (_, source, type) => {
const snippet = createSqlSnippetSkeletonV2({ ...baseArgs, source, idOverride: 'snippet-id' })
expect(snippet.type).toBe(type)
expect(snippet.status).toBe('new')
expect(snippet.content?.unchecked_sql).toBe('select 1')
expect(snippet.content?.content_id).toBe('snippet-id')
})
})
describe('SQLEditor.utils.ts:planDiffRequestApplication', () => {
test('replaces the editor content when it is empty', () => {
const plan = planDiffRequestApplication({
existingValue: '',
request: { diffType: DiffType.Modification, sql: 'select 1;' },
})
expect(plan).toEqual({ kind: 'replace', text: 'select 1;' })
})
test.each([DiffType.Modification, DiffType.NewSnippet])(
'opens a %s diff against the existing content when it is not empty',
(diffType) => {
const plan = planDiffRequestApplication({
existingValue: 'select 0;',
request: { diffType, sql: 'select 1;' },
})
expect(plan).toEqual({
kind: 'diff',
diff: { original: 'select 0;', modified: 'select 1;' },
diffType,
})
}
)
})
describe('SQLEditor.utils.ts:resolveDiffKeyAction', () => {
type KeyEvent = Pick<KeyboardEvent, 'key' | 'metaKey' | 'ctrlKey'>
type Ctx = Parameters<typeof resolveDiffKeyAction>[1]
const diffOpen: Ctx = { isDiffOpen: true, isPromptOpen: false, os: 'macos' }
const promptOpen: Ctx = { isDiffOpen: false, isPromptOpen: true, os: 'macos' }
const key = (k: string, mods: Partial<KeyEvent> = {}): KeyEvent => ({
key: k,
metaKey: false,
ctrlKey: false,
...mods,
})
test.each<[string, KeyEvent, Ctx, ReturnType<typeof resolveDiffKeyAction>]>([
[
'nothing when neither a diff nor the prompt is open',
key('Escape'),
{ ...diffOpen, isDiffOpen: false },
{ type: 'none' },
],
['nothing for keys other than Enter/Escape', key('a'), diffOpen, { type: 'none' }],
['accept on Cmd+Enter on macOS', key('Enter', { metaKey: true }), diffOpen, { type: 'accept' }],
[
'accept on Ctrl+Enter on Windows',
key('Enter', { ctrlKey: true }),
{ ...diffOpen, os: 'windows' },
{ type: 'accept' },
],
[
'nothing on Ctrl+Enter on macOS (wrong modifier)',
key('Enter', { ctrlKey: true }),
diffOpen,
{ type: 'none' },
],
['nothing on Enter without a modifier', key('Enter'), diffOpen, { type: 'none' }],
[
'nothing on Cmd+Enter when only the prompt is open',
key('Enter', { metaKey: true }),
promptOpen,
{ type: 'none' },
],
[
'escape and discard when a diff is open',
key('Escape'),
diffOpen,
{ type: 'escape', shouldDiscard: true },
],
[
'escape without discard when only the prompt is open',
key('Escape'),
promptOpen,
{ type: 'escape', shouldDiscard: false },
],
])('%s', (_, event, ctx, expected) => {
expect(resolveDiffKeyAction(event, ctx)).toEqual(expected)
})
})
describe('SQLEditor.utils.ts:checkDestructiveQuery', () => {
const doBlock = (body: string) => `DO $$\nBEGIN\n ${body}\nEND\n$$;`
test.each([
'drop table films, distributors;',
'truncate films;',
"delete from films where kind <> 'Musical';",
"DELETE FROM films WHERE kind <> 'Musical';",
"select * from films;\n\ndelete from films where kind <> 'Musical';",
"delete from countries; update countries set name = 'hello';",
'ALTER TABLE test DROP COLUMN test;',
'TRUNCATE TABLE test;',
])('flags plain destructive statement: %s', (sql) => {
expect(checkDestructiveQuery(sql)).toBe(true)
})
test.each([
// string literal / format / concatenation
`EXECUTE 'DROP TABLE users';`,
`EXECUTE 'DELETE FROM users';`,
`EXECUTE 'TRUNCATE TABLE users';`,
`EXECUTE format('DROP TABLE %I', table_name);`,
`EXECUTE format('DELETE FROM %I', table_name);`,
`EXECUTE format('TRUNCATE TABLE %I', table_name);`,
`EXECUTE 'DROP TABLE ' || table_name;`,
`EXECUTE 'DELETE FROM ' || table_name;`,
`execute 'drop table users';`,
`EXECUTE Format('DROP TABLE users');`,
// EXECUTE IMMEDIATE / OPEN cursor / RETURN QUERY
`EXECUTE IMMEDIATE 'DROP TABLE users';`,
`EXECUTE IMMEDIATE 'DELETE FROM users';`,
`EXECUTE IMMEDIATE 'TRUNCATE TABLE users';`,
`OPEN ref FOR EXECUTE 'DROP TABLE users';`,
`OPEN ref FOR EXECUTE format('DELETE FROM %I', tbl);`,
`OPEN ref FOR EXECUTE 'TRUNCATE TABLE ' || tbl;`,
`RETURN QUERY EXECUTE 'DROP TABLE users';`,
`RETURN QUERY EXECUTE format('DELETE FROM %I', tbl);`,
// dollar-quoted, concat, E-strings
`EXECUTE $sql$DROP TABLE users$sql$;`,
`EXECUTE $body$TRUNCATE TABLE logs$body$;`,
`EXECUTE concat('DROP TABLE ', 'users');`,
`EXECUTE concat_ws(' ', 'DELETE FROM', 'users');`,
`EXECUTE E'DROP TABLE users';`,
// ALTER TABLE ... DROP COLUMN through every dynamic form
`EXECUTE 'ALTER TABLE users DROP COLUMN email';`,
`EXECUTE format('ALTER TABLE %I DROP COLUMN %I', 'users', 'email');`,
`EXECUTE IMMEDIATE 'ALTER TABLE users DROP COLUMN email';`,
`OPEN ref FOR EXECUTE 'ALTER TABLE users DROP COLUMN email';`,
`RETURN QUERY EXECUTE 'ALTER TABLE users DROP COLUMN email';`,
`EXECUTE $sql$ALTER TABLE users DROP COLUMN email$sql$;`,
`EXECUTE concat('ALTER TABLE ', 'users DROP COLUMN email');`,
// DO blocks
doBlock(`EXECUTE 'DROP TABLE users';`),
doBlock(`EXECUTE 'ALTER TABLE users DROP COLUMN email';`),
stripIndent`
DO $$
BEGIN
FOR rec IN SELECT tablename FROM pg_tables WHERE schemaname = 'public' LOOP
EXECUTE format('DROP TABLE %I', rec.tablename);
END LOOP;
END
$$;
`,
stripIndent`
DO $$
DECLARE
func_name text;
BEGIN
FOR func_name IN
SELECT routine_name FROM information_schema.routines WHERE routine_schema = 'public'
LOOP
EXECUTE format('DROP FUNCTION %I', func_name);
END LOOP;
END
$$;
`,
])('flags destructive dynamic SQL: %s', (sql) => {
expect(checkDestructiveQuery(sql)).toBe(true)
})
test.each([
stripIndent`
create policy "Users can delete their own files"
on storage.objects for delete to authenticated using (
bucket id = 'files' and (select auth.uid()) = owner
);
`,
'-- Going to drop this in here, might delete later\nselect * from films;',
`EXECUTE 'SELECT * FROM users';`,
`EXECUTE 'INSERT INTO users (name) VALUES (''test'')';`,
`EXECUTE 'UPDATE users SET name = ''test'' WHERE id = 1';`,
`EXECUTE 'ALTER TABLE users ADD COLUMN email text';`,
`sql := 'SELECT * FROM users';`,
`OPEN ref FOR EXECUTE 'SELECT * FROM users';`,
`RETURN QUERY EXECUTE 'SELECT * FROM users';`,
])('does not flag: %s', (sql) => {
expect(checkDestructiveQuery(sql)).toBe(false)
})
})
describe('SQLEditor.utils:isUpdateWithoutWhere', () => {
test.each([
`UPDATE public.countries SET name = 'New Name';`,
`UPDATE "public"."countries" SET name = 'New Name';`,
`UPDATE "messages" SET id = 1;`,
`UPDATE "public".messages SET id = 1;`,
`UPDATE public."messages" SET id = 1;`,
`UPDATE "my table" SET id = 1;`,
`UPDATE "weird""name" SET id = 1;`,
// the word "where" inside an identifier or string literal is not a where clause
`UPDATE "where table" SET id = 1;`,
`UPDATE messages SET name = 'where x';`,
`delete from countries; update countries set name = 'hello';`,
])('flags update without a where clause: %s', (sql) => {
expect(isUpdateWithoutWhere(sql)).toBe(true)
})
test.each([
`UPDATE public.countries SET name = 'New Name' WHERE id = 1;`,
`UPDATE "public"."countries" SET name = 'New Name' WHERE id = 1;`,
`UPDATE "messages" SET id = 1 WHERE id = 2;`,
`UPDATE messages SET name = 'where x' WHERE id = 1;`,
])('does not flag update with a where clause: %s', (sql) => {
expect(isUpdateWithoutWhere(sql)).toBe(false)
})
})
describe('SQLEditor.utils:getCreateTablesMissingRLS', () => {
it('flags a basic CREATE TABLE without RLS', () => {
const result = getCreateTablesMissingRLS('create table foo (id int8 primary key);')
expect(result).toEqual([{ schema: undefined, tableName: 'foo' }])
})
it('flags CREATE TABLE IF NOT EXISTS', () => {
const result = getCreateTablesMissingRLS(
'create table if not exists foo (id int8 primary key);'
)
expect(result).toHaveLength(1)
expect(result[0].tableName).toBe('foo')
})
it('flags schema-qualified CREATE TABLE', () => {
const result = getCreateTablesMissingRLS('create table public.foo (id int8 primary key);')
expect(result).toEqual([{ schema: 'public', tableName: 'foo' }])
})
it('flags quoted identifiers', () => {
const result = getCreateTablesMissingRLS(
'create table "public"."user_table" (id int8 primary key);'
)
expect(result).toEqual([{ schema: 'public', tableName: 'user_table' }])
})
it('flags quoted identifiers containing spaces', () => {
const result = getCreateTablesMissingRLS(
'create table "public"."My Table" (id int8 primary key);'
)
expect(result).toEqual([{ schema: 'public', tableName: 'My Table' }])
})
it('matches RLS to a table whose name contains spaces', () => {
const sql = stripIndent`
create table "My Table" (id int8 primary key);
alter table "My Table" enable row level security;
`
expect(getCreateTablesMissingRLS(sql)).toEqual([])
})
it('does not flag when ENABLE ROW LEVEL SECURITY is in the same SQL', () => {
const sql = stripIndent`
create table foo (id int8 primary key);
alter table foo enable row level security;
`
expect(getCreateTablesMissingRLS(sql)).toEqual([])
})
it('does not flag when ENABLE RLS shorthand is in the same SQL', () => {
const sql = stripIndent`
create table foo (id int8 primary key);
alter table foo enable rls;
`
expect(getCreateTablesMissingRLS(sql)).toEqual([])
})
it('matches RLS to the right table when multiple tables created', () => {
const sql = stripIndent`
create table foo (id int8 primary key);
create table bar (id int8 primary key);
alter table foo enable row level security;
`
const result = getCreateTablesMissingRLS(sql)
expect(result).toHaveLength(1)
expect(result[0].tableName).toBe('bar')
})
it('does not flag when CREATE TABLE is inside a comment', () => {
const sql = stripIndent`
-- create table foo (id int8 primary key);
select 1;
`
expect(getCreateTablesMissingRLS(sql)).toEqual([])
})
it('does not flag when there is no CREATE TABLE at all', () => {
expect(getCreateTablesMissingRLS('select * from foo;')).toEqual([])
})
it('schema-qualified RLS matches schema-qualified CREATE', () => {
const sql = stripIndent`
create table public.foo (id int8 primary key);
alter table public.foo enable row level security;
`
expect(getCreateTablesMissingRLS(sql)).toEqual([])
})
it('does not flag when ALTER TABLE IF EXISTS enables RLS', () => {
const sql = stripIndent`
CREATE TABLE IF NOT EXISTS public."Conversations" (id int8 primary key);
ALTER TABLE IF EXISTS public."Conversations" ENABLE ROW LEVEL SECURITY;
GRANT ALL ON TABLE public."Conversations" TO postgres, anon, authenticated, service_role;
`
expect(getCreateTablesMissingRLS(sql)).toEqual([])
})
it('flags CREATE TEMP TABLE', () => {
const result = getCreateTablesMissingRLS('create temp table foo (id int8 primary key);')
expect(result).toHaveLength(1)
expect(result[0].tableName).toBe('foo')
})
it('does not flag `select ... into var` inside a plpgsql function body', () => {
// Regression: the SELECT..INTO detector used to match variable assignments
// inside $$...$$ function bodies and surface them as \"new tables\".
const sql = stripIndent`
create or replace function schema_checks()
returns jsonb
language plpgsql
as $$
declare
ret jsonb;
begin
select jsonb_build_object('value', 'ok')
into ret;
return ret;
end;
$$;
`
expect(getCreateTablesMissingRLS(sql)).toEqual([])
})
it('does not flag `select ... into var` inside a DO block', () => {
const sql = stripIndent`
do $$
declare
result int;
begin
select count(*) into result from information_schema.tables;
end
$$;
`
expect(getCreateTablesMissingRLS(sql)).toEqual([])
})
it('does not flag CREATE TABLE text that appears inside a function body', () => {
const sql = stripIndent`
create or replace function noop()
returns void
language plpgsql
as $$
begin
-- create table foo (id int);
perform 1;
end;
$$;
`
expect(getCreateTablesMissingRLS(sql)).toEqual([])
})
it('flags top-level CREATE TABLE alongside a function with INTO assignments', () => {
const sql = stripIndent`
create table public.foo (id int8 primary key);
create or replace function bar()
returns int
language plpgsql
as $$
declare
v int;
begin
select 1 into v;
return v;
end;
$$;
`
const result = getCreateTablesMissingRLS(sql)
expect(result).toEqual([{ schema: 'public', tableName: 'foo' }])
})
it('does not flag CREATE TABLE inside nested dollar-quoted dynamic SQL', () => {
// Regression: the `$sql$...$sql$` block inside the outer `$fn$...$fn$`
// body was previously pairing with the outer tag, letting the inner
// semicolon split the statement and exposing `create table fake` to the
// RLS warning.
const sql = stripIndent`
create function f()
returns void
language plpgsql
as $fn$
begin
execute $sql$create table fake(id int);$sql$;
end;
$fn$;
`
expect(getCreateTablesMissingRLS(sql)).toEqual([])
})
it('handles custom dollar-quote tags (e.g. $body$...$body$)', () => {
const sql = stripIndent`
create or replace function f()
returns int
language plpgsql
as $body$
declare
v int;
begin
select 1 into v;
return v;
end;
$body$;
`
expect(getCreateTablesMissingRLS(sql)).toEqual([])
})
it('does not collide quoted identifiers that differ only by case', () => {
// "MyTable" and "mytable" are distinct tables in Postgres, so the ALTER
// here targets a different table than the CREATE — the warning must fire.
const sql = stripIndent`
create table "MyTable" (id int8 primary key);
alter table "mytable" enable row level security;
`
const result = getCreateTablesMissingRLS(sql)
expect(result).toHaveLength(1)
expect(result[0].tableName).toBe('MyTable')
})
})
describe('SQLEditor.utils:appendEnableRLSStatements', () => {
it('appends a single ALTER TABLE ENABLE RLS statement', () => {
const result = appendEnableRLSStatements('create table foo (id int8 primary key);', [
{ tableName: 'foo' },
])
expect(result).toContain('ALTER TABLE foo ENABLE ROW LEVEL SECURITY;')
})
it('appends one ALTER per table', () => {
const result = appendEnableRLSStatements(
'create table foo (id int8); create table bar (id int8);',
[{ tableName: 'foo' }, { tableName: 'bar' }]
)
expect(result).toContain('ALTER TABLE foo ENABLE ROW LEVEL SECURITY;')
expect(result).toContain('ALTER TABLE bar ENABLE ROW LEVEL SECURITY;')
})
it('schema-qualifies the table when schema is provided', () => {
const result = appendEnableRLSStatements('create table public.foo (id int8);', [
{ schema: 'public', tableName: 'foo' },
])
expect(result).toContain('ALTER TABLE public.foo ENABLE ROW LEVEL SECURITY;')
})
it('quotes identifiers that are not simple', () => {
const result = appendEnableRLSStatements('create table "My Table" (id int8);', [
{ tableName: 'My Table' },
])
expect(result).toContain('ALTER TABLE "My Table" ENABLE ROW LEVEL SECURITY;')
})
it('quotes mixed-case identifiers so Postgres does not fold them to lowercase', () => {
const result = appendEnableRLSStatements('create table "MyTable" (id int8);', [
{ tableName: 'MyTable' },
])
expect(result).toContain('ALTER TABLE "MyTable" ENABLE ROW LEVEL SECURITY;')
})
it('quotes mixed-case schema and table identifiers', () => {
const result = appendEnableRLSStatements('create table "MySchema"."MyTable" (id int8);', [
{ schema: 'MySchema', tableName: 'MyTable' },
])
expect(result).toContain('ALTER TABLE "MySchema"."MyTable" ENABLE ROW LEVEL SECURITY;')
})
it('returns the original SQL unchanged when there are no tables', () => {
const sql = 'select 1;'
expect(appendEnableRLSStatements(sql, [])).toBe(sql)
})
it('puts the terminator on its own line when SQL ends with a line comment', () => {
// Without this, the appended ';' would be swallowed by the line comment and
// the following ALTER TABLE would be parsed as part of the CREATE TABLE.
const sql = stripIndent`
create table foo (id int)
-- forgot the semicolon
`
const result = appendEnableRLSStatements(sql, [{ tableName: 'foo' }])
expect(result).toMatch(/-- forgot the semicolon\n;\n/)
expect(result).toContain('ALTER TABLE foo ENABLE ROW LEVEL SECURITY;')
})
})
describe('SQLEditor.utils:hasActiveEnsureRLSTrigger', () => {
test.each<[string, DatabaseEventTrigger[] | undefined, boolean]>([
['undefined triggers', undefined, false],
['an empty list', [], false],
['an active "ensure_rls" trigger', [buildTrigger()], true],
[
'a renamed trigger using rls_auto_enable',
[buildTrigger({ name: 'something_else', function_name: 'rls_auto_enable' })],
true,
],
['a DISABLED matching trigger', [buildTrigger({ enabled_mode: 'DISABLED' })], false],
[
'unrelated triggers',
[buildTrigger({ name: 'audit_log', function_name: 'log_changes' })],
false,
],
])('%s -> %s', (_, triggers, expected) => {
expect(hasActiveEnsureRLSTrigger(triggers)).toBe(expected)
})
})
describe('SQLEditor.utils:filterTablesCoveredByEnsureRLSTrigger', () => {
it('returns the input unchanged when the trigger is not present', () => {
const tables = [{ tableName: 'foo' }, { schema: 'private', tableName: 'bar' }]
expect(filterTablesCoveredByEnsureRLSTrigger(tables, false)).toEqual(tables)
})
it('drops public-schema tables when the trigger is present', () => {
const tables = [
{ schema: 'public', tableName: 'foo' },
{ tableName: 'bar' }, // no schema → defaults to public
]
expect(filterTablesCoveredByEnsureRLSTrigger(tables, true)).toEqual([])
})
it('keeps tables in non-public schemas when the trigger is present', () => {
const tables = [
{ schema: 'public', tableName: 'foo' },
{ schema: 'private', tableName: 'bar' },
{ schema: 'app', tableName: 'baz' },
]
expect(filterTablesCoveredByEnsureRLSTrigger(tables, true)).toEqual([
{ schema: 'private', tableName: 'bar' },
{ schema: 'app', tableName: 'baz' },
])
})
it('matches the public schema case-insensitively', () => {
const tables = [{ schema: 'PUBLIC', tableName: 'foo' }]
expect(filterTablesCoveredByEnsureRLSTrigger(tables, true)).toEqual([])
})
})
describe('SQLEditor.utils:checkAlterDatabaseConnection', () => {
test.each([
'alter database postgres connection limit 0;',
'alter database postgres allow_connections false;',
'ALTER DATABASE postgres CONNECTION LIMIT 0;',
'select * from countries;\nalter database postgres connection limit 0;',
])('detects: %s', (sql) => {
expect(checkAlterDatabaseConnection(sql)).toBe(true)
})
test.each([
'alter database postgres set statement_timeout = 60000;',
'select * from countries;',
'-- alter database postgres connection limit 0;\nselect 1;',
])('does not flag: %s', (sql) => {
expect(checkAlterDatabaseConnection(sql)).toBe(false)
})
})
// Minimal fake editor for exercising getEditorSql's selection/value logic.
const makeEditor = ({
value,
selectionValue,
hasSelection = selectionValue !== undefined,
}: {
value: string | undefined
selectionValue?: string
hasSelection?: boolean
}): IStandaloneCodeEditor =>
({
getValue: () => value,
getSelection: () => (hasSelection ? { startLineNumber: 1 } : null),
getModel: () => ({ getValueInRange: () => selectionValue }),
}) as unknown as IStandaloneCodeEditor
describe('SQLEditor.utils:getEditorSql', () => {
it('returns the selected text when there is a selection', () => {
const editor = makeEditor({ value: 'select 1;', selectionValue: 'select 2;' })
expect(getEditorSql(editor)).toBe('select 2;')
})
it('returns the full editor value when there is no selection', () => {
const editor = makeEditor({ value: 'select 1;', hasSelection: false })
expect(getEditorSql(editor)).toBe('select 1;')
})
it('falls back to the full value when the selection is empty', () => {
const editor = makeEditor({ value: 'select 1;', selectionValue: '' })
expect(getEditorSql(editor)).toBe('select 1;')
})
it('falls back to snippet content only when there is no editor value', () => {
// Real Monaco always returns a string from getValue(); this guards the
// last-resort snippet-content fallback contract.
const editor = makeEditor({ value: undefined, hasSelection: false })
expect(getEditorSql(editor, untrustedSql('stored sql'))).toBe('stored sql')
})
})
describe('SQLEditor.utils:computeErrorHighlightLine', () => {
it('parses the LINE marker and offsets by the selection start line', () => {
const error = { formattedError: 'ERROR: syntax error\nLINE 3: slect 1;\n ^' }
expect(computeErrorHighlightLine(error, 0)).toBe(3)
expect(computeErrorHighlightLine(error, 5)).toBe(8)
})
it('returns NaN when there is no LINE marker', () => {
expect(computeErrorHighlightLine({ formattedError: 'boom' }, 0)).toBeNaN()
})
it('returns NaN when formattedError is missing', () => {
expect(computeErrorHighlightLine({}, 2)).toBeNaN()
})
})
describe('SQLEditor.utils:assembleCompletionDiff', () => {
it('assembles original and modified from before/selection/after', () => {
const result = assembleCompletionDiff(
{ textBeforeCursor: 'a ', selection: 'b', textAfterCursor: ' c' },
'X'
)
expect(result).toEqual({ original: 'a b c', modified: 'a X c' })
})
it('treats missing metadata fields as empty strings', () => {
expect(assembleCompletionDiff({}, 'X')).toEqual({ original: '', modified: 'X' })
})
})
describe('SQLEditor.utils:buildDebugPromptText', () => {
it('builds the debug prompt with the error message and SQL block', () => {
const result = buildDebugPromptText('select 1;', 'relation does not exist', 'database')
expect(result).toContain('relation does not exist')
expect(result).toContain('```sql\nselect 1;\n```')
expect(result).not.toContain('ClickHouse')
})
// This text is copyable and gets pasted into external models, so it has to name
// the dialect itself rather than relying on the message metadata.
it('names the dialect for a logs snippet', () => {
const sql = "select count() from logs where source = 'edge_logs'"
const result = buildDebugPromptText(sql, 'Unknown expression identifier', 'logs')
expect(result).toContain('Unknown expression identifier')
expect(result).toContain('ClickHouse')
expect(result).toContain('not Postgres')
expect(result).toContain('```clickhouse\n' + sql + '\n```')
})
})
describe('SQLEditor.utils:analyzeQueryIssues', () => {
it('flags a destructive query with no event triggers', () => {
expect(analyzeQueryIssues('drop table countries;', undefined)).toEqual({
hasDestructiveOperations: true,
hasUpdateWithoutWhere: false,
hasAlterDatabasePreventConnection: false,
createTablesMissingRLS: [],
})
})
it('filters out a CREATE TABLE covered by an active ensure_rls trigger', () => {
const result = analyzeQueryIssues('create table foo (id int);', [buildTrigger()])
expect(result.createTablesMissingRLS).toEqual([])
})
it('reports no issues for a clean select query', () => {
expect(analyzeQueryIssues('select * from countries;', undefined)).toEqual({
hasDestructiveOperations: false,
hasUpdateWithoutWhere: false,
hasAlterDatabasePreventConnection: false,
createTablesMissingRLS: [],
})
})
})
describe('SQLEditor.utils:hasBlockingIssues', () => {
const noIssues = {
hasDestructiveOperations: false,
hasUpdateWithoutWhere: false,
hasAlterDatabasePreventConnection: false,
createTablesMissingRLS: [],
}
test.each([
['no issues', {}, false],
['hasDestructiveOperations', { hasDestructiveOperations: true }, true],
['hasUpdateWithoutWhere', { hasUpdateWithoutWhere: true }, true],
['hasAlterDatabasePreventConnection', { hasAlterDatabasePreventConnection: true }, true],
['createTablesMissingRLS non-empty', { createTablesMissingRLS: [{ tableName: 'foo' }] }, true],
])('%s -> %s', (_, overrides, expected) => {
expect(hasBlockingIssues({ ...noIssues, ...overrides }, false)).toBe(expected)
})
it('returns false when force is true, even with issues present', () => {
expect(hasBlockingIssues({ ...noIssues, hasDestructiveOperations: true }, true)).toBe(false)
})
})
const buildDatabase = (overrides: Partial<Database> = {}): Database => ({
cloud_provider: 'AWS',
connectionString: 'postgres://primary',
connection_string_read_only: 'postgres://primary',
db_host: 'db.example.com',
db_name: 'postgres',
db_port: 5432,
db_user: 'postgres',
identifier: 'primary',
inserted_at: '2024-01-01T00:00:00Z',
region: 'us-east-1',
restUrl: 'https://example.com',
size: 'micro',
status: 'ACTIVE_HEALTHY',
...overrides,
})
describe('SQLEditor.utils:resolveConnectionString', () => {
it('returns the matching database connection string', () => {
const databases = [
buildDatabase({ identifier: 'primary', connectionString: 'postgres://primary' }),
buildDatabase({ identifier: 'replica-1', connectionString: 'postgres://replica-1' }),
]
expect(resolveConnectionString(databases, 'replica-1')).toBe('postgres://replica-1')
})
it('returns undefined when no database matches', () => {
const databases = [buildDatabase({ identifier: 'primary' })]
expect(resolveConnectionString(databases, 'unknown')).toBeUndefined()
})
it('returns undefined when databases is undefined', () => {
expect(resolveConnectionString(undefined, 'primary')).toBeUndefined()
})
it('normalizes a null connectionString to undefined', () => {
const databases = [buildDatabase({ identifier: 'primary', connectionString: null })]
expect(resolveConnectionString(databases, 'primary')).toBeUndefined()
})
})