Month: August 2024

Roger Cheng

Budibase With PostgreSQL

I now have PostgreSQL running in a Docker container, and it will host my personal finance tracking database while I learn how to build a user-friendly interface front end using Budibase. I decided against using Budibase’s built-in database because, as the documentation stated, “Budibase DB is a NoSQL database and not generally ideal for extensive relationship functionality“. Having a separate data structure has another benefit: if I decide Budibase won’t suit my needs, I can keep the PostgreSQL database and start a new interface project with different technology. There’s a chance this will happen as I’m starting to think perhaps the rest of Budibase aren’t ideal for extensive relationship functionality either.

But I haven’t used it enough to make that declaration, so onward with my project. First I will need to send my normalized data into PostgreSQL. Since I already had everything in a Jupyter notebook, I searched for a PostgreSQL interface library and found psycopg. Thanks to the quickly iterative nature of Jupyter it didn’t take long before all my tables went into a PostgreSQL database. If all goes well, I won’t need my Jupyter notebook anymore.

During these iterations, I would connect my Budibase app to the partially-migrated database to verify what I’ve done so far worked. I add more data with each iteration and I found that if I update PostgreSQL table structure (add/remove column) Budibase starts failing left and right. I guess this isn’t a supported scenario, either. I found the workaround is to go into Budibase “Data” section and click “Fetch tables” again so it can update its own internal handling of new database structure.

Once I migrated all of my data, I can see them within Budibase’s PostgreSQL connector under the “Tables” tab. Pretty straightforward. I could then use “Relationships” table to declare the foreign keys in each table, something I couldn’t do for CSV-imported data tables in Budibase DB. So that’s a win! The third tab “Queries” is interesting. I could figure out custom queries in psql then copy the result into this section for use elsewhere in my Budibase app. Creating an entry in this list is a simple button click, as is editing an existing entry. But if I decide I didn’t need a custom SQL query… I couldn’t find any way to delete an entry from the list of queries. How odd! Well, no matter, I will edit its title to be “ABANDONED” and if I need a custom query in the future, I’ll recycle the slot and edit the abandoned query instead of creating a new one. And there’s a lot of creation ahead, as I learn the division of labor between PostgreSQL queries and Budibase data binding.

Roger Cheng

PostgreSQL Docker Container Has Tools I Need

I’m learning to build data apps with Budibase, and I have decided to use PostgreSQL as my database. It should let me do things I can’t do with the default database built-in to Budibase. I am not a complete PostgreSQL beginner thanks to a Codecademy course some time ago, but I found that course a bit too beginner-friendly as it didn’t go into as much depth as I liked. This time as a refresher I tried the W3Schools course and didn’t finish it, finding it to be too shallow as well. In the quest for something with more meat, I went to the source: PostgreSQL official documentation. The tutorial section itself is just about perfect for what I want today, and the remainder of the reference is there when I’m ready for more detail.

I’m running Ubuntu Server in my Budibase VM, and PostgreSQL is available as a part of Ubuntu’s application distribution package. For security partitioning purposes, PostgreSQL uses the “postgres” user account instead of the logged-in user and I had trouble logging in with the correct credentials. After a bit of unsuccessful fiddling, I tried an alternative approach of running PostgreSQL as a Docker container. Now I can log in by specifying -u postgres at the docker exec command line.

Once logged in, I verified the Docker image included two tools that would greatly help with my experiments. I had thought I would need to write similar tools myself via PostgreSQL API but reading PostgreSQL tutorial I learned they already existed.

The first is psql, a text command-line interface to PostgreSQL. It allows me to experiment and iterate through my queries at a low level before I try to make them fancy and easy to use with Budibase. The second tool is pg_dump, which backs up my database data in plain text format. The text file is a list of psql commands to recreate my database. I always have the fear that I would accidentally make a hugely destructive change to my database and not even realize it until much later. Now I can periodically take snapshot backups with pg_dump so I can recover from such mistakes. And more than that, I can check the text file diff between these snapshots. I can’t always mentally parse every line of difference but I can rest assured scope of my changes were not more drastic than I had intended as I work with my data.

Roger Cheng

Choosing PostgreSQL As Budibase External Database

I’m learning how to build data apps with Budibase and thought it would be a good idea to start with its default database so I minimize the number of moving parts as I learn. Unfortunately I ran into limitations on data imported in JSON (JavaScript Object Notation) format, and that was after I ran into entirely different limitations on data imported in CSV (comma-separated values) format. I think the default database approach is a bust.

While disappointing, I have decided not to fault Budibase for my problems. As I learn about Budibase I see it is focused on solving relatively simpler problems where people would be tempted to use a spreadsheet. The target audience is unlikely to care about table joins or database normalization so it’s no surprise Budibase has not prioritized such things with their default tools. If someone really cares, they can use an external relational database. I’ve decided that is me so I will need to choose one.

SQLite is a great choice for small-scale database projects like mine, available in a wide variety of ways including as part of the Python standard library. Unfortunately part of its lightweight design is for SQLite to be built into the client application, and that’s not a pattern Budibase supports. Next step up in the complexity scale is MySQL, which used to be a darling of the open-source world until it was acquired. Now they’re owned by Oracle and I am not fond of their business practices so I will stay clear of MySQL. I don’t need big iron like Oracle or competitor like Microsoft SQL Server. My project is certainly not worth spending money on such databases.

