Replies: 1 comment 1 reply
-
|
I'm not sure how do you plan to create a "secure, read-only query tool", but there is no easy/direct way to do that with just the JSVM. There is an option to scan the row result in a NullString map but you still will have to iterate through the columns and analyze its type/settings in order the result to be unmarshalized/casted into the correct format (with SQLite this is even more troublesome because in non-strict tables a single column could contain values from different types). To avoid the manual type conversions (at least for the primitive types like text, number, null, etc.), a hacky workaround could be to try to wrap your query and return the result as one big json array, something like: SELECT
json_group_array(json_object(
'col1', col1,
'col2', col2,
...
)) as result
FROM (
YOUR_QUERY
)But this still require to get at least the column names from somewhere (either as executing a dummy 0row version of the query, creating a temp view and use With that said, the scenarios where allowing any select execution from the client-side is rarely a good idea because there are a lot of edge cases and it is difficult to determine what query is safe or not, so unless you plan to allow this only for superusers, I would strongly suggest against doing it. |
Beta Was this translation helpful? Give feedback.
-
|
thanks very much |
Beta Was this translation helpful? Give feedback.
Uh oh!
There was an error while loading. Please reload this page.
-
Hello! I'm building an AI tool system that needs to execute arbitrary SELECT queries against the PocketBase database, but I'm struggling to retrieve the results when I don't know the column names in advance.
What I'm trying to do
I want to create a secure, read-only query tool that allows AI to dynamically query the database. The queries can be anything from simple SELECT id, name FROM users to aggregate queries like
SELECT COUNT(*) FROM x WHERE status = 'pending'.What I've tried
Approach 1: DynamicModel with empty shape
Result: Error: [DynamicModel] missing shape data
Approach 2: DynamicModel with predefined shape
Result: Works, but requires knowing column names in advance, which defeats the purpose.
Approach 3: Using rows() with scanMap()
Result: No error, but rowData remains empty {} after scanMap() call.
Approach 4: Using rows() with scan()
Result: Error: sql: Scan error on column index 0, name "COUNT(*)": destination not a pointer
What is the correct way to execute arbitrary SELECT queries in PocketBase JSVM and retrieve results when column names are unknown at development time?
Ideally, I'm looking for something like:
Is there a built-in method or pattern I'm missing? How do other developers handle dynamic queries in PocketBase hooks?
Environment
Any guidance would be greatly appreciated! Thank you!
Beta Was this translation helpful? Give feedback.
All reactions