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 MariaDB datasource
11
February
2022
Resources

A simple front-end for your MariaDB datasource

A simple front-end for your MariaDB datasource
Vihar Kurama
0
 minutes ↗
#
crud
#
mysql
#
databases
#
guide
Resources

There are many parts to building an app, and designing UI elements can take up most 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 quickly build tools on top of the database of your choice. For example, you can create 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. There are so many possibilities!

In this blog, I will teach you how to build a frontend that can connect to MariaDB as a datasource.

MariaDB Server is one of the most popular open-source relational databases. It’s made by the original developers of MySQL and guaranteed to stay open source. It is part of most cloud offerings and the default in most Linux distributions. It is built upon the values of performance, stability, and openness.

Getting Started: Connecting MariaDB on Appsmith

On Appsmith, it’s pretty straightforward to establish a connection with any datasource, including MariaDB; 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 MySQL datasource, you’ll see the following screenshot:
CleanShot 2022-02-03 at 00.54.54@2x.png
  • When you’re using MariaDB on a hosted service, all these details can be found under the configuration settings. For this example, I’m using a free MariaDB RDS on Amazon Web Services (AWS).
CleanShot 2022-02-03 at 00.56.52@2x.png
  • Rename the Datasource to MariaDB CRUD by double-clicking on the existing one.

Here’s what the configuration would look like:

CleanShot 2022-02-03 at 00.57.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 MariaDB.

Creating a New Table on MariaDB

We are done with the basic configuration. Now, let’s create a new table and seed it to build a fully customisable CRUD app on MariaDB.

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

  • Click on the + icon next to Datasource and click on Create New + from the MYSQL CRUD we’ve just created.
  • This will redirect you to the query pane, where you can write MariaDB SQL queries, now rename the query to seedData, and choose to create from the template and paste the following in the query body:
create table projects(
    project_id int auto_increment,
    project_name varchar(255) not null,
    created_at timestamp default current_timestamp,
    primary key(project_id)
);

INSERT INTO projects (project_name, created_at)
  VALUES ('appsmith', '2022-02-02T19:11:23');


This is a simple SQL query that’ll create a projects table, the idea for us is to build a simple CRUD application, that’ll let us manage open-source projects.

Also, note that we’ve seeded the table with an insert statement where we added the appsmith project.

Alrighty, not that our table is created, let’s get into CRUD.

CRUD on MariaDB 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 MariaDB CRUD datasource.
  • Rename the query to getProjects
  • Copy the following SQL script to query all the Projects from the projects table:
SELECT * FROM projects LIMIT 10;


  • This is a simple query that returns all the projects present in the sample data item. Hit the RUN button to view all the results.
