Build and deploy the PostgreSQL admin panel in under 15 minutes

PostgreSQL is a powerful and open-source relational database management system developers love working with. It provides excellent data integrity and robustness, along with a plethora of features and extensions that make it an ideal choice for many applications.

However, managing a PostgreSQL database can be challenging, especially as the data grows in size and complexity. By enabling admins to directly interact with the database, there are significant security risks involved. To address these concerns, it is recommended to establish an isolated admin layer that sits between the database and the team. This approach helps enhance data security by limiting the scope of available actions and, in addition, provides an improved user experience.
This is where an admin panel comes in handy. With an admin panel, you can easily view and manage your database, execute queries, and perform routine maintenance tasks without needing to write complex SQL statements or interact directly with the database server.

In this tutorial, we’ll guide you through the steps of building a PostgreSQL admin panel using DronaHQ, including

1. Connecting with the PostgreSQL database
2. Creating the interface
3. Binding data to the interface

What you will need:

1. A DronaHQ account
2. PostgreSQL account or any other platform(like bit.io) that lets you store the PostgreSQL database
3. The connection string and other credentials to your PostgreSQL database

So, let’s begin!

Connecting with PostgreSQL database using DronaHQ

First login to your DronaHQ, then either select from pre-built templates or create a blank application.

Once you created the application, then in the left panel you can see the “Connectors” options that let you connect your app with databases or APIs. Since we will be working with PostgreSQL, we need to connect our app with the PostgreSQL database.

Once you open connectors options, you will see plenty of options to connect your app with. Here, select the PostgreSQL database.

After selecting the PostgreSQL database, the next step is to fill in the required fields of the form. Here, you will need all the connection information of your PostgreSQL database like connection string, username, password, and more to connect to your database.

Note: we are keeping the connector name as “PostgreSQL Admin Panel”.

After putting all the required information, you need to test the connection. Once it is successful you can click on “save” and your connection will be saved.

Then, you can add multiple queries to fetch data from your database.

Note: Here, we are using bit.io, where we have our database stored on “Product inventory”.

Let’s start with a simple query to pull in all the data:

select * from product_inventory

Note: Here we kept the query name as “Product_inventory_data”.

Once this is done and your data has been pulled successfully, you can name the query as per your reference and click on “Save”. You can also write queries with filters and save them for other particular use cases.

Creating the interface

When building an admin panel for a PostgreSQL database, several key UI components can help streamline the process. Forms, tables, and charts are some of the most commonly used components. Forms are useful for data entry and modification, while tables are great for displaying large sets of data in an organized manner. Charts can help visualize complex data, making it easier to identify trends and patterns. By using pre-built UI components like these, developers can save time and effort that would otherwise be spent designing and coding these components from scratch.

DronaHQ provides a wide range of pre-built UI components that are perfect for building a PostgreSQL admin panel. These components include tables, charts, buttons, text inputs, and dropdowns, among others. With these pre-built components, developers can easily create a custom admin panel interface that is both functional and visually appealing.

Now in the studio, create an app or choose from ready templates, once you create the app in the left panel, you will find an option for “Controls” where you will get a variety of controls, and choose whatever suits your needs.

For building the admin panel, we will be choosing a table grid to fetch data based on multiple queries, & a form with multiple text inputs to display items, a number quantity input, and a button to create a form that updates the database. Once you add all the necessary controls to the screen, arrange them according to your convenience.

Binding data to the interface

You have your database connected successfully, and also a query that can fetch all the product inventory/details for you. Additionally, you have the interface ready where you have arranged all the required UI controls.

Fetch data into the table grid

Firstly we will see how to fetch data from the database to the table grid control.

To do this, first select the table grid control, then in the right panel you will get an option of “Data”. Open the data option then you will find a bunch of options to bind the data, as we already have our connector setup, so we will choose “Connector” as our data binding option.

After selecting the “connector” option you will find the connector that you already configured earlier.

Select that connector, then either you can make a new query or you can use the existing one.

Here we will be choosing the existing query that is “Product_inventory_data”.

Once you select the query then in the next step you have to select the column you need to get displayed in the table grid control and after saving it, the data will be fetched to the table grid control.

After selecting the required columns click on “Test & Finish” and all the data will be displayed in the table grid.

Update an item on PostgreSQL with DronaHQ

With DronaHQ, it’s not just limited to reading data, as you can perform all CRUD operations on your PostgreSQL database. Once you’ve created a form in the admin panel, you can easily update any selected value on the table. Let’s take a closer look at how this works.

Before we can update any data using the form, we need to establish a link between the form and the table. This link will ensure that when a user clicks on a specific product on the table, the corresponding information is automatically populated in the form.

In the form, we have placed three text input fields and one quantity control. You can configure the text inputs to show the details of the product selected on the table by respectively setting the data to:

{{tablegrid.result.rows.product}}

{{tablegrid.result.rows.category}}

{{tablegrid.result.rows.sub_category}}

You can do the same for the quantity input control so that the default quantity is picked up automatically.

{{tablegrid.result.rows.balance_qty}}

The final step involves writing a query that extracts the data from the form and uses it to update the selected product on the table. This query will be triggered when the user clicks the submit button on the form.

Create a new query in the existing “PostgreSQL Admin Panel” connector, the query will be

UPDATE product_inventory SET balance_qty = {{quantity}}

WHERE id = {{id}}

Here “id” is our unique identifier.

Once your update query is set up. Go to the form and click on the button, choose actions, then add an action that is server-side action and search for your connector and select the update query that is “UpdateProductData”.

Then fill the required fields with the specific values using keywords. Then click on continue and save the action.

Now, your admin panel is set up to update the product quantity using form, Go to the preview and test the admin panel.

Publishing and sharing the admin panel

Once you’ve built your PostgreSQL admin panel using DronaHQ, it’s time to share it with the rest of your team or clients. DronaHQ provides an easy way to publish and share your admin panel with others. You can publish your admin panel as a web app or a mobile app, depending on your specific needs. With DronaHQ’s built-in app distribution platform, you can securely share your app with others, and even restrict access to certain users or groups. This ensures that your data is kept safe and only accessible to authorized individuals. With DronaHQ, publishing and sharing your PostgreSQL admin panel is simple and hassle-free, allowing you to focus on your core business needs.

Get started here