The first step is to connect to your database.

The connection options will be slightly different, depending on your database. The example below uses Postgres.

Note that if you are connecting to a database on `localhost`, your host should be set to `docker.host.internal`, or `172.17.0.1` if running on Linux.

Once you have input your connection details, click on “Fetch tables from database”. This will read all the tables in your database.

Once Budibase knows about your SQL tables, it can autogenerate screens (the user interface) that allows listing, creating, editing a deleting of records from tables.

Of course, these generated screens are completely customisable - but Budibase gives you a huge headstart.

1. Go to the “Design” section.

2. In the left panel, click the “+” button, to create a new screen.

3. For the “Template”, choose “Vehicles - List”.

4. Click create

This produces a screen that lists row in the Vehicles table. You can now repeat the steps above for “Vehicles - New” and “Vehicles - Detail”, to give you “Create” and “Edit” screens. For these screens, it’s best to uncheck the “Create Link in navigation bar”.

Next, we are going to add a “Create new” button to our Service Log. We want to enter the new details into a form, inside a modal.

First, we will create and configure a button.

1. Create the button, and position it on the screen using a container. We place the container above our Service Log table, then place the button inside, and right-align it.

2. Click on your button, and choose “Define Actions”.

3. Add a “Navigate To” action, with the URL “/servicelog/new/row”, and tick the box to “Open screen in modal”.

Next, we will build the screen for “New Service Log”.

1. Click on the “+” button to create a new screen.

2. For “Template”, choose “ServiceLog - new”.

3. Set the URL to “/servicelog/new/row”.

4. Uncheck “Create Link in navigation bar”.

5. Click “Create”.

6. Budibase has generated a form for us, but we should remove some unnecessary columns:

   • ID - this is generated by our database

   • VehicleID - this is handled by the “Vehicle” dropdown

7. Finally, we need to tell our screen to “Close Modal” on save, which is not the default action for this type of screen.

   • Click on the “Save” button

   • Click on “Define Actions”

   • Remove the “Navigate To” action.

   • Add a “Close screen modal” action.

Budibase allows you to declare relationships between your table. We are going to set up our one-to-many relationship between Vehicles and Deals.

Note that Budibase does not modify your tables. We are simply telling Budibase about your existing database structure.

To create a relationship:

1. Select your SQL datasource

2. Click on “Create Relationship”

3. Set relationship type to “One”

4. The from (“One”) table is “Vehicles”

5. The to (“Many”) table is “Service Logs”

6. The “From Table Column” is how your relationship will be named in your Vehicles table. We will call this “Service Logs”.

7. The “To Table Column” is how your relationship will be name in your Service logs table. We will call this “Vehicle”

The relationship is now created, however, we need to tell Budibase how to describe the Vehicle from the Service Log table, and vice versa. To do this, we need to set the “Display Column”. This is equivalent to a column alias:

SELECT ServiceLogs.*, Registration as Vehicle

FROM ServiceLogs

INNER JOIN Vehicle on ServiceLog.VehicleId = Vehicle.id

I.e. use the Vehicle “Registration” column when displaying it in the Service Table.

So, we will set up our display columns

1. Click “Update Display Columns”

2. For “Vehicles”, choose “Registration”

3. For “Service Log”, choose “Description”

We are now going to list Service Logs on our home screen, ordered by the most recent logs - by Service Date.

To achieve this we:

1. Navigate to the home screen, under “Design”.

2. Remove any default components that are on the page.

3. Add a “Data Provider” component.

4. Give the data provider a name, so that we can easily identify it in our component tree (left panel).

5. Setup the data provider to connect to the “Service Log” table.

6. Set the “Sort Column” to “Service Date”

7. Add a table component, underneath the data provider

8. Finally, we select the columns that we want to see in the table

At the time of writing, Budibase has fantastic support for Postgres, MySQL and MariaDB. We intend to add more SQL connectors.

We automate all the most common SQL queries - INSERT, UPDATE, DELETE and SELECT by ID. You can also write custom queries if you need something more complex or use stored procedures.

This guide will take show you how to build a “Vehicle Maintenance Log” application. For this guide, I will use Postgres. As far as Budibase is concerned, the process is the same, regardless of your database.

We will use the following table structure.

Below are Postgres and MySQL scripts to create the necessary tables, and insert test data.