Now that Bubble has so many database integration options (Blockspring + Google Sheets, Database plugin, etc.), I’m having a hard time deciding what to use
Does anyone have any opinions or positive/negative experiences to share?
The use-case is a simple data visualization:
I have a data viz of health and demographic data (so it’s not small by any means, but I don’t need to scale it). The dataset doesn’t need to be modified, but I’d like users to be able to play with different filters.
For now, I’ve stored the table on my Bubble app and have been using “Search for…” in Bubble’s frontend to filter the data. However, when I try to do slightly more complicated calculations (ie, find the largest number in the set and divide all other numbers by it, to resize the bar chart’s bars [not using the Data Charts plugin for aesthetic reasons] ) it really slows down the website, since I manually had to create a workflow that compares all the numbers one by one and arrives at the largest (yeah, so clunky, but I was foolishly trying to arrive at a quick fix).
Doing these calculations at the query stage will make it faster, I’m sure …
Are there any opinions out there about which solution (of all the ones available to Bubble users) is the least workflow-intensive and cheap (ideally, free?)
I was having a play with some data viz tools recently.
Klipfolio connected very easily to the Bubble API and it looked like I could do some fairly complex manipulation. And also embed the dashboard back into bubble. But … it was a surprisingly steep learning curve and the embed failed. Asked for help, none provided. Cheap but not free.
My usual tool, Microstrategy Desktop, has recently added the ability to connect to JSON REST Api. So looking at that currently. But not a cloud tool, and VERY expensive beyond the free service (they deprecated their free cloud service and gave users a free desktop tool, so not sure how free it is now).
Of the other tools … things like Geckoboard can use Zapier to get Bubble data into. Set up Zapier to poll Bubble’s API …
Early days for all this, some of the simpler (cheaper) visualization tools are too complex to integrate at the moment.
Alternatively, pump out the data into mySQL etc and use that.
@NigelG thanks for this, really interesting to hear how these different services panned out for you and hopefully v. helpful to others down the line who want to do the same thing!
As follow-up, here’s what happened with me:
Tried blockspring + Google Sheets. Very quickly started hitting walls with GQL, Google’s query language – it’s similar to MySQL but still missing some chunks. Doesn’t appear to be sophisticated enough to execute the queries I’m looking to do. I can only recommend this if you are certain you will not want to do more creative things with your data later.
Next, I tried AWS + MySQL, and have been using that ever since. The only hiccups here so far have been:
On the Bubble side, it seems you can only run one query at a time. I tried some where I had to initialize a variable in a separate query first and it didn’t work. Fortunately I could cross join with a table where the variable was initialized, and keep it to 1 query.
In my particular AWS setup, I have to set the security settings so that the database is accessible by everyone in order for it to talk to Bubble. I’m somehow not able to restrict it to Bubble’s server – this looks like an issue on the AWS side, not Bubble. You still have the extra layer of username/psw credentials, but I’m wondering if this is a security risk.
Other than that, using the MySQL database plug-in has been a dream! It’s not workflow-intensive at all, it’s fast, and it makes my dynamic data much more readable.
The rest of this post is about getting a MySQL database up and running w/AWS. The documentation about how to do this online for beginners is thin, and I’m putting this here to save anyone from the confusion I went through …
(Some of this might be specific to installing on a Yosemite 10.10 mac)
Amazon: MySQL database instance
In AWS, go to RDS.
Set up a MySQL database instance. There’s lots of settings to choose from. In general, I just selected the free trial setting and AWS only allowed me to make the most basic of databases (which is all I needed). Demonstration here: https://www.youtube.com/watch?v=LnAvUOmH1n0
Your computer talks to Amazon db To access your database instance, first make sure MySQL is running (for Mac it’s in the settings). In the terminal, type “mysql -h yourendpoint.blahblahblah.us-west-2.rds.amazonaws.com -u username -p” then you should be prompted for the password you set with this instance. Import your .csv files, edit your db, etc.
Bubble talks to Amazon db This initially confused me because I thought I’d need a server address that resembled an IP address, and went about getting an elastic IP through Amazon. Don’t do it. Instead, you should just use the endpoint address, in the “server address” part of @georgeciobanu 's explanation:
My connection string ended up looking like this:mysql://username:password@db_instance_name.12345abc.us-west-2.rds.amazonaws.com:3306/db_name
Thank you so much for sharing your insight and experience!
You should be able to run two consecutive queries in a workflow - share more details or create an example using the open MySQL server we provided and we’ll help you troubleshoot or fix it if there’s a bug.
We’ll see if there is a way to provide a static IP for these calls.
Could you elaborate on this part? I’ve got the connection established between my AWS PostSQL db and Bubble via the Database Connector plugin, but I’m unsure of the steps to export my db from Bubble in order to load it into the AWS db.
My end goal is to have a backup of my Bubble db in AWS that will update itself based on a schedule and allow me to query it from my computer via connecting to the AWS db through SQL Workbench. Is this possible with the Bubble Database Connector? @georgeciobanu@NigelG
