budibase

Roger Cheng

Observations After My First Budibase App

I’ve converted my personal finance tracking information from a spreadsheet to a database. And while I can input new data with a PostgreSQL command line tool (psql) I’m learning how to build a friendlier interface with Budibase. I’ve run into a few problems but since I’m such a beginner it’s too early to say if they are Budibase problems or if they’re just beginner problems. One thing I can say for sure: Budibase made it extremely easy to get up and running with a table-based interface. So my “minimum viable product” is basically what I had before (table in a spreadsheet) and it’s all upside from here.

One thing I’ve noticed about myself is that I have an easier time taking my idea and figure out the PostgreSQL operations needed to make it happen. I have a much harder time figuring out which Budibase tools exist to solve the same data manipulation problems. As a result I have a nice long list of custom SQL queries registered in my Budibase app, trusting Budibase to display the results in a sensible table which it has always been able to do. I think a big part of this is because I have a better idea what keywords to search for when looking to do something with SQL, reinforced by the fact there’s significantly more information on the web about SQL than about Budibase. A smaller part is that I’m still having a hard time understanding certain details in Budibase documentation. For one example: Budibase data binding helpers for array data has two very similar methods: equalsLength and lengthEqual. Budibase documentation explains two of them using very similar language and I can’t figure out how they are different. Why do they both exist? When should I use one versus the other? Budibase documentation does not say, leaving me frustrated.

I also went poking around for entry point/hooks for external code. I guess custom SQL queries technically count as “external code” but I was looking for a general programming language. Earlier I found that Budibase data binding supported snippets of JavaScript for data operations. I found a fairly similar mechanism available for creating Budibase automation. Both cases are intended for fairly small snippets of code, restricted only to standard JavaScript operators. The only input are Budibase binding syntax and the only output is a return statement. In contrast to this limited API, the deep end is a REST API. I didn’t find anything in between these two extremes.

I appreciate how easy it was to in Budibase to build a table-based interface on top of database tables. It is perfectly suited to help people use a real database if they would have otherwise abused a spreadsheet as a database. If they don’t know (or care about) relational database concepts, or SQL query syntax, or writing code, it’s a great tool. But I know all of those things and I’m already itching to do more with them outside of Budibase constraints. Such a project would have to wait, though, because I got distracted by a video game called MechWarrior 5: Mercenaries.

Roger Cheng

Budibase Relationship Problems: Create

Budibase aims to make solving common problems very easy, and I can understand the design decision to not letting more complex scenarios clutter up their UI. But it still causes headaches for me. I ran into problems trying to display data across multiple tables, and then I learned creating a multi-table record in Budibase can get convoluted for related reasons. The first restriction is the built-in relationship picker tool, which helpfully presents the display column text (Target) and when selected enters the foreign key (476) into the table. But relationship picker has a hard coded ceiling of 100 entries. I don’t have 100 accounts or 100 categories so those will work, but I have definitely shopped at more than 100 vendors! I had to create my own UI to manage the vendor relationship.

I want to write down the next problem here even though it is not yet well understood. Since Budibase couldn’t help me create these multi-table entries directly, I wrote my own interface using Budibase form objects, button objects, and actions associated with button click. The idea is that each form would be a row in one table, and when I click a button, I would use several “Update Field Value” actions to update multiple forms with data generated by my data binding. Then I would use the “Save Row” action on each of those forms to submit my multi-table entry. The idea is to have a mechanism to handle regularly occurring repetitive tasks with a single click.

In practice, my single button would intermittently fail with “Null not allowed” error. If I click my button again, it would always succeed, but then I might have duplicate entries in my database due to my multi-table hack failing partway. This feels like an asynchronous race condition with “Save Row” being executed before all previous “Update Field Value” actions had completed. When I click again a second later, all of the fields are already updated so save row works.

Many databases have a mechanism to designate set of operations as a single “transaction” unit. The database is responsible for guaranteeing “all or nothing”. Either every part of the transaction is successful and written to the database (“commit”) or if something goes wrong, it should be as if nothing had happened. (“revert”) I looked for transaction support via Budibase but didn’t find anything.

Looking for a workaround, I searched for a “Wait for previous action to complete before proceeding” action but didn’t find one, nor did I find anything like a “Wait X second” action. So my workaround is to use two buttons: one has all the “Update Field Value” actions, and a second button with all “Save Row” actions. I click one, then I click the other. Two button clicks is double the amount I thought it should take, but it is still better than doing all the repetitive steps by hand. Still, it doesn’t mask the fact the whole situation is pretty silly and I think reflects a bug in Budibase. But since I’m a beginner it could be my own fault for reasons I don’t understand yet. I’ll have to play with Budibase some more.

