Build an Admin Panel with Redshift
26
May
2022
Tutorial

Build an Admin Panel with Redshift

Build an Admin Panel with Redshift
Victory Tuduo
0
 minutes ↗
#
analytics
#
applications
Tutorial

Different organizations rely on admin panels to manage, read and mutate data stored in their database. An admin panel is a console used to control features configurations of an application and manage content in that application. Admins in most organizations use these to help control and manage content and provide security for the application. Anyone looking to handle applications or site functions easily will need to use an admin panel.

Goals

In this tutorial, you will learn how to set up a Redshift database and create and deploy an admin panel template built on Appsmith.

Setting up our Redshift Database

To set up your Redshift database: Navigate your browser to Redshift. Click on the 'Get started with Amazon Redshift' button. Sign in if you already have an account; else, create a new user account.

Next, you need to set up a new role for RedshiftDB. Click on Services in the top navigation, then IAM. Choose Roles from the left navigation panel and click on Create role. In the AWS Service group, choose Redshift. Next, choose Redshift - Customizable, then select Next: Permissions. On the Attach permissions policies page, scroll down and select AmazonS3FullAccess. Enter the role name and description, then click on Create role.

1.png

Creating a Cluster

2.png

On the dashboard, click on ‘Create Cluster’ and enter your cluster name, cluster plan, admin username, and password in the form on the new page that opens up. Ensure to store your admin username and password as you will be using it to connect the database to the Appsmith app. You can finally click on the 'Create Cluster' button when you are done with that.

3.5.png

When the cluster is created, select the cluster and click on Actions; under the 'permissions' section, click on Manage IAM roles. When the management screen appears, select the IAM role you had created earlier, click on Associate IAM role, and click ‘Save Changes.’

With the newly created cluster, you can use a boiler template database. Click on the Query data button.

Testing the Database

Select the newly created cluster in the left panel to use a cluster.

4.png

By default, Redshift creates a dev database that we will use in this tutorial. You can create your table data using the following command and run it to view your output.

6.png

Click on the 'save' button to save this query, enter the name of your query and finally, click save to store it.

3.png

To access the database's credentials, click on 'security credentials' in the dropdown menu on your user name at the top navigation bar. Click on 'Create new access key,' then download the key file in the access key.

Creating the Appsmith Tool

After setting up the database, the next step is to create the panel on Appsmith.

  • First, create a new application to get started.
  • Next, click on the '+' icon on Datasources in the left navigation pane.
  • Click 'Create new' and select Redshift from the list of databases.
  • By default, our data source is named 'Untitled,' you can change the name to something of your choice.
  • You can get your host address and port information from your cluster's information.
11.png

The endpoint is your hostname, copy and paste it into the required field without the port number at the end. By default, the port number is 5429, but you can find this information in your endpoint.

  • Enter the name of your database. In this case, the name of my database is dev.
  • Add your username and password to the authentication section
  • Whitelist the IP instances in your Redshift database settings
  • Finally, click on test, then save.

Creating our Table

With the Redshift data source connected, the data from the database can be displayed using a table. Navigate to the page and add a Table Component from the widgets section by dragging and dropping it onto the canvas. Next, open the table's property pane by clicking on the cogwheel icon on the table.

8.png
a1.png

Before that, we need to set up a query for the data with the command:

COPY

COPY

SELECT firstname, lastname, total_quantity
FROM (SELECT buyerid, sum(qtysold) total_quantity
           FROM sales
           GROUP BY buyerid
     ORDER BY total_quantity desc limit 10) Q, users
WHERE Q.buyerid = userid
ORDER BY Q.total_quantity desc;

Then on our page, we can get this data. In Appsmith, we use the mustache syntax {{}} to add Javascript. To get our query, we use {{Query1.data}}

Setting up a Form with Update Functionality

Apart from reading the data to the table, you can use Appsmith to create and update functionalities.

To do this:

  • First, we will create a form that will contain the input parameters for our table.
  • This form will contain text fields for the firstname, lastname, and total_quantity.

Drag the form widget onto the canvas from the widgets section and add the fields for the firstname, lastname, and total_quantity.

