What is a web app database?

A web app database is a type of database that is designed to be accessed and managed through a web application. It stores data that is related to the web application, such as user information, content, and other relevant data.

Web app databases can be created using different types of database management systems, such as relational databases (e.g., MySQL, PostgreSQL, Oracle), NoSQL databases (e.g., MongoDB, Cassandra, DynamoDB), or graph databases (e.g., Neo4j). The type of database used depends on the specific requirements of the web application and the type of data that needs to be stored.

Web app databases are typically designed to be scalable and flexible, as they need to handle a large amount of data and support multiple concurrent users. They can also be integrated with various web application development frameworks and programming languages, such as Ruby on Rails, Django, and Node.js, to provide efficient data management and access.

Guide to Building a Web Application Database

Building a web app database can be a complex process, but here are some general steps to follow:

  1. Choose a database management system (DBMS): There are many DBMS options available, including MySQL, PostgreSQL, MongoDB, and Acho. Choose one that fits your needs and has the necessary features for your app.
  2. Define your data schema: Determine what data your app needs to store and how it should be organized. Create a data schema that outlines the structure of your data. The data source can flat files, APIs, and DBMS. Often you can design it in a spreadsheet.
Field Name Data Type Description
User ID Integer Unique identifier for each user
Username String The username for each user
Email String The email address for each user
Password String The password for each user, encrypted for security
  1. Create your database: Use your chosen DBMS to create your database. Define tables and relationships based on your data schema.
  2. Set up your database server: Set up your Acho account to host your database and set up the necessary software and configurations.
  3. Connect your database to your web app: Use a programming language and a database driver or ORM to connect your web app to your database.
  4. Create a data access layer: Create a layer of code that handles interactions between your web app and your database. This can include creating, reading, updating, and deleting data.
  5. Implement security measures: Protect your database by implementing security measures like user authentication and encryption.
  6. Optimize your database: Optimize your database for performance by indexing your data and tuning your DBMS settings.
  7. Test your database: Use testing tools to ensure that your database works as expected and can handle the expected workload.
  8. Backup and restore your database: Regularly back up your database to prevent data loss in case of an unexpected event. Have a plan in place to restore your database from backups if needed.

By following these steps, you can build a reliable and scalable database for your web app.

Example: Building a web app for car database

We had already grasped the basic concepts of constructing a web app database before. To illustrate how to create one from scratch using Acho App Builder, I will use a car database as an example.

1. Prepare your database

To start building a web app database, the initial step is to prepare a database. If your data is already stored in a relational database such as MySQL or PostgreSQL or a No-SQL database like MongoDB, you can just connect to your database directly on the “Resources” page.

However, if you do not possess a database and your data is stored in CSV files or other third-party platforms like Google Ads or HubSpot, you can either upload the files or connect to the third-party platforms via an API connector. Acho automatically constructs a database for you, so you won't have to start from scratch.

2. Navigate to App Builder and create a new app

After successfully setting up your data source, you can navigate to the “App Builder” page and create a new app. You can create a blank app and follow the instructions to create a web app database. Alternatively, you can save time by choosing the "Searchable Car Database" template and replacing the existing database with your own.

Create an app

Once the app is created, you'll be taken to a canvas where you can modify both the data source and page layout to your liking. To preview your web app and see how it operates, simply click the preview button located in the top right corner.

3. Construct your interface

Let's move on to constructing the front-end interface for your app. If you've created an app from a template, you likely already have an interface and can skip this step. However, if you're building an app from scratch, navigate to Create > Elements on the left and drag visual components to your page. (Note that you can double-click the page to enter editing mode and start constructing your page.)

For this particular app, we'll need a table element, a pagination element, and a search bar. You can also add other elements, such as a text element to title your app or provide instructions. To learn more about the available elements and styling, visit this link.

4. Create a data source for your table

To import a data source to your app, navigate to Create > Data on the left panel and drag the “Acho Resource” data node to the canvas. Then, double-click on the data node and write a query below to retrieve all the data:

SELECT  *
FROM car_database

Then, click the “Run” button to preview the result. If the result is right, click the “Save” button to save the query results.


5. Create a pagination in the data node

If your data is too large, you will need a pagination for your data. Generally, the browser easily gets stuck when it displays more than hundreds of rows. Pagination allows you to set the maximum number of rows to display at a time and also allows your users to explore all the data.

To create pagination, we can add “LIMIT” and “OFFSET” statements in the query and use parameters to create a dynamic page value.

SELECT  *
FROM car_database
LIMIT 10 OFFSET {{page-1}} * 10

  • The LIMIT clause is used to restrict the number of rows displayed for each page. "Limit 10" here means to display 10 rows for each page.
  • The OFFSET clause skips a specified number of rows from the beginning of the result set and returns the remaining rows. For example, “LIMIT 10 OFFSET 10” means to skip the first 10 rows and return rows 11 to 20.
  • We use {{page}} as a parameter whose value can be changed dynamically via the front-end component. Parameters can get the value sent from the app and pass it to the query. Double curly brackets declare the parameter in the query, and you can do simple mathematical calculations within them. In this case, we calculate how many rows to skip. If the value of {{page}} is 5, the query will be “OFFSET (5-1) * 10”, or “OFFSET 40”, which means skipping the first 20 rows and returning rows 41 to 50.

6. Connect the interface to the data source

Now that we have both a data source and an interface, we need to connect them to make the app work. To do this, simply double-click the page to enter editing mode and select the table element. Then, change the data source to the one we just created.


7. Create an interaction between the pagination element and the data source

First, we need to change the data source when the page number changes. To do this, we should add an interaction to the pagination element.

  1. Create a “Page Change” event (the green square), which means the next action will be triggered after the page number changes.
  2. Create a “Set SQL Parameter” action to send the page number to the data node, car_database. Then, specify ${event.page} in the page input box.

8. Send the total number of rows in the data source to the pagination element

Sending the total number of rows in the data source to the pagination element is essential for the pagination to function properly. To achieve this, we need to create a data node and calculate the total number of rows. We can then send this information to the pagination.

a. Create a data node called “total_rows” and write a query to calculate the total number of rows. Then, save the query by clicking the “Save” button.

SELECT COUNT(*) AS total_rows
FROM car_database ;

b. Next, navigate to the interaction panel and add an action to send the total number of rows to the pagination element.

c. Furthermore, it is necessary to retrieve the total number of rows every time the user opens the app. Click the page and add an action to run the data node.

9. Add a search bar for your table

We now have a table and pagination that allow users to browse the data. Let's add a search bar to enable deeper interaction with the database.

First, open your data node and create a string parameter named search. Then, add a search statement to your query as shown below, which allows users to search for any ticker's price:

SELECT *
FROM car_database
WHERE CONCAT(make, ' ', model, ' ', year::text) ILIKE '%{{search}}%'
LIMIT 10 OFFSET {{(page-1) * 10}} 

Next, create an interaction for the search bar to send the search term to the data node.

Contact us in the chat box on the bottom right corner of this page if you have any questions!

>> How to build an Applicant Tracking System (ATS)?
>> How to build an app with only SQL?
>> How to Share Data Between Two Applications