Building a Simple Front-End for Your SnowflakeDB Datasource

Designing UI elements can take up a majority of a developer’s time when building from scratch. However, with Appsmith (an open-source low-code framework to build powerful internal apps), it’s possible to create a fully functional and custom frontend in minutes. A vast array of pre-built UI components, that is widgets are available to help you build good-looking applications. 

Connecting data sources with Appsmith takes a few minutes, and you can easily build tools on top of the database of your choice. For example, you can build admin panels to manage product catalogs, read content data from your database and use that to populate your e-commerce website, and then write more data and update your existing orders in the database. The possibilities are countless.

In this blog, I will teach you how to build a front-end that can connect to SnowflakeDB as a data source.

Snowflake is a cloud-based data warehouse-as-a-cloud-service (SaaS for DB) that requires no hardware or software installation. Snowflake handles the maintenance and tuning of cloud infrastructure. It is based on a new SQL database engine with unique features and advantages over a more traditional data warehousing technology approach.

Getting Started: Connecting Snowflake on Appsmith

On Appsmith, it’s pretty straightforward to establish a connection with any data source, including SnowflakeDB; be it on the cloud, self-hosted version, or local environment. What we need to make the connection are the endpoint, database name, and user credentials. With this in mind, let’s get started!

• Create a new account on Appsmith (it’s free!), if you are an existing user, log in to your Appsmith account.
• Create a new application under the dashboard under your preferred organization.
• On your Appsmith application, click on the + icon next to Datasources on the left navigation bar under Page1
• Next, click on the Now, navigate to the Create New tab and choose SnowflakeDB data source, you’ll see the following screenshot:

• When you’re using Snowflake cloud, all these details can be found under the SnowflakeCloud settings:

• Rename the Datasource to SnowflakeDB CRUD by double-clicking on the existing one.

Here’s what the configuration would look like:

• Next, click on the Test button at the bottom right of the screen. This will help you with understanding whether your configuration is valid or not. If it returns a successful message, hit the ‘Save’ button to establish a secure connection between Appsmith and SnowflakeDB.

We are done with the basic configuration. Now, let’s use the default database from SnowflakeDB to build a fully customizable CRUD app.

Note: After the connection is established, we can see all the sample data (tables) under the connected data source.

Now that we have the sample data, in the next section, let’s build a fully-fledged CRUD application (on the customer table) on top of our SnowflakeDB using Appsmith.

CRUD on SnowflakeDB With Appsmith

Implementing the Read Operation

First, let’s read our seed data from the database and display it on a beautiful table widget. Follow the below steps:

• Click on the + icon next to the data sources and choose to Create New + from the SnowflakeDB CRUD data source.
• Rename the query to getCustomers.
• Copy the following SQL script to query all the Customers from the CUSTOMER table:

SELECT * FROM TPCDS_SF100TCL.CUSTOMER LIMIT 10;

• This is a simple query that returns all the customers present in the sample data item. Hit the RUN button to view all the results.

We now have our query; let's bind this onto the table widget; for this follow the below steps:

• Click the + icon next to widgets on the sidebar, search for the table widget, and drag and drop it onto the canvas.
• Configurations to any widget can be made through the property pane. Click on the table widget on the canvas, you will see the property pane docked to the sidebar on the right. Now, under the Table Data property, use the mustache syntax to bind the query:

{{getCustomers.data}}

With this, we should see all the data displayed on the table. The column names can be configured and re-organized under the property pane.

Implementing the Create Operation

To add the create operation on SnowflakeDB, let’s make UI.

Drag and drop a button widget onto the canvas. Open its property pane, set the onClick property to Open a New Modal, and choose Create New.

This will open up a new modal now; let’s drag and drop a few widgets to create a form that we can use to add new customers into our database.

Here, we have five input widgets to add our customers. We can configure the default values, labels, and placeholders by selecting the respective property panes. Now, let’s write the query that lets us create a new customer on SnowflakeDB.

Follow the steps below:

• Click on the + icon next to the data sources and choose to Create New + from the SnowflakeDB CRUD data source.
• Rename the query to insertCustomer
• Copy the following SQL script:

INSERT INTO TPCDS_SF100TCL.CUSTOMER (C_FIRST_NAME,C_LAST_NAME, C_BIRTH_YEAR, C_BIRTH_COUNTRY, C_EMAIL_ADDRESS) VALUES ({{Input1.text}}, {{Input2.text}}, {{Input3.text}}, {{Input4.text}}, {{Input5.text}});

Here, we have an insert query that collects all the data from the form widgets we've created. Note that we use the mustache syntax to bind the data from the widgets onto the query body.

Lastly, we’ll need to configure the submit button; for this, go back to the modal and set the button’s onClick property to execute a query and choose insertCustomer under the events property:

Implementing the Update Operation

The Update operation is quite similar to the create operation. First, let’s build UI by creating a new custom column on the table by clicking on ADD A NEW COLUMN under the columns property.

Now, rename the column to Edit Customer, and click on the cog icon next to it, to configure column settings. Under this, we’ll see column-type properties set to a Button type. When clicked, a modal should open up with the necessary fields to update the item.

Now, copy-paste Modal1, and set the onClick property of the Edit Customer button to open Modal2. Here, in the form, we can also set the default value to show existing information, to display this, use the selectedRow property from the table widget.

Let’s write the Edit query using SQL:

• Click on the + icon next to the datasources and choose to Create New + from the SnowflakeDB CRUD data source.
• Rename the query to editCustomer.
• Copy the following SQL script:

UPDATE TPCDS_SF100TCL.CUSTOMER SET
    C_FIRST_NAME = "{{Input1Copy.text}}",
    C_LAST_NAME = "{{Input2Copy.text}}",
    C_BIRTH_YEAR = "{{Input3Copy.text}}",
    C_BIRTH_COUNTRY = "{{Input4Copy.text}}",
    C_EMAIL_ADDRESS = "{{Input5Copy.text}}",
WHERE C_CUSTOMER_ID = {{Table1.selectedRow.C_CUSTOMER_ID}};

Here, we have an edit query that collects all the data from the form widgets on Modal2. Note that we use the mustache syntax to bind the data from the widgets onto the query body.

We’ll now need to configure the submit button; for this, go back to Modal2 and set the button’s onClick property to execute a query and choose **_editCustomer_** under the events property.

Implementing the Delete Operation

The delete operation is pretty straightforward with the Table’s selectedRow property; before we dive into it, let’s create a new column on the table and set it to the button. For this:

• Create a new custom column on the table by clicking on Add a New Column under the columns property.
• Now, rename this column to ‘Delete Customer,’ and click on the cog icon next to it, to configure column settings. Under this, we’ll see column-type properties set to a button type.

Now, let’s write the Delete query using SQL:

• Click on the + icon next to the data sources and choose the Create New + from the SnowflakeDB CRUD data source.
• Rename the query to deleteCharacter.
• Copy the following SQL script:

DELETE FROM TPCDS_SF100TCL.CUSTOMER_DEMOGRAPHICS
  WHERE C_CUSTOMER_ID = {{Table1.selectedRow.C_CUSTOMER_ID}};

Set the Delete Character button’s onClick property to run the deleteCharacter query.

With these four operations configured, you will be able to read and analyze information from your database, edit the data, add or delete information and update records.