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.

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.

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.

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.