Building an E-commerce Store using Appsmith and Postgres

Purchasing products online has become a common practice across the world. In fact, according to a recent survey, approximately 36% of U.S. consumers are now buying retail goods online. And this number is expected to grow! With the number of digital buyers rising, and the ease and convenience of online shopping, e-commerce portals are the obvious answer. Several big chain retailers are re-evaluating their presence online and continuing their digital transformation. Building an eCommerce store is an important part of this transformation. Fortunately, building an e-commerce platform is now as easy as 123, thanks to Appsmith.

In this blog, we will take you through the process of building the store.

Set up Postgres on Appsmith

The first step is to build an e-com store on Appsmith is to connect to a database to store the information regarding all the products and orders. For this, you can connect to the existing demo database that we’ve used to build this application. You could also use your own PostgresDB in case customization is required. Now, follow the steps below to connect the PostgresDB on Appsmith.

1. Create a new account on Appsmith (it’s free!), if you are an existing user, log in to your Appsmith account.
2. Create a new application by clicking on the Create New button under the Appsmith dashboard.
3. We’ll now see a new Appsmith app with an empty canvas and a sidebar with two directories: Datasources and Widgets.
4. Click on the + icon next to the data sources section and choose the PostgresDB integration.
5. Rename the data source to Store DB, and we can do this by clicking on the existing one and choosing the rename option.
6. Use the following details to connect with the PostgresDB (demo or your own) for this application.
7. After entering the credentials, you can test the data source by clicking on the Test button to check if the connection is valid or not.
8. Then, click on the Save button to save the data source permanently on your organisation if the connection is valid.

Host Address: db.ircvafghkrkovtjtnllv.supabase.co
Port: 6543
Database Name: postgres

Username: postgres
Password: appsmithdemo123#

Note: The connected data source has two tables with the following fields:

• orders - [id int8, contact_no varchar, address text, total_items json, cart_value int8]
• products - [id int8, product_name varchar, product_description text, product_image text, price float4, quantity int4]

If you’re using new PostgresDB instances, copy and run the following queries by creating a new query on the previously created Supabase Store DB data source.

Manage Products with a CRUD App

The first and foremost thing for any eCommerce application is to build a CRUD (CREATE, READ, UPDATE, DELETE) application to manage the store items. Usually, for making this, we need to spend a lot of time creating queries and building UIs. But at Appsmith, we have a cool feature where you can generate a CRUD application from your database using just one click. To learn more about this, read this blog here.

Now follow the below steps to build a CRUD app on the products table from the connected Postgres data source.

1. Click on the + icon next to the Datasource section from the entity.
2. Choose the Supabase Store DB data source and click on the Generate New Page button.
3. Here you’ll be asked to select a table from the data source, choose the products table, and set the searchable item to the product name.
4. Now, click the Generate Page button; this will automatically create all the UI and queries and build a completely working CRUD app. Yeah, I know, it’s pretty awesome!

Note that this will be created under a new page with a unique name, we can change the page name to Product Admin, and this is how it looks like:

Creating product: We can add new products by clicking on the New Row button. A modal pop up with a form to add details of the product.

Read products: All the products are listed on the table; you can order by any parameter listed on the table and customize it based on different use-cases.

Update product: To update a product, we can select the row from the table, update the Form placed on the right, and hit the save button.

Delete product: To delete the product, scroll horizontally on the table and use the delete button.

Awesome, that’s really easy. In the next section, let’s create a new page. We display all the products and add functionality to add products into a cart and checkout the order.

Create Store Page and Implement Cart using the Appsmith Context Object

Now that we have an admin page to manage all our products, let's add a new table widget to display all the products and implement the cart functionality. Follow the below steps:

• Firstly, let's rename this page to Store Home Page, click on the + icon next to data sources, and create a new query from the Supabase Store DB.
• Rename the new query to getAllProducts and paste the following SQL in the query body pane.

SELECT * FROM public." products";

