Integrating a Bubble.io app with CrateDB

[rafaelasantana]

Bubble.io is a powerful platform for building web applications without code. One of the features it offers is the ability to connect to external databases, such as CrateDB, and display the data in your Bubble app. This tutorial will walk through binding to a CrateDB database and displaying the data in a Repeating Group in your Bubble app.

Before we start, make sure you have CrateDB up and running. To learn how to start CrateDB for the first time, check out our Getting started documentation or our Getting started with CrateDB video tutorial.

Import Major Cities dataset into CrateDB

For this tutorial, I will use the Major cities of the world dataset. It provides a list of cities above 15,000 inhabitants and each city’s country and subcountry. To import this dataset into your CrateDB instance, create a table to store the data.

CREATE TABLE "major_cities" (
   "name" TEXT,
   "country" TEXT,
   "subcountry" TEXT,
   "geonameid" INTEGER
)

Then, run the COPY FROM statement to import the dataset.

COPY "major_cities" FROM 'https://pkgstore.datahub.io/core/world-cities/world-cities_csv/data/6cc66692f0e82b18216a48443b6b95da/world-cities_csv.csv';

Create a new Bubble app

With the data in place, let’s move on to the bubble.io website to create your Bubble app.
If you don’t have a Bubble account, sign up or log in with an existing one.
On the Apps page, select Create an app to create an empty project.

Connect to CrateDB with the SQL Database Connector

Select the Plugins tab on the bubble editor and then Add Plugins. To connect to CrateDB, search for the SQL Database Connector and install it.

On the SQL Database Connector, select Add a new connection with the following inputs:

• Connection name: CrateDB
• Database type: PostgreSQL
• Connection string: postgresql://user:password@host:port/doc?ssl=true

Select Test the connection to complete this step and then select Add a new query to fetch the cities’ data from CrateDB with the following statement:

SELECT name, country, subcountry, geonameid
FROM major_cities
LIMIT 100;

Select Initialize this query and confirm the return values. From now on, the cities’ data will be available in your Bubble app. So let’s set up your app to display this data.

Design your app

The Design tab in Bubble.io is where you can create and customize the layout and appearance of your app. It allows you to drag and drop elements such as text, images, and buttons onto the page and then adjust their properties and styles.

Create a Repeating Group

One way to display a dataset in Bubble is by using a Repeating Group. Each item in the dataset is displayed as a card within the Repeating Group, and these cards can include various elements such as text, images, and buttons. A Repeating Group allows you to create a single template for displaying items and then automatically repeat that template for every item in the dataset.

Start by dragging the Repeating Group from the Containers tab and drop it on your empty page. Click on it to open the configuration options. On the Appearance tab, set the Type of content to cities. For the Data Source, select Get data from an external API and select cities from the dropdown list. Uncheck Set fixed number of rows and check Show all items immediately to display all returned values.

Create a card template for the Repeating Group

Now it’s time to set the card template for the Repeating Group. Let’s start by displaying the name of the cities. Drag and drop a Text element into the first row of the Repeating Group and click on it to open the configuration options.
On the Appearance tab, select insert dynamic data in the text box and select Current cell’s cities’ Name from the dropdown list.

Select Preview on the top right of your Bubble editor to build your app. Now you should see a list of city names!

You can add more information to your Repeating Group by adding more elements to the first group row. For example, you can add the country and subcountry in the same fashion and add static labels to the card.

Apply filters to the returned values

There is a workaround to apply filters to the data coming from CrateDB. Before the filter is applied, here is what you will need to do:

• Create custom states in the Page index to store the data
• Create an event to fill the states with data
• Create an input field for the filter
• Create a button that implements the filtering to the custom state

Let’s start by creating the custom states. First, select the Page index from the Elements tree on the top left of the Design tab in Bubble editor.

Select the element inspector from the Page index on the top right and click on add a new custom state. Call it cities_list, with the type cities, and check the list option. Repeat this step to create another state with the same settings, but call it cities_list_filtered instead.

When the page loads, you create an event to fill cities_list and cities_list_filtered with the returned values from CrateDB. To do that, navigate to the Workflow tab on the right of your Bubble editor. Create a new event by selecting General, and then Page is loaded.

Then, fill in cities_list with data as follows.

• Click on add a new action → Element Actions → Set state
• Choose the element index
• Choose the custom state cities_list
• Set the value as get data from an external API and select cities from the dropdown list

Repeat these same steps to fill in cities_list_filtered.

Navigate to the Design tab and select the Repeating Group. Change the Data source to index’s cities_list_filtered. From now on, the Repeating Group will display the filtered data, so let’s set up the filters and create a search button.

To create a filter, insert an Input field, set the placeholder to Search for a country, and its type to Text.

Then take a Button, call it Search and select Start/Edit workflow on the button settings.

When you click on Search, you want to apply the Search for a country filter to the cities_list and store the result in cities_list_filtered, which will then be displayed in the Repeating Group. To do that, create a new action as follows:

• Set the cities_list_filtered value to index’s cities_list:filtered
• On the filter tab, select Add a new constraint and set Country = Input Search for a country’s value.
• Check the Ignore empty constraints option.

To apply more filters to the data, add more input fields to the index page and set their values as constraints in the Search button workflow action.

In conclusion, connecting CrateDB to a Bubble.io app is a straightforward process that can be accomplished using the SQL Database Connector. By following the steps outlined in this tutorial, you should now be able to retrieve data in CrateDB from your Bubble.io app. We hope this tutorial was helpful and that you can now leverage the power of CrateDB in your Bubble.io app. If you have any questions or issues, contact the CrateDB community for support. Happy (no-)coding!