Dealing with dev and prod environnement using bubble SQL connector

Hi everyone!

I’m seeking some guidance on a tricky issue we’re facing with SQL connectors in our app. We’re dealing with complex queries that involve multi-faceted filtering, and we’re running into some challenges.

The Challenge:

We cannot define an environment variable directly on the plugin and as we can’t dynamically change the Data Type on the page this results in a cumbersome process where we have to create RG twice every time we use SQL, complicating maintenance and impacting page load performance due to the increased number of elements.

Our Setup:

  • We’re using Bubble on a dedicated server.
  • We have read-only access to the SQL server.

Current Workaround:

The only solution I’ve found so far is to return a list of IDs from my SQL query, then use the Toolbox plugin to convert these into my App Thing. This then becomes my RG datasource. However, this approach isn’t ideal because:

  • It doesn’t allow for custom responses (it return all Thing columns).
  • It can’t return columns from JOIN operations, forcing us to redo this within Bubble.

What We’re Looking For:

  • A more efficient way to handle dynamic Data Types without the need for redundant RG creation.
  • Solutions or plugins that might simplify our process.
  • Any tips or best practices for managing complex SQL queries and filtering in Bubble.

Thanks in advance for any suggestions or advice you can offer!

You probably should create a new API endpoint for each SQL query.

Hi @senecadatabase

I did try the following already:

  1. API Endpoint Creation: Set up an API endpoint in the backend to handle SQL queries.
  2. Dynamic Data Handling: Use this endpoint as the datasource for our repeating group on the page.
  3. Filter and Environment Variables: Pass all filter fields and the environment variable (live or dev) to the API endpoint.
  4. SQL Query Execution: Execute the appropriate SQL query in the backend based on the environment variable.
  5. JSON Response: Return the query result as a JSON response, ensuring both live and dev calls have identical columns for consistent data types on the page.

Issues faced
When testing I got the folliwing results:

  • Using SQL connector in page: Load time = 498ms

  • Using ‘get data from an external API’ : Load time = 815ms (here I’m basically calling my own endpoint from api connector)

  • Using Javascript+Toolbox: Load time = 536ms (here basically I call my endpoint and process the result to add ‘api_c2’ prefix as needed for data type from api connector and convert into App Type with JS2Bubble)

If you do it this way, at least you can dynamically set the version. So just one repeating group. The load time stinks though. :cry: