When you already have a database running, or would prefer to manage your database separately, starting with MariaDB or MySQL is a great way to start. You can add external databases through the Budibase interface with a few clicks.
To add a new remote data source choose the Data tab in Budibase , then choose the
+ icon to the right of 'Sources'.
You'll be promoted to choose the type of data source you want. Choose MySQL if your database server is MySQL or MariaDB. Next enter the connection parameters as shown below:
Your MySQL database server will need to be whitelisted in any firewalls protecting your database. If you are using the Budibase Cloud you should Whitelist the Budibase IP addresses in your firewall. If you are self-hosting you should whitelist the IP address of your Budibase server on your database server.
If you use Docker to host your own Budibase installation and are connecting to a database on the same machine as your Budibase installation you should set the host to host.docker.internal, or 172.17.0.1 (if running on Linux).
By default when you add a new data source, the tables will be fetched after the connection has been confirmed. However, in the dialog where you configure a new data source, you can skip fetching tables.
Fetching tables can also be triggered at any stage after the source has been added. This is especially useful when you've added, removed, or updated a new table after you've added the connection to Budibase.
To fetch tables, head over to the added data source in the sidebar, and scroll past the configuration of the data source.
To help Budibase understand your database you will need to create relationships between the different tables. This can be done from the configuration screen of your data source. To get there, click your data source in the sidebar and scroll down until you find
When you click the define relationship button, you get a form in which you can specify the relationship.
When you define a one-to-one or one-to-many relationship you'll need to pick the "One" relation type. Then you will have to choose the table and column of the primary index you want to refer to. After that, you need to choose the table and the column in which this primary ID is referenced. For example, if you have a pets table, and an owner table, you first will have to select the owner primary index, and then select the pets "owner" column.
After setting this up, an
owner_id column will be added to the
pets table, as well as a
pets column to be added to the
owner table. These columns will only be added internally, meaning this column will not be added to the remote data source. This is to properly keep track of the relations without messing up a remote source. Visually, when you navigate to both tables, you'll see the relations displayed as blocks referring to the related row in the other table.
With a many-to-many relationship, a joining table will be needed. The steps will be similar to the above, however a "through" table needs to be selected.
through table will be the joining table.
This can be useful, for example, if you had many students in many classes.
When saving rows, you will not need to save entries to the joining table - Budibase will automatically do this for you.
Updated 25 days ago