creating a form

You will need to link the form to the database to add the new data with the form setup. You will need to write a query to collate the data from the form when the submit button is clicked and append it to the table. To do this, click on the cogwheel icon on the submit button. Then set the onClick to execute a database query and then click on create a query:

COPY

COPY

INSERT INTO users
 ('firstname', 'lastname', 'userid')
VALUES
('{{Input1.text}}', '{{Input2.text}}', '1');

       INSERT INTO sales
       ('qtysold', 'salesid', 'listid', 'sellerid', 'buyerid', 'eventid', 'dateid')
VALUES
('{{Input3.text}}', '1', '1', '1', '1', '1', '1')

Here we entered the values of the input fields into the firstname, lastname, and qtysold. Also, I've set the id's to '1' for every entry; usually, you may use default values for this. You can now add new entries through the form to the table.

Updating and Deleting Table Data

Apart from reading and creating table entries, we can also update and delete data from our table. You must first target a particular row and set it as the default value in the form's input fields to update the data.

update selected fields

Above, we set the Default Text for the input field to {{Table1.selectedRow.firstname}} to always get the data from the firstname column of the selected row in the input field. We repeat this step for lastname and total_quantity by entering {{Table1.selectedRow.lastname}} and {{Table1.selectedRow.total_quantity}} as their default text respectively. We need to add new functionality via a button to update the table. Add a button from the widgets pane and name the ‘Update’ button.

Next, create a new Javascript query for the on-click handler in the button option pane. For our new query to handle updates, we have the following:

COPY

COPY

update users
set firstname='{{Input1.text}}', lastname='{{Input2.text}}'
where firstname='{{Table1.selectedRow.firstname}}';

The query above checks for the row where the firstname corresponds to the selected row's firstname column value. It then replaces the firstname and lastname columns with the value of the input fields.

Delete Functionality

We can add another button called Delete to our form to do this.

delete button

Create a new query as follows:

COPY

COPY

delete from public.users where firstname = '{{Input1.text}}'

When executed at the click of the Delete button, this query checks the user's table and deletes the corresponding row, which has a firstname with its value corresponding to the selected data.

You can try out the query and observe that the selected row is removed when the 'Delete' button is clicked.

Deploying the Redshift Template

To deploy the Redshift panel template, click on the deploy button at the top right of the navigation bar. You can then make it publicly accessible or share it with specific individuals via email. The admin panel can be further modified with added functionalities such as user authentication for restricted access and email integrations to inform the admin when a change has occurred.

Build a Growth Dashboard Using Appsmith, Orbit, and Slack
28
April
2022
Tutorial

Build a Growth Dashboard Using Appsmith, Orbit, and Slack

Build a Growth Dashboard Using Appsmith, Orbit, and Slack
David Atanda
0
 minutes ↗
#
analytics
Tutorial

Proper feedback and community management is a significant part of building the organic reach of products, especially open source products. So, marketing teams might decide to build customized apps that contain valuable metrics. Products like Orbit allow us to get live data from our community forums to view them more concisely. In this tutorial, you will learn how to build an internal growth dashboard, where we’ll be able to display key metrics, including monitoring a Slack group and viewing existing members of the channel. The goal is to display specific growth metrics that enable better decision making in the marketing team.

as1.gif

First, we’ll be learning how to connect our Slack directly to Orbit, where we can view our information, activities, and members on their dashboard. Then, we’ll use Orbit’s REST API to view the data on our Appsmith dashboard.

as2.png

This is useful in cases where we want to view our Slack data in a custom Appsmith app outside something like Orbit, mainly when the dashboard includes other external community data, not data from just the Slack channel.

Getting Started with Orbit

We’ll [signup to Orbit] (app.orbit.love) and create our Orbit’s workspace:

as3.png

Once we’ve created the workspace, we can now integrate our Slack community to orbit.

as4.png

You can now open the Slack workspace you just connected with Orbit.

Go into your channel and open the Channel Details menu by clicking on the top-left channel name. Then, we’ll open the integration panel and click on “add apps.”

We’ll then see a bunch of apps where you’ll select Orbit.

