Custom SQL Query of internal Budibase Database

[conorw]

Hi,
Is it possible to run a custom SQL query against the internal Budubase database, and potentially save the output as a view?

For example:
I have a Budibase table for recording attendances that contains a date column and an email column.
For reporting, I need to identify all users who have not attended in 2 years i.e. I need to calculate the last date that each user attended and if that date is > 2 years.
I can do this with a fairly straightforward SQL query, but I dont know if this is an option in Budibase?

Thanks,
Conor

[mjashanks]

Hey Conor.

This is not currently possible with a saved view.

The Budibase internal DB is not SQL, which is what makes custom queries difficult to implement.

You should be able to achieve this by filtering a data provider. You might need to use a bit of javascript to calculate "Today - 2 years". I appreciate that views would be useful here for reusability.

If you're interested, there are a whole set of view features that we would like to implement: #2999

[daawaan4U]

Hey @mjashanks, as a temporary alternative, is it possible to give direct access to the APIs for couchdb in the meantime? It won't be a replacement for views, just an addon/band aid to fill the gaps for now

[shogunpurple]

Hi @daawaan - you should be able to use the CouchDB connector for this if you are self hosted. We can’t give direct access to the production CouchDB database in cloud for obvious reasons.

[daawaan4U]

@shogunpurple i only have general knowledge regarding securing databases so my presumption might be wrong, but how does supabase allow running custom sql queries in the internal postgresql databases in their cloud services and what are the details behind that limits budibase from providing the same thing (i.e. allowing budibase users to query directly from couchdb through code executed on the server)?

[shogunpurple]

@daawaan - I don't know for sure as I am not a maintainer of supabase, but:

However my understanding is that they either spin up a new, segregated postgres database or schema for each user, and configure the appropriate roles for each user to only allow direct queries against their own PG database or schema. With budibase, all the calls to couchDB go directly through the backend, and giving direct, production access to a couchDB cluster could potentially allow a user to mess up their own data structures, and break their app.

This isn't as much of an issue in supabase, as you can execute queries on your data (rows) only.

This isn't to say theres not a way we could provide locked down, segregated access to particular documents (rows) etc, but it would require a fair amount of work, a large security risk, and is not something that is ever asked for or requested by the community because its generally not necessary. CouchDB is also not as queriable as SQL, so it's of limited benefit. You can use the multi-filter component and data providers to get powerful filtering capabilities in your budibase apps, both in the data UI and your apps themselves.

[daawaan4U]

@shogunpurple - I see, it makes sense. For context, I was thinking of a quick alternative to executing aggregations on filtered queries with dynamic parameters since views do not currently support it yet as discussed by @mjashanks here. I thought giving direct access to couchdb would be a quick temporary solution but yeah its unfortunate to hear this wasn't case :( Looking forward to an overhaul for the views!