There’s an open-source fork of MySQL called MariaDB that formed in response to MySQL acquisition. I had considered MariaDB until I researched its governance. The people behind MariaDB wants to build a business to sustain the project, which is laudable, but they want to do it as a publicly-traded company and that gives me pause. Open source software financials rarely align with Wall Street desires and I don’t think that will end well for its users.

So that leaves PostgreSQL, which i briefly looked at earlier as subject for a Codecademy course and it’s been on my “I should try building something with it” list for some time. Reading up on PostgreSQL history, I learned it started as a university research project and is now receiving corporate sponsorships in addition to continued university involvement. I don’t know if this is necessarily more financially stable than MariaDB, but at least I don’t have to worry about PostgreSQL doing anti-consumer things to maximize shareholder value. I will use PostgreSQL as my external relational database for my Budibase project, and I will learn more about both PostgreSQL and Budibase as I go.

Roger Cheng

No Budibase Support For Imported Nested JSON

I took my personal finance tracking spreadsheet and reorganized it into a set of normalized database tables each in a CSV (comma-separated values) file. They imported into Budibase fine but I could not define declare logical relationships between them. Looking for another entry point for Budibase built-in database, I found JSON was another supported format so I had my Python code output my spreadsheet data as JSON. Each entry looks something like this:

{
  "Date": "12/18/2016",
  "Vendor": "Target",
  "Total": 41.24,
  "Items": [
    {
      "Price": 26.27, 
      "Category": "Food"
    },
    {
      "Price": 14.97,
      "Category": "Durable"
    }
  ]
}

This is no longer a set of normalized database tables. It’s closer to how things were laid out in my spreadsheet. A date, a vendor name, and a total that may be divided across multiple items each in a different category. Except each category had a column in my spreadsheet and now it is a nested array in my JSON.

I don’t like this layout as much as my normalized tables. The vendor name (“Target”) is repeated over and over in this JSON format, instead of split off into a dedicated vendor table and referenced via ID. This is vulnerable to data entry errors like a misspelling, which could be found all over my original spreadsheet. The nested item categories are likewise repeated instead of sitting in their own table of categories.

Despite my dislike, I was willing to give it a shot. This data schema is vaguely familiar from the time I briefly played with MongoDB, another NoSQL database. Now I built this table with my Python processing code to see what CouchDB (what Budibase built-in database is based upon) can do with this data. The answer: CouchDB can handle this just fine, but building interactions for this data is too far off the beaten Budibase path.

To Budibase’s credit, it was very easy to build UI to interact with all the top-level items: date, vendor, and total. But then I ran into a brick wall with those nested Items remaining opaque. I could only get my list of two items to show up as:

Items: [ (object), (object) ]

Which is not very useful at all! Searching around Budibase GitHub discussion for help, I found #3394 How to create form to accept an array of items which pointed to #2568 JSON (Key / Value) type. It is possible to define a schema for JSON data that includes nested arrays. However, there’s a catch: this option is only available for JSON data tables newly created within Budibase. This is not available for existing data that was imported. Darn! This is probably on Budibase to-do list for the future, but today I will have to abandon JSON approach and try something else.

Roger Cheng

No Budibase Support For CSV Data Relationships

I want to migrate my personal finance tracking from a spreadsheet to a database. I knew my spreadsheet had accumulated errors over the years, but I didn’t really know how big of a mess it was. Not until I tried to perform database normalization where I brought up a Jupyter notebook to quickly iterate through this side quest. It handled all the repetitious fixes, some search/replace rules to fix inconsistency, and split the data into multiple tables as per normalization rules. While slicing the data up for multiple tables, my code is also responsible for generating unique identification numbers in a table (primary keys) to be used in references from other tables (foreign keys).

I had used Python default CSV (comma-separated value) library to read data exported from Excel into dictionary objects. I performed my error correction and data normalization with Python dictionaries and at the end I wrote those processed dictionaries back out as CSV files. One file per dictionary representing one database table. I thought I was in good shape as I went into Budibase data import menu to upload these CSV into the Budibase built-in database. The file uploads were uneventful, but then I tried to define relationships between those tables and got stuck. It seems Budibase does not allow relationships to be defined for tables of data uploaded from CSV files, which feels like an odd oversight.

Defining relationships between tables is definitely supported in Budibase. Almost every supported data source type has a “Define existing relationships” section. There’s one for MySQL/MariaDB, there’s a section for Oracle, another for Microsoft SQL Server, and a section for PostgreSQL. There is no such section for CSV Import, but I had thought that was merely a documentation omission. Surely there is a way to define existing relationships between tables! But the option is absent from Budibase user interface, so it wasn’t an omission after all. I probably should have tested this assumption earlier, before I put in all the work for data cleanup and normalization. Now I have to figure out some other way forward. Next candidate: JSON data import.

Roger Cheng

Jupyter Notebook For Spreadsheet Clean-Up

Reading through Budibase documentation and playing around with the quick start tutorial app, I learned many important details I will need to know as I embark on my own project: converting my personal finance tracking spreadsheet into a database app. Before I start working on the app, though, I’ll need to migrate my data over to a Budibase-friendly format.

Budibase accepts uploading data in CSV (comma-separated values) or JSON (JavaScript Object Notation) format. Excel can export a spreadsheet into CSV file format so I thought it would be easy, but once I loaded up my spreadsheet and took a good close look I realized it won’t be that simple. My multi-sheet Excel file has built up many idiosyncrasies. Most of it was because I evolved my system over time but I didn’t go back and change my older entries to match. My brain can parse the inconsistencies within, because it’s the same brain that created them, but they’d trip up a computer database.