as5.png

The Orbit integration is now added to our #general channel! This automatically imports all messages and profiles (up to one year). Plus, it will listen for any new activities, including messages, thread replies, etc.

A message will automatically appear in the channel you just added, saying, “Orbit was added to #channel by you.”

as6.png

To add the Orbit integration to more channels, we need to repeat the same steps or take advantage of the message shown above: Clicking on the name or icon of “Orbit” in that message will open a menu, from which you can add the integration to more channels with a click.

We’ve successfully connected Slack to orbit and can view all activities on our Orbit dashboard.

as7.png
as8.png

Setting Up Our Orbit API

Orbit offers RESTful APIs that we’ll use to pull our Slack data into our dashboard. The specific data pulling is the list of members from this Slack channel.

Here’s the endpoint:

app.orbit.love/api/v1{workspace_slug}/members

Note: You can can check the docs for more endpoints and use-cases.

You can find the workspace_slug under the workspace settings. It’s the text in bold below:

as9.png

Setting Up Appsmith

Let’s sign up to Appsmith and create our first app. On our first page we’ll add the container and text widgets to act as the header. Then, we’ll also add the list widget.

as10.png

Now, we’ll go under QUERIES/JS dropdown on the left and create a new Datasource.

as11.png

Set the API request to GET and add the endpoint. You’ll have to add your {workspace-slug} to the endpoint, as explained earlier. Once that’s done, we’ll click Edit Datasource, and it’ll take us to a page where we’ll add our header token.

as12.png

Scroll down to the Authentication section of the page, down to the Bearer Token input, and add your Orbit’s API token. You can find that on the settings page. Note that you won’t add Bearer as a prefix, just the token. (that’ll be done automatically)

as13.png

Let’s go back to our API datasource page and test it again

as14.png

We now have everything functioning as it should, and the API provides the appropriate response. Look at the left and open a new page:

as15.png

Once we select the list widget, we go to the left and see all related options. Under items, we’ll get the response preview by adding the name of the API: Orbit_Members.data.data.

{{Orbit_Members.data.data}}

Similarly, you’ll add an image widget inside the List item. On the right, we’ll use currentItem to refer to the current API in question. So it’ll be currentItem.attributes.avatar_url.

as16.png

In the same vein, we add the name and email to the List item with {{currentItem.attributes.name}} and {{currentItem.attributes.email}}. If we do this correctly, we should have a similar screen to the one we have above.

Displaying Member Details

First, we’ll create a new page called Member Details

as17.png
as18.png

We’re now going to add a button to each user that navigates us to a new page (Member Details). While navigating, we’ll save the user’s slug as a store value, and send it as a query param to the next page.

The aim is to use that slug to make another request on the new page. This request displays that particular user’s details. Under Member Details, we’ll name that request Member_Details and get our slug value from Appsmith.value.slug. This allows us pull the data for that single member that was selected on the Dashboard page.

as19.png

Let’s go back to Member Details and display our data.

as20.png

First, we add an image component to display the picture and use {{Member_Details.data.data.attributes.avatar_url}} to get our image URL from the Member_Details DataSource we created.

We also use {{Member_Details.data.data.attributes.location}} and {{Member_Details.data.data.attributes.avatar_email}} to display our location and email respectively.

Next, we’ll manually add other data to our dashboard. These include our Daily Active Users (DAU) and New Community Members.

as21.png

Sales Report

There’s a sales report graph that helps us display our sales data.

as22.png

In this case, we’re manually adding our data as an array.

as23.png

Finally, we’ll hardcode other important data like churn rate, conversions, and our MRR.

as24.png

Deployment

Now that our Appsmith app is complete, what’s next? Deploying our shiny new app for other team members to use. Here’s the link to our application.

There’s also a tutorial below that shows you how to deploy with digital ocean. youtube.com/watch?v=6fitHGX2G4E&; youtu.be/6fitHGX2G4E

Conclusion

In this article, we’ve covered how to create a growth dashboard with Appsmith and Orbit‘s REST APIs. This shows us how to use low-code tools to optimize time and valuable resources when building internal tools in your company.

