RxIDB - Dynamic Database

Introduction

After a deep dive into the programming world, I felt it was time to return to the familiar realm of medicine.

• I paused for a moment to reflect: with my current understanding of app development, how can I address some common needs of pharmacists?
• In the past, we often relied on no-code platforms like Open As App and Wistify to convert spreadsheets into databases.
• While convenient, these platforms often came with limitations, specifically imperfect implementations and premium pricing.

That’s when the idea came: what if we built a PWA that directly parses data from a public spreadsheet into a local IndexedDB?

• Thus, RxIDB was born.

Start Using the App

To begin using the app, go to Settings.

• Click Import New Database.
• If your spreadsheet has introductory text or empty rows, you may adjust the header row number accordingly.
• Name your database and paste the public Google Sheet URL (make sure it is shared with "Anyone with the link can view").
• An optional informational URL which will be used to hyperlink the database in the main page.

Here are a few important things to take note:

• For your security, only import public Google Sheet URLs that you trust are free from malicious script.
• Avoid using this app with spreadsheets containing sensitive or private information.
• The file must be a Google Sheet, not an Excel file.
• An import error notification will pop up if it is an invalid Google sheet link.
• Only the first sheet will be used to build the database.
• Ensure there are no missing fields in the header row or the first column of the spreadsheet.
• The fields in the header row will be used as the headers of the detail page.
• If any missing value is detected in the first column, there will be an import confirmation notification.

Once imported, you will see the database listed under Data Sources, with options to edit, sync or delete it.

• To ensure your database is stored persistently on your device, enable storage persistence at the bottom of the setup page.
• At the bottom of the Settings button, there is also a Clear All Local Data button, but it is unable to revoke the permission that you gave the browser for storage persistence functionality.

NOTE: Data syncing occurs client-side; the Google Visualization API is employed to populate a local IndexedDB, while DOMPurify provides essential sanitization.

Setting up Display and Search

In the following section of Settings, you can:

• Set the title and subtitle fields for how the list displays.
• Choose which fields to include in the searchable index.
• Tick those fields that you wish to hide from the detail page.

Start Searching

Back on the home page, give the app a moment to load your IndexedDB database.

• The time it takes to load the database depends heavily on the spreadsheet size and the local device's processing power.
• Once ready, you can begin searching immediately.

Features available:

• There is a Sync button at the top right to manually update your database from the Google Sheet.
• You can also quickly switch between local databases by clicking the Database icon at the top right corner, next to Sync button.
• If your sheet includes HTML content, it will be rendered properly in the app.
• To switch to a new spreadsheet source, just return to Settings and update your data source.
• On the detail page, you will find a simple calculator built in for your convenience.
• This app also works fully offline once your data is synced.

NOTE: Offline database functionality is enabled through the combination of state-based navigation and a service worker.

Summary

It took me just a few hours to build this project.

• With focused effort and a good grasp of the Firebase Studio, designing the app was not too difficult.

Interestingly, while I initially designed it for drug databases, the app interface can actually support any spreadsheet-based database.