1
September
2021
Resources

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

5
 minutes
How To Display Data Visually From a Database in under 5 Minutes
đź“Ť
Blog
/
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.

What’s a Rich Text element?

asdsadasdsa

The rich text element allows you to create and format headings, paragraphs, blockquotes, images, and video all in one place instead of having to add and format them individually. Just double-click and easily create content.

sfdfsdfds

dsfdsfdsf

adfkaldf

  • dfghhghff
  • dfgdfgfd
The rich text element allows you to create and format

sadadasdasdas dsada sadas asd ad

Static and dynamic content editing

  • dcdsdfd
  • dfdfvddf
  1. vdfgdgd
  2. gjgjg

A rich text element can be used with static or dynamic content. For static content, just drop it into any page and begin editing. For dynamic content, add a rich text field to any collection and then connect a rich text element to that field in the settings panel. Voila!

‍

How to customize formatting for each rich text

Headings, paragraphs, blockquotes, figures, images, and figure captions can all be styled after a class is added to the rich text element using the "When inside of" nested selector system.

swzdswxzdsw

Square
Try Appsmith
The Modern Stack to Build Internal Tools: Supabase, Appsmith, n8n
8
November
2021
Resources

The Modern Stack to Build Internal Tools: Supabase, Appsmith, n8n

The Modern Stack to Build Internal Tools: Supabase, Appsmith, n8n
Vihar Kurama
8
 minutes ↗
#
applications
#
community
#
crud
#
automation
#
beginners
Resources
October Roundup: Widget Improvements, New Appsmith functions, and ARM Architecture Support
2
November
2021
Monthly Round-up

October Roundup: Widget Improvements, New Appsmith functions, and ARM Architecture Support

October Roundup: Widget Improvements, New Appsmith functions, and ARM Architecture Support
Vihar Kurama
5
 minutes ↗
#
announcement
#
applications
#
app-development
#
community
#
crud
Monthly Round-up
Learn How to Push Content from Multiple Sources to Discord in One Go
20
October
2021
Tutorial

Learn How to Push Content from Multiple Sources to Discord in One Go

Learn How to Push Content from Multiple Sources to Discord in One Go
Vihar Kurama
8
 minutes ↗
#
community
#
applications
#
dashboard
Tutorial