Orbit and Appsmith complement each other when it comes to monitoring community platforms like Slack, Discord, Discourse, etc. With Orbit, you get access to direct feedback and necessary information without spending engineering time building infrastructure from scratch. While Appsmith lets you focus on creating a visual dashboard with metrics, using minimal effort to implement and maintain.

Beyond using Orbit as a datasource, we can make use of custom REST APIs, Airtable, Supabase, Backendless, etc. Also, you can add other third-party integrations like intercom, email, or even add Zapier into your application. Appsmith can be customized into any form to fit your technical use-case.

If you have blockers or issues using Appsmith, you can always jump into our community Discord server to ask questions. Plus, you can connect with other Appsmith users.

Also, feel free to check other Appsmith tutorials using similar services like Cube, SnowflakeDB, Hasura, etc.

How 40 Lines of Code Improved Our React App’s Performance by 70%
14
February
2022
Engineering

How 40 Lines of Code Improved Our React App’s Performance by 70%

How 40 Lines of Code Improved Our React App’s Performance by 70%
Vishnupriya Bhandaram
0
 minutes ↗
#
engineering
#
engineering diaries
#
web-development
#
reactjs
#
analytics
Engineering

On Appsmith, developers can quickly build any custom business software with pre-built UI widgets that connect to any data source. These widgets can be controlled with JavaScript. We created this framework to help developers save on valuable time building complex applications for internal uses within their organizations. For this to work for everyone, we believe that the core parts of Appsmith need to run smoothly and should be continuously improved.  Appsmith’s UI is built using React, Redux, Web Workers, Immer among other things. 


In this blog, Sathish Gandham, a frontend engineer focusing on UI performance at Appsmith, will talk about how we improved the editing experience on Appsmith.

What is the Editing Experience on Appsmith? 

The editing experience on Appsmith involves writing bits of code to customize the functionality of the widgets and writing special commands and actions into the application. It is a crucial function in the Appsmith framework. 

Lag and Delay 

Building an application on Appsmith involves dragging and dropping widgets onto the canvas and writing custom code on the editor; however, we noticed that while editing text/code in the canvas, the editor would often freeze, resulting in a less than optimal user experience. When building an application, there should be no delay or lag. This was a huge issue that needed our immediate attention from an engineering perspective. For a natural typing experience, users want the keypress latency to be under 100ms, though 50ms would be ideal. 

To solve this problem, we needed to understand what happens when a user types. For this, we used: 

React profiler: This measures how often components in the application render and the “cost” of rendering. The profiler helps in identifying parts of an application that are slow. In our case, this allowed us to understand what components were rendered as we typed. 

Chrome Performance tools: This helped us quantify the problem, measure our progress, find the code taking longer to execute, and find unnecessary repaints. 

Please note that the React profiler is part of the React Developer tools browser add-on which is available for Chrome and Firefox.

From the React profiler, we see three pairs of long commits; each of these corresponds to the following property pane, and UI widget renders. Ideally, the property pane should render much faster than the canvas since there is nothing changing in the property pane except the input we are editing. Even the canvas should be rendering the widgets currently in use and not the rest. We realized that this was not the case and needed to be fixed. 

We profiled the property pane issue in isolation to identify what it takes to render it. For this, we used the performance tab in Chrome DevTools to see what happens when the property pane opens. This gives us some helpful information. 

  • ComponentDidMount of code editor is taking a lot of time 
  • Styles are also taking a long time to render
If you see the property pane commit in the screenshot above, you will notice that evaluatedValuePopup also takes significant time.


Here’s how we listed the tasks that lay ahead of us: 

  1. Identify as to why all the widgets were rendering when they don’t have to and fix it
  2. Optimize the code editor [Not apparent from the React profiles]
  3. Identify why all the controls in the property pane are rendering and fix it
  4. Optimize the global styles
  5. Optimize the evaluatedvalue pop-up

In this blog, I will talk about how we went about the first task. Before I get to that, here are a few tips for profiling: 

  • Try to split your problem into smaller pieces and profile them. With this, you won’t crowd your profile, and you can find the performance issues with ease. 

