Since sql sandbox (Forge is not available for ODC), what are some other alternatives ? specifically for viewing, possibly fixing data in a prod environment on ODC?
Hello.
Sorry, but at the moment there are no SQL manipulation assets in ODC's Forge. No SQL Sandbox or DB Cleaner.
You have to do aggregates and SQL in the app.
It's possible to create a screen to run queries in any environment but it's a security risk, so make sure to implement it safely!
Disclaimer: I won't be responsible for any vulnerabilities introduced by implementing this.
Basically, you'll need to create a screen with an input where a user can write a query. The input value will be passed to an advanced query which will execute the query provided. In the application you implement this, you'll need to reference the entities you want to query.
To run SELECT commands:
SELECT json_agg(T) FROM (@Command) AS T
Where @Command is a Text input with 'expand inline' = yes. The output structure should have a Text attribute. The query will return a JSON, so you can use a Forge component to display it nicely in the screen (e.g. JSONPrettyFormat).
To run write commands (UPDATE, DELETE, etc):
@Command
Place each advanced query in a server action and before you run the query, you need to at least check if the current user has the application role. This is the minimum security you should implement. Please implement other measures as you see fit.