SQL Database Connector Bubble.io

Context

At the moment, it is not possible to choose the country where our data will be hosted, which can be frustrating. And depending on the needs of the app to be developed, we may need to integrate an external database to our Bubble app.
Fortunately Bubble has already provided a solution to facilitate the integration of its SQL database hosted on a private server or at AWS (RDS) for example.
SQL Database Connector plugin allows you to perform SQL queries on SQL, My SQL PostGres and Microsoft SQL databases with ease. Although it requires some technicality that we will see in the following.

SQL Database Connector Plugin

The SQL database connector plugin connects to databases and executes SQL queries from Bubble. These queries can be triggered as actions, data sources (they appear as external APIs) or both. Bubble supports connection to PostGres, MySQL and Microsoft SQL. This gives an infinite number of possibilities and can be adapted to many use cases.

To use this plugin, you need to master SQL queries to be able to query your database from bubble. You also need to know Bubble for a better integration in your workflows.

At the database level, you will have the data to facilitate your authentication at the Bubble level. The interface is convenient and easy if you have already manipulated the APIs with the Bubble API Connector.

You will be able to CRUD (Create, Read, Update and Delete) things in your database from your workflow in Bubble. Depending on your use cases. There is no limit to the complexity of the queries and the speed of loading.
In some cases it is best to use a specialized backend system like Xano or Backendless to ease the maintenance and scalability of your backend. If you have any questions about this, I will be happy to help you.

My Tips

Here are my tips to make the most of this plugin and take advantage of this integration in Bubble and the benefits it offers :

All SQL statements are allowed, but for SELECT statements, you must add LIMIT N at the end, where N <= 100. In order not to have problems on the Bubble side.
Once you have tested and saved the query, you can use it as an action, a data source or both, depending on how you set it up. Please note that a SELECT query returns a list. Therefore, when you use it as the result of a previous step in a workflow, you must select a specific item using :first item, :last item, etc. As is typical with Lists in Bubble.

Special characters are not supported by the default MySQL integration, which uses UTF8 encoding. You can add ?charset=UTF8MB4_GENERAL_CI to solve this problem and send characters like emojis to your SQL database appropriately. Although I don’t recommend it for the sake of fluidity.

A connection to a database should look like this:
mysql://username:password@my-db-instance.endpoint.us-east 1.rds.amazonaws.com:PORT/db_name
If the password contains special characters, they should be encoded in URL, for example by replacing # with %23. As we have seen Bubble only supports UTF8. But I think Bubble will improve this encoding over time. But here are the rest of the encodings listed.

If you add parameters, in the query put a ? as a placeholder to bind the parameter, example for two parameters:

SELECT * FROM mytable WHERE mycolumn1 = ? AND mycolumn2 LIKE ? LIMIT 200

Set the “Use as” to “Data” if you will refer to it directly as a data source, or “Action” if using it as a workflow step, then the following step refers to its results. Once the query has been initialized, it creates a pseudo data type, that you can set your repeating group.

Only use an external database if it is the only solution for your use case. Only use an external database if it is the only solution for your use case. In most cases, Bubble will be sufficient. This will save you the technical constraints of maintenance and implementation.
And unfortunately, Bubble doesn’t have a complete documentation about this. But if you have knowledge of code and SQL database with a mastery of Bubble everything will be clear to you because its integration is seemingly. If you have any questions you can leave a comment or ask a question on the Bubble forum.