Hi there!
I'm trying to test my retool apps on data in different schemas of a postgres database ("test" and "prod").
I'd like to dynamically be able to switch between those schemas without having to edit all of the sql queries that reference them within the app (where, for example, I have a query like select * from test.users).
Here's what I've tried to no avail:
-Connect to the db with a connection string that specifices the schema (doesn't seem to work per this issue: Selecting schema for CSV upload )
-Set a state variable in the app that references the schema and puts it into the query (retool renders that string as $1 within the sql query
Any thoughts on how to do this?
Thanks!
Hi!
By default, all of our SQL queries are converted to prepared statements to prevent SQL injection, meaning that table/database names and SQL functions aren't able to be defined using a string created dynamically. The main reason we currently convert all statements into prepared statements, is so that users can't enter malicious syntax (like DROP TABLE) into the variable fields.
You can disable this setting in the resource setup, but keep in mind the potential of submitting dangerous SQL through any of the variables referenced in a query. Disabling prepared statements can also break other existing queries. If that's something you'd like to explore, I often recommend setting up another copy of a resource with that setting enabled to help limit the surface area that you have to keep in mind SQL injection for.