CleanShot 2022-02-03 at 01.07.32@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:
{{getProjects.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-03 at 01.08.54@2x.png
Implementing the Create Operation

To add the create operation on MariaDB, 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 projects into our database.
CleanShot 2022-02-03 at 01.10.07@2x.png

Here, we have two input widgets to add to our project. 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 project on MariaDB.

Follow the steps below:

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


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 createProject under the events property:

CleanShot 2022-02-03 at 01.12.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 Project, 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 Project 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-02-03 at 01.14.09@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 MariaDB CRUD datasource
  • Rename the query to editProject
  • Copy the following SQL script:
UPDATE projects SET    project_name = {{Input1Copy.text}}'    created_at ='{{Input2Copy.text}}'WHERE  project_id = "{{Table1.selectedRow.project_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 **_editProject_** 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 Project,’ 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 MariaDB CRUD datasource
  • Rename the query to deleteProject
  • Copy the following SQL script:
DELETE FROM projects  WHERE project_id = {{Table1.selectedRow.project_id}};


Set the Delete Project button’s onClick property to run the deleteProject 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 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 to Build a User Waitlist Manager on Appsmith
28
December
2020
Tutorial

How to Build a User Waitlist Manager on Appsmith

How to Build a User Waitlist Manager on Appsmith
Ravi Vyas
0
 minutes ↗
#
guide
#
applications
#
tools
#
tutorial
Tutorial

Waitlists are a powerful way to test your app or feature with a small group of users.

In this guide, we’ll build a waitlist manager that integrates with a SQL DB to provide access control. If you are new to Appsmith, read about Appsmith's core concepts before following this guide.

Features of the waitlist manager

Our app will access data from a MySQL DB that stores all user data.

  • Add or edit users on a waitlist.
  • Turn on beta features by version number
  • Enable/disable logging.
  • Provide a few KPIs, like the number of users by version

The application consists of 3 pages: A dashboard that lists a few useful metrics, an Add users page, and a Manage Users page. The 3 pages will be powered by 7 queries.

This guide will walk you through the structure of the database & the queries used. It won’t dive deeper into how the product will access this data. We assume your product can already talk to your backend, if not you can build APIs using solutions like PostgREST or build your own with NodeJS, Express & Sequelize.

Requirements

Database

This uses a Mysql DB with a single table, Users, as described below.

Column NameTypeDescription
Email IDtextUser’s identifier in our product
NametextUser’s full name
Unique_idintUnique identifier for each user
Enable_BetaBooleanA value that is 1 for users with access to beta and 0 for the rest
From_Version_codeIntThe version number when the user got access
LoggingBooleanA boolean that enables logging for the user
created_atDateUser Creation Date
updated_atDateDate User was last updated

Queries

This application requires seven queries listed below.

List All users Rather than running a select(*) we are listing all columns with human-friendly names. Query:

SELECT id as ID,
  name as name,
  email as Email,
  logging as Logging_Enabled,
  from_version_code as From_Version,
  enable_beta as Enabled_Beta,
  created_at as Created_on,
  updated_at as Last_Updated
FROM waitlist_manager

Add User

This insert query adds users to the waitlist and sets enable_beta & logging to false. The function NOW() set Created_at & Updated_at to the current date. You can modify this behavior if needed.

INSERT INTO waitlist_manager (
    name,
    email,
    logging,
    enable_beta,
    created_at,
    updated_at
  )
VALUES (
    '{{ input_name.text }}',
    '{{ Input_email.text }}',
    false,
    false,
    NOW(),
    NOW()
  );

Update User

This query updates all the properties of the user based on the update form. This dynamic query sets the status of from_version_code to the given value if enabled_beta is set to true, else from_version_code is set to null. The updated_at column is set to NOW() to capture the date of the update.

UPDATE waitlist_manager
SET enable_beta = { { Dropdown_enable_beta.selectedOptionValue } },
  from_version_code = { { Dropdown_enable_beta.selectedOptionValue == = "true" ? Input_version_code.text :null } },
  email = '{{Input_email.text}}',
  logging = { { Dropdown_logging.selectedOptionValue } },
  updated_at = NOW()
WHERE id = '{{ text_user_id.text }}';

List Users By Version

This query provides the number of users per beta version

SELECT from_version_code as Version,
  count(id)
from waitlist_manager
where from_version_code is not null
group by 1

Dashboard Queries

This set of 3 queries will help us build a set of KPIs to monitor:

Total Users Query

SELECT count(id) as count FROM waitlist_manager

Beta Enabled Users

SELECT count(id) as count FROM waitlist_manager where enable_beta = true

Logging Enabled User

SELECT count(id) as count FROM waitlist_manager where logging = true

Interface

Add Users Page

This page is a form with two input fields, for the user’s name and email address. On submit, we add the user to our database using the Add_User query.

image1.png

To make sure the user is entering a valid email, we set the field type to email.

image3.png

Manage Users Page

This page is the workhorse of this application. The table widget lists all the users and the form updates the user details & permissions for the selected user.

image6.png


The page is dynamically modified based on certain states.

1. To provide a better user experience, The form is hidden when the table does not have a row selected. To hide the form, set the Visible JS property to

{{table_users.selectedRow.ID > 0}}
image10.png

2. The “Beta Enabled” dropdown disables the “Beta Version” field when set to No.

To achieve this, set the Disabled JS property to

{{Dropdown_enable_beta.selectedOptionValue === "false"}}

as shown below.

image9.png

Finally, we connect the update button to the update_user query.

image5.png

Set the onSuccess property of the form to run the All_users query to refresh the table on any modifications.

Quick Dashboard Page

This page provides the following metrics.

  • Total users
  • Users who have beta enabled
  • Users who have logging enabled
  • Count of users by beta version.
image8.png

For the three metrics, connect the three labels to the three aggregation queries listed above. Unlike a table, setting a label to the result of the query needs to point to a single value rather than an Array. Here it is set to {{kpi_count.data[0].count}} as the queries will always return a single row and a single column named count.

image4.png

Finally, add a table and connect it to the List Users By Version query described above.

This completes the application. You can deploy the application by clicking on the deploy link on the top right. Once deployed, it will be visible to everyone in your organization.

For more details on sharing, you can refer to the Access control documentation.

You can play around with it using the link provided below. A few possible extensions are also listed below for you to try out.

Demo Application

You can try the application out here.

Possible Enhancements

As an exercise, you can clone the application and try to extend it. Here are some possible directions you can take.

Add more Features to the current application.

Control more features by adding more columns. For example add an Enable_Premium column, to give the user access to premium features.

Separate Features table

A possible larger refactor of this application is moving the features out of the Users table. The sample table diagram below shows a possible structure.

image7.png


This enables the addition of another form to add new features if needed.

Add More Charts

Since we are capturing the creation date of users, you can add a chart that shows the number of new users added each day.

Create a Public User Submission page.

Making the Add users form public allows users to direct join the waitlist. The best way to do this would be to create a separate application with just the Add users form.