Build a CRUD App with a Redshift Database
1
March
2022
Resources

Build a CRUD App with a Redshift Database

Build a CRUD App with a Redshift Database
Vihar Kurama
0
 minutes ↗
#
admin
#
ui
#
mysql
#
app-development
#
guide
Resources

Amazon Redshift is a fast, fully managed, petabyte-scale data warehouse service [A data warehouse is a type of data management system that is designed to enable and support business intelligence (BI) activities, especially analytics.]. It makes it easy to manage your data warehouse and automatically distributes your data across multiple nodes to eliminate hot spots and provide high availability. It’s also an excellent option for building a frontend. However, it does have a few specifics that might make you rethink your current strategy. However, with Appsmith, 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 quickly build tools on top of the database of your choice.

This blog will teach you how to build a frontend that can connect to Redshift as a datasource.

Connecting Redshift on Appsmith

On Appsmith, it’s pretty straightforward to establish a connection with any datasource, including Redshift. We need to make the connection to 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 Redshift datasource; you’ll see the following screenshot:
CleanShot 2022-02-22 at 13.51.39@2x.png
  • All these details can be found under the configuration settings when using Redshift on a hosted service.

Note: I’m using a free Redshift account on Amazon Web Services (AWS) in this example.

CleanShot 2022-02-22 at 13.52.25@2x.png
  • Rename the Datasource to Redshift CRUD by double-clicking on the existing one.

Here’s what the configuration would look like:

CleanShot 2022-02-22 at 14.23.12@2x.png
  • 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. Hit the ‘Save’ button to establish a secure connection between Appsmith and Redshift if it returns a successful message.

Using Redshift’s Seed Data

The basic configuration is complete, so now, we will now use the seed data already loaded on the Redshift Data (TICKIT data).

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

CleanShot 2022-02-22 at 14.26.50@2x.png

Now, let’s use the Category table to build our CRUD APP!

CRUD on Redshift with Appsmith

Implementing the Read Operation

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

  • Click on the + icon next to the datasources and click on create New + from the Redshift CRUD datasource.
  • Rename the query to getCategories
  • Copy the following SQL script to query all the Categories from the category table:


SELECT * FROM public."category" LIMIT 10;


  • This simple query returns all the category details present in the sample data item. Hit the RUN button to view all the results.
CleanShot 2022-02-22 at 15.07.07@2x.png

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.
  • You can make any configurations to any widget via 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 moustache syntax to bind the query:
{{getCategories.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.

CleanShot 2022-02-22 at 15.07.42@2x.png
Implementing the Create Operation

To add the create operation on Redshift, 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 Categories into our database.
CleanShot 2022-02-22 at 15.09.07@2x.png

Here, we have three input widgets to add to our category. We can configure the default values, labels, and placeholders by selecting the respective property panes. Now, let’s write the query to create a new category on Redshift.

Follow the steps below:

  • Click on the + icon next to the datasources and choose to Create New + from the Redshift CRUD datasource
  • Rename the query to addCategory
  • Copy the following SQL script:
INSERT INTO public."category" ("catgroup", "catname", "catdesc")  VALUES ('{{Input1.text}}', '{{Input2.text}}', '{{Input3.text}}');


CleanShot 2022-02-22 at 15.10.54@2x.png
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 Category, 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. A modal should open up with the necessary fields to update the item when clicked.

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

CleanShot 2022-02-22 at 15.15.53@2x.png

Let’s write the Edit query using SQL:

  • Click on the + icon next to the datasources and choose to Create New + from the Redshift CRUD datasource
  • Rename the query to editCategory
  • Copy the following SQL script:

UPDATE public."category" SET
    "catgroup" = '{{Input1Copy.text}}'
    "catname" = '{{Input2Copy.text}}'
    "catdesc" = '{{Input3Copy.text}}'
  WHERE "catid" = {{Table1.selectedRow.catid}};

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 **_editCategory_** 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 Category,’ 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 Redshift CRUD datasource
  • Rename the query to deleteCategory
  • Copy the following SQL script:
DELETE FROM public."category"  
    WHERE catid = {{Table1.selectedRow.catid}};


Set the Delete Category button’s onClick property to run the deleteCategory 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.

If you’re interested in using a database not listed on our website as an integration, please let us know about it by raising a PR on Github, and we will do our best to include it at the earliest.

Join our growing community on Discord, and follow us on Youtube and Twitter to stay up to date.

A simple Front-end for Your SnowflakeDB Datasource
1
February
2022
Resources

A simple Front-end for Your SnowflakeDB Datasource

A simple Front-end for Your SnowflakeDB Datasource
Vihar Kurama
0
 minutes ↗
#
ui
#
admin
#
databases
#
crud
Resources

Designing UI elements can take up a majority of a developer’s time when building from scratch. However, with Appsmith, 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 catalogues, 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 frontend that can connect to SnowflakeDB as a datasource.

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 datasource, including SnowflakeDB; be it on 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 datasource, you’ll see the following screenshot:
CleanShot 2022-01-26 at 23.05.25@2x.png
  • When you’re using Snowflake cloud, all these details can be found under the SnowflakeCloud settings:
CleanShot 2022-01-26 at 23.07.29@2x.png
  • Rename the Datasource to SnowflakeDB CRUD by double-clicking on the existing one.

Here’s what the configuration would look like:

CleanShot 2022-01-26 at 23.09.58@2x.png
  • 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 customisable CRUD app.

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

CleanShot 2022-01-26 at 23.13.45@2x.png

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 datasources and choose to Create New + from the SnowflakeDB CRUD datasource
  • 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.
CleanShot 2022-01-26 at 23.17.06@2x.png

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 moustache 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.

CleanShot 2022-01-26 at 23.19.19@2x.png

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.

CleanShot 2022-01-26 at 23.36.01@2x.png

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 datasources and choose to Create New + from the SnowflakeDB CRUD datasource
  • 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 moustache 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:

CleanShot 2022-01-26 at 23.54.51@2x.png

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.

CleanShot 2022-01-27 at 00.44.39@2x.png

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 datasource
  • 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 moustache 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 datasources and choose the Create New + from the SnowflakeDB CRUD datasource
  • 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.


We’ve got extensive documentation on the SnowflakeDB datasource integration!

If you’re interested in using a database that is not listed on our website as an integration, please let us know about it by raising a PR on Github and we will do our best to include it at the earliest.

Join our growing community on Discord, and follow us on Youtube and Twitter to stay up to date.

How I Created My Own Stock Index Tracker with Time-Series Charts using Low-Code
12
January
2022
Tutorial

How I Created My Own Stock Index Tracker with Time-Series Charts using Low-Code

How I Created My Own Stock Index Tracker with Time-Series Charts using Low-Code
Vihar Kurama
0
 minutes ↗
#
dashboard
#
admin
#
beginners
#
automation
#
code
Tutorial

I recently started investing and came across a product called Smallcase. The way it works is — experts create a diversified long-term portfolio of stocks & ETFs and manage it on a timely basis for you. I really love the idea of how stocks can be added into portfolios or baskets to reduce market volatility. Unfortunately, at the moment, this product is only available for Indian investors and that led me to create an app, where I could create a portfolio, and also create a custom index on top of it, with features to compare benchmarked indices like Dow Jones / S and P.

Building this kind of application from scratch would have taken me a lot of time; I would have had to choose a front-end framework, configure a database, and work on different visualizing frameworks to build time-series charts. With Appsmith, I was able to build this app in just a few hours. If you don’t already know, Appsmith is an open-source low code framework to build internal apps. In this blog, I will take you through the steps I followed to create the app.

First, let me share the specs of the app in a little more detail.

With this application, you can do the following:

  1. Track stocks: Set filters on what stocks should be tracked, throughout the day by default or create specific lists.
  2. Make your own baskets: Create customized stock portfolios as baskets.
  3. Time-series charts to compare performances: Create a custom index for these portfolios and compare them with benchmark indexes using time-series charts.
  4. Notify: Send notifications and triggers when there are fluctuations in the market.

In this blog, I will also dive into building some nifty time-series charts and build our stock index tracker that can compare with S&P 500, Dow Jones, and NASDAQ.

Here are a few screenshots of the application:

Screenshot of Custom Index Tracker on Appsmith
Screenshot of Custom Index Tracker on Appsmith — 2

Now, let’s get started!

What All Did I Use to Build This Application?

TLDR; I’ve used Appsmith, Fusion Charts on Appsmith, APIs from MarketStack, Alphavantage, and Google Sheets as the backend to build this application.

This application primarily uses Appsmith, an open-source low-code tool that can connect to any database, API, or GraphQL to build admin panels, dashboards, and internal tools. Appsmith also supports charting options; it has an in-built extension that uses Fusion Charts to create data visualizations; this made it much easier to build my own time-series charts. To pull all the stock data and prices in real-time, I’ve utilized free APIs from MarketStack and Alphavantage.

(Please remember to generate your own API keys if you’re following this tutorial to build a similar app! To know more about generating your own API keys, read this blog)

I’ve used Google Sheets as my backend to watchlist stocks and store customized stock portfolios and baskets.

Creating Baskets to Group and Track Stocks

The first step to creating portfolios, or as I like to call them, “baskets,” is to build a user interface. Through this, a form can take in our portfolio name, time created, and a logo for easy viewing… and some vanity! 😎

It took me less than 5 minutes to create this with Appsmith and Google Sheets; here’s how I did it:

Note: I’ve used the Cloud Version (Community Edition, which is free) of Appsmith; you can build this either on a local version using Docker or a self-hosted version of Appsmith.

  • Sign-up for a new free account on appsmith.com
  • Click on the + icon next to Datasources and choose Google Sheets as Datasource.
  • Authorize your Google Account, and create a new query by hitting the NEW API button after the data source is saved.
  • Renamed the query name to assBasket, as we’ll need to access query names on the UI.
  • I created a new google sheet with the following columns:

Basket Name
Basket Image URL
Basket Create On

  • Copied the URL of google sheets and pasted it under the SpreadSheet URL property.
  • Set the Query Type to Insert Sheet Row

With this, our query is ready. Next, to build UI on Appsmith, you can just drag and drop widgets from the library on the right side of the screen. Appsmith’s canvas is quite responsive, and you can even drag widgets between two other widgets, and it will auto-resize, saving you valuable time! I added a container widget so that our app looks unified; we even have an option to add colors to this if we want to stylize the background a little more. I then dropped a text widget to set the app’s name and a button widget.

Here’s how it looks:

Image by Author | Screenshot of Page 1 Custom Index Tracker

Neat, yeah?

Next, since we want there to be a form to create new baskets, we should get the button to open a modal. To do this, click the settings icon on the button widget to see the property pane.

Property pane is where we can configure all the properties of the widgets. You can open this, by simply clicking on the widget, and you’ll see it docked on the right sidebar, just as shown in the above screenshot.

We need to update the onClick property of the button and set it to open a new modal. You can create it there or drag and drop a modal widget onto the canvas. Both options work. You do you.

Next, we need three types of inputs for the form, so I added three input widgets to this modal. You can set the labels as follows:

Basket Name
Basket Image URL
Basket Create On

Here’s a Loom on how it looks like:

Things look in great shape so far, in order to be able to track the index of the portfolios based on created time, I thought it was best to use a pre-installed library on Appsmith since it’s not possible to track this manually. I used moment.js to do this.

We can set the Basket Created On default value to:

{{moment().format(“MM ddd, YYYY HH:mm:ss a”)}}

This will save time when the portfolio is created.

On Appmsith, we can write JS anywhere across the platform to add customization using the moustache {{ }} bindings. It also comes with the moment.js library; hence, calling the function will save time when these portfolios are created.

Lastly, I opened the buttons property-pane and set the onClick property to run a query and choose addBasket query.

With this, my UI is all set; lastly, I went back to addBasket query and set the Row Object to the following:

{ "Basket Name":{{Input1.text}},
"Basket Image URL": {{Input2.text}},
"Basket Create On": {{Input3.text}}
}

Note that we will be collecting the values from the Input widgets and passing them to the Google Sheets via the addBasket query.

Next, to display all our baskets on this page, we need to create a new API. I created another one called getBasket, from the Google Sheets datasource. I updated the query method to Fetch sheet row list everything from the excel sheet. Here’s a screenshot:

Screenshot of getBasket query

Next, we have to bind our queries to the UI widgets. This part can be done like this:

  • Open the property pane of List1 widget, and set the Items to {{getBasket.data}}
  • Inside the list widget, select the image and set the image property to {{currentItem[“Basket Image URL”]}}
  • Set the text widget properties to {{currentItem[“Basket Name”]}}, and Basket Created On: {{currentItem[“Basket Create On”]}}

This is how it looks now:

Screenshot of getBasket query

Here’s what we’ve been able to do till now:

  • Added functionalities to add a new portfolio/basket and list them on Appsmith.

Now, we will work on adding stocks inside these portfolios. For this, it would be best to create a new page to search for stocks and add them to baskets.

Let’s create a new page; you can choose the option on the left sidebar and name it as ‘Basket Page’. The idea is to redirect to this page from Page1 and filter all the stocks present in the basket.

But how do we filter stocks? Usually, we do on a regular backed to consider an id and filter based on it. Similarly, on Appsmith, while navigating to Basket Page, I stored the value of the basket page to filter and display data on the Basket page.

I can use the code snippet I mentioned below on a new button widget placed on the list widget. So, when clicked, it should save the selected item on the list, redirect to the basket page, and filter accordingly.

We need to the button’s onClick property to:

{{(function () {
    storeValue("selectedBasket", List1.selectedItem["Basket Name"]);
    navigateTo("Basket Page") })() 
}}

Here, we need to use the storeValue from Appsmith to store the basket name in a variable named selectedBasket, the List1.selectedItem copies the data from the selected list item.

Next, we need to use the navigateTo function to redirect to a new page.

Adding Stocks into Baskets

Before we add any stocks into our portfolios, we’ll have to ensure that this page filters my stocks according to the selected basked from page one. So let’s add a container and a text widget to display the Basket name.

Note that this value comes from the global store, so on the Text property from Text widget’s property pane, we will have to bind value from the Appsmith store, and this can be done with a simple line:

{{appsmith.store.selectedBasket}}

Alright, the next cool feature for us to add is a search function. This way, we can easily search for stocks and add them to baskets; for this, we need to add another button and modal with the following fields:

  • An input widget
  • A button labbeld as Seach
  • A table widget to list out all the results
  • A select widget to add them to the list of baskets I created

The table widget definitely deserves high praise. It allows us to add filters and sort results. We can add relevant stocks based on where they are listed on. For example, I could simply search NASDAQ, which filters stocks listed there. If you want to know everything that you can do with a table widget, you’ve got to check out this detailed blog.

Before I tell you how I built this, here is how it looks like:

Screenshot of Basket Page UI

The search feature works with a simple API from Stockdata, to integrate this, I followed the following steps:

  • Click on the + icon next to the Datasources
  • Select Create New API, and paste the following URL:

https://api.stockdata.org/v1/entity/search?search={{stockcode.text}}&api_token=YOUR_API_HERE

  • Rename the API query to stockSearch

To get this working, make sure you add API token from Stockcode here. Please note that I use moustache bindings here. This adds the values I’m searching for from the input widget dynamically. Now, let’s head back to the modal and update the names of the widgets.

I renamed the widget names to the following, to make it easy while pushing data onto sheets:

Input Widget: stockcode

Table Widget: stockSearchTable

Now, the search API takes the value from the input widget, and on the table widget, we can bind the results from the API for the following functionalities:

  • Set the buttons onClick property to run a query and choose stockSearch query.
  • Bind the stockSearch query to the table widget, using {{ stockSearch.data }}

Next, we want to select which basket we want to add the searched stock to; for this, I just copied my API (getBaskets) from Page1, and bound it onto the selected widget with the following code:

{{
    getBaskets.data.map((row) =>   
    {
    return {
    		label: row["Basket Name"],
        value: row["Basket Name"]    
     }  })
 }}

Last, we need to save all this information onto a Google Sheet, so let’s create a new spreadsheet under the same Google Sheet and create a new API from the connected data source. I named this sheet Watchlisted Stocks and named the query addtoBasket.

These are the columns I have on my Google Sheet:

symbol
name
type
exchange
exchange_long
mic_code
country
watch_listed_on
basket

Now, on the query to collect all this information, we have to update the Row Object to the following:


{"symbol": "{{stockSearchTable.selectedRow.symbol}}","name":"{{stockSearchTable.selectedRow.name}}","type":"{{stockSearchTable.selectedRow.type}}","exchange":"{{stockSearchTable.selectedRow.exchange}}","exchange_long": "{{stockSearchTable.selectedRow.exchange_long}}","mic_code": "{{stockSearchTable.selectedRow.mic_code}}","country":"{{stockSearchTable.selectedRow.country}}","watch_listed_on": {{moment().format('MMMM Do YYYY, h:mm:ss a')}} ,"basket": {{Select1.selectedOptionValue}}}

With this last step, our watchlist will be ready. However, in order to show it on the page, we have to add a new table widget. Wait, I still had to pull all the watchlisted stocks from the google sheets. This is pretty straightforward, I create a new query from the GSheets datasource, set the method to Fetch Sheets Row, copy my GSheets URL, and update my Sheet Name to Watchlisted Stocks.

Now, we have to bind it on the new table widget, but here, it should only filter stocks that are added to this basket or portfolio, I do this by using a simple transforming when binding using the following code snippet:


{{     
    getStocksfromBaskets.data.filter((item) => {
    return item.basket == appsmith.store.selectedBasket
    })
 }}

As you can see, we’re now filtering our stocks based on the selectedBasket value from the Appsmith store that we saved while redirecting!

We now have all the portfolios and stocks ready.
Now it’s time for data visualization!

Building Time Series Charts and Tracking Index

As mentioned, Appsmith’s chart widget comes with the Fusion Chart configuration, which makes it much easier to build time-series charts. The other thing I love about this is, I can even create candlestick charts for the stocks I watchlisted inside the application. These charts are used by traders to determine possible price movement based on past patterns.

First, let me show you how to fetch details of all the stock prices, using the MarketStack API:

  • Inside the basket page, I created a new API (marketStackAPI), with the following URL:

http://api.marketstack.com/v1/eod?access_key=YOUR_API_KEY&symbols={{appsmith.store.current}}&date_from=2021-11-01&date_to=2021-12-12

Note: I’m filtering the data from 2020–11–01 to 2021–12–12, you can update this API accordingly based on your requirements.

In the API, as you can see, I have added an Appsmith store variable named current; this is to dynamically fetch the stock prices of the symbol or stock we wanted to analyze or add to our tracker.

Now, we need to write a code snippet under JS Objects, that stores all the data for all the stocks that are there inside my portfolio.

This is what I’ve used:

export default {

getBasketGraph: () => {let stocks = getStocksfromBaskets.data.filter((item) => item.basket == appsmith.store.selectedBasket)    let stockcodes = stocks.map(item => item.symbol)    let allStockData = [];    stockcodes.map((item, index)=> {    storeValue("current", item);     marketStackAPI.run(()=> {     let tempData = marketStackAPI.data;     allStockData.push(tempData.data);    }, () => {}, { allStockData });  })    storeValue("allStockData”, allStockData)    return appsmith.store.allStockData; 
}}

Here is what this code snippet does:

  • Stores all the symbols that are present in the basket in the stockcodes variable
  • Iterates through the stockcodes array and stores all the stock prices in a new variable allStockData
  • Returns the store variable when the function is executed

To create a time series chart, we need to drag and drop a new chart widget onto the canvas, below the table widget.

This is how it looks like:

Image by Author | Chart Widget on Appsmith

It comes with a default configuration, so we will have to change the type to a Custom Chart to configure our data onto this. We will need to follow a particular format in order to do this. Here’s the basic config for Fusion Charts:

{  "type": "",  "dataSource": {    "chart": { },    "data": [ ]  }}

Here, we need to pass in three parameters, the chart configuration, data, and the categories that need to be displayed on the x-axis. Here’s the updated code:


{    "type": "MultiAxisLine",    "dataSource": {        "chart": {            "xaxisname": "Date",            "vdivlinealpha": "",            "labeldisplay": "ROTATE",            "theme": "fusion"        },        "categories": [{                "category": {                    {                        marketStackAPI.data.data.reverse().map((row, index) => {                            return {                                "label": moment(row.date).format("LL"),                            }                        })}                }            }        ],        "axis": [{                "title": "Price",                "dataset": [{                        "seriesname": "Apple",                        "data": {                            {                                marketStackAPI.data.data.map((row, index) => {                                    return {                                        "value": row.close,                                    }                                })}                        }                    },                }            ]        }    }

Finally, this is how our graph will look like with the aforementioned configurations:

Image by Author | Charts from Custom Index

Additionally, I also created a list, that tells us the index value with simple math:

Image by Author | Custom Index and Predictions with Simple Math

Imagine having your own custom app terminal to track all your stock indexes in one place. If you’re into crypto, you can extend this app to even have a crypto tracker.

I hope that you had success following this tutorial and building your own app. If you got stuck somewhere, I’d be happy to help you out :)

More content at plainenglish.io. Sign up for our free weekly newsletter. Get exclusive access to writing opportunities and advice in our community Discord.

It Took Us Less Than 1 Hour to Build a PgAdmin Clone with Low Code: Here’s How We Did It?
3
January
2022
Engineering

It Took Us Less Than 1 Hour to Build a PgAdmin Clone with Low Code: Here’s How We Did It?

It Took Us Less Than 1 Hour to Build a PgAdmin Clone with Low Code: Here’s How We Did It?
Vihar Kurama
0
 minutes ↗
#
admin
#
postgresql
#
dashboard
#
community
Engineering

PostgresDB is an open-source database that uses and extends the SQL language. It’s used widely across many organizations to build a variety of apps. Developers tend to love and prefer PostgresDB for its simplicity and extensibility. Postgres Admin (PgAdmin) has a neat interface for accessing any Postgres instance from any device with a web browser. It allows administrators to carry out tasks like managing user accounts, databases, clusters, and more.

However, PgAdmin has a few biting downsides; here are a few:

  • Installation can be difficult
  • Troubleshooting and debugging is a complication, especially if you’re new to Postgres
  • Takes time to load on a machine, and is prone to freezing, especially when establishing a new database connection
  • Slow to respond to queries
  • Cumbersome interface when dealing with multiple databases

I think these are problems that can be dealt with if there was just a better user experience. I set out to build a new version of the PgAdmin! And I did this in under a few minutes. In this blog, I will tell you how I did this.

But, I have to say, cloning the PgAdmin app is not an easy task; there are multiple challenges here, as we have to implement several functionalities. With a fully functional PgAdmin app, we should be able to:

  • Establish connection on any remote cloud-based Postgres instances
  • Manage databases, roles and users
  • Create, alter, and drop tables on the connected databases
  • Provide UI for data export/import from CSV files, schema checker, etc.
  • Write queries on an editor to run SQL statements and see the results of querying against your database

Doing all this in under minutes is an impossible task if we have to code things from scratch, so we will be using a low code platform like Appsmith. Using Appsmith to build internal applications is super fun; we can connect to any data source, write queries, and build UI 10x faster than usual. This is entirely web-based; depending on the preference, we either self-host this on any server or simple use the community cloud edition.

How did we build this?

It took us less than an hour to build this, as Appsmith comes with a Postgres data source integration. With this, we can run any PG query and use it anywhere across the application using dynamic JS bindings. We will talk more about the features of the application in the following sections; before that, here is a sneak peek of how the application looks:

CleanShot 2022-01-03 at 15.21.31@2x.png

The application has two pages, one for managing tables (Table Manager) and another for testing and running queries (Query Executor). Now, let’s deep dive into each of these tables, and talk a bit more about their functionalities.

Table Manager

On the table manager, you can find all the tables from the connected PG data source. It offers all the functionalities that a PgAdmin does; you can create, read, update and delete these tables on this admin panel. Performing these CRUD operations on tables is pretty straightforward on Appsmith. Additionally, you can filter the tables based on the schema and update them whenever needed.

When you click any table name (select a row) from the table widget, you’ll see a new container on the right, where we display the three essentials:

  1. General Table Details: Here, we can update the information of the table and set the owner.
  2. Column Details: Configure all the settings for columns inside the selected table.
  3. Constraints: Add any relations between the columns inside the table.

Using these three features, we can manage the column properties and add different relations between them. We’ll talk a bit more about this in the next sections.

Query Executor

This is the second feature of our Appsmith PGAdmin. Here, we can execute any queries.

CleanShot 2022-01-03 at 15.42.41@2x.png

Our auto-complete and slash commands feature will make it much easier to test queries in real-time. For building this, we’ve used the RichText Editor widget on Appsmith; whenever we execute the query, we display all the responses on the table widget. However, this can be customized within minutes based on different use-cases.

Now, Let’s Use the Appsmith PgAdmin

In this section, we’ll talk about the different functionalities provided by the Appsmith PgAdmin with a few examples. We’ll also deep dive into some of the queries that were used on Appsmith to implement the functionality.

To see this in action, you will need to switch data sources on Appsmith. For this, you can connect your PG database by clicking on the + icon next to the Datasources section. Next, on every query we will see the data source it’s connected to. Here, we can switch the data source by selecting it in the data source dropdown.

Managing Tables: When working with databases, we often need to configure names, schemas, on Appsmith PgAdmin. Doing this is super easy on Appsmith! As soon as you open the PgAdmin, you will see the list of all the tables on the table widget. To see their configuration, we can simply select any row, as soon as it’s selected, we’ll see a new container on the right, where we can configure the entire table details. Here’s a screenshot:

CleanShot 2022-01-03 at 15.31.01@2x.png

As we can see, on the right, when a table is selected, we can see all the details under the general tab. We can also delete a particular table by clicking on the delete button inside the table. This is done by adding a custom column on the table widget and setting the column type to a button. If you prefer modals, then you can configure tables to show modals where we can update the necessary details.

Configuring Columns in Table: To manage columns inside the table, select the table and navigate to the Columns tab inside the container on the right.

CleanShot 2022-01-03 at 15.32.17@2x.png

Here, we will see a list widget, which has all the details from the selected table. We can edit the data type and also the column names, using the interface that we’ve created on the list widget. In case, you want to delete a particular column you can use the delete button that’s present on the list item. Additionally, you can toggle the not-null option to set the ‘accept null values’ for individual attributes.

Here’s a GIF:

CleanShot 2022-01-03 at 15.33.48.gif

In the list above, we only see the data types from the table columns; to add constraints such as foreign keys, we can simply navigate to the constraints tab and add or configure the attributes.

With this, our custom PgAdmin is ready for use. We use this application for internal processes like managing our Postgres instances. We definitely find it faster than the native PgAdmin app, and it’s definitely nicer to look at!

If you want to build any admin panel, you can do it with Appsmith. We’ve got all the popular database integrations, and this list is growing! Do join our growing community on Discord for more tips, and tricks on how to make the most out of Appsmith.

How To Build A Catalog Manager for a Shopify Store
3
December
2021
Tutorial

How To Build A Catalog Manager for a Shopify Store

How To Build A Catalog Manager for a Shopify Store
Confidence Okoghenun
0
 minutes ↗
#
Shopify
#
admin
Tutorial

In this article, we will be building a Shopify Dashboard that fits into your custom workflow. But before that, let’s set some context. Lots of store owners choose Shopify as their go-to online store. It’s a mature product with excellent features like the store admin panel and full API access.

That said, it’s not without its flaws.

Integrating with no-native Shopify services can be a real pain, and it’s almost impossible to build custom workflows with the Shopify admin panel. We’ve also published another post on how to make a Shopify admin panel.

In this article, I’ll be showing you how to overcome all of these hurdles so you can build a dashboard app for your Shopify store that fits into your workflow. We’re going to do this by covering the following concepts:

  • How to integrate with Shopify APIs on Appsmith
  • How to set a custom theme for your Shopify dashboard
  • How to use dynamic parameters in API calls
  • How to use the Google Sheets integration

Feel free to peek into the app we’ll be building.

Sounds good? Let’s begin!

How to integrate with Shopify APIs on Appsmith

Lots of services provide secure access to resources via APIs. This makes it possible to design complex workflow and unlock new capabilities. Shopify is not exempt from this rule as we’ll be extensively making use of their APIs.

To get started, enable Private apps in your Shopify dashboard. Create a new app and configure the permission level. You’ll be presented with the following details to interact with the API securely: Your store endpoint i.e https://your_store_name.myshopify.com/admin/api/2021-10/ A username And also a password i.e shppa_cc*******

With these details, we can begin building the dashboard. So signup for Appsmith if you’re yet to do so, and create a new app.

In the new app, click on Datasource > Create New > Authenticated API. Be sure to set Authentication Type to Basic and then fill the form. Here’s a quick snapshot of mine:

creating a Shopify datasource

Using the Shopify data source, let’s create a new API to get all products from the store. Call this get_products and it should be a request to the products.json route.

writing a query to get all products

Now we have data coming into the app. Let’s go build some styled UI components to display this.

How to set a custom theme for your Shopify dashboard

There are many widgets to choose from on Appsmith; the best part is that you can customize and style the look and feel of many. Let me show you how to do this.

From the widgets tab, drag a container widget into the canvas. In the opened configuration menu (called property pane), give it a Border Radius of 5 and set some Box Shadow. You should have a cool looking card container:

styling the container widget

Now you can bring in a list widget into the container and bind the data from the get_products query like this:

binding data to the list widget

And similarly, you can display the product image and name by accessing the data from {{currentItem}}. Here’s an example:

displaying list item data

Cool! That’s how easy it is to style components by using the Styles section in the property pane.

How to use dynamic parameters in API calls

So far, our app can read data and display it in the UI. Now let’s add a flow that allows us to write data. To do this, let’s build a product update feature.

Using the concepts illustrated in the previous sections, build an update form UI that looks like this:

update from UI

And should be configured such that all input widgets are named and have default values based on the product selected from the product_list widget i.e the tile input widget is named up_title and has a Default Text of {{product_list.selectedItem.title}}:

customizing the title input

Cool. Do the same for all the other input widgets in the form.

Now, using the Shopify data source configured earlier, create a new update query that takes input from the form widgets as parameters. Here’s an example:

{
    "product": {
        "title": {{up_title.text}},
        "body_html": {{up_body_html.text}},
        "product_type": {{up_product_type.selectedOptionValue}},
        "status": {{up_status.selectedOptionValue}}
    }
}


creating an update query

You’ll also notice that the ID of the product to be updated is populated from the product selected on the product_list widget and used in the endpoint URL.

Awesome! To finish this up, go back to the form submit button and configure it to run the update query. We should also add a get_products query so that all data in the app is refreshed i.e:

{{update_product.run(() => get_products.run())}}


linking the update query to the submit button

And that’s how easy it is to pass dynamic parameters to API calls!

Lastly, let’s talk about how to integrate with Google Sheets.

How to integrate with Google Sheets

This section will be quite advanced, so stick with me! We will build a bulk import functionality from Google sheet, a feature not natively supported by the Shopify API.

To get started, create a new page and under Datasource > Create New, click on Google Sheets. Authorize the datasource and create a new API that we’ll call get_products_from_sheets.

You can use this sample sheet as a reference for the Spreadsheet URL config. Here’s a sample configuration:

get products from sheets config

Now, you can build UI widgets to display the data returned as we did for the product list. In this sample, I used a table widget:

displaying sheet data

When the Sync button is clicked, we loop through the items and create each product by making a couple of API calls. Also, notice here that we have parameters passed to the API calls as the third arguments:

{{
    (() => {
      get_products_from_sheets.data.forEach((i) => {
        new_product.run(
          (res, i) => {
            new_image.run(
              () => {},
              () => {},
              { ...i, id: res.product.id }
            );

            new_price.run(
              () => {},
              () => {},
              { ...i, id: res.product.variants[0].id }
            );

            new_track_inventory.run(
              (res, i) =>
                new_inventory.run(
                  (res, i) => showAlert('Added: '+i.Title, 'success'),
                  () => {},
                  i
                ),
              () => {},
              { ...i, id: res.product.variants[0].inventory_item_id }
            );

          },
          () => {},
          i
        );
      });
    })();
}}


And here’s a snapshot of each of the queries call:

new product
new image
new price
new track inventory
new inventory

You can learn more about these resources/endpoints in the official Shopify docs.

What’s next?

Congratulations, you did it!

So far, we’ve implemented the core functionalities such as viewing, updating, and bulk import of products. But you can expand on this and add additional services i.e., payment with Stripe.

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.

Appsmith Basics: How to Query Databases and Bind Data to Widgets
3
December
2021
Tutorial

Appsmith Basics: How to Query Databases and Bind Data to Widgets

Appsmith Basics: How to Query Databases and Bind Data to Widgets
Confidence Okoghenun
0
 minutes ↗
#
admin
#
beginners
Tutorial

Every application involves some level of CRUD operations, be it reading records from a database, creating or updating records, or deleting records. All of which is put together by well-designed application UI and logic.

In this article, I will show you how to build a task manager in Appsmith using Firestore as the database. By doing this, we will cover and solidify core concepts like connecting to databases, writing queries, infusing data to widgets, and writing custom functionality using JavaScript.

In this article, you will learn how to do the following:

  1. Connecting to and querying Firestore
  2. Binding data to widgets on Appsmith
  3. Deploying the application you built

Let’s get started with the first.

1. Connecting to and querying Firestore

Before we go in-depth into using Firestore, you need to create an app. Signup for Appsmith cloud and click on the New button with orange. Now, we can connect the created app to the database.

Click on Datasources > Create New and then Firestore. Fill in the credentials to connect to your firebase instance. To make sure all details are correct, click on the Test button, after which you can save the data source.

To get your Service Account Credentials, go to your project settings in Firebase and generate a new Nodejs private key that will be copy-pasted into your Appsmith app.

Here’s a snapshot of my setup:

my Firestore configuration

Using the Firestore data source, we need to query for data. Let’s write a query to get tasks from the database. To display some data, I have a simple collection called asana that has some seeded data. A query to get tasks would look like this:

query to get tasks from database
The response data in the snapshot above shows the shape of each entry in the database. This will be useful in seeding your database.

Now that we have linked to Firestore and have some data coming in, let’s display that data in the app using widgets.

2. Binding data to widgets

To display the data coming in, we’ll need a List widget. Switch to the widget tab in the sidebar and drag a List widget into the canvas. (The widgets tab is below the app name)

widgets tab

On the List widget, a simple binding can be written like this:

binding data into the list widget

Similarly, we can display the data for each item in the List widget by bringing in Text widgets and writing their corresponding bindings.

displaying data in the list

That’s all you need to know about binding data to widgets; write {{}}, and you’re good to go.

Before we move on to deploying the app, let’s spend a few minutes building a form that will allow us to update/delete tasks.

To do this, drag in a Form widget into the canvas, and use a couple of text widgets to display the information about the task selected from the List widget, such as clicking on a new item on the list will automatically update the form. Your binding should look like this:

binding to display selected item from list

You should also do the same for the Datepicker, Input, and Select widgets required to build the form. For example, you should configure the selected widget this way:

![select widget configuration] (res.cloudinary.com/confidenceappsmith/image..)

You should also rename your widget to something more descriptive as I did in the Select widget shown above

Now, using the Firestore datasource configured earlier, you can write an update query like this.

query to update a task

And then, configure the submit button on the form to call the update_task query when clicked.

running update query on submit button click

Wasn’t that was quite easy? Using the same principles, you can write a query to delete the task.

3. Deploying the application

This is by far the easiest step. To deploy an app, click on the Deploy button to the top right corner of the screen.

You may also invite others to use/develop the app or even make it publicly available. You can use the share button just beside the Deploy button. This brings up a modal:

share button modal

Wrap up

you made it

You’ve made it! You’ve learned the core concepts of building apps with Appsmith, and I’m sure you’re ready to put this knowledge to use.

I’m going to leave a link to the app here with more features added to make it production ready. Click on this text to see the app.

Awesome! Go into the world and build! (Also consider giving us a star on GitHub)

If you have specific questions for me, just send me an email and I’ll be happy to help!

Building an Admin Panel with Appsmith
29
April
2021
Tutorial

Building an Admin Panel with Appsmith

Building an Admin Panel with Appsmith
Confidence Okoghenun
0
 minutes ↗
#
admin
#
dashboard
#
developer-tools
#
tools
Tutorial


Admin panels are an important part of every organization. They are necessary to serve customers better and run business processes smoothly. They help reduce management and people overhead significantly. But on the other hand, they take a lot of time to build and are troublesome and expensive to maintain. As a result, it is not the choice of project for most developers.

Thankfully, there are a lot of options available to developers today to build complex admin panels and dashboards fairly easily while making maintenance easier. An example of such a framework that we have explored, React-admin. We’ve written an article on how to use it. It’s better than building an admin panel from scratch, but it is still cumbersome to move from a simple admin panel to an admin panel that behaves like an internal app.

And that’s one of the reasons why we built Appsmith. Building Admin panels and internal apps should be easy and, more importantly, fun. Appsmith offers a drag and drop interface for creating UI elements and provides the option to write code wherever you may need to. You can build dashboards a hundred times faster on Appsmith, and that’s not an exaggeration.

For this article, I’d like you to imagine it's 2005. Netflix doesn’t stream video yet. You run a local DVD store, and you need software to manage your rentals. You’re the only developer in the organization. How do you go about building one that is fast, scalable and easily maintainable? In this article, I’m going to show you how to build an admin panel using Appsmith to manage DVD rentals for your store.

The app would have two pages. The first page would have a table listing all registered members of the store. The second page will have a table that holds all rental records. From here, new rental entries can be created, and existing rentals can be updated i.e from borrowed to returned. We would also be able to click on a customer from the first page and then taken them to the rentals page to see his rental history.

Before we begin, I’d like to give you a quick overview of the dataset we’ll be making use of. We’ll be using a modified version of the Sakila dataset. The database of choice is MongoDB on MongoDB Atlas and here’s the schema for each collection

// Customer collection
{
  "store_id": String,
  "first_name": String,
  "last_name": String,
  "email": String,
  "address_id": String,
  "activebool": Boolean,
  "create_date": Date,
  "last_update": Date,
  "active": Number
}
// Film collection
{
  "title": String,
  "description": String,
  "release_year": Number,
  "language_id": String,
  "rental_duration": Number,
  "rental_rate": Number,
  "length": Number,
  "replacement_cost": Number,
  "rating": String,
  "last_update": Date,
  "special_features": String,
  "fulltext": String
}
// Rental collection
{
  "status": String,
  "rental_date": Date,
  "film_title": String,
  "customer_email": String,
  "return_date": Date,
  "staff_id": String,
  "last_update": Date
}

That’s the overview of everything. Now let us get started!

Oh, snap! I almost forgot. Here are some of the core Appsmith concepts we’re going to cover:

  • How to connect Appsmith directly to your database (eliminates the need for an API server)
  • How to create data-driven widgets
  • How to perform CURD operations
  • How to securely share your application (no auth required)
  • How to deploy your application

We can get started now!

Connecting to your data source

It’s quite important that our application is linked to a persisted data source because admin panels are almost always data-driven applications. This data can come from an API, a database, or a Google sheets table as we have the freedom to use any of these on Appsmith. We’ll be with the database route.

Create an Appsmith account if you do not have one yet and let’s begin to smith our application 😁

As highlighted earlier, our application would be based on a MongoDB database. Don’t despair if you would like to follow along and do not have a ready-to-go database. Here’s a database connection you can make use of. Mind you, it’s read-only (don’t forget to say thank you)

Username: article_reader

Password: 0HkfXpxVpvvSvUOx

Connection url: cloud.r4vra.mongodb.net

Database name: movie_store

Alright, we’ve removed all hurdles from the track, now let’s start racing!

Go ahead to create a new application from your Appsmith dashboard and give it a fancy name. Next, let’s connect to the mongo database. Click on the DB Queries button on the left panel on the page and select MongoDB from the list of supported databases. Now go in and supply the database credentials to the from, also give this data source a descriptive name i.e. movie_store or movie_store_db. I think I’ll go with movie_store. And lastly, hit the big green save button.

1-connecting to mongodb.png

Awesome! We’ve connected the application to the database. We can now do some querying and build data-driven widgets. Let’s move on to the next section.

Creating data-driven widgets

We now have a database connected to the application. The next thing we need to do is to create a widget that can be used to create, update and display data from the database. To achieve this, we need a way to fetch data from the database. This can be done on Appsmith by writing database queries.

Rolle up your sleeves because we’re going to be writing our first DB query! Click on the DB Queries section again, and you’ll see a button to create a new query on the MongoDB movie_store data source. Give the query a name i.e. get_customers, and select a read template from the list because we’re going to be reading documents from the Customer collection.

The get_customer query should find all customers in the Customer collection, sort them by email and return a fixed limit of customers. Your query should look like this.

// Customer collection
{
  "find": "Customer",
  "sort": {
    "email": 1
  },
  "limit": 10
}

Configure your query and hit the run button, and you’ll get a list of 10 customers. Sweet!

It would be much readable to display this data in a table. So let’s do exactly that. Click on the Add widget button just below the query editor, and you’ll get a new table widget on the canvas that has been automatically linked to the get_customers query. An alternative way would be to click on the Widgets section and drag a table widget to the canvas. Then, data from the query can be bound to the widget via the Table Data property on the widget’s configuration menu using moustache syntax.

// Customer collection
{{get_customers.data}}
It’s important you give the table a descriptive name. Consider changing the name from Table1 to customers_table
2-writing binding for customer table.png

Go ahead to customize the table by re-ordering the columns and hiding columns you don’t like. We’ll also need to paginate the table, so on the table’s config menu, turn on Server Side Pagination and configure the onPageChange action (in the Actions subsection of the menu) to execute a DB query and then select the get_customers query.

We’ll need to update the query and add a skip property that calculates a skip based on the current table page number to simulate pagination. The moustache syntax would be very handy in this case to perform quick evaluations, so let’s make use of it. Your updated get_customer query should look like mine.

{
  "find": "Customer",
  "sort": {
    "email": 1
  },
  "skip": {{(customers_table.pageNo - 1) * 10}},
  "limit": 10
}

As you may have noticed, we are accessing the table’s page number from within the {{}} through the object bearing the widget’s name (customers_table in this case). You can learn more about writing Javascript in Appsmith here.

Click on the page number on the table widget now fetches new records. We’ve been able to query the database, bind data to a widget and perform customization on the widget. Good job!

Now, we’re going to create a new page to manage rentals. Click on the plus button on the Pages section of the left-hand panel and create a new page. Call this the Rentals page and update the name of the home page to Customers. On this new page, you’re going to be building a table to display rentals like we just did for the Customers page. You’ll need a query (should be named get_rentals) and a table widget (should be named rentals_table) for this. Your query to get rentals should look like this.

{
  "find": "Rental",
  "sort": {
    "_id": 1
  },
  "skip": {{(rentals_table.pageNo - 1)*10}},
  "limit": 10
}

Where rentals_table is the name of your table widget on the Rentals page.

Good luck!

Creating new rentals

After completing the table on the Rentals page, the next feature we need to build in is the ability to create new rental records.

Here’s the game plan: We’re going to build a form that takes as input the status, rental data, film title, customer email, and return date. This form would be housed within a modal widget that would be triggered by clicking on a ‘Create new rental’ button. Now, you have a good overview, let’s get started!

On the Rentals page, head to the widget’s section and drag in a modal widget. In the modal’s config menu, update the name to create_rental_modal and set the Modal type to Form Modal. Now you see we have more space to work with.

With create_rental_modal open, drag in three dropdown widgets and two date picker widgets. The name of the first dropdown widget should be updated to customer_dropdown. Following this convention, go ahead and rename the other two dropdowns to film_dropdown and status_dropdown. For the two date pickers, their names should similarly be updated to rental_datepicker and return_datepicker. You should drag in a few text widgets to label these inputs.

4-create rental modal.png

We need a button on the UI to trigger the opening up of the modal. Let’s drag a button from the widgets section to the canvas. Give this button a new label i.e Create New Rental, and set the onClick action to Open Modal, then select create_rental_modal. Clicking on the button now opens up the modal.

Nice! We’ve completed the UI for the crate form. Now let’s wire it up with some queries.

The first query we’ll be writing for the create form would get all registered customers from the DB. We would use the returned data to build the options for the customer_dropdown. This query would be similar to the get_customers query from the Customers page.

Go ahead and create a new query named all_customers. It should be configured as shown below

{
  "find": "Customer",
  "sort": {
    "email": 1
  }
}

Now, we can use the data returned from this query to build the options of the customer_dropdown. Open up the create_new_rental modal and head to the config menu of the customer_dropdown. Now replace the content of the Options section with the snippet shown below.

{{all_customers.data.map(c => ({label: c.email, value: c.email}))}}

This returns an array of objects whose label and value are the email addresses of the customers from the all_customers query. You can play around with the dropdown and see that the options generated from the query. Sweet 🤩

Now you’re going to do the same thing for the film_dropdown. Create an all_films query and use the title field of each document to build the options for the dropdown. Your query should look like this.

{
  "find": "Film",
  "sort": {
    "title": 1
  }
}

Cool! For the status_dropdown, we can simply hard code the Options with this simple JSON array.

[
  { "label": "borrowed", "value": "borrowed" },
  { "label": "delayed", "value": "delayed" },
  { "label": "lost", "value": "lost" },
  { "label": "returned", "value": "returned" }
]
Don’t forget to set a default option for each dropdown widget The rental_datepicker and return_datepicker widgets do not require further configuration, and they are good to go. The last thing we need to do is to write a query that would create a new document on the ‘Rental’ collection using the data from the input widgets. Alright, go ahead and create a new query called create_rental and configure it this way.
{
  "insert": "Rental",
  "documents": [
    {
      "staff_id": "{{appsmith.user.email}}",
      "status": "{{status_dropdown.selectedOptionValue}}",
      "customer_email": "{{ customer_dropdown.selectedOptionValue }}",
      "film_title": "{{ film_dropdown.selectedOptionValue }}",
      "rental_date": "{{ rental_datepicker.selectedDate }}",
      "return_date": "{{ return_datepicker.selectedDate }}",
      "last_update": "{{moment().format()}}"
    }
  ]
}

Now we can go back to configure the big green Confirm button on the modal to run this query when it is clicked. We also want to close the modal and refresh the rental table when the button is clicked. Because what we want to do is a complex chain of actions, we can write some JavaScript to achieve this. Click on the JS button just beside the onClick button of the Confirm button and supply the bellow code snippet.

{
{create_rental.run(
  ()=> {
      closeModal("create_rental_modal");
    get_rentals.run();
  }
)}
}

Great Job! Go ahead, give it a spin and create a few new rentals.

Updating a rental

The flow for updating a rental is quite similar to that of creating a rental. We’ll need a modal that would be opened by clicking on a button. The modal would only contain form elements for updating the status and return date of the rental.

The only difference here is that we want the rental in question to be the one selected from the table. To do this, we need to head back to the config menu for the rentals_table and create a new column by clicking on the Add a new column button. Click on the gear icon of the newly created column and set the column name to Update. Also, set the Column type to button and set its Lebel property to Update. For its onClick action, it should be configured to open up the update_rental_modal which you’re going to build on your own.

And that all you need. Oh, you’ll also need to write a update_rental query too. It should look somewhat like this.

{
  "update": "Rental",
  "updates": [
    {
      "q": {
        "_id": "{{rentals_table.selectedRow._id}}"
      },
      "u": { 
        "$set": {
      "status": "{{ update_status_dropdown.selectedOptionValue }}", 
          "return_date": "{{update_return_date_datepicker.selectedDate}}",
          "last_update": "{{moment().format()}}"
     } 
      }
    }
  ]
}

Now you have everything you need to know. Go ahead and build the update flow.

Putting it all together

You’ve done a good job making it this far. I’m so proud of you that I’m going to leave the gif below. 🤗

We’ve been able to accomplish most of what we set out to do. We have a table on the Customers page to view all customers. On the Rentals page, we can view all rentals, create a new rental or update existing rentals. It was no small feat we accomplished.

I’ll like us to add one more feature. Let’s make it such that clicking on a customer on the customers_table takes us to the Rentals page, and then we can view all rentals made only by that customer.

We’ll need column buttons for each row like we did for the update flow. In this case, the onClick action of the custom column buttons would perform a navigation to the Rentals page and pass the selected customers’ email from the table as query params. Use the below snippet in the Query Params configuration for the navigation.

{{{customer_email: customers_table.selectedRow.email}}}

And lastly, we need to update the get_rentals query in the Rentals page to filter by customer_email coming in as a query param. We also add a fallback just in case no email is passed.

{
  "find": "Rental",
  "sort": {
    "customer_email": 1
  },
    "filter": {
    "customer_email": {
      "$regex": "{{appsmith.URL.queryParams.customer_email || ""}}"
      }
    },
 "skip": {{(rentals_table.pageNo - 1) * 10}},
  "limit": 10
}

And that’s it! You’ve completed the app. 🥳️🎉🎉

Sharing and deploying the app

It’s quite easy to hand off your application to the team that would make use of it i.e customer support. You don’t have to worry about an authentication/authorization flow; Appsmith does all the work for you.

There’s a SHARE button on the top right side of the page. You can easily use it to invite users to the application and set roles i.e app viewer or developer. No hassle is required!

Clicking on the ‘DEPLOY’ button to the right of the share button will publish all changes made in the editor to the hosted version of the application. Isn’t that cool?

Wrapping up

We set out to build an admin panel, and we’ve seen how we can do it very easily using Appsmith. Going this route will drastically reduce the development time and resources put into maintaining the app.

If you found this tutorial helpful, please consider giving us a star on Github.

Cover Photo Credits: Photo by Serpstat from Pexels

Building an Admin Dashboard with React-admin
29
April
2021
Resources

Building an Admin Dashboard with React-admin

Building an Admin Dashboard with React-admin
Confidence Okoghenun
0
 minutes ↗
#
admin
#
dashboard
#
frontend-frameworks
Resources

React admin has been one of the holy grail frontend frameworks for building responsive admin panels. It offers a lot of really cool features such as data validation, optimistic rendering, accessibility, and action undo. React-admin is also plug-and-play as it supports standard REST APIs and a handful of GraphQL dialects. Being a Reactjs framework, it also gives you access to thousands of plugins and libraries available in Javascript and the React ecosystem.

In this article, I would like to show you how to build an admin panel using React-admin.

We’re going to be building a dashboard to manage DVD movie rentals for a local rental store. The first page would have a table listing all registered members of the store. The second page will have a table that holds all rental records. From here, new rental entries can be created and existing rentals can be updated i.e from borrowed to returned. We would also be able to click on a customer from the first page and then be taken to the rentals page to see his rental history.

Here’s a gif and a link to the completed application

You can view the demo app here

Dashboard link: as-react-admin.netlify.app

username: cokoghenun@appsmith.com

password: 123456

Through building this dashboard, we’re going to cover core React-admin concepts such as

  • Resources
  • List view
  • Edit/Create view
  • Reference inputs and
  • Authentication

Since React-admin requires an API server we would need to build one on top of the database. Speaking of the database, we’ll be making use of MongoDB and the demo dataset is a modified version of the Sakila dataset.

To save time and get to the fun part of building the dashboard with React-admin, we’ll be making use of Loopback to generate a Nodejs API over the database. If you are not familiar with Loopback, it is a highly extensible Node.js and TypeScript framework for building APIs and microservices.

You can skip this if you already have an API to use

We’re almost set. But before we begin, I’d like to give you a mini-map of the entire article. The first part of this article will focus on generating an API server over the database on MongoDB using Loopback. The second part of this article would cover how to use React-admin to build a dashboard from the API generated in the first section.

Alright, everything looks good. Let’s get started!

Generating an API server

There are many ways to build an API server. You can roll up your sleeves and build one yourself(this takes a lot of time) or you can choose to go with a framework. Loopback is the fastest framework I found to build Nodejs APIs over a database. It supports a host of databases ranging from in-memory to document to relational databases.

The API that would be generated using Loopback will have three resources, the first being the customer resource that represents customers who come to rent DVDs from the store. We also have the film resource, representing DVDs that are in stock. Lastly, we have the rentals resource, which records each rental.

Here’s the schema for each resource

// Customer resource{
  "store_id": String,
  "first_name": String,
  "last_name": String,
  "email": String,
  "address_id": String,
  "activebool": Boolean,
  "create_date": Date,
  "last_update": Date,
  "active": Number
}


// Film resource
{
  "title": String,
  "description": String,
  "release_year": Number,
  "language_id": String,
  "rental_duration": Number,
  "rental_rate": Number,
  "length": Number,
  "replacement_cost": Number,
  "rating": String,
  "last_update": Date,
  "special_features": String,
  "fulltext": String
}

// Rental resource
{
  "status": String,
  "rental_date": Date,
  "film_title": String,
  "customer_email": String,
  "return_date": Date,
  "staff_id": String,
  "last_update": Date
}

Okay! Now let’s get started by install Loopback CLI with npm

npm i -g @loopback/cli

We can easily scaffold the Nodejs server using the Loopback CLI. It configures a Typescript compiler and installs all required dependencies. Let’s run the CLI and answer a few prompts to generate a new app

lb4 app

You should have your app configured as shown below

Hit enter and give the CLI some time to set up the app.

Creating a model

Now that the loopback app has been scaffolded, cd (change directory) into the app folder, and let’s start by creating a model for each resource. A model communicates the shape of each document for a particular resource, much like the schema shown earlier.

Let’s create a model for the customer resource using the Loopback CLI

lb4 model

As we did when generating the app, answer the CLI prompts. Yours should look like this

3-configuring the customer model.png

Great Job! Now, go ahead and do the same for the film and rental resources. Don’t forget that to create a new model, you’ll need to run the lb4 model command.

Connecting to the database

Next, we’ll need to link the Loopback app to the Mongo database. Loopback provides two entities to help us accomplish this, and they are the datasource and repository mechanisms.

A datasource represents a database connection that would be used to store and retrieve documents from the database i.e MongoDB or PostgreSQL. On the other hand, a repository links a resource on the Loopback app to a particular table or collection in the database. For example, the customer resource is linked to the Customer collection in the database using a repository.

Now, let’s add a datasource to the app, and link it to our MongoDB database. We can easily do this using the CLI command below

lb4 datasource

As usual, go ahead and answer the CLI prompts, supplying the database credentials to the CLI

4-configuring the mongo datasource.png

Awesome! Now we can add a repository for each resource.

Run the command below and let’s set up a repository for the customer resource. Notice that we have to link the created resource to the target resource, and in this case, it is the customer resource

lb4 repository

Cool! Go ahead and do the same for the film and rental repositories. I’m confident you can finish up on your own 😜

Adding CRUD functionality

Great Job! That was a lot we just covered. Right now, we have models for each resource, a datasource, and repositories linking each model to its respective collection in the database.

The last piece of the puzzle is to add CRUD functionality for each resource.

We can do this by creating controllers. Controllers do the grunt work of creating, reading, updating, and deleting documents for each resource.

As you may have already guessed, we can create a controller using the controller command. Now, let’s create a REST controller for the customer resource. Notice we’ll need to use the model and repository created earlier for the customer resource.

lb4 controller
Note that the Id is a string and is not required when creating a new instance
6-creating a controller.png

As usual, go ahead and do the same for the film and rental resources.

Awesome! We now have a full-blown REST API that was generated in a few minutes. Open up the project folder in your favorite code editor and you’ll see all the code(and folders) generated by Loopback.

I recommend you change the default port in the index.ts file to something else i.e 4000 because Create React App (used by React-admin) runs by default on port 3000

You can start the server using the start script

npm start

You can find a playground and the auto-generated API documentation for your server by visiting the server address on your browser i.e http://localhost:4000/

7-api explorer.png

Alright! Now we have a REST API server with CRUD functionality, we can move on with creating the admin dashboard for using React-admin.

Enter React-admin

We’ve finally gotten to the fun part, yay!

As a quick recap, we have a Loopback API generated in the last section that serves the customer, film, and rental resource with the following endpoints and data schema

// /customers endpoint
{
  "store_id": String,
  "first_name": String,
  "last_name": String,
  "email": String,
  "address_id": String,
  "activebool": Boolean,
  "create_date": Date,
  "last_update": Date,
  "active": Number
}


// /films endpoint
{
  "title": String,
  "description": String,
  "release_year": Number,
  "language_id": String,
  "rental_duration": Number,
  "rental_rate": Number,
  "length": Number,
  "replacement_cost": Number,
  "rating": String,
  "last_update": Date,
  "special_features": String,
  "fulltext": String
}

// /rentals endpoint
{
  "status": String,
  "rental_date": Date,
  "film_title": String,
  "customer_email": String,
  "return_date": Date,
  "staff_id": String,
  "last_update": Date
}

So here’s the game plan. We’re going to use this API to build a dashboard to manage DVD movie rentals. The first page would be a table showing all customers. Then we can click on a customer and view all his rentals on a new page. We can update the return date and status of each rental i.e from borrowed to returned. Lastly, we can view all rentals on the rentals page and create a new entry or edit an existing one.

Phew! Now we can finally begin with React-admin 😅

React-admin is a powerful front-end framework for building admin panels and dashboards. It is highly customizable and has a host of other great features. Since it is based on Reactjs, it can be used with thousands of other Reactjs and Javascript libraries.

React admin requires a base Reactjs project. We are going to be going with Create-React-App (CRA) in this article. So let’s set up the project with CRA

npx create-react-app rental-admin-panel

Give the CLI some time to install all dependencies and finish setting up the project. Then, cd into the project directory and go-ahead to install React-admin and the Loopback dataprovider.

npm install react-admin react-admin-lb4

A dataProvider is the mechanism with which React-admin communicates with a REST/GraphQL API. The Loopback provider for React-admin enables it to understand and use Loopback APIs i.e how to paginate or filter requests. If you aren’t using a Loopback generated API, you should look into using one of these dataProviders for React-admin.

Open up the project in your favourite code editor and replace everything in the App.js file with the below starter code

//src/App.js

import React from 'react';
import lb4Provider from 'react-admin-lb4';
import { Admin, Resource } from 'react-admin';

function App() {
  return (
    // ------------- Replace the below endpoint with your API endpoint -------------
    
      
    
  );
}

export default App;

So far so good. But we have some new concepts to clear up. In the starter code above, we supply a dataProvider to React-admin which enables it to query the API. The next thing we did up there is to register a resource from the API that we would like to use in React-admin. This is done simply by supplying the endpoint as a name prop to the <Resource> component.

You don’t need to add the forward-slash “/” to the resource name

Going by this rule, we must register it as a resource whenever we need to query a new API endpoint. In this way, React-admin becomes aware of it. Moving on...

Creating the Customers' table

The easiest way to view all customers’ info is to have a paginated table displaying all customers’ info. React-admin makes it easy to do this by providing us with a <List> component.

The <List> component generates a paginated table that lists out all documents in a particular resource. We can choose which fields we want to show up on the table by wrapping them in the appropriate <Field> component i.e a date property on a document would be wrapped in a <DateField> component.

The data property on the document is linked to the <Field> component using the source prop. This prop must contain the exact property name. And the field name showing up on the table can be customized using the label prop.

We can also create a filter for the table using the <Filter> component and specify an action to be triggered whenever an item is clicked on the table using the rowClick props on the <Datagrid> component. You can learn more about filtering here and row actions here

Alright! So we want a customer table to show all the customers. We also want this table to be filterable by customer email. Lastly, we want to be able to click on a customer and see all his rentals (we haven’t created the rentals page yet, but we will shortly).

Let’s see all of this in action. Go ahead to create a customer list component with the following content

//src/components/CustomerList.js

import React from 'react';
import { List, Filter, Datagrid, TextField, SearchInput, } from 'react-admin';

// ------------- filter component which filters by customer email -------------
const CustomerFilter = (props) => (
  
    
  
);

const CustomerList = (props) => (
  } title='List of Customers'>
// ------------- rowclick action that redirects to the rentals of the selected customer using the customer id -------------
     {
        return `/rentals?filter=%7B%22customer_email%22%3A%22${record.email}%22%7D&order=ASC&page=1&perPage=10&sort=film_title`;
      }}
    >
      
      
      
      
      
    
  
);

export default CustomerList;

Next, we need to link the <CustomerList> component with the customer resource component.

//src/App.js

// ------------- import CustomerList -------------
import CustomerList from './components/CustomerList'; 

//…

// ------------- use CustomerList as the list view on the customer resource -------------

Save your code and let’s head over to the browser. You can see we have a nice paginated, and filterable customer table that has been automatically generated and is rendering customer information from the API. Cool right? 😎

8-customer table ui.png

Not so fast! Go ahead and create a similar list table for the rental resource. You can name this component RentalList. If you are curious or get stock, feel free to fall back on the code here.

Creating and Editing a Rental

We have two more views to create and they are the edit and create view for the rental resource. They are quite similar to each other and are both similar to the list view but with a few differences.

The edit view would be used to edit an item clicked on the rental table.

To wire up this behaviour ensure that you have rowClick='edit' on the <Datagrid> component in <RentalList>

An edit view uses a <SimpleForm> component, which in reality is a simple form with nested <Input> components. Like with the <Field> components, each <Input> component used is based on the data type of the property to be edited i.e a <TextInput> component is used on a text property. Inputs also require the source props and optional label props as we’ve already seen with the <Field> component.

Bringing it all together, the edit view for the rental resource would look like this:

Notice that some inputs have been disabled using the disabled props
// src/components/RentalEdit.sj
import React from 'react';
import {
  Edit,
  SimpleForm,
  TextInput,
  DateTimeInput,
  SelectInput,
} from 'react-admin';

const RentalEdit = (props) => (
  
    
      
      
      
      

      
      
    
  
);

export default RentalEdit;

Don’t forget to import and use the edit view in the rental resource component in your App.js file.


//src/App.js

// ------------- import RentalEdit' -------------
import RentalEdit from './components/RentalEdit'; 

//…

// ------------- use RentalEdit as the edit view on the rental resource -------------
 

Save your files and let’s head to the browser. Click on an order to see the magic!

Okay, so we’ve completed the edit view. Now moving on to make the create view.

The create view is quite similar to the edit view. It’s so similar that I’m just going to paste the code right here and you wouldn’t be able to tell the difference. Just kidding 😜. Anyway, here’s the code for the create view

// src/components/RentalCreate.js
import React, { useState, useEffect } from 'react';
import {
  Create,
  SimpleForm,
  DateTimeInput,
  SelectInput,
  useNotify,
  useRefresh,
  useRedirect,
  useQuery,
  TextInput,
} from 'react-admin';

const RentalCreate = (props) => {
  const notify = useNotify();
  const refresh = useRefresh();
  const redirect = useRedirect();

  const onSuccess = ({ data }) => {
    notify(`New Rental created `);
    redirect(`/rentals?filter=%7B"id"%3A"${data.id}"%7D`);
    refresh();
  };

  const [customers, setCustomers] = useState([]);
  const { data: customer } = useQuery({
    type: 'getList',
    resource: 'customers',
    payload: {
      pagination: { page: 1, perPage: 600 },
      sort: { field: 'email', order: 'ASC' },
      filter: {},
    },
  });

  const [films, setFilms] = useState([]);
  const { data: film } = useQuery({
    type: 'getList',
    resource: 'films',
    payload: {
      pagination: { page: 1, perPage: 1000 },
      sort: { field: 'title', order: 'ASC' },
      filter: {},
    },
  });


  useEffect(() => {
    if (film) setFilms(film.map((d) => ({ id: d.title, name: d.title })));
    if (customer)
      setCustomers(customer.map((d) => ({ id: d.email, name: d.email })));
  }, [film, customer]);

  return (
    
      
        
        
        
        

        

        
      
    
  );
};

