Configuring Database connectors - Snowflake

Studio enables you to use the database connectors to connect to your relational databases like MySQL, PostgreSQL and Microsoft SQL, MongoDB, Snowflake, Redshift and so on.

Snowflake has been gaining recognition for its powerful cloud-based data platform. It is an efficient warehouse-as-a-cloud-service (SaaS for DB) that requires no hardware or software installation. It is based on a new SQL database engine that enables faster, easier and flexible data storage, processing, and analytics. Studio provides the Snowflake connector that allows you to integrate with SQL database for your data storage and processing needs using your Custom apps.

Configuring a Snowflake Connector

To add third party DB connectors, under Studio > Connectors, click (+) Connector.

You first need to configure the Snowflake Connector with the necessary domain details and the database name.

To configure the connector, enter a Category name and Category description. Add an appropriate icon and click Continue.

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.

The most important part of the process is to provide the connection string. You would now need to add the Connection string 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 Snowflake database you would need a few parameters or fields. You can add them as a Connection String or may provide the respective connection parameters 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 DB connector

Once the Connector is configured it would now be available for use within your Studio Apps as required. You need to create or add a query using the Add Query option available to fetch, add, update or delete data using the connection. Here you can add your queries - simple as well as complex ones.

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. Now 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 Snowflake 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 Snowflake Connector. So click bto 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 OFFSET and LIMIT clause value to specify the number of rows and the page number to be shown for pagination. 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.

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, select the Connector from the list of connectors available. You can see in the Custom Formula the BINDAPI formula which you can customize further if you want to format how your data would be displayed in the Tablegrid. 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.

Add Trips / Insert

Let us now see how to add a Trip 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. Optionally you can 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 values 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.

Using the DB Connector for Snowflake 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.