Using an SQL database as a backend is a necessity for those of us who need to build complex data-heavy apps.
Main reasons why you need a SQL backend:
-
Data automation: Certain apps are data heavy and require lots of ongoing automation (such as updating millions on rows on a constant basis.)
Try making this automation on Bubble and see if you don’t max out your 175k WUs in the first week. It’s a lot quicker and cheaper to simply hook a script to your SQL database to perform all your automation externally.
-
Ability to migrate from bubble: The only table I would keep on bubble is the User table while storing everything else on an external backend. For example, let’s say you have a table that stores a users favourite products, it’s easier to migrate if you have an SQL table with ([user_id], [product_id]) columns rather than a bubble table that stores the list of product objects in the User table.
Main challenges you may face:
-
Bubble’s SQL Connector: Bubble users who need to securely connect to a SQL database require a static IP address from bubble to whitelist. However, static IPs are only available on the enterprise price plan which is expensive for most users.
-
Setting up an API for your SQL database: You need an API connector to read and write from your database if you don’t have bubble’s enterprise plan. The no-code API solutions are quite expensive (E.g. DreamFactory can be setup within 5 mins but costs $1.5k/mo)
The Solution
The best way that I’ve found to read and write SQL data from your bubble to app is with the use of Azure Functions.
This solves all the above challenges by acting as the API endpoint you need to interact with your database using bubble’s API connector.
Price is also affordable as you get 1 million free API call requests per month + pennies for any additional million API requests.
Along with Azure functions, there’s also the Data API builder which I plan on looking into also.
These are just my findings based on joining bubble last month and trying to find the best solution for my Azure backend.
Would be great to hear other peoples experiences with using Azure as a bubble backend.
8 Likes
Thanks for this, I had a question, after whitelisting those 6ish ip’s, how long have you had your app running without issues? I’m developing an app that requires Azure connection and am trying to find the most cost effective route.
@andy31 I personally wouldn’t go down the route of whitelisting non-dedicated IPs to a production app because your query request will fail whenever a new IP requires whitelisting. The API method is the only secure and reliable option for non-enterprise users.
However, @doug.burden should be able to advice based on his experience.
1 Like
I haven’t experienced that problem for the few months that I did that however I still think the best option is to create an azure server less function you can call with the api connector is going to more safer, more reliable, and more solid.
@EliteDataEngineer Nice thread! Your solution was tailored to your existing use of Azure SQL.
I’m starting from scratch. I’m curious, if you had to start a db from scratch, do you think Xano or Backendless could handle the sort of capacity you’re referring to ? (ie, upload + export + CRUD on millions of rows on a regular basis)
Is there a reason you would want to stick with a more traditional stack?
@maximvallee I’ve never used Xano or Backendless, so I can’t speak on their capacity.
However, if starting from scratch, I would still choose SQL/code over those two.
Why? Simply because using nocode inherently has limitations compared to using code.
I’m happy to use nocode for frontend, however, I would never wish to put a limit on what I’m able to do on the backend since that’s ultimately what matters the most.
That’s what separates an advanced Saas/product from a mediocre one.
Now, with that being said, I’m not sure what the true limits of nocode backends are since I’ve never used them. Is it 50% of what I can do using python + SQL or is it closer to 95%? I can’t really say. Someone with its experience may be able to advice.
If you know how to code, choosing a nocode frontend makes sense due to the speed benefit of not worrying about CSS/HTML, however, I can’t think of a reason why I would choose a nocode backend… especially with new tools like Perplexity that will write your code for you.
@EliteDataEngineer Hey, thanks for your reply! I’m not familiar with Microsoft’s solutions but I’ve worked with Django rest framework for years so I’m thinking setting up some endpoints to hit a Postgres database hosted on Heroku. I’m also exploring Supabase since they’re using Postgres, so might be easier to migrate in the future.
I’ve never integrated an external database with Bubble before. One of my concerns is accessing realtime data. Doesn’t seem like we can use websockets. Has this been an issue on your end?
@maximvallee The need for realtime updates when using an external backend can be broken down into two scenarios
Scenario #1 of 2: User triggered action
Case: This is when you need to display the results of a user’s action.
For example, a user may want to add an item to a repeating group, which you then want to immediately display on the RG.
Solution: Simply create a workflow that updates the group or repeating group after the action is made using Display Lists/Data
Scenario # 2 of 2: Non-triggered data update
Case: You may have a sales dashboard whose figure you want to update each time a new sale is made.
Solution: I’ve never had the need for non-triggered updates in my app, so I’ve not spent any time on finding an optimal solution. However, my off the cuff approach would be to either refresh the data every set interval or to use web hooks if possible.
2 Likes
@EliteDataEngineer Yes, for scenario 2, I was thinking of setting up a webhook.
Another question for you. Do you manage the app’s users on Bubble or Azure?
@maximvallee I manage users on Bubble.
Its ‘Current Users…’ design feature makes it convenient to work with, along with its other account workflow features.
Also, the user table is so critical that I would want it to sit within bubble itself.
Edit: The most important reason actually is because a lot of the external tables you’ll be using will have 'Where current user = " filters.
This makes things a lot quicker to call the bubble user table than to have to set the users ID in a custom state for each page load and workflow sequence.
1 Like
@EliteDataEngineer Man, this is really interesting + useful. I don’t mean to turn this post into a chatroom, but…
If your user object lives on Bubble, how do you go about ensuring that users can only access/manipulate their own records? I suspect you’ve set up some privacy policies on your SQL database and you send a user-specific authorization key on each request? In other words, how did you structure your database so that each created record points to a user on Bubble and ensure that they are authenticated AND authorized ?
I was inclined to do the exact opposite, which is to create and manage all users on the external database. But then, to your point, it’d be a hassle to use Bubble’s built in functions like “If user logged in…”, etc. without native Bubble users.
Doing it the way you describe seems to make more sense.
@maximvallee I don’t use privacy policies since I’m working with external tables.
Instead, I create user_x tables
So for example, a list of the items that a user likes would go into a user_like table that contains two columns: [user_id] & [item_id]
Then in bubble, I simply return every row where user_id = current user. That way, users only see their own records.
This logic can be applied to every scenario
1 Like
@EliteDataEngineer So that “user_id” is referring to the user’s unique id generated by Bubble?
Maybe there’s something I don’t understand, but if user_id is the only parameter you’re passing when querying your SQL database, what prevents someone from fetching a user’s private data if they know a user’s id? Aren’t you passing an authorization token? Seems like the db is exposed otherwise.
Yes, user_id refers to bubble’s generated id.
An auth key is also used, as would be the case for any secure API connection. (Goes without saying)
Authorisation is separate from privacy rules. The latter isn’t needed when using an external backend.
Looking at this discussion, I will go for APIs through an Azure function app.
I don’t want to have to manage a whitelist of multiple and changing Bubble IP addresses.
APIs in a function app will be more robust and more secure.
1 Like
Great choice.
Over a year since making this thread and Azure functions have proven to be even more versatile than I imagined – serving as a powerful backend for not just data management but also general functionalities, such as sending automated emails based on user actions, and other features that are limited on bubble.
… and zero costs incurred so far due to the free consumption plan. (Outside of expected database costs.)
3 Likes
Hi @EliteDataEngineer, Your article is very helpful, I am also learning to create a bubble application and use Azure DB, I thought to use SQL Database Connector but will have to create queries, as I have read about using Function App to process data between them, I do not have much experience using Azure service as well as Bubble, do you mind helping me configure it?
@truonglam.classic Sure, happy to help out.
There are essentially 2 stages;
- Create your API using Azure Functions
- Connect your API to Bubble using the API Connector
Those two tutorials are a good starting point.
Let me know if you have any specific questions regarding config or anything else.
1 Like