export default RentalCreate;

The only difference here is that we have two select inputs that display a list of all customers and films by manually querying those resources.

Instead of writing custom logic to query the customer and film resources, we could have easily use the built-in <ReferenceInput> component. But currently, there's no way to set the selected value from the <SelectInput> component to something other than the document id. In the create form, we require the email field from the customer resource and the title field from the film resource. That is why we are manually querying, else the <ReferenceInput> component would have been awesome.

Do not forget to import and use the create view we just made. Also, register the film resource in App.js

//src/App.js

// ------------- import RentalCreate -------------
import RentalCreate from './components/RentalCreate';


//…

// ------------- use RentalCreate as the create view on the rental resource -------------
 
// ------------- register the film resource -------------
 
If a resource is registered and no list view is passed to it, React-admin hides it from the navbar. But the resource is still useful for querying as we did for the film select input in the <RentalCreate> component.

This is the moment you’ve been waiting for! Save your files and head over to the browser. You’ll notice that we now have a create button on the rentals table, and clicking on a rental takes you to edit that rental. Sweet!

We’ve finally completed the dashboard! 🥳 🎉 🎊

We have a complete admin panel to manage rentals. We can see a list of customers, select a customer and view all his orders and lastly, we can create new rental entries or edit existing ones. Awesome!

