PostgreSQL + MySQL
When you already have a database running, or would prefer to manage your database separately, starting with PostgreSQL or MySQL is a great way to start. You can add external databases through the Budibase interface with a few clicks.
Adding the remote data source
When you want to add a new remote data source, you need to head over to the data page in the BudibaseUI, en press the +
on the top left.


You'll be promoted with choosing which data-source you want. You'll either pick PostgreSQL or MySQL. If you want to connect a MariaDB database you'll also have to pick MySQL as they're compatible sources.
After selecting the preferred data source you'll be prompted to configure the connection.


Whitelisting IP
Make sure you have the Budibase IP Whitelisted if you're using the Cloud-hosted solution.
Fetching Tables
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.


Creating Relationships
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 Relationships
.


When you click the define relationship button, you get a form in which you can specify the relationship.
One-to-one / One-to-many
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.




Many-to-many
With a many-to-many relationship, a join table will have to be created. The steps as above will be followed but a "through" needs to be selected during configuration. This through
refers to the join table that needs to be created.
This can be useful, like the example above, if every pet can have multiple owners.
Updated about 2 months ago