I made a copy of my spreadsheet and started editing it to prepare for migration. I plan to create new analysis tools around my migrated database, so I deleted all of my analysis cells leaving just raw data. I removed all empty rows and columns I used for visual separation, because they’d just be noise in the migration. I exported these results to a CSV, imported into Budibase, and it was still a mess. I need even more cleanup.

My next step was a Jupyter notebook. Data cleaning is a pretty common task for data scientists and Panda is a common tool for the job. I don’t think I need that level of complexity, though. Python has a built-in class to parse CSV into a dictionary and that was sufficient for my needs. It was enough to let me iterate through my records and write rules to repair my data entry errors. Typos in names, inconsistent date formats, stuff like that.

I started updating structure of data as well, at first just to maintain data consistency between old and new data. But then I started changing things more drastically because my brain shifted gears from a spreadsheet table to a database. I started normalizing my data to the best of my ability (I think I got to 4NF but I’m not sure) because I understood that allows more efficient database operation. I’m pretty pleased at how it turned out! I’m sure I could have created spreadsheet macros to enforce data consistency, but a properly normalized database makes many of my old mistakes outright impossible. This was the point when I really felt I am making the right move: my personal finance tracking system should have been a database all along. I like my set of CSV files, one per table, and the next step is to upload them into my Budibase project.

Roger Cheng

Initial Budibase Documentation Lessons

Going from Budibase “Quickstart” to a basic understanding of application structure was a bigger step than I had expected, but I think I’m there now. Here are some notable lessons from my first (and second) passes through Budibase documentation with the caveatI wouldn’t know for sure I got this right until I get some hands-on app-building experience.

Blocks Are Not Components

When reading Budibase documentation on user interface elements, I came across terms like “Form”, “Form component”, and “Form block”. I thought they all referred to the same thing, that words “block” and “component” were synonyms. I was wrong, which caused a lot of confusion. A component in Budibase parlance is a single interface unit (example: textbox) and a block is a predefined composition built from multiple components to address common scenarios. (example: form block contains many textboxes.)

The ability to “Eject block” was mentioned in “Quickstart” but I didn’t understand what it meant at a time. I had understood it as a way to break up into smaller pieces so I can access its internals for customization, which should be great for learning how it was built under the hood. But most of the time when I wanted to look inside something I couldn’t find that “Eject” button. Eventually I figured out I had been trying to eject components and that’s why I got nowhere.

Data Provider, Repeater, and Binding

I’ve learned that things are a little more complex than ‘database stores data, components display data, binding connect them together.’ A few additional pieces are required. First is the fact we don’t want everything in the database all at once. For tasks like filtering or pagination, there are “data provider” components that fetch a desired subset from the database. The subset is still a collection of data, so a “repeater” component is deployed as an enumerator. Child components of a repeater will receive that data one row at a time, and that’s when UI components can use a binding to pick up the exact information they want. A hypothetical example:

  • Database: “Employees” table
  • Data provider: “Name” of first 50 employees sorted by name.
  • Repeater: for each of those employees…
  • Paragraph: display text of “name” binding.

When we use a Table block (like the auto-generated screens in the tutorial) these steps are handled automatically. But if we want to built tailored UI, implementing these details become our job.

App State

Sometimes an app will need to convey data between parts outside of the standard data flow hierarchy. (Provider, binding, etc.) I was curious how it was done and hit “eject” on an existing block to see its implementation details. The answer is app state, an application-wide key/value store. Or in other words: global variables!

With these lessons in mind, I am ready to start building my Budibase app to work with my existing data on personal transactions. And wow, it’s a mess.

Roger Cheng

Budibase Documentation: Mind the Gap

I want to learn Budibase and I hope it will be a tool to solve my “Excel is not a database” situation with my personal finance tracking. Keeping that project in mind to anchor my study, I returned to Budibase documentation ready to learn more. They had an excellent “Overview” page, followed by an informative “Quickstart” project. Given those two precedents, I had expected “Quickstart” to be followed by a smooth ramp-up for beginners. Turns out I was overly optimistic. I found a significant gap between “Quickstart” and the knowledge needed to build my own Budibase app.

Going down the documentation index sidebar, “Quickstart” is followed by “Guides and resources”. This is a collection of solutions to common problems, sometimes called a recipe cookbook. I’m sure it’s a great resource, but for a beginner like myself coming right out of “Quickstart”, a cookbook is not what we need. Beginners lack experience to understand the solutions being offered. Even worse, we don’t even necessarily understand the problems being solved. I suffered a similar problem when I encountered the Node-RED cookbook.

After the “Getting Started” section, Budibase documentation had sections titled “Data”, “Design”, and “Bindings” but I saw nothing telling a beginner how they tied together. I was undaunted — I’ve been in similar situations before — so I dove in to read those sections and learn what I can. There were a lot of cross-referencing in those sections (each had references to the other two) meaning it wouldn’t help to read the three sections in any particular order. I had to accept I wouldn’t understand everything the first pass. Then, after reading all three sections once, return to read them all a second time. Only then did I feel I have a basic grasp of how a Budibase app worked.

I think there’s room for improvement here. If my experience is representative, I could see potential Budibase users getting discouraged trying to jump over this post-“Quickstart” gap. In hindsight, I think a few sentences at the end of the “Quickstart” guide would have gone a long way to help. Here’s my first draft:

Budibase Data Flow

This tutorial covered the core of every Budibase app, but things will be done differently beyond a tutorial.

  • Obviously real Budibase apps will not use our sample data. Visit Introduction to data to see how Budibase can work with your data.
  • This tutorial used autogenerated screens to get started quickly with a generic table interface. A good application will tailor an interface to best display and interact with its underlying data. Data in design goes into more details on how to do so.
  • Our autogenerated tables also handled linking data between database and user interface elements. These links are called bindings and Introduction to bindings covers how to define them.

I might submit this, or an evolution of it, as a proposed contribution after I get some Budibase experience to be confident this is actually correct. There’s a lot to learn.

Roger Cheng

Resurrecting Project Idea: Personal Finance Software

I took a quick look under the hood of Budibase to get a rough idea of its capabilities. Before I start reading Budibase documentation in detail, I also want to designate a “north star” project. I’ve found this helps organize my thoughts when I explore a new piece of technology. Without a goal, I risk wandering aimlessly and losing interest. Keeping a project idea in mind helps me stay focused and absorb information in terms of how it relates to my project. For Budibase, I decided to dust off my idea to build personal finance software tailored exactly for myself.

I’ve used personal finance software to track my money ever since I had money to track. I started with Microsoft Money. After Microsoft shut it down, I switched to Intuit Quicken. As the world became more online, I switched to Mint. It didn’t take long before I decided I didn’t like doing my personal finances that way. (Well before Intuit bought and eventually shut down its competitor.)

When I started with Money, the usage pattern is for me to enter my financial transactions into my PC software. Then when I received my bank statements, reconcile what I entered with my statement to make sure everything matched up. By the time of Mint, such usage was still possible but no longer the focus. I was encouraged to connect to my financial services online to download account activity. Everything is entered and reconciled automatically. I totally understand why this was done. Most people don’t want to spend the time to manually enter their transactions, then manually reconcile them. The modern usage pattern takes no time. But to my mind, such automation took away the first layer of awareness of my own finances. It’s easy for small fraudulent transactions to slip through unnoticed, for example. I didn’t like it.

After transactions are entered and reconciled, all personal finance software include analysis tools to help the user better understand where their money has gone. But as a data nerd, such graphs and charts were never good enough. I can bring up a chart, click to see more details about what contributed to that graph, and the drill-down always ends before I am satisfied with the level of detail. I understand that most people wouldn’t care to spend time for such analysis, but I do! Spending reports. Flow of money. Account activity. More, I crave more!

I have come to accept the fact my own desires are not aligned with the general populace. Which meant I will have to roll up my sleeves and do it myself. Microsoft Excel seemed like an obvious choice as spreadsheets are intrinsically linked to finance. I started a spreadsheet to track my expenses and things worked well enough at the beginning. As my records grew, though, I started hitting limits of my Excel skills. It was good motivation for me to learn more Excel as I go, but eventually I recognized my mistake. Yes, Excel is the king of finance, but I’m not doing any financial calculations. (No compound interest, etc.) What I’m really doing is record keeping, which is a database job! I can continue learning Excel techniques to compensate for the fact it isn’t a database, or I can switch to using a database.

I’ve approached this project idea many times. I had this in the back of my mind when I took Codecademy’s PostgreSQL and MongoDB database classes, for example. I even got as far as creating a GitHub repository called “rogmoney”, but I didn’t do anything beyond setting up a Node.js environment. Looking at the timestamp I believe my thought was to build a personal finance app on Angular but I never even got as far as committing an empty Angular boilerplate app. Now I will make another attempt. It will help me learn Budibase whether the attempt results in my ideal personal finance tool or not.

Roger Cheng

Quick Survey of Budibase Foundation

I’m playing with Budibase on a self-hosted instance courtesy of Docker Compose. I didn’t edit the docker-compose.yaml file provided by Budibase but I did open it up to look around to see what Budibase was built atop of. Later I found answers within Budibase documentation which included a page on its system architecture and other scattered information on how it was built. Most of these building blocks are things I might use in my own future projects, with one major exception.

CouchDB

I knew CouchDB is one of the “NoSQL” databases out there, but not much more than that. My own NoSQL database adventure was with MongoDB and I don’t yet know enough to tell when I might use one versus another. Budibase uses CouchDB for its own internal operational data like user accounts. It is also available as the default store for client app data if I don’t want to connect to an external database. My to-do list includes better understanding the tradeoffs between those two paths.

Redis

Not everything involved in running an application needs to be written to disk. Sometimes an in-memory data storage system like Redis is the right tool for the job, because it is far faster than any disk. I’ve known Redis existed but never had a project that needed its capabilities. Budibase says it uses Redis as a high performance cache store.

MinIO

One thing that caught my eye about Budibase is file-handling ability. Meaning data entries aren’t limited to classic database types like dates or numbers or text, I can also upload file attachments. I had guessed it was implemented using something like PostgreSQL binary data format, but I was wrong. Attachments are actually stored in an instance of MinIO which I knew nothing about until now. A quick skim of documentation looks like MinIO is a way to get much of the functionality of AWS S3 but on my own data storage instead of AWS datacenter hardware. Like CouchDB, MinIO also used to store files for Budibase internal operation in addition to storing client app data.

Svelte