For some extra credit, let's add some authentication.

Extra credit: Authentication

You must add some authentication to your apps, else anyone would be able to use it, even malicious individuals! Thankfully, adding authentication to our API and admin dashboard is not too difficult.

The first part of this section will focus on adding authentication to the Loopback API. You can skip this if you’ve been following along with your API. Next, we’ll look at implementing auth on the React-admin panel.

Securing the API

Loopback has various authentication strategies that we can implore to secure the API. We are going to be going with the JWT authentication strategy, mostly because it’s super easy to set up and is fully supported by React-admin.

Enough talk, let's get started by installing the JWT auth extension library and Validatorjs on the Loopback API server.

npm i --save @loopback/authentication @loopback/authentication-jwt @types/validator

Next, bind the authentication components to the application class in src/application.ts

// src/appliation.ts

// ----------- Add imports -------------
import {AuthenticationComponent} from '@loopback/authentication';
import {
  JWTAuthenticationComponent,
  SECURITY_SCHEME_SPEC,
  UserServiceBindings,
} from '@loopback/authentication-jwt';
import {MongoDataSource} from './datasources';
// ------------------------------------

export class TodoListApplication extends BootMixin(
  ServiceMixin(RepositoryMixin(RestApplication)),
) {
  constructor(options: ApplicationConfig = {}) {

    //…

    // ------ Add snippet at the bottom ---------

    // Mount authentication system
    this.component(AuthenticationComponent);
    // Mount jwt component
    this.component(JWTAuthenticationComponent);
    // Bind datasource
    this.dataSource(MongoDataSource, UserServiceBindings.DATASOURCE_NAME);
    // ------------- End of snippet -------------
  }
}

