Build Powerful and Scalable Tools with Appsmith and Neon

Goal

Prerequisites

Overview

1. Building the Data Model with Neon

   Before diving into the implementation details, we need to design the data model for our gym management system. With Neon's powerful capabilities, we can create tables to store customer information, exercise details, progress tracking data, and exercise plans. By separating storage and compute, Neon ensures that we have bottomless storage and can scale seamlessly as our gym grows.

   Let’s go ahead to the Neon Platform and sign up for a new account, the free tier should be enough for this integration Then after signing up, create a new Project, our’s will be called Appsmith.

   

   Now with our newly project created, you’ll see a dashboard showing key information about your database, you’ll see the connection details, we’ll use that in the next steps when connecting this database to Appsmith.

   For this example, we’ll need just 3 tables, one for Customers, other for Exercises and other one for Plans, in Neon’s left menu go to SQL Editor and run the following SQL sentence to create these tables.

   -- Create the "customers" table
   CREATE TABLE customers (
     id SERIAL PRIMARY KEY,
     name VARCHAR(255) NOT NULL,
     email VARCHAR(255) NOT NULL,
     age INT NOT NULL,
     weight INT NOT NULL,
     fat VARCHAR(255) NOT NULL,
     muscle VARCHAR(255) NOT NULL,
     calories VARCHAR(255) NOT NULL,
     water VARCHAR(255) NOT NULL,
     location VARCHAR(255) NOT NULL,
     status VARCHAR(255) NOT NULL,
     objective VARCHAR(255) NOT NULL,
   	subscriptionduedate DATE NOT NULL,;
     created TIMESTAMPTZ DEFAULT NOW()
   );
   
   -- Create the "exercise" table
   CREATE TABLE exercise (
     id SERIAL PRIMARY KEY,
     name VARCHAR(255) NOT NULL,
     picture VARCHAR(255) NOT NULL,
     video VARCHAR(255) NOT NULL,
     description TEXT NOT NULL
   );
   
   -- Create the "Plan" table
   CREATE TABLE Plan (
     id SERIAL PRIMARY KEY,
     customer_id INT NOT NULL,
     exercise_id INT NOT NULL,
     weight VARCHAR(255) NOT NULL,
     repetitions VARCHAR(255) NOT NULL,
     rest_time VARCHAR(255) NOT NULL,
     FOREIGN KEY (customer_id) REFERENCES customers (id),
     FOREIGN KEY (exercise_id) REFERENCES exercise (id)
   );

   The first 2 tables are really simple, just data about our customers and the exercises our customers are able to do in our gym. The Plan table will relate these 2 to control the plan each customer most train in our gym.

   • id: A unique identifier for each plan (auto-incremented).
   • customer_id: The foreign key referencing the primary key of the "customers" table, establishing a relationship with a specific customer.
   • exercise_id: The foreign key referencing the primary key of the "exercise" table, establishing a relationship with a specific exercise.
   • weight: The weight associated with the plan, stored as a string (for example 25lb) .
   • repetitions: The number of repetitions associated with the plan, stored as a string (for example 3x10).
   • rest_time: The rest time associated with the plan, stored as a string (for example, 1 minute)

   The FOREIGN KEY constraints ensure referential integrity by linking the customer_id and exercise_id columns to the respective tables.

   

   For the sake of testing, let’s create one customer and one exercise just to make sure when we create our Interface in Appsmith we are able to see data coming in, run the following SQL sentence in Neon:

   -- Insert a new customer into the "customers" table
   INSERT INTO customers (name, age, weight, fat, muscle, calories, water, location, status, objective, email, subscriptionduedate)
   VALUES ('Kevin Blanco', 31, 110, '52', '32', '3200', '59', 'San José, Costa Rica', 'Active', 'Losing Weight', 'kevin@appsmith.com', '2023-09-01');
   
   INSERT INTO exercise (name, description, picture, video)
   VALUES ('Body Pull', 'Grab the hanging cable, grab it strongly and make squads keeping your back straight', 'https://images.unsplash.com/photo-1616279969856-759f316a5ac1?ixlib=rb-4.0.3&ixid=M3wxMjA3fDB8MHxwaG90by1wYWdlfHx8fGVufDB8fHx8fA%3D%3D&auto=format&fit=crop&w=930&q=80', 'https://www.youtube.com/watch?v=2eA2Koq6pTI');

   Now we are ready to start creating our amazing internal tool in Appsmith!

2. Connecting Appsmith with Neon

   To bring our gym management system to life, we need to establish a connection between Appsmith and Neon. Appsmith provides built-in integrations and connectors that simplify the process of connecting to external data sources. By leveraging Neon's multi-cloud serverless Postgres, we can securely connect our Appsmith application to the backend database and fetch or update data seamlessly.

   Go to https://app.appsmith.com/ and signup for an account if you don’t have one, or sign if you already have, and create a new App, I will call mine Appsgym, and in that newly created app let’s create a new Datasource by clicking in the “+” icon next to Datasources in the left menu.

   

   In the next page, we’ll have to enter our database credentials, remember the connection string we saw in the first step when we created the Neon app? Copy that value and let’s grab the values we need from that connection string, mine looks like this:

   postgres://kevin:password@ep-young-fog-676436.us-east-2.aws.neon.tech/neondb

   o break it down into pieces, after postgres:// you’ll see the database user, in my case kevin, then, the database password, in my case is password (clearly that’s not the real password) then after the @ you’ll find the database host, for me is ep-young-fog-676436.us-east-2.aws.neon.tech and lastly, the database name, for me is neondb . Let’s put that data into Appsmith and test the connection, it should look something like this:

   

   And that’s it, Appsmith is plugged into Neon and is ready to start reading/writing/editing/deleting data from and to it.

3. Creating Gym Customers

   With the foundation in place, we can now focus on implementing key functionality within our gym management system.

   Using Appsmith's form components and Neon's database capabilities, we can create a customer management module that allows gym administrators to add, edit, and delete customer records. In the Appsmith app, down bellow the datasource we created you’ll see that Appsmith shows the tables from Neon, in this case, customer, exercise, etc, let’s click on public.customer and then SELECT . This will automatically create a SELECT sentence to pull the customers from the database.

   

   Power tip: You can use AI to create SQL statements if you are not familiar with SQL or you need to build s complex query, you can just type /ai and then ask for the query you need, AI will generate it you for 😏

   If you click “RUN” you’ll get the customer data from Neon, Appsmith will preview this data in a table and then on the right, it will suggest some widgets to display this information on the UI.

   

   Go ahead and click on the “Table” suggested widget on the right, and BOOM, just like that Appsmith will create a page with the customers table in it, and a bunch of amazing features like search, filters, sorting, pagination, downloading and rearranging.

   

   Now that we have our data connected into the UI to View customers, let’s work on the functionality to add Customers.

   In the widgets left menu, find a “Button” widget, drag and drop it any place you want, I put it at the top of the table, then on the right side properties of the button put the label “Create Customer” and on the “onClick” functionality click the “+” icon and select “Show Modal” and click the “New Modal” option.

   

   So this new button whenever is clicked it will open a Modal window. In that modal or popup window, we will show a form to create a New Customer.

   Using the widgets list, drag and drop a Form widget into the newly modal that Appsmith created for us, and in that form, drag and drop inputs for each of the fields we need to create a customer, in this case name, email, fat percentage, muscle percentage, calories (kcal) water percentage, age, weight, location, goal and membership expiration date. Give every field a name using customer_ as the prefix, for example for name it has to be customer_name and so on with all the fields. The modal will look something like this:

   

   Now that we have the UI to create a new customer, let’s create the Query to save this data into Neon, in the left menu in the Queries section, click the “+” icon to add a new Neon Query and name it Create_Customer . For the SQL statement use the following:

   INSERT INTO customers (name, age, weight, fat, muscle, calories, water, location, status, objective, email, subscriptionduedate)
   VALUES ({{customer_name.text}}, {{customer_age.text}}, {{customer_weight.text}}, {{customer_fat.text}}, {{customer_muscle.text}}, {{customer_calories.text}}, {{customer_water.text}}, {{customer_location.text}}, 'Active', {{customer_goal.text}}, {{customer_email.text}}, {{customer_expire.selectedDate}});

   As you can see in this statement, we are using Appsmith’s powerful data binding to pass the values from the form fields into the query. Now that we have the query ready, let’s execute this query when the modal form is submitted. Go back to the form in the modal and in the “Submit” button properties, in the “onClick” click the “+” icon and select “Execute a query” and select Create_Customer.

   

   And that’s it ! Since Appsmith will handle that form fields are not empty and correct and sanitized data values are passed to the query we don’t have to worry about SQL injection. We have the customer creation workflow done!

4. Editing Gym Customers

   Appsmith makes complex features a breeze, editing existing records into Neon is really simple, including standard workflows like confirmations and success/fail notifications.

   In order to make editing available, just select the customers table and you’ll see an “Editable” for each data value, just thick the values you want to make editable, for example, I will select “name”, “email”, “weight”, “fat”, “calories” and by just doing that, Appsmith will add a new Column with a “Save” and “Discard” button, and will make table fields editable for those you selected.

   

   Power tip: You can use roles and permissions to control who have access to editing records, see Programmatic and Granular Access Control.

   Now, let’s hook the “Save” button to an SQL update query, in the left menu under the Neon Datasource, create an Update query for the “customers” table.

   

   And by using the updatedRow binding Appsmith will securely pass along the updated data from the table into the SQL statement.

   -- Update a customer in the "customers" table based on the primary key (id)
   
   UPDATE customers
   SET name = {{Table1.updatedRow.name}},
   age = {{Table1.updatedRow.age}}, 
   weight = {{Table1.updatedRow.weight}}, 
   fat = {{Table1.updatedRow.fat}},
   muscle = {{Table1.updatedRow.muscle}},
   calories = {{Table1.updatedRow.calories}},
   water = {{Table1.updatedRow.water}},
   location = {{Table1.updatedRow.location}},
   status = {{Table1.updatedRow.status}},
   objective = {{Table1.updatedRow.objective}},
   email = {{Table1.updatedRow.email}}
   WHERE id = {{Table1.updatedRow.id}};

   Now all we have to do is edit the “Save/Discard” and for the “onSave” action select the “Execute a query → Update Customer” and that’s it! You can also add actions to the “success” and “failure” results, in my case, I added an alert notification on both scenarios to inform the user.

   

    

5. Managing Exercise Library, Designing and Assigning Exercise Plans.

   Our gym management system can have a comprehensive exercise library, including exercise names, descriptions, images, videos, and instructions. Using Neon's storage capabilities, we can securely store these media assets and retrieve them as needed. Appsmith's data table component can be utilized to display and manage the exercise library, allowing administrators to add, edit, or remove exercises. Also Gym administrators can create customized plans by selecting exercises from the exercise library, specifying weights, repetitions, rest timings, and other relevant parameters. These plans can be assigned to individual customers, enabling trainers to monitor and track their progress effectively.

   Now that you know how to do it from the previous example, we encourage you to make the same process for managing the exercises table and plans table and if you are lost, here we have a sample app you can fork and use it yourself!

   https://app.appsmith.com/app/neon-gym-app-demo/customers-64aeb2fd50f71339d158c48b

Conclusion

Additional Resources