I was curious how Budibase built the HTML UI I had been looking at, and part of the answer is Svelte. This was on my “maybe look into later” list due to its connection with Vite, which I briefly looked at earlier. It’s all layers upon layers, and there appears to be yet another layer on top: SvelteKit. I didn’t see any mention of SvelteKit, though, so I think Budibase is using things at Svelte level.

Adobe Spectrum

Another component of Budibase HTML UI is Adobe’s Spectrum design system. I don’t recall ever coming across Spectrum before, but in hindsight it made sense Adobe would create its own interface design. Several years ago I became infatuated with Google’s Material Design system, and it was a big motivation for me to learn Google’s Angular platform. Since then Google corporate behavior turned me from a fan into a skeptic, and I’m not inclined to further pursue either Angular or Material Design. Adobe Spectrum Design would be an alternative… if it weren’t for the fact I’m even less fond of Adobe business practices. The fact Budibase uses Adobe Spectrum is not a deal-breaker against Budibase, but I’m never going to use Spectrum for my own web development projects.

Speaking of my own projects, I need to pick something interesting I can use to learn Budibase as I try to turn idea into reality. Fortunately I have no shortage of project ideas and have a long standing “Excel is not a database” problem I’ve wanted to solve: personal finance tracking.

Roger Cheng

Self-Hosting Budibase (Docker Compose)

I went through Budibase quick start guide and it made a great first impression. I want to learn it and add it to my toolbox for times when I need to build a data web app in a hurry. The quick start used Budibase cloud hosting for an easy low-friction way to kick the tires. Good enough for me to continue, but before I do it was important that I continue with an instance of Budibase I run for my own use. Wary of the example set by AWS Honeycode, which Amazon had shut down and took all user data offline with it, it was critically important that I can run my own local instance on data I control. This is a pretty common requirement so Budibase offers many alternatives to their cloud-hosted solution. I clicked on the link for deploying via Docker Compose and it turned out to be really easy.

I didn’t expect it to be that easy. I was no stranger to install procedures that went awry and causing collateral damage elsewhere. I spun up a Proxmox virtual machine running Ubuntu server 24.04 LTS for the exclusive purpose of experimenting with Budibase. If anything goes wrong in a VM, damage should be contained and I can easily erase everything and start over. I followed Budibase instructions to install Docker Compose then downloaded their docker-compose.yaml file for deploying Budibase and an .env file for configuration variables. I didn’t edit their docker-compose.yaml file, just the .env file to set my own passwords. I then typed “docker compose up“, waited for all the startup and deployment procedures to run, then pointed my browser at the address for my Budibase virtual machine. I was greeted with the initial setup screen to create an account on my newly-created Budibase instance. No mysterious failures, no inscrutable error messages, excellent.

I repeated the quick start tutorial on my own instance, and it worked exactly as it did on the cloud hosted instance. For the most part this is good, but not always. My first rude surprise was finding that Budibase app backup was locked off. Backup was clearly indicated as a Premium subscription tier feature, but I had thought that applied only to the cloud-hosted service. I was wrong: subscription features are locked away whether I’m running on their cloud hosting platform or on my own. If Budibase is truly and fully open source I suppose I can clone their GitHub repository and build my own version without these locks. Even if I don’t want to do that, the fact I’m running with Docker Compose means I can back up my data via Docker volumes. So, yes, there are solutions, but this is proof that levers still exist for Budibase to restrict self-hosted instances.

This is a demerit against Budibase, but not a deal-breaker. After all, I’m putting up with much the same risk in other self-hosted services like Plex Media Server, though in that case I have an alternative Jellyfin lined up. For now it’ll just be a note here on my project notebook and I will continue exploring Budibase.

Roger Cheng

Budibase Quickstart Is A Great Tour

I want to investigate tools for quickly building data applications and thought Budibase was the most promising candidate. Since the main pitch is ease of getting something useful up and running, they have put together a quick start guide to serve as a lightning tour of product features. Their effort to make a great first impression certainly worked on me.

Minimizing barriers to entry, we are pointed to their cloud-hosted service where we register to create our quick start app without worrying about setup or deployment. Then a sample database is only one click away. I examined the sample database and found a decently structured example with multiple tables and relationships between them. I had expected to see an easy single table database and was impressed they gave us something more representative of real world applications.

Once sample database tables were in place, the tour takes us to the interface design screen. One of the default templates is an editable table that would be comfortably familiar for any spreadsheet user. Simple data navigation like sorting by a column or searching in a column works much the same way as well, except this editable table also has a basic ability to interact with relational database tables.

If the goal is to take a small first step away from abusing Excel as a database, Budibase lives up to its promise. It really did take only a few minutes to set up a web app that presents an Excel-like interface but with underlying relational database capabilities managed with web app access control and data validation capabilities. It’s a credible “Minimal Viable Product” and if that’s all somebody needs, they can stop right there and have a perfectly usable tool.

But of course that is only the beginning. The real power comes from building interfaces better tailored to the task at hand instead of a spreadsheet-like table. For data entry, Budibase offers the usual set of form elements (text entry, number entry) plus a few intriguing surprises (file attachment upload?) Playing around with these tools I got the distinct feeling this section of Budibase is aiming to surpass Google Forms for ease of use. [UPDATE: Yep, there’s a page in Budibase documentation dedicated to pitching itself against Google Forms.] I’m not enough of a Google Forms user to judge if Budibase has succeeded, but I see no reason why I need to go back to Google Forms.

This quick start guide ends with links to other sections in Budibase documentation for further exploration. I like what I see so far, and I want to continue exploring, but on my own self-hosted instance instead of their cloud-hosted one.