Great job! We now have a foundation for auth.

Authentication usually works by validating the credentials of the user attempting to sign in and allowing him to go through if valid credentials are supplied. Thus, we’ll then need to create a user resource to represent a user. For our purposes, a user only has an id and an email field.

Alright, let’s create the user model using the Loopback CLI. Answer the CLI prompts as usual

lb4 model

11-user model.png

We’ll also need to create a controller for the user resource that handles all authentication logic. You can use the CLI to generate an empty controller.

Note that this controller would need to be an empty controller and not a REST controller
lb4 controller
12-user controller.png

The generated empty controller file can be found in src/controllers/user.controller.ts. Copy the contents of the file linked here into your controller file. It contains all the authentication logic. You can find the file here

Visit the link above and copy its contents into the user.controller.ts file

Finally, we can secure the customer resource by adding the authentication strategy we just implemented to its controller. Here’s how to do it:

// src/controllers/order.controller.ts

// ---------- Add imports -------------
import {authenticate} from '@loopback/authentication';

// ------------------ Add auth decorator -----------
@authenticate('jwt') // <---- Apply the @authenticate decorator at the class level
export class CustomerController {
  //...
}

Do the same for the film and rental resources by adding the authentication strategy to their respective controller files.

And that’s it! Visiting the API playground on the browser http://localhost:4000/explorer/ you’ll notice we have a nice green Authorize button at the top of the page. We also now have signup and login routes to create user accounts and log in.

You’ll need to use this playground/explorer to create a new user

Now, let’s use this authentication on the React-admin dashboard.

Adding authentication to React-admin

Implementing authentication on the React-admin dashboard is fairly straightforward. We need an authProvider which is an object that contains methods for the authentication logic, and also a httpClient that adds the authorization header to every request made by the dashboard.

Create an Auth.js file in src/Auth.js that contains the authProvider method, and the httpClient function. Here’s what the content of the file should be

// src/Auth.js

export const httpClient = () => {
  const { token } = JSON.parse(localStorage.getItem('auth')) || {};
  return { Authorization: `Bearer ${token}` };
};

