PowerSync should log/throw an error for invalid query syntax

[kevinschaich]

I was debugging a query for about half an hour in PowerSync only to realize the query was not actually valid SQL.

Was trying to figure out why I was getting zero rows back but it was failing silently even with log level set to DEBUG. Upon pasting the same query into Supabase and running it I figured it out the issue and then copying that modified query back to PowerSync solved my issue.

It seems weird and bug-prone for PowerSync to not give me any indication that this query is failing against SQLite.

[rkistner]

That is indeed not expected.

Was this a query in the sync rules or your app? If it's in sync rules, was that with the cloud version or self-hosted? If it's in the app, which platform did you get the issue on? (web / react-native-quick-sqlite / react-native with op-sqlite / nodejs)

[kevinschaich]

This is a query in my React Native / Expo app.

package.json:

"@op-engineering/op-sqlite": "^14.0.2",
"@powersync/op-sqlite": "^0.5.6",
"@powersync/react-native": "^1.20.2",
"expo": "~53.0.5",
"react-native": "0.79.2",

/app/my-page.tsx:

import {View} from 'react-native'
import {useMessages} from '~/lib/powersync'

export const Component = () => {
    const messages = useMessages()

    return <View>{/* ... */}</View>
}

/lib/powersync/index.ts:

import { useAuth } from '@clerk/clerk-expo'
import { OPSqliteOpenFactory } from '@powersync/op-sqlite'
import {
    AbstractPowerSyncDatabase,
    createBaseLogger,
    LogLevel,
    PowerSyncBackendConnector,
    PowerSyncDatabase,
} from '@powersync/react-native'
import { useEffect, useMemo, useState } from 'react'
import { AppSchema, Database, SmsMessage } from '~/lib/powersync/schema'

const logger = createBaseLogger()
logger.useDefaults()
logger.setLevel(LogLevel.DEBUG)

export const sqlite = new OPSqliteOpenFactory({
    dbFilename: 'sqlite.db',
})

export const powersync = new PowerSyncDatabase({ database: sqlite, schema: AppSchema })

export class Connector implements PowerSyncBackendConnector {
    private getClerkToken: ReturnType<typeof useAuth>['getToken']

    constructor(getClerkToken: ReturnType<typeof useAuth>['getToken']) {
        this.getClerkToken = getClerkToken
    }

    fetchCredentials = async () => {
        const token = await this.getClerkToken({ template: 'PowerSync' })
        return token ? { token, endpoint: process.env.EXPO_PUBLIC_POWERSYNC_ENDPOINT! } : null
    }

    // TODO: NOOP: Implement uploadData if we prefer to use PowerSync vs. API calls
    uploadData = async (database: AbstractPowerSyncDatabase) => {}
}

export const usePowerSync = () => {
    const { getToken } = useAuth()

    useEffect(() => {
        if (getToken) {
            const connector = new Connector(getToken)
            powersync.connect(connector)
        }
    }, [getToken])

    return powersync
}

export type GenericQueryResult<T> = {
    /** Represents the auto-generated row id if applicable. */
    insertId?: number
    /** Number of affected rows if result of a update query. */
    rowsAffected: number
    /** if status is undefined or 0 this object will contain the query results */
    rows?: {
        /** Raw array with all dataset */
        _array: T[]
        /** The length of the dataset */
        length: number
        /** A convenience function to acess the index based the row object
         * @param idx the row index
         * @returns the row structure identified by column names
         */
        item: (idx: number) => T
    }
}

export const usePowerSyncQuery = <T>(sql: string, parameters?: any[]): { result: GenericQueryResult<T> } => {
    const powersync = usePowerSync()
    const [result, setData] = useState<GenericQueryResult<T>>({ rowsAffected: 0 })

    const paramsString = useMemo(() => {
        return JSON.stringify(parameters)
    }, [parameters])

    useEffect(
        () => {
            const abortController = new AbortController()

            ;(async () => {
                for await (const update of powersync.watch(sql, parameters, { signal: abortController.signal })) {
                    setData(update as GenericQueryResult<T>)
                }
            })()

            return () => {
                abortController.abort()
            }
        },
        // Parameters are an array and if we don't stringify them it will use a shallow equals and cause infinite loop
        // eslint-disable-next-line react-hooks/exhaustive-deps
        [sql, paramsString, powersync],
    )

    return { result }
}

export const useMessages = (): SmsMessage[] => {
    const { result } = usePowerSyncQuery<Database['sms_message']>(`SELECT * FROM sms_message`)
    return result.rows?._array
}

[kevinschaich]

@rkistner any ideas here

[stevensJourney]

@kevinschaich The powersync.watch query in your code example should throw an exception if the provided SQL is not valid. I've added a unit test for thrown exceptions as a test here. Could you share an example query which does not throw an exception?

[stevensJourney]

I'll close this one due to inactivity. I've verified that the async iterator watch method should throw errors for SQL errors in #615.

Readers of this issue should also note that our React Hooks package provides the functionality shown in the snippet above including error reporting.