Roger Cheng

Budibase Relationship Problems: Read

Once I got a working knowledge of Budibase data binding, I feel like I’m past the “I have no idea what I am doing” beginner phase and entered into the “I can probably figure out what key words to search for an answer” intermediate phase. The road from here to “I know how to do this” experienced phase is likely paved with a lot of hands-on time. I think Budibase will be able to cover 90% of what I want with very little effort on my part, fulfilling its promise. But that last 10% could motivate me to examine other tools to add to my toolbox.

One example is how Budibase handles table relationships. For every database table, we have to designate one column as a special display column. When displaying tables with defined relationships to other tables, Budibase can automatically replace an foreign key reference with the display column from that column table. My “vendors” table has a “name” column as its display column. Each row in my “transactions” table has a “vendor_id” column that points to a row in “vendors” table. Budibase can combine them so when it shows a row from my “transactions” table, instead of showing the vendor ID (476) it queries my “vendors” table and shows the “name” for that ID instead (Target). This is great and it covers majority of what people would need.

But if I want to go deeper, I’m on my own. My example is my “payments” table, which was my way of implementing classic double-entry bookkeeping and each row has two transaction IDs. One is a deduction transaction from my checking account, the other a credit transaction for the same amount to my Target card account. I wanted to create Budibase dashboard showing a payment amount from “Checking” to “Target”, but those names are two levels away (“payments” to “transactions” to “vendors“) and I couldn’t find a way to do it within Budibase. I ended up solving my problem with a custom PostgreSQL query that performed multiple table joins, then returned the selected results to Budibase, which then dutifully displayed it as a table. I may find a more elegant way to accomplish what I want in the future, but for today this is good enough while I struggle with other problems.

Roger Cheng

Budibase Data Binding

After migrating from a spreadsheet into a PostgreSQL database, I could get to work building an interface for my data with Budibase. The big sales pitch for Budibase is that I could be up and running in minutes. And that is indeed true as long as the interface I want is a big spreadsheet-like table. If I want something more specifically tailored to my data, it’s going to take more than a few minutes. Laying out a page with data input/output elements is a pretty straightforward drag-and-drop WYSIWYG affair, but bringing that page to life with real data requires Budibase data binding.

Immediately following introduction of data binding, Budibase documentation jumps into discussing the two supported languages. (“Handlebars” and “JavaScript”.) I felt they should have covered data binding scope first. In hindsight understand that would have been a useful mental boundary before diving into syntax details of the two methods.

Handlebars

The syntax more commonly seen in Budibase documentation is referred to as “handlebars”, and it can be recognized by the two curly braces that open and close a handlebar expression. Judging by a package.json file, this was built on top the HandlebarsJS library alongside a Budibase helper library. My first exposure to it in Budibase documentation was this example from the Design/Components/Table page:

{{ toFixed (sum (pluck Table Block 1.Selected Rows 'KGS')) 2 }} KG

This extracts data from the “KGS” column of all selected rows in Table Block 1, add those values together, round the sum to two decimal places, then append a “KG” suffix for display. Handlebars syntax allowed specifying such common operations quickly, a really powerful tool but completely inscrutable to a beginner not versed in the compact syntax. It also has its limitations. A significant one is that output of every handlebar expression is always a string. (Called out at the end of the formula page.) Plus, its extensive helper library can’t cover everything. There would inevitably come a time when we need access to a general-purpose language for the job.

JavaScript

If I want to create a binding that can’t be done in handlebars (either fundamental limitation or just I don’t know how) I can click a button to switch to JavaScript mode. This opens up a small text window for a JavaScript code snippet editor and my first step from “no-code” Budibase to “low-code” Budibase. Input comes from an expression that starts with a jQuery-like “$”. The counterpart to the handlebars example above would be something like $('Table Block 1.Selected Rows'). That expression returns a JavaScript array (one element is one row) of dictionaries (key/value pairs of column name and value). From there, standard JavaScript syntax and libraries apply, and my JavaScript code runs until a return statement at the end with a processed value for Budibase to use.

Since the pattern is to consume an array and produce a single output, many of the JavaScript data binding examples use reduce(). That seems to be the most popular one, though other JavaScript array iterators are applicable. I think it’s because handlebars can do most of the rest. In contrast, reduce() takes a snippet of custom logic and applies it. Generally speaking there are times not to use reduce() but as far as I can tell a Budibase binding function is unlikely to touch upon those scenarios.

To venture beyond Budibase default data presentation of a big spreadsheet-like table, I needed a bare minimum working knowledge of data binding in order to build my own interfaces. It didn’t take long before I encountered problems, as expected for a beginner diving in for the first time.

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.