export const authProvider = {
  // authentication
  login: ({ username, password }) => {
    const request = new Request(
      process.env.REACT_APP_API_URL + '/users/login',
      {
        method: 'POST',
        body: JSON.stringify({ email: username, password }),
        headers: new Headers({ 'Content-Type': 'application/json' }),
      }
    );
    return fetch(request)
      .then((response) => {
        if (response.status < 200 || response.status >= 300) {
          throw new Error(response.statusText);
        }
        return response.json();
      })
      .then((auth) => {
        localStorage.setItem(
          'auth',
          JSON.stringify({ ...auth, fullName: username })
        );
      })
      .catch(() => {
        throw new Error('Network error');
      });
  },
  checkError: (error) => {
    const status = error.status;
    if (status === 401 || status === 403) {
      localStorage.removeItem('auth');
      return Promise.reject();
    }
    // other error code (404, 500, etc): no need to log out
    return Promise.resolve();
  },
  checkAuth: () =>
    localStorage.getItem('auth')
      ? Promise.resolve()
      : Promise.reject({ message: 'login required' }),
  logout: () => {
    localStorage.removeItem('auth');
    return Promise.resolve();
  },
  getIdentity: () => {
    try {
      const { id, fullName, avatar } = JSON.parse(localStorage.getItem('auth'));
      return Promise.resolve({ id, fullName, avatar });
    } catch (error) {
      return Promise.reject(error);
    }
  },
  getPermissions: (params) => Promise.resolve(),
};

Alright! Now let’s make use of the authProvider and httpClient in our app. Import authProvider and httpClient from ‘Auth.jsintoApp.jsand passhttpClientas a second parameter tolb4Provider. Then add an authProvider prop to theAdmincomponent and pass inauthProvider` as its value.

Simple and easy!

// ----------- Import Auth -------------
import { authProvider, httpClient } from './Auth';

//…

// ------------ Use httpClient and authProvider in the Admin component ---------
 //...

Save the files and head back to the browser and you’ll be greeted with a login screen. Fill in the email and password of your registered user and you’ll be taken to the customers’ table like before.

And that’s it! We now have a super-secured app! 💪

Deploy 🚀

We now have a fully functional admin dashboard with authentication. Lastly, I’ll like to walk you through deployment to your favourite cloud provider.

Since the API generated using Loopback is a standard Nodejs server, you can deploy your app to any Nodejs hosting provider i.e Heroku or Glitch. But note that you will need to move all packages under devDependencies to the dependencies section in your package.json file.

And for the React-admin dashboard, you can deploy it on any static hosting service i.e Netlify or Vercel. Don’t forget to replace the lb4Provider URL with that of your hosted backend.

Building an Admin Panel with Django Admin in 2021
15
June
2021
Tutorial

Building an Admin Panel with Django Admin in 2021

Building an Admin Panel with Django Admin in 2021
Confidence Okoghenun
0
 minutes ↗
#
django
#
python3
#
python
#
admin
Tutorial


Admin panels are at the heart of every business operation, be it sales, analytics, user management, and so much more. That said, it isn't easy to find the right tools to help you build robust and scalable systems without spending too much time.

Django admin has been the go-to solution for many developers over the years. It provides a fast, easy-to-use, and highly maintainable framework for building full-stack applications and admin dashboards. These reasons make it a great choice to build admin panels.

To show you how to build admin panels with Django, we will build a dashboard for moderators to manage business reviews. All of this will be built on Postgres and the Public Yelp dataset.

Here’s a sneak peek of what we’ll be building:

Awesome right? Let’s get started!

Setting up a project

We start by setting up a project. A project is a collection of settings for an instance of Django, which includes a database and app configuration. A project may contain multiple apps, but we are only going to need just one.

To set up a project for our Yelp review dashboard, use your terminal and cd into a directory where you’d like to store the project and run the below command:

django-admin startproject yelp

When that’s done, cd into the yelp directory that was created and start up the server by running the command below:

python manage.py runserver
The server automatically watches the project for changes, so you don’t need to restart it manually

Creating an app

In Django, you can have multiple apps inside a project. This means that apps are modular and can be used in multiple projects.

Go ahead and create a reviews app that will hold the code for the dashboard by running the below command:

python manage.py startapp reviews

You’ll notice that a reviews directory has been created with a couple of files. This is where all the code will go.

Registering the app and database

Next, we’ll need to let Django know about the reviews app we just created. We do this by registering the app.

To register the app, open yelp/settings.py file and add reviews.apps.ReviewsConfig to the INSTALLED_APPS list. After adding this, INSTALLED_APPS should look like this:

INSTALLED_APPS = [
    'django.contrib.admin',
    'django.contrib.auth',
    'django.contrib.contenttypes',
    'django.contrib.sessions',
    'django.contrib.messages',
    'django.contrib.staticfiles',
    'reviews.apps.ReviewsConfig',
]

When that’s done, move on to configure your database connection in the DATABASES dictionary:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'your_database',
        'USER': 'your_database_user',
        'PASSWORD': 'your_database_password',
        'HOST': '127.0.0.1',
        'PORT': '5432',
    }
}

Save the changes and run the below command so that Django can set up tables in the database for the app in the INSTALLED_APPS list:

python manage.py migrate

The last thing that needs to be done here is to modify the path of the admin dashboard in the yelp/urls.py file so that it is mounted on the root path instead of /admin. Update the urlpatterns list to:

python manage.py migrate

Creating models

With everything set up, we can continue with creating modes that describe the database layout. Models also show relationships between tables and can be used to specify validation and the widgets that Django will create for the dashboard UI.

We are going to need three models: one for businesses, users, and reviews. To create these, head to reviews/models.py and paste the below code:

from django.db import models
# Model for Businesses
class Business(models.Model):
    id = models.CharField(primary_key=True, max_length=100)
    name = models.CharField(max_length=100)
    address = models.CharField(max_length=100, null=True)
    city = models.CharField(max_length=100)
    state = models.CharField(max_length=100)
    stars = models.FloatField()
    categories = models.TextField(null=True)

    def __str__(self):
        return self.name

# Model for Users
class User(models.Model):
    id = models.CharField(primary_key=True, max_length=100)
    name = models.CharField(max_length=100)
    yelping_since = models.DateTimeField()

    def __str__(self):
        return self.name

# Options of the select widget on the UI
status_choice = (('approved', 'APPROVED'), ('pending',
                 'PENDING'), ('rejected', 'REJECTED'))

# Model for Reviews
class Review(models.Model):
    id = models.CharField(primary_key=True, max_length=100)
    status = models.CharField(
        max_length=10, choices=status_choice, default='pending')
    user = models.ForeignKey(User, on_delete=models.CASCADE)
    business = models.ForeignKey(Business, on_delete=models.CASCADE)
    stars = models.IntegerField()
    text = models.TextField()
    date = models.DateTimeField()

    def __str__(self):
        return self.user.name

You’ll notice that each class represents a model of a table in the database. Each model has variables that represent the fields. The fields are annotated with their respective field types and validation/relationship where necessary.

Now let’s create migrations for these models. Run the below command to do this:

python manage.py makemigrations reviews

Then apply the migrations

python manage.py migrate

Taking a look at the database, you’ll notice that tables for reviews_business, reviews_user, and reviews_review have been created. At this point, you can import the yelp dataset.

Download the public Yelp dataset and import it into the reviews_business, reviews_user, and reviews_review tables respectively.

Taking the app for a spin

Are we ready to take the app for a spin? Not so fast. You’ll need to create an admin user to be able to view the site.

To create an admin user, run the following command and answer the prompt:

python manage.py createsuperuser

Once that’s done, start the server(if you previously closed it) and visit http://127.0.0.1:8000/:

python manage.py runserver
image showing admin dashboard

After logging in, you’ll notice that none of the models we created is showing up. Let’s fix this in the next section.

Displaying the reviews app

To make the models from the reviews app visible in the admin panel, you’ll need to register them in the reviews/admin.py file.

This is quite easy to do. Add the below code to your reviews/admin.py file:

from django.contrib import admin

from .models import Business, Review

admin.site.register(Review)
admin.site.register(Business)

Save the file and head back to the browser. Upon reloading, you should now see Business under the Review app.

image showing dashboard with review app

Easy as pie, right?

Play around with the dashboard to see the power of Django. But you’ll notice that the way data is displayed a little off, and we can’t see reviews for each business. Django gives us the room to improve user experience, so let’s do that.

Customizing the dashboard

We can make the reviews dashboard much better by adding some options to customize it.

Head to the reviews/admin.py and replace its content with the below code:

from django.contrib import admin
from .models import Business, Review


# A class that displays Reviews in a table form
class ReviewInline(admin.TabularInline):
    model = Review

    # Don't add any extra blank form for new Reviews
    extra = 0


# A class that lets us customize the Business list
class BusinessAdmin(admin.ModelAdmin):
    # Embed reviews in related businesses
    inlines = [ReviewInline]

    # Show the below properties in the Business list
    list_display = ('name', 'city', 'stars', 'categories')

    # Add filters for state and stars
    list_filter = ['stars', 'state']

    # Make the Business list searchable by name
    search_fields = ['name']

    # We don't want ids showing up
    exclude = ('id',)


admin.site.register(Business, BusinessAdmin)

Save the file and head to your browser. You’ll immediately notice that you have a much better dashboard experience.

image showing improved dashboard

We can further make the dashboard cleaner by removing the annoying AUTHENTICATION AND AUTHORIZATION app that shows up by default.

To do this, add these lines to your reviews/admin.py file:

# Add this to the top of your file
from django.contrib.auth.models import Group, User

# …previous code goes here

# Add this to the bottom of your file
admin.site.unregister(User)
admin.site.unregister(Group)

Save the file and reload your browser. Now we have a super clean functional Django reviews dashboard.

image of landing page with no auth

Conclusion

Now that the application is fully built, you can play around with it. This dashboard makes it easy for moderators to search for a business and approve reviews made by users.

Here’s everything in action:

And that’s a wrap!

If you found this article helpful, please leave a like and check out our awesome product for building internal tools appsmith.com