Roger Cheng

Window Shopping Budibase

Occasionally I’ve had project ideas that require a database to keep track of everything, and then I would shelve the idea because I didn’t want to spend the time. Or even worse, try to do it with a spreadsheet and suffer all the ills of a square peg in a round hole. I want an easier way to use databases. The good news is that I’m not the only one with this problem and many solutions exist under the marketing umbrella of “low-code” or “no-code” web app platforms. Skimming available information, I think Budibase is worth a closer look.

Budibase is one of the “low-code” platforms and I understand this to mean I don’t have to write any code if my project stays within the lane of a supported usage pattern. But if I must go outside those boundaries, I have the option of inserting my own code to customize Budibase instead of being left on my own to re-implement everything from scratch. Sounds good.

On the business side, Budibase is a free open source project with source code hosted on GitHub. Compiled binaries for self-managed deployments are available a few different ways, including Docker containers which is how I’d test things out. Users are allowed to run their own apps on cloud infrastructure of their choice. However, the licensing agreement forbids reselling Budibase itself as a cloud-hosted service. That privilege is reserved exclusively to Budibase themselves and serves as an income stream alongside consultancy contracts to build apps for businesses. I can support this arrangement as a tradeoff between proprietary commercial software and totally free open-source projects without a reliable income stream. This way Budibase is less likely to shut down and, even if it does, I should be able to limp along on a self-hosted setup until I can find a replacement.

Which brings me to the final point: the database. For the sake of simplicity, some of these low-code/no-code app platforms are built around their own database and assume full control over that database from beginning to end. In some cases this puts my data into a black box I might not be able to port to another platform. In contrast, I can build a Budibase interface on top of an existing database. And that database will still be available independently if Budibase goes away, or if I just want to explore something else. I like having such options and the data security it implies.

I like what I see so far, more than good enough for me to dive into Budibase documentation. Learn how I can translate its theoretical benefits to reality. Starting with an excellent quick start guide.

Roger Cheng

Many Options For Create/Read/Update/Delete (CRUD) Web Applications

In my quest to stop abusing Excel for database tasks, I returned to the world of web development. I remember studying web development for a while before I came across the term “CRUD app”. Though I might have encountered it even earlier and thought it was derogatory insult instead of a descriptive acronym: Create, Read, Update, and Delete. These four operations neatly encapsulates all fundamental functionality of a productive application (and even some entertainment ones, too.) This set of data manipulation activities drove HTML design from the very start: HTML <form> exist to enter and update data, and HTML <table> exist to present data.

Corresponding to those fundamental HTML 1.0 concepts were server-side mechanism that started with the Common Gateway Interface. Server-side infrastructure has evolved since their beginning, just as browsers have. By the time I started my web development education I had the luxury of platforms like Ruby on Rails. Which offered a “scaffolding” mechanism to automatically generate CRUD infrastructure so I didn’t have to write my own from scratch. Other development platforms offer similar counterparts, but that’s not the only way to go. I have since come across many more options for building a CRUD web app.

The simplest and easiest way into this world is Google Forms (and its competitors). I frequently encounter it for surveys, registration, etc. Which meant I always see the “Create” side of the app, though some forms allow me to return and “Update” my data. Whoever created the Google Form can then “Read” submitted responses and “Delete” if needed. Google Forms make creating a CRUD web app as easy as creating a Google Doc, and with better control over data than sharing a spreadsheet and telling people to add their data to a row.

I want to learn something quick and easy to use so I can use a database when it’s the right tool for the job. Minimize the hurdle of getting over the “ugh, I don’t want to spin up a database” hump. Google Form is a very simple way to go, but it goes too far: the data is output to a spreadsheet (Google Sheets) instead of a database perpetuating the “Excel is not a database” legacy. So I went looking for something between super simple Google Forms and full web development platforms like Ruby on Rails.

What I found are products that advertise themselves as “no-code” or “low-code” web app tools. The first one I came across was Amazon’s Honeycode under their AWS umbrella, but that has since been shut down. (I was going to link to the shutdown announcement, but it was posted to the Honeycode site, which is now dead.) Keenly reminded of the perils of putting my data into an online service, I focused on solutions I could run at home and found Budibase as a promising candidate.

Roger Cheng

Browser Based Database Front End

I have added WebUSB to my ever-growing to-learn list, hoping it would enable some really cool project ideas. As indicated by its name, I would need to know both web development and USB development before I can make them work together towards my goals. While I work my way through my self-directed USB study syllabus, I will also spend some time reviewing web development.

I’m not a complete beginner with web development, but that world is broad and it evolves quickly. Every time I applied browser-based technology to a project idea, I find some of my knowledge out of date. Plus I have to learn something new in an area I haven’t dealt with before. This is great! Approaching from a different direction every time helps me get a more well-rounded picture of the whole thing. This time I want revisit building web apps that present an user interface for a database.

My motivation is twofold. One is directly related to my desire to use WebUSB. Since any WebUSB project will be dealing with data flowing in and out of an external source (the USB peripheral) I expect many JavaScript constructs to resemble those used to communicate data to and from an external database. The second is that I’ve wanted to get better at applying database technology. So I can use it when it is the right tool for the job instead of, say, abusing Microsoft Excel. “Excel is not a Database” is an ongoing joke in the computer world. People do it because starting an Excel spreadsheet is far easier than setting up a database. Excel is “good enough” at small scales, but spirals into chaos as data set grows. There’s no shortage of horror stories ranging from Formula 1 race car construction to losing important healthcare data.