• This query is used to fetch all the products from the connected data source.
• Next, click on the + icon next to Widgets and drag and drop a new table widget onto the canvas. You can click on the table name on the top right to open the property pane to configure table data and its columns.
• On Appsmith, it's super easy to bind the queries, use the moustache bindings and query name; similarly, open the table's property pane and use the following snipper in the Table Data property: {{getAllProducts.data}}. And, just like that, we should see all the products populated on the table.
• Here, the table data is only filled with the products, but we should be able to have one more column where users can add the selected products to the cart. For this, open the table property pane, and select the ADD A NEW COLUMN option. This will create a new column on the table, rename it to Add to Cart and set the column type to Button.
• Open the column settings and set the label to: Add to Cart, now when clicked the item should be saved to a local store, to implement this logic, toggle JS on the onClick property and use the following code snippet.

{{
(function() {
    storeValue('cart', [...(appsmith.store.cart || []), Table1.selectedRow.id])
})()
}}

• Here, we have a function, that stores the ID of the selected rows into a new array called cart when the button is clicked. To learn more about appsmith local store, read the documentation here.

Awesome! Now, let’s display all the products that are added by the users in the Appsmith local store on a list widget. Follow the below steps:

1. Drag and drop a list widget from the Widgets section, and open its property pane by clicking on the cog icon on the top right.
2. Paste the following code snippet in the Items property.

{{

(function() {
const groupedItems = _.groupBy((appsmith.store.cart || []).map(id => getAllProducts.data.find(product => product.id == id)), 'id');
const items = Object.keys(groupedItems).map(id => {
 return {...groupedItems[id][0], totalQuantity: groupedItems[id].length }
});
return items;
})()

}}

In the above code snippet, first we are grouping all the product items using the ID. Next, we’re mapping the grouped items to get the quantity of the items added, this will allow us to handle multiple items in a single cart. Following is the screenshot of the property pane of the list widget.

As we can see, in the evaluate value pane, we have the products in the cart, and also the quantity gets added to the totalQuantity variable directly from the function.

Next, we’ll add a text box where we calculate the total price in the cart and display it to the users, this drag and drop a new text box widget from the widget sections, and paste the following in the Value property by opening the property pane:

${{ (function() {

const groupedItems = _.groupBy((appsmith.store.cart || []).map(id => getAllProducts.data.find(product => product.id == id)), 'id');

const items = Object.keys(groupedItems).map(id => {
 return {...groupedItems[id][0], totalQuantity: groupedItems[id].length }
});

return items.reduce(function (previousValue, currentValue) {
  return previousValue + currentValue.price * currentValue.totalQuantity;
}, 0);
})() }}

Here, we’ve considered the same grouping function, but instead of counting the items, we’re simple calculating the total price by summing them up.

Checking Out the Cart

We now have the complete cart, the next step is to store all the orders in a new table, and show them on a Product Admin Page. Follow the below steps to implement this:

• First, drag and drop a button and set the value to checkout, let’s open a new modal to gather all the information from the users. So we can set the onClick property to Open a Modal and create a new one.
• Next, add three text widgets, and input widgets, for us to gather information of their order. Following is how the UI looks like:

Let’s name the input widgets to follow, to use them in the query while creating orders:

Name Widget: userName

Delivery Address Widget: userAddress

Contact Number: userContact

Next, we’ve added the cart price again for UX purposes so that the users can have an idea of their cart value when checking out. Lastly, when the confirm button is clicked, let’s create a new query submitOrder to post the query details.

INSERT INTO public."orders" ("name", "contact_no", "address", "total_items", "cart_value") VALUES ('{{userName.text}}', '{{userContact.text}}', '{{userAddress.text}}', '{{ (function() {

const groupedItems = _.groupBy((appsmith.store.cart || []).map(id => getAllProducts.data.find(product => product.id == id)), 'id');

const items = Object.keys(groupedItems).map(id => {
 return {...groupedItems[id][0], totalQuantity: groupedItems[id].length}
});

return items;
})() }}', {{userAmount.text}});

Here we're using the same details that we've collecting from the Modal, we copied the same function that was used for the cart to post cart items into the orders table.

Lastly, we've created one more page, to display all the items that are in the orders table. For this, we simply use a table widget and customise the data. Following is how the UI looks like:

---

Building this app from scratch, including writing snippets of code is likely to take 30 minutes! Isn’t that simple?

If you liked this tutorial, and are planning to build this, let me know. I’d love to help you make it as complex as you’d like. Write to me at [e-mail]

Comments

[Saurav Pathak]

How can appsmith helps merchants who wants to make the best use of any existing opensource eCommerce platform like Bagisto, Magento etc