AI Agents & MCPs & AI Workflow Automation • (~400 MCP servers for AI agents) • AI Automation / AI Agent with MCPs • AI Workflows & AI Agents • MCPs for AI Agents
TypeScript ai-agentai-agent-toolsai-agentsai-agents-frameworkmcpmcp-servermcp-toolsmcpsn8n-alternativeno-code-automationworkflowworkflow-automationworkflows
import { describe, expect, it } from 'vitest';
import {
assertReadOnlyStatement,
buildOrderBy,
buildWhere,
identifier,
identifierValue,
qualifiedName,
sqlString,
} from '../src/lib/common/sql';
import { normalizePrivateKey } from '../src/lib/common/private-key';
import { buildCopyInto, stageReference } from '../src/lib/common/stage';
import { buildInsert } from '../src/lib/common/operations';
import { buildMerge } from '../src/lib/common/merge';
describe('identifiers', () => {
it('keeps plain and quoted identifiers, quotes everything else', () => {
expect(identifier({ name: 'orders' })).toBe('orders');
expect(identifier({ name: '"My Table"' })).toBe('"My Table"');
expect(identifier({ name: 'my table' })).toBe('"my table"');
expect(identifier({ name: 'x"; DROP TABLE y; --' })).toBe('"x""; DROP TABLE y; --"');
expect(() => identifier({ name: ' ' })).toThrow('empty');
});
it('splits qualified names outside quotes', () => {
expect(qualifiedName({ value: 'DB.PUBLIC.ORDERS' })).toBe('DB.PUBLIC.ORDERS');
expect(qualifiedName({ value: 'DB."a.b".T' })).toBe('DB."a.b".T');
expect(qualifiedName({ value: 'db.my schema.t' })).toBe('db."my schema".t');
expect(() => qualifiedName({ value: 'a.b.c.d' })).toThrow('not valid');
expect(() => qualifiedName({ value: 'a..b' })).toThrow('not valid');
expect(() => qualifiedName({ value: '"open' })).toThrow('unclosed');
});
it('resolves stored identifier values', () => {
expect(identifierValue('orders')).toBe('ORDERS');
expect(identifierValue('"orders"')).toBe('orders');
});
it('escapes string literals', () => {
expect(sqlString("it's \\ ok")).toBe("'it''s \\\\ ok'");
});
});
describe('read-only guard', () => {
it('accepts read statements and strips one trailing semicolon', () => {
expect(assertReadOnlyStatement('SELECT 1;')).toBe('SELECT 1');
expect(assertReadOnlyStatement('-- c\nWITH a AS (SELECT 1) SELECT * FROM a')).toContain('WITH');
expect(assertReadOnlyStatement("SELECT 'DELETE FROM x' AS s")).toContain('DELETE');
expect(assertReadOnlyStatement('SHOW TABLES')).toBe('SHOW TABLES');
expect(assertReadOnlyStatement('SELECT "update" FROM t')).toContain('"update"');
});
it('rejects writes, multiple statements and SYSTEM$ calls', () => {
expect(() => assertReadOnlyStatement('DELETE FROM t')).toThrow('read-only');
expect(() => assertReadOnlyStatement('SELECT 1; DROP TABLE t')).toThrow('Only one statement');
expect(() => assertReadOnlyStatement('WITH a AS (SELECT 1) DELETE FROM t')).toThrow('DELETE');
expect(() => assertReadOnlyStatement('/* x */ INSERT INTO t VALUES (1)')).toThrow('INSERT');
expect(() => assertReadOnlyStatement("SELECT SYSTEM$CANCEL_QUERY('x')")).toThrow('SYSTEM$');
expect(() => assertReadOnlyStatement('SELECT 1 /* open')).toThrow('unclosed');
expect(() => assertReadOnlyStatement(' ')).toThrow('empty');
});
});
describe('filters and sort', () => {
it('turns filters into bound conditions', () => {
const where = buildWhere([
{ column: 'status', operator: '=', value: 'open' },
{ column: 'id', operator: 'in', value: '1, 2' },
{ column: 'deleted_at', operator: 'IS NULL' },
]);
expect(where.clause).toBe(' WHERE status = ? AND id IN (?, ?) AND deleted_at IS NULL');
expect(where.binds).toEqual(['open', '1', '2']);
});
it('rejects unknown operators and missing values', () => {
expect(() => buildWhere([{ column: 'a', operator: '; DROP' }])).toThrow('not supported');
expect(() => buildWhere([{ column: 'a', operator: '=' }])).toThrow('needs a value');
expect(buildWhere(undefined).clause).toBe('');
});
it('builds a validated ORDER BY', () => {
expect(buildOrderBy([{ column: 'created_at', direction: 'desc' }])).toBe(' ORDER BY created_at DESC');
expect(() => buildOrderBy([{ column: 'a', direction: 'sideways' }])).toThrow('ASC or DESC');
});
});
describe('private key normalisation', () => {
const body = 'A'.repeat(100);
it('rebuilds PEM lines from a key pasted with spaces', () => {
const pem = normalizePrivateKey(`-----BEGIN PRIVATE KEY----- ${body.slice(0, 50)} ${body.slice(50)} -----END PRIVATE KEY-----`);
expect(pem.split('\n')).toEqual(['-----BEGIN PRIVATE KEY-----', 'A'.repeat(64), 'A'.repeat(36), '-----END PRIVATE KEY-----']);
});
it('keeps the ENCRYPTED header', () => {
const pem = normalizePrivateKey(`-----BEGIN ENCRYPTED PRIVATE KEY-----\n${body}\n-----END ENCRYPTED PRIVATE KEY-----\n`);
expect(pem.startsWith('-----BEGIN ENCRYPTED PRIVATE KEY-----\n')).toBe(true);
expect(pem.endsWith('-----END ENCRYPTED PRIVATE KEY-----')).toBe(true);
});
it('accepts a bare body and rejects garbage', () => {
expect(normalizePrivateKey(body)).toContain('-----BEGIN PRIVATE KEY-----');
expect(() => normalizePrivateKey('not a key!')).toThrow('not valid PEM');
});
});
describe('stages and COPY', () => {
it('builds stage references safely', () => {
expect(stageReference({ stage: '@db.public.my_stage', path: 'data/' })).toBe('@db.public.my_stage/data/');
expect(stageReference({ stage: 'my_stage/data' })).toBe('@my_stage/data');
expect(() => stageReference({ stage: 's', path: "x'; DROP" })).toThrow('not allowed');
expect(() => stageReference({ stage: 's', path: '../x' })).toThrow('not allowed');
expect(() => stageReference({ stage: '~' })).toThrow('not supported');
});
it('validates enums in COPY INTO', () => {
expect(buildCopyInto({ table: 'T', stage: '@S', fileFormat: 'CSV', skipHeader: 1, onError: 'CONTINUE', pattern: ".*'x" })).toBe(
"COPY INTO T FROM @S FILE_FORMAT = (TYPE = 'CSV' SKIP_HEADER = 1) PATTERN = '.*''x' ON_ERROR = 'CONTINUE'"
);
expect(() => buildCopyInto({ table: 'T', stage: '@S', fileFormat: "CSV' x" })).toThrow('File format');
expect(() => buildCopyInto({ table: 'T', stage: '@S', fileFormat: 'CSV', onError: 'NOPE' })).toThrow('On Error');
});
});
describe('insert and merge builders', () => {
it('uses VALUES for scalars and PARSE_JSON for objects', () => {
expect(buildInsert({ table: 'T', rows: [{ a: 1, b: 'x' }] })).toEqual({ sql: 'INSERT INTO T (a, b) VALUES (?, ?)', binds: [1, 'x'] });
expect(buildInsert({ table: 'T', rows: [{ a: 1, tags: ['x'] }] })).toEqual({
sql: 'INSERT INTO T (a, tags) SELECT ?, PARSE_JSON(?)',
binds: [1, '["x"]'],
});
expect(() => buildInsert({ table: 'T', rows: [{ a: 1 }, { b: 2 }] })).toThrow('different set of columns');
});
it('quotes merge columns and binds through aliases', () => {
const merge = buildMerge({ table: 'T', keyColumns: ['id'], values: { id: 1, 'first name': 'Jo' } });
expect(merge.sql).toContain('ON target.id = source.C0');
expect(merge.sql).toContain('target."first name" = source.C1');
expect(merge.binds).toEqual([1, 'Jo']);
expect(() => buildMerge({ table: 'T', keyColumns: ['id'], values: { id: 1 } })).toThrow('other than the key');
});
});