I want to avoid such disasters myself, because I’m definitely guilty of abusing Excel for poorly-suited problems. Some novel hacks turn out to be a delightful success, others times I find I really needed an actual database. I want it in my toolbox. This is something of a “return to where it all began” because my first web technology lesson many years ago came from The Ruby on Rails Tutorial. The main tutorial project was a web application interfacing with a database server. It’s a pattern common enough that I’ve since learned of an acronym for it: CRUD apps. And there exist many ways for me to build one of my own.

Roger Cheng

Potential WebUSB Study Syllabus

I followed through the steps of an Adafruit WebUSB example and established connection between Chrome browser on my Android phone to a microcontroller plugged into the USB port on the phone. I think WebUSB would enable many of my project ideas. But before I can turn any of my vague ideas into reality, I have a lot of homework to do.

The Adafruit example was thin on background information. I think it was written for people who already know how to work with TinyUSB library and just wanted to see Adafruit’s adaptation into an Arduino library. This impression is backed up by the fact its GitHub repository README (https://github.com/adafruit/Adafruit_TinyUSB_Arduino) is written using vocabulary I don’t understand. Following the link to TinyUSB’s site gives me similar language, so I have to start climbing my learning curve from somewhere else.

Searching from the browser side, I found a Chrome developer documentation page on WebUSB. I was able to comprehend more of this page, but not all of it. Here I learned one constraint on WebUSB: web apps are only allowed to connect to USB devices the operating system doesn’t already have a driver for. This is a mitigation against malicious apps bypassing operating system protection for USB devices like security keys. It also avoids ambiguity/duplication with existing functionality. For example, there’s no real need for a web app to interface with a USB keyboard via WebUSB when the operating system can already deliver key press events. Though there’s an interesting wrinkle here around USB serial, a common way to connect to microcontroller projects. By this rule, a web app can’t connect to a USB serial device on my desktop via WebUSB because my operating system already knows how to work with a serial device. (So it’s over to Web Serial land.) But apparently Android lacks a built-in handler for serial, so maybe it’s available via WebUSB? At this point I don’t yet know if that’s an opportunity or just a source of confusion.

Fortunately for beginners like myself, author of this Chrome developer documentation page included a link to USB in a NutShell for those unfamiliar with fundamental USB concepts. Hey, that’s me! I will try to start with USB in a NutShell and work my way back to TinyUSB and its various incarnations like Adafruit’s Arduino library. But that’s just the “USB” part, I have a lot I’ll have to learn for the “Web” part as well.

Roger Cheng

Adafruit WebUSB Arduino Example

I knew WebUSB existed but not much more than the name implying some sort of USB capability for web applications. I was motivated to look into it a bit more after learning about an index of browser diagnostics tools. Initial inconclusive signs were not promising but I kept looking. After a bit I thought: I want to explore this capability for my electronics projects. Maybe somebody at Adafruit has already looked into this? Searching for WebUSB on Adafruit Learn gave me a hit: Using WebUSB with Arduino and TinyUSB. So yes, they have!

I was happy to see the hardware in this example was Adafruit’s Circuit Playground Express (3333), because I have one already on hand. When following an example for the first time, it’s always nice to have the exact same hardware that I know will work, rather than similar hardware that should work. Despite that advantage it was not smooth sailing. I got stuck when it came to changing my Arduino IDE’s Tools/USB Stack to “TinyUSB”: there wasn’t a “USB Stack” option under “Tools” menu! I ran around in circles for a while before I eventually figured out I was using the wrong Arduino board support package. This example required “Adafruit SAMD Boards” and not “Arduino SAMD Boards”. I was thrown off because “Arduino SAMD Boards” included support for a bunch of Arduino boards and Adafruit’s Circuit Playground Express. I was able to select the proper board without realizing I was in the wrong board support library. I don’t know why Arduino claims support for boards that aren’t theirs, when the manufacturer has provided their own board support. It’s confusing, this is the second time they bit me, and I’m not happy.

Anyway, once I installed Adafruit’s board support library and selected Circuit Playground Express under Adafruit’s umbrella, I had a “USB Stack” option under “Tools” and could proceed to follow along with the example with no further issues. My first run used Chrome on my desktop computer, and after that success I tried it with Chrome on my Android phone. It works there, too!

And I can verify chrome://device-log is no longer empty on the phone, it now shows the newly-connected USB hardware.

This is huge! WebUSB might enable many project ideas that involve using one of my retired Android phone as the display (or more) of an electronics project. Which ones? I won’t know for sure until I learn more about the constraints of Android Chrome WebUSB support. I would have to pick a relatively simple one as a starting point before jumping into the more complex ideas. There’s a lot of study ahead. This Adafruit example was unfortunately lacking on background and theory of WebUSB so I’m on my own. I think it was written for people who already have the appropriate background, and that’s not me. Well, not yet. I need a refresher course on web development, and I will need to learn technical details of USB as well.

Roger Cheng

Android Chrome Device Log Strangely Empty

Learning about Chrome’s index of special URLs was very interesting. Aside from satisfying curiosity, it also gave me the tools to investigate an idea: can I write a web app to use an Android phone as interface to an electronics project that communicates over USB?

I want to repurpose my old retired Android phones as project UI, and have been making small incremental steps. My AS7341 spectral color sensor project presented its data as a web page served by the ESP32 on board, and my Android compass app for magnetometer exploration was also a web app to visualize data from my phone’s onboard sensors. But I haven’t been able to combine a phone’s onboard capability with external offboard capability. The barrier is a security measure: only web apps served from public TLC-secured https address is allowed to access extended capabilities like magnetometer. Web apps served locally over unencrypted http, like those served by my ESP32, is not allowed to access such things.

At one point in the past, web apps served via secured https was allowed to retrieve data from non-secure http sources, but I found that has been locked down in modern browsers. Now they require https all the way. I found this restriction during research for an earlier iteration of my AMG8833 thermal camera idea: I thought I could pair AMG8833 data with a phone’s onboard camera, but the https/http barrier sunk that plan. I had to wait for my Adafruit Memento to revisit that idea.

WebUSB is another one of these https-only features. If I can communicate with external peripherals over WebUSB, I can serve a web app from a https source (like GitHub pages) and talk to my hardware over USB instead of forbidden insecure http. To test this hypothesis, I took a USB keyboard and plugged it into my desktop PC running Google Chrome. I brought up chrome://device-log to verify that a USB keyboard shows up as a newly attached HID peripheral.

I then plugged the same keyboard into my Google Pixel 7. The keyboard is recognized and functional: I brought up Google Chrome and could type chrome://device-log. But unlike Chrome on my desktop, Chrome on my phone does not show a newly attached USB keyboard as HID peripheral. It just shows a completely empty device log. I know that even if a device shows up here it is not a guarantee that it supports WebUSB. But it’s not very promising when the log shows nothing at all. Does this necessarily mean Android Chrome doesn’t even see the hardware? That would be discouraging.

I know USB doesn’t work the same way on an Android phone as it does on a PC. For one thing, Android control panel has this “USB Preferences” screen to control how my Android phone uses its USB port. This screen represents a mechanism unique to Android USB behavior. There may be others, and I’ll have to learn to work with them. I checked https://caniuse.com and it says WebUSB is supported on Chrome for Android. That encouraged me enough to keep searching for more information on how this might work and found a WebUSB example from Adafruit which managed to make my Android device log less empty.

Roger Cheng

Looking Under A Browser’s Hood

Every aspiring web developer quickly learns about the collection of developer tools built into a browser, where we can look at how the browser has interpreted the HTML/CSS/JavaScript for the current page. It’s a very powerful set of diagnostic tools, but they’re about the content. What about the browser itself? There’s an entirely different set of tools to look at how the browser itself functions.

I’ve seen bits and pieces of these behind-the-scenes browser configuration and diagnostics pages before. Like when I had to enable Android Chrome’s compass API, or switch Firefox PDF viewer to full page zoom default. While I didn’t know the full extent of what’s available, I had assumed there would be a central directory buried somewhere in their respective developer documentation. I was half right: there is indeed a directory, but it’s not buried in documentation, it’s accessible right in the browser itself.

For Google Chrome, the master index is chrome://chrome-urls/. I learned of this from an old Tweet by Massimo Banzi (a.k.a. Mr. Arduino) who pointed to chrome://device-log/ and from there to the master list of all similar URLs. chrome://device-log/ shows all USB-related activity visible to Chrome and helps diagnose problems with things like WebUSB and WebSerial. It’s a sensible introduction for someone deep into the Arduino world.

But chrome://chrome-urls/ opens up a much broader view into Chrome browser internals. I was fascinated by chrome://omnibox/ which shows what happens when we type into what was originally Chrome’s address bar but had grown into so much more. How does Chrome choose to interpret what I typed as an address, or as a search term, or if I’m trying to go back in my browser history? chrome://omnibox/ shows all. And that’s just one of many special URLs listed by chrome://chrome-urls/.

For Microsoft Edge, which is now a Chromium-based browser, it has its own counterpart with search-and-replaced name edge://edge-urls. Mozilla Firefox went to a slightly different route, with their index available at about:about. I didn’t find an equivalent index for Apple Safari. Maybe there isn’t one built-in like the rest? I don’t use Safari as much anyway.

Roger Cheng

Introduction to Electronics With Tom Thoen

My personal path of electronics education was not a planned curriculum. It was a meandering haphazard collection of lessons I learned by browsing, observing, and screwing around. Absorbing knowledge as I go whenever and wherever I can. So when someone asks how they can get started, I didn’t know where to point them. “Visit Radio Shack some decades ago, before they sucked” is not a useful pointer. Eventually I learned of Adafruit and all the resources they put online for beginners, and have been pointing people in that direction. But I got a Radio Shack flashback when I read A 65-in-1 the 2024 Way on Hackaday.

I remember that type of educational electronic kits on Radio Shack shelves and remember wanting one of my own, but I never did. I’ve talked to many people who fondly remember their kit and credit them with changing the trajectory of their life. Pretty amazing and occasionally I wondered if getting one would have significantly changed my own life. I’ll never know the answer, but I’m glad somebody is working to recreate that kind of magic for a new generation of potential tinkerers.

Following the link on that page, I learned the project creator Tom Thoen has been doing more than just nostalgically recalling his old kit. He has instructed introductory electronics courses at local colleges and his class materials are available online at https://www.profthoen.com. Reading through the lecture slides by themselves isn’t as good as getting it alongside their accompanying lecture, but still informative. I think this is a great addition to the collection of pointers I can give to aspiring beginners.

[Header image: Main logic board from my Form 1+ teardown]