Example 1: To improve the editing experience, we just profiled a single keypress. 

Example 2: To profile a drag and drop action, we can split that into drag start, move, and drop.

  • Leave the application idle for 5 seconds after starting the profile and before stopping it. It will make it very easy to identify the work that has been done. [See A & C From profile above]
  • To measure the overall performance improvements, instead of measuring each optimization individually, it’s better to consider focussing on the overall scripting and time taken to render during an action. You can get this number from the chrome performance tab. [B & D from profile above]
  • In the React profiler, don’t just focus on the large commits. Go through each commit at least once, and see what’s getting rendered in that commit. The chances are that one or two small components are accounting for all those renders.

Here’s a short guide on reading the React profile: 

  • A: List of commits during our profile
  • B: The commit we are looking at
  • C: Details about the selected component (WidgetsEditor). Our widgets editor rendered three times during the profile at 6.1s, 8.6s, and 14.1s. 102ms, 328ms,83.1ms is the duration each commit took; it is not the total time the selected component took to render.
  • D: Zoomed in view on the component we selected and its children.

Here are the notes on the profile based on which we worked on improving the editing experience. You can download the attached profile and import it in your React profiler to follow along or just refer to the image above.

Please note that the React profiler is available only when you open a react app in dev mode in Chrome/Firefox, if you don’t have a local React development set up, you can use the standalone React developer tools to read the profile. 

Here are instructions on how to install it and start it: 

Install

# Yarn

yarn global add react-devtools

# NPM

npm install -g react-devtools

Run

react-devtools

Follow this link to read the detailed notes from the profile we did to improve the editing experience on Appsmith. 

I’ve put some notes here for your reference: 

1. Evaluated value opening. Not related to editing.

2. Widgets editor, not sure why.

3. Editor focused. We should be able to avoid the rest of the property pane from rendering.

4. Small changes to property pane, its header, lightning menu, and action creator. Nothing changes for them, so they should not be re-rendering. Memoization can help here.

5. Same as above. 

6. We get the evaluated value back. Entire widgets editor is re-rendered (Deduced this from one of the two updates to table), we can optimise this

- If each widget subscribes to its data independently, we should be able to avoid the unnecessary renders of the widgets by

- Doing a deep check at the widget level

- update the store with only values that changed. 

7. PropertyPane is rendered with the updated value. EvaluatedValue taking most of the time.

8. From 8 to 17, these are commits like 4 & 5 above. 

9. 18 & 19 are widgets editor and property pane. I don’t see why these are required. I will look into it. 



Widgets Render When Not Needed


One of the most loved features of Appsmith is reactive updates. With reactive updates, you can see the widget change and show data. With traditional programming, you would have to reload the page in order to see the update in the widget. This is achieved by updating the data tree as and when you change something on the canvas and using the updated data tree to re-render the app. Due to the amount of data we have and the number of calculations we need to do, it took a long time and blocked the main thread.

To solve this problem, we moved the evaluations to a web worker freeing the main thread. A brilliant move to solve the problem at hand, but this created a new issue. The problem here was due to object reference changing. Since the data tree is coming from the worker, we would always get a new reference for every item in the tree even though only some of them changed. This reference change was making all the widgets re-render unnecessarily.


A few approaches we tried to solve this problem were:

  1. Get what keys changed from the worker (worker has this information) and update only those values in the reducer. This did not work because the list of keys was not complete. 
  2. Compute the diffs between the current data tree and the one received from the worker and update only what changed. Though this prevented the renders, we did not improve the overall scripting time we measured earlier. The reason is, computing the diffs itself took a lot of time, which would happen twice for each change.


Web Worker to the Rescue 


We moved the task of computing the diffs to the worker and used the deep-diff library to compute the diffs and let immer take care of immutability.

This helped us in two ways:

  1. Offloaded the expensive task of computing the diffs on the main thread.
  2. Reduced the size of the data we transfer between worker and the main thread (this was never a bottleneck for us, though).


This change alone brought down the keypress latency by half.


Instead of replacing the entire data tree from the worker, we get the only changes (updates) and apply them to the current state. applyChanges is a utility method from deep-diff. Immer takes care of the immutability.


If there’s anything to be said about performance improvement, it’s this, don’t take performance for granted and profile your code on a regular basis. Even a few lines of change or configuration can lead to a great performance gain. 



I hope you found this blog helpful. If you’d like to get in touch with Satish, ping him on Discord or visit his website.

Building an Employee Survey Dashboard with Supabase and N8N
3
December
2021
Tutorial

Building an Employee Survey Dashboard with Supabase and N8N

Building an Employee Survey Dashboard with Supabase and N8N
Confidence Okoghenun
0
 minutes ↗
#
analytics
Tutorial

Many companies use questionnaires to keep track of their employee’s health and general wellbeing. Because of the time and complexity involved in building one, many choose to go ahead with off-the-shelf solutions that may be quick to set up but have poor analytics tools. For example, the organization might be interested in knowing specifics about the use of the survey/questionnaires. For example (include one or two examples here) But there’s something better than off-the-shelf, and it can be built using Appsmith.

In this article, I will teach you how to build a custom employee survey dashboard to fit your specific requirements. We’ll achieve this by tying together the best open-source tools in the business to build powerful workflows.

We’re going to cover a lot, but specifically, you will learn how to:

  • Use Supabase as a data source
  • Use filters in DB queries
  • Display data in charts
  • Perform email automation with N8N

You can peek ahead to see the app we’ll be building here. Let’s get started!

Project set up

As mentioned earlier, we’ll be using Supabase as our storage DB. Signup for Supabase and create three tables; an employee, a question, and a responses table. You can seed the database with this dummy data

You should also go-ahead to set up N8N. You can signup for the cloud version or self-host it. Create a new workflow, and we’ll come back to it in a bit.

Lastly, signup for Appsmith and create a new app. Go to Datasources > Create New > PostgreSQL and fill the form to connect to your Supabase instance.

Awesome! With this, we have completed all the setup. Let’s begin writing queries.

How to use filters in DB queries

We need to write a query to get questions from the DB. Let’s call this query get_questions, and it should look like this:

SELECT * FROM public."questions";


With the returned data, we can build a question filter. Drag in a select widget and configure its Options property shown below.

The name of the widget has been updated to q_filter
{{
[{label: 'all', value: '%'}]
.concat(get_questions.data.map(q =>({label: q.question, value: q.id})))
}}


question filter

Similarly, go ahead to build a team filter. Name the select widget t_filter, and its Options property should be:

[
    {
    "label": "all",
    "value": "%"
  },
    {
    "label": "hr",
    "value": "hr"
  },
  {
    "label": "marketing",
    "value": "marketing"
  },
  {
    "label": "engineering",
    "value": "engineering"
  },
    {
    "label": "logistics",
    "value": "logistics"
  }
]


Using the filters we just built, we can write filtered queries i.e., to get employees based on the selected team and get responses based on the chosen question and team.

Create a new query for employees and update its name to get_employees. The query should look like this:

SELECT id AS employee, name, email, team FROM public."employees"
WHERE team LIKE "{{t_filter.selectedOptionValue}}";

Similarly, the get_responses query for responses should look like this:

SELECT r."completedAt", r.employee, r.response, r.comment, e.age, e.sex, e.team
FROM responses r
INNER JOIN employees e ON r.employee=e.id
WHERE
    r.question LIKE "{{q_filter.selectedOptionValue || '%'}}"
    AND e.team LIKE "{{t_filter.selectedOptionValue}}";


Using a table widget, you can display the employee completion status like so:

employee completion

And, here’s the snipped used for the Table Data property:

{{
    get_employees.data.map
      (e => _.omit({
        ...e,
        ...get_responses.data.find(r => r.employee === e.employee)

      }, ['sex', 'age', 'response', 'employee', 'comment'])
      )
}}


How to display data in charts

In the last section, we wrote a query to get responses for the selected question and team. Now, we’re going to focus on displaying the data in charts.

From the widgets tab, drag a chart widget into the canvas. We want this chart to display the aggregate number for responses for a particular rating level, i.e. 26 employees rated 5. So for the Series Data property on the chart widget, use the snippet below:

[
  {
    "x": "5",
    "y": {{get_responses.data.filter(r => r.response === 5).length}}
  },
  {
    "x": "4",
    "y": {{get_responses.data.filter(r => r.response === 4).length}}
  },
  {
    "x": "3",
    "y": {{get_responses.data.filter(r => r.response === 3).length}}
  },
  {
    "x": "2",
    "y": {{get_responses.data.filter(r => r.response === 2).length}}
  },
  {
    "x": "1",
    "y": {{get_responses.data.filter(r => r.response === 1).length}}
  }
]


And your chart should look like this:

responses chart

We can also display responses by gender. For this, we’ll need a sex filter. You know the drill, so go and make one. Here’s mine (named f_sex):

sex filter

And now, we can display the data using a chart widget set to a Pie Chart Chart Type. Here’s the snippet:

chart for responses by gender

For some extra credit, use the principles above to build a filtered chart to display responses by age group. Here’s mine:

chart by age group

How to perform email automation with N8N

In this section, we’re going to look at how to send out emails from the dashboard using N8N.

For employees who haven’t completed a survey questionnaire, we need a way to resend it to them via email. Let’s build an N8N workflow for this.

The workflow would consist of two nodes, a webhook that accepts post requests and the second being a Gmail node. Here’s a snapshot of the workflow:

n8n workflow

The Gmail node should be configured to send an email by setting the Resource to Message and the Operation to Send. The subject should contain a link to the questionnaire form, and the recipient email should be an expression that is evaluated the payload of the webhook as shown below:

email field

Going back to the dashboard, we can create a new API to trigger this workflow. Go to Datasources > Create New > Create New API and enter the URL of the N8N webhook. Give the request a name i.e. send_email, set the request type to POST, and the body would be the email of the employee selected from the table:

send email API

Now, going back to the table widget, we can create a custom column of Column Type Button. Configure this column to run the send_email API when it is clicked. Thus, clicking on the button for a particular employee would resend the questionnaire to that employee via email.

Wrap up

you did  it

Awesome! We’ve been able to build the employee survey dashboard. You can take a look at the app here https://app.appsmith.com/applications/61281a99695af65b46d75843/pages/61281a99695af65b46d75845

You can expand on this and add more features to the app. The floor is all yours! We’d love to see what you add next. By the way, give us a star any time you visit github.

How To Display Data Visually From a Database in under 5 Minutes
1
September
2021
Resources

How To Display Data Visually From a Database in under 5 Minutes

How To Display Data Visually From a Database in under 5 Minutes
Vyom Srivastava
0
 minutes ↗
#
databases
#
analytics
#
dashboard
#
mysql
Resources

If you’re dealing with numbers, graphs and charts happen to be the best way to make sense out of those numbers! Charts and graphs can help you understand your data and help you make decisions based on them. With Appsmith, you can connect your data sources in just a few steps and generate beautiful graphs and charts.

As of now, Appsmith supports the following databases (in addition to using any REST APIs):

  • MySQL
  • MS SQL
  • Postgres
  • Amazon S3
  • Google Sheets
  • Snowflake
  • Redshift
  • Redis
  • Elastic Search
  • Firestore
  • ArangoDB

In this article, we will display data from our MySQL database to our app on Appsmith.

Step 1: Create app, and connect database

Login to your Appsmith account or create a new account (if you don’t have one). Once you're logged in, click on the “Create New” button:

Screen Shot 2021-08-23 at 10.35.33 AM.png

After that, click on the Build with Drag & Drop widget. You’ll see this:

Screen Shot 2021-08-23 at 10.39.42 AM.png

On the left sidebar, you can see an option for “Datasources”. Click on the “+” button and it will open a list of all supported data sources by Appsmith:

Screen Shot 2021-08-23 at 11.20.45 AM.png

For this blog, I will use the Mock Database (provided to all users of Appsmith to help with quick prototyping), which is based on MySQL; you can go for your preferred database.

NOTE: Since I am using Mock Database, it’s not asking for database credentials. But, when you select another data source, you’ll need to enter the database credentials. Let’s say you want to connect to a new data source. You’ll see the below page:

Screen Shot 2021-08-28 at 5.49.10 PM.png

Once you select the data source (for example, MySQL), you now need to enter your credentials:

Screen Shot 2021-08-28 at 5.49.43 PM.png

Step -2: Add the first query and widget

Once you have added the database (in this case, selected the data source), you should see the list of all the tables present in your database.

Here’s how it will look:

Screen Shot 2021-08-23 at 11.21.49 PM.png

This allows you to perform CRUD operations against those tables. Let’s try to add our first widget. Now, click on the “+” button in front of Datasources. You should see the list of all connected data sources. Now click on “+ New Query” for the data source you want to run the query. Since we’re using a mock database so we’ll select that:

Screen Shot 2021-08-28 at 5.56.11 PM.png

Once you click the “+ New Query” button, you now need to click on the “Select” option from the list to run the SELECT queries:

Screen Shot 2021-08-28 at 6.02.01 PM.png

This will open a MySQL editor where you need to paste the below query:

SELECT count(public."users"."name"), date(public."users"."createdAt") FROM public."users"group by date(public."users"."createdAt") order by date(public."users"."createdAt") asc;
Screen Shot 2021-08-28 at 6.05.07 PM.png

Now, to add the widget, we need to add it as a Chart. So, just select the chart widget, which is available at the right sidebar of the page.

Screen Shot 2021-08-24 at 8.44.21 AM.png

Step -3: Add Stats for Orders

Hover the cursor on "public.orders" and click on the “Add” button and select the SELECT command option. Now, paste the below query:

SELECT count(public."orders"."deliveryAddress"), public."orders"."orderDate" FROM public."orders" group by public."orders"."orderDate"  LIMIT 7;

This will return the orders data for the last seven days. We now need to select the chart widget so that the data can be displayed.

Screen Shot 2021-08-24 at 8.52.29 AM.png

Step -4: Add Stats for Revenue

Hover the cursor on “public.orders” and click on the “Add” button and select the SELECT command option. Now, paste the below query:

SELECT sum(public."orders"."orderAmount"), public."orders"."orderDate" FROM public."orders" group by public."orders"."orderDate"  LIMIT 7;

This will return the revenue data for the last seven days. We now need to select the chart widget so that the data can be displayed.

Screen Shot 2021-08-24 at 8.56.52 AM.png

Step -5: Adding employees’ data

Hover the cursor on “public.employees,” and click on the “Add” button and select the “SELECT” command option. Now, paste the below query:

select public."employees"."employee_id", public."employees"."employee_name", public."departments"."department_name" from public."employees" LEFT JOIN   public."departments"  on public."departments"."department_id" = public."employees"."department_id";

This will return the employees’ data which includes employee id, employee name, and employee department. We now need to select the chart widget so that the data can be displayed.

Screen Shot 2021-08-24 at 9.00.25 AM.png

Step -6: Add To-Do List for Employees

Hover the cursor on “public.standup” and click on the “Add” button and select the “SELECT” command option. Now, paste the below query:

SELECT public."standup_updates"."date",public."standup_updates"."name", public."standup_updates"."notes" FROM public."standup_updates" LIMIT 10;

This will return the employees’ standup data which includes the date, employee name, and employee notes. We now need to select the chart widget so that the data can be displayed.

Screen Shot 2021-08-24 at 9.03.34 AM.png

Step -7: Deploying the dashboard

Once we’re done with adding and aligning all the widgets, we also need to deploy it; just click on the “Deploy” button at the top of the page including, and it’ll get deployed instantly!

screencapture-app-appsmith-applications-61231d24695af65b46d7449b-pages-61231d24695af65b46d7449d-2021-08-24-09_12_41.png

You can check out the live example here also.

Final Words

Displaying data from the database is very easy with Appsmith. Our plug-n-play UI allows you to add any database and display the data in graphs, charts, tables, dropdown or even as a normal text. You can even add a lot more functionalities in this dashboard like create new orders, create/update employee data, or even perform CRUD operations using our widgets to your database tables.