Studio enables you to use the database connectors to connect to your relational databases like MySQL, PostgreSQL and Microsoft SQL, MongoDB, Snowflake, Amazon Redshift, and so on.
Amazon Redshift is a fully managed, petabyte-scale data warehouse service in the cloud with a fast query performance using the same SQL-based tools and business intelligence applications that you use today. Studio provides the Amazon Redshift connector that allows you to integrate with the database system for your data storage and processing needs through your Custom apps.
Configuring Amazon Redshift Connector
To add third party DB connectors, under Studio > Connectors, click (+) Connector.
You need to first configure the database category. 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.
You can specify the environment to be used for your SQL operations. If you simply want to Test your app before making it ready for production you can set the environment to Dev or Beta depending upon your test scenario and connect your Dev database. Whenever your app is ready for production, and you want to publish and distribute the package, you can connect your Prod database and simply update the environment to Prod for the connectors added in your App. Here, the basic assumption is your Dev, Beta, and Prod environment databases have the same schema, only the data is different so it will work seamlessly.
You would now need to add the Connection string fields required for establishing a database connection.
Add the exact connection string if you have it or enter the connection parameters that are required, namely Host, username, password, port, and database name. You can also enable further action to Connect using SSL, Enable SSH Tunneling, and Whitelisting IP.
Once these configurations are done, you need to Test the request and connection. If the authentication is successful you would get the response accordingly. You can now click Continue to Finish and Save your configuration.
Adding queries to Amazon Redshift connector
Now that you have configured the connector you will find it under the Custom Database connectors list. Now to fetch data or undertake any other action, click Add query option available once your connection is ready.
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 for use in your apps later under your specific connector under Custom Database connectors.
Using Amazon Redshift connector
Fetch Data and apply pagination
Now here consider an example where we want to find the author names from the table. Let us first fetch data and apply pagination using the Redshift Connector. So click Add Query to go to the Query Editor. Here you would add the query with the OFFSET clause.
Run the query to view the results. You can add a dynamic variable if you want to define the LIMIT and OFFSET values to specify the number of rows for pagination and the number of rows to be skipped if any. Save the query. Your connector is now configured.
Now whenever you want to make use of these Database connectors in your Apps, under Studio Console, click Connectors > + Add. From the list of connectors select Custom Database Connectors and select your ready connector and click Continue. Now from the connector select the query for pagination and click Continue. Connect your authenticated account. You can also view the SQL query being used using Show query. If you want to hide the query for some reason, click Hide Query. Beyond the configured queries you feel the need a few more queries you can use the Add Query option.
Based on the query selected, you would have the connector fields that would be used in the BINDAPI formula. Add the connector name and other details and click Finish.
In this example form, to bind the fetched data to the Tablegrid control need to use BINDAPI formula either by adding it to the Custom Formula or through Data > Connectors > Dropdown selection (API name and keys) as shown below.
Now if you take a preview it would be shown as seen below.
Add a record
Let us now see how to add a Category with the Insert query. Click Add Query and open the query editor and add the Insert statement with the values for the different fields and then run the query.
When the record is added you can view it with the select statement. Typically, you would add dynamic values using the variables in the insert statement. These values can be added using a form designed to add records.
If you are using the dynamic value you need to configure and link the controls to the variables added to the Insert statement. Further, you need to add an action flow to trigger the Server side action that calls the insert query in the connector that you have already configured.
Add the DB connector’s Insert query from Action flow > Server side actions > DB Connectors configured.
If you are using the dynamic values you need to configure and link the controls to the variables added to the Insert statement.
Now whenever you enter category data and click the Action button (Add CATID in this example below), the record is inserted and you can view it by refreshing the table grid here.
Using the DB Connector for Amazon Redshift is similar to any other SQLs like PostgreSQL, MySQL and so on. 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 need to ensure the syntax which would vary a bit for different SQL systems.