Skip to content

:hedgehog: PostHog is the leading platform for building self-driving products. Our developer tools – AI observability, analytics, session replay, flags, experiments, error tracking, logs, and more – capture all the context agents need to diagnose problems, uncover opportunities, and ship fixes. Steer it all from Slack, web, desktop, or the MCP.

5.9 KB
Raw Blame
import {
    filtersPlaceholderBindings,
    normalizeIdentifier,
    parseQueryTablesAndColumns,
    queryUsesFiltersPlaceholder,
    queryUsesPlaceholder,
} from './sql-utils'

describe('sql-utils', () => {
    describe('normalizeIdentifier', () => {
        test.each([
            ['plain identifier is lowercased', 'Events', 'events'],
            ['backtick-quoted identifier is stripped and lowercased', '`MyTable`', 'mytable'],
            ['double-quoted identifier is stripped and lowercased', '"MyColumn"', 'mycolumn'],
            ['single-quoted identifier is stripped and lowercased', "'MyField'", 'myfield'],
            ['already lowercase plain identifier is unchanged', 'events', 'events'],
            ['identifier with underscores is lowercased', 'My_Table', 'my_table'],
        ])('%s', (_name, input, expected) => {
            expect(normalizeIdentifier(input)).toEqual(expected)
        })
    })

    describe('queryUsesFiltersPlaceholder', () => {
        test.each([
            ['plain placeholder', 'SELECT * FROM events WHERE {filters}', true],
            ['field placeholder', 'SELECT * FROM events WHERE {filters.properties}', true],
            ['column-bound placeholder', 'SELECT * FROM v WHERE {filters(day AS timestamp)}', true],
            ['placeholder with inner whitespace', 'SELECT * FROM events WHERE { filters }', true],
            ['field placeholder with inner whitespace', 'SELECT * FROM events WHERE {\n  filters.properties\n}', true],
            ['line-commented placeholder', 'SELECT * FROM events\n-- {filters}', false],
            ['inline line-commented placeholder', 'SELECT * FROM events -- WHERE {filters}', false],
            ['block-commented placeholder', 'SELECT * FROM events /* WHERE {filters} */', false],
            ['single-quoted placeholder', "SELECT '{filters}' FROM events", false],
            ['double-quoted placeholder', 'SELECT "{filters}" FROM events', false],
            ['backtick-quoted placeholder', 'SELECT `{filters}` FROM events', false],
            ['real placeholder after comment', 'SELECT * FROM events -- {filters}\nWHERE {filters}', true],
            ['real placeholder after block comment', 'SELECT * FROM events /* {filters} */ WHERE {filters}', true],
        ])('%s', (_name, query, expected) => {
            expect(queryUsesFiltersPlaceholder(query)).toBe(expected)
        })
    })

    describe('queryUsesPlaceholder', () => {
        test.each([
            ['placeholder in code', 'SELECT * FROM events WHERE timestamp >= {start}', true],
            ['line-commented placeholder', 'SELECT * FROM events -- WHERE timestamp >= {start}', false],
            ['quoted placeholder', "SELECT '{start}' FROM events", false],
            ['placeholder with a longer name', 'SELECT * FROM events WHERE timestamp >= {start_date}', false],
        ])('%s', (_name, query, expected) => {
            expect(queryUsesPlaceholder(query, 'start')).toBe(expected)
        })
    })

    describe('filtersPlaceholderBindings', () => {
        test.each([
            ['no placeholder', 'SELECT * FROM v', null],
            ['plain placeholder is not column-bound', 'SELECT * FROM events WHERE {filters}', null],
            ['single binding', 'SELECT * FROM v WHERE {filters(day AS timestamp)}', ['timestamp']],
            [
                'several bindings, one quoted',
                "SELECT * FROM v WHERE {filters(created_at AS timestamp, plan AS 'plan')}",
                ['timestamp', 'plan'],
            ],
            ['null opt-out still binds the key', 'SELECT * FROM v WHERE {filters(null AS timestamp)}', ['timestamp']],
            [
                'binding over a function call keeps the outer key',
                'SELECT * FROM v WHERE {filters(toDateTime(day, 0) AS timestamp)}',
                ['timestamp'],
            ],
            ['no timestamp binding', "SELECT * FROM v WHERE {filters(plan AS 'plan')}", ['plan']],
            ['commented-out placeholder', 'SELECT * FROM v -- {filters(day AS timestamp)}', null],
            ['key case is kept', 'SELECT * FROM v WHERE {filters(day AS Timestamp)}', ['Timestamp']],
        ])('%s', (_name, query, expected) => {
            expect(filtersPlaceholderBindings(query)).toEqual(expected)
        })
    })

    describe('parseQueryTablesAndColumns', () => {
        it('returns empty object for null queryInput', async () => {
            expect(await parseQueryTablesAndColumns(null)).toEqual({})
        })

        it('returns star column for SELECT * FROM events', async () => {
            const result = await parseQueryTablesAndColumns('SELECT * FROM events')
            expect(result).toEqual({ events: { '*': true } })
        })

        it('maps bare columns to their table', async () => {
            const result = await parseQueryTablesAndColumns('SELECT id, name FROM users')
            expect(result).toEqual({ users: { id: true, name: true } })
        })

        it('assigns table-qualified column to the correct table', async () => {
            const result = await parseQueryTablesAndColumns('SELECT users.id FROM users')
            expect(result).toEqual({ users: { id: true } })
        })

        it('returns empty object for invalid SQL', async () => {
            const result = await parseQueryTablesAndColumns('NOT VALID SQL')
            expect(result).toEqual({})
        })

        it('handles star with JOIN — both tables get star', async () => {
            const result = await parseQueryTablesAndColumns(
                'SELECT * FROM events JOIN persons ON events.id = persons.id'
            )
            expect(result).toEqual({
                events: { '*': true },
                persons: { '*': true },
            })
        })

        it('handles mixed star and named columns', async () => {
            const result = await parseQueryTablesAndColumns('SELECT *, id FROM events')
            expect(result).toEqual({ events: { '*': true, id: true } })
        })
    })
})