Building an SQL Server Admin Panel in Retool

Microsoft SQL Server is a hugely popular SQL database. It's ACID compliant, making it an excellent choice for financial applications, while its optimistic concurrency model is good for large-scale applications with frequent writes, such as e-commerce platforms.

However, MS SQL admins typically find directly managing the database overwhelming. And, more importantly, allowing admins to directly interface with the database opens security risks. The solution to these problems is to create an isolated admin layer between the team and database. This technique ensures data safety by limiting the range of available actions. Plus, it offers a better user experience.

In this tutorial, you will build an admin panel for a team managing a database of shops. You will use an MS SQL database to store the information and create a user interface using Retool to show a table containing the stores. Admins will be able to view the owners' contact information, make changes to store information, and remove a closed store from the database. You will also create a form for admins to easily add newly opened stores to the database.

To follow this tutorial, you must first sign up for a free Retool account and an MS SQL server to host your database.

Note: This tutorial uses Azure as the database provider platform, but you can follow the same steps with any provider.

To get started, create a new SQL database in your provider.

Select an existing server or create a new server; then create a new database called retool-example.

Create the admin login credentials for your database. Save the server name, username, and password securely to use later.

You can use the configuration below to get started quickly. Alternatively, you can choose the server configuration to suit your database needs.

With the database in place, create the stores table and migrate the sample data so you can reference this while creating queries in the Retool application.

Go to the retool-example database page on the Azure dashboard and click Query editor on the left navigation.

Now log in with the admin credentials you created in the previous step. You might get an error because the database server doesn't allow access from any public address, so you'll see an error on the first attempt. Click on Allowlist IP: on server retool-example in the error message to proceed. Then click OK.

In the query field, paste the following SQL queries to create a new stores table and insert the mock data. Click Run to execute the queries:

The image below shows how the Azure dashboard's query editor should look after the above code is pasted.

Now that you've got the database ready, it's time to create and connect the SQL server to Retool. To do that, log in to your Retool account and go to the Resources page by clicking on the Resources tab on the top navigation. On this page, hover over Create new and click Resource.

Select Microsoft SQL as the resource type.

Now fill in the configurations for this resource as follows:

Check the Connect using SSL option to enable an encrypted connection to the database. Before finishing the form, you need to add the Retool IP addresses to your Azure database server's allowlist.

To do that, go to the Networking page of the database server and choose Selected networks under Public network access. Then click Add a firewall rule to add the IP addresses range shown on the Connect a resource page on Retool. Once that's done, click Save to save the configuration.

Now go to the retool-example database page on the Azure dashboard and click Set server firewall on the top menu.

On the retool-example database's Networking page, click on Add a firewall rule to add the IP addresses range shown on the Connect a resource page on Retool. Once that's done, click Save to save the configuration.

Now, go back to the Connect a resource page in the Retool dashboard. Click Create resource at the bottom to finish creating the SQL resource.

Now that you've configured the database as a resource on your Retool dashboard, you can start creating the user interface using the Retool application editor.

On your Retool dashboard, click on the Apps tab on the top navigation bar.

Now select App by hovering over Create new. Fill in with "Stores" as the app name and click Create app.

Add a new text component to the heading section from the right pane on the application editor and use ## Stores as its value. With this, the basic admin panel template is ready.

Note that you should always select the newly created stores-example resource while creating a query.

With the basic Retool app ready, you can start creating the queries to read, delete, update, and create database items. You can use Retool to implement the user interface to execute them.

Before showing the stores in a table, you first need to fetch the data from your SQL server database to display a list of all stores in a table. Rename the default query in the bottom section of the application editor as getAllStores. Use the SELECT * from stores SQL query. Click Run to test the query.

Now that the getAllStores query is ready, drop a Table component from the right pane in the Main section of the application editor. Rename the Table component to storesTable for easy access and specify {{ getAllStores.data }} as the Data field for this component. You'll see that the table automatically shows the data fetched from the stores table of your database.

When a store is shut down, the admin will have to remove the store from the database. To add this feature, create a new query removeStore using the GUI mode feature of the Retool query editor, select dbo.stores as the Table, and Delete a record as the Action type. Set Filter by id = {{ storesTable.data.id[i] }}. This will provide this query with the id of the selected row from the storesTable.

In the Event Handlers section of the query editor, click on Add next to Success to add an event handler to trigger the getAllStores query when the removeStore query runs successfully. This will refresh the table with the latest data after removing a store.

To enable the removeStore query, select the storesTable component and click Add next to the Actions section on the right pane.

Fill out the form as follows:

This will add a new Actions column to the table with a Remove button. Select the right column position to make it the rightmost column.

Click Preview on the top right to see the remove store feature in action.

To allow the users to update the store information, make the email, phone, and ownerName columns editable by checking the box next to the column name on the right pane.

Now create a new query updateStore to save the updated values. Select dbo.stores as the Table and Update an existing record as the Action type. Set Filter by id = {{ storesTable.recordUpdates['0'].id }}, which will provide this query with the id of the updated row from the storesTable.

In Changeset, select the Key value pairs tab and fill in the details as follows:

Add the success event handlers to trigger the getAllStores query and another one to show a notification.

Now select the storesTable component and add a Save changes event handler under the Interaction section on the right pane. Select updateStore in the Query drop-down to trigger it whenever the user clicks Save changes.

Click on Preview on the top right to see the update store information feature in action.

The admins will also like to add new stores to the database from time to time. You need to create a form with which they can submit the store information.

Select a Modal component from the right pane and drop it in the Main section above storesTable. Set the Button text to Add Store and rename the component to addStoreModal.

Click Add Store on the editor, which will open the empty modal component. Now drag and drop a Form component from the right pane. Click on generate form from data in the empty form component.

This will open up a form generator and keep all fields selected. Click Generate form. Now rename the form component to newStoreForm and set the form heading text to #### New Store.

In the query editor, create a new query called addNewStore with Action type as Insert a record and Changeset selected as Object, with the value of {{ newStoreForm.data }} to fill the form data on submission. Additionally, add the success event handler to trigger the getAllStores query and another handler to show a notification.

Now select the Submit button of the newStoreForm component in the editor. In the right-pane menu, click Add next to Event handlers of newStoreForm to add a new event handler. This event handler will trigger the addNewStore query and save the form data to the retool-example MS SQL database.

Add another event handler to close the newStoreModal on form submission. Set the Action to Control component, Component to addStoreModal, and Method to Close.

Your form to add a new store is ready. Click on Preview on the top right to see the add new store feature in action.

You've successfully built an admin panel to create, read, update, and delete the information from your MS SQL server database. In particular, you learned how to set up a new database in the Azure dashboard, secure the database server with firewall rules, and integrate it with your Retool application.

If you're looking to efficiently build an admin panel with a familiar user experience and controlled actions when they're accessing a database, you should explore adopting Retool.