There could be scenarios where you would be using Relational Databases like MySQL, Microsoft SQL, PostgreSQL, and so on instead of using sheets. You would need operations like insert, update, create, and delete that you would need to perform. Studio offers you database connectors that can be used to connect to your relational databases. Studio supports MySQL, PostgreSQL, and Microsoft SQL at the moment.
To use the database connector you simply need to select the necessary Connectors, configure the connector using the connection string, provide the necessary values and test it before you validate. Once your configuration is complete you can add a query as per your requirement.
Let us have a quick look at the video first to get an idea how to configure the DB Connector for MySQL.
Adding a DB Connector
To add a third party DB connectors, under Studio > Connectors, click (+) Connector.
Let us select MySQL connector for this example.
Configure Database Category
You first need to provide the details of your Connector, thus enter a Category name and Category description. Add an appropriate icon and click Continue.
The most important part of the process is to provide the connection string. It will be dependent upon the different types of relational databases that you might want to use.
Now let us configure our MySQL database connector. You would now need to add the Connection string fields. Depending upon the relational database that you have chosen, you would find the fields required for establishing a database connection. These are non-editable fields, you cannot add or remove these fields.
In this case, note that to connect to the MySQL database you would need a few parameters or fields. Here, for the sake of the example, we will be taking a sample public MySQL database provided by RFam. The connection parameters that are required are Host, username, password, port, and database name.
Note that the RFAM database that we have used here is read-only. So we would be able to only fetch data for this example. However, your databases would not be restricted to read-only access. So you would be able to carry out insert - update and other operations as well. Add the configuration details that are applicable to your relational database and click Submit.
Once these configurations are done, you need to Test the request and the connection. If the authentication is successful you would get the response accordingly. You can now click Continue to Finish and Save your configuration.
Add a query to the Database connector
The DB connector is now added to the Custom Database connectors. You would have the Add query option available as your connection is already done.
So click Add Query to go to the Query Editor.
Here you can add your queries - simple as well as complex ones. To the right-hand side of the screen you can see the list of tables from the database. You can expand the tables to view the fields from the table.
You can provide a name to the query using Enter a Service name. Add your query and click Run or press Ctrl + enter to run the query. The rows returned from the table are shown in the Response section.
You can also add dynamic values using the Variables. You can add the variable using Variables > +Add.
Add the FieldName and select the Field type. Ensure that you select the field type that matches the column type of the table so that the query works correctly. Add a Test Value to check the results before saving. You can also add a Default value or make it Mandatory. Add the Help text that would be shown with the field. You can also add any preset formula. Once you have entered the details, click Add Variable. The variable would be listed under Variables. You can make use of the variable in the query where you created the variable.
To use a variable inside a query, you simply need to put it into double Curly brackets. The Test value that you entered would be considered for fetching data.
You can now view the queries that you saved under your connector under Custom Database connectors.
Now consider an example where we want to find the author names from the table whose initials would be the ones that you input through the input text control.
Now that you want to make use of these Database connectors in your Apps, Under your app, click Connectors > + Add. From the list of connectors select Custom Database Connectors > Your connector name, and click Continue.
Now from your connector, select the query that would be used to fetch data (FetchAuthorInitials for this example) and click Continue .
Now your account needs to be authenticated. So select your Connected Account and click Continue. Usually, there would be just one account that you have added.
Based on the query selected, you would have the connector fields that would be used in the BINDAPI formula. In this example, we are going to fetch author names based on the field Initials which would use the value of the text control “enterinitials” in this example. Add the connector name and other details and click Finish.
In this example form, to bind the fetched data to the Tablegrid control you need to use BINDAPI formula either by adding it to the Custom Formula or through Data > API > Dropdown selection (API name and keys) as shown below.
Now whenever you run the app, you can enter the initials in the text control and the table grid would display only those rows from the table that match the condition given.
Using the DB Connector is similar to using the sheets with your apps. You can make use of the Queries to fetch data as well bind data such that you can undertake the insert, update, delete actions as well.
You can refer to this link here to understand how to use the API services.