Walkthrough of a spreadsheet/database-hybrid with Clojure(Script)

15 Mar 2021

My latest project is a spreadsheet/database hybrid called mixgrid. Mixgrid is a record based database that is editable like a spreadsheet, similar to Airtable, Stackby, Google Tables and Microsoft Lists. While mixgrid is in its early stages it already supports some nice features:

• Real time collaboration
• Link records between tables
• Lookup values from linked records
• Apply formulas per row
• Undo & redo support
• Sorting and nested grouping of rows

You can try mixgrid here: https://mixgrid.thegeez.net. The bottom part of this post is a walkthrough of the features.

Used libraries and technologies

The project is ClojureScript on the front-end and Clojure on the back-end. It is deployed on Amazon Web Services (AWS).

The front-end uses ClojureScript, Reagent and Re-Frame. Together with using Figwheel for live updating during development this remains my favorite technology to make applications in a browser.

The back-end uses Clojure and jdbc.next to talk to a Postgres database. The 'plan' function in jdbc.next, which returns the result of a query as reducible collection, is great to only query the data you need from the database without doing superfluous conversions to Clojure data. jdbc.next is similarly convenient as efficient for inserts and updates with its PreparedStatement support. A great help during development has been the REBL tool. It serves as scaffold UI for larger and more complex data during REPL-driven development. This removed the need for a lot of ad-hoc code during development.

The project is deployed on AWS using Lambda, Postgres RDS and API Gateway with an http and websocket api. These are all managed and serverless services. This means the code in the mixgrid project only handles events. This removes the need for a lot of webserver and connection management code and configuration. To keep the start-up time of the code on Lambda low, the project is compiled using GraalVM native-image.

Demo and walkthrough

You can try mixgrid here: https://mixgrid.thegeez.net. Below is a walkthrough of the currently supported features:

Opening mixgrid will log you in as a guest and you'll have a demo table available. You can add rows and columns in the table. To follow along the walkthrough you can import a dataset using the orange button. This dataset contains about 9k rows of information of power stations in Europe. You can inspect the data and group and sort it. Clicking on "[#]" at the start of a row opens the view for a single record.

The data grouped by "Country Code" and "Primary fuel" and showing a single record view.

For the imported data, all the columns have values as strings. By using the "[=]" icon in the header of each column, this type can be changed. For instance the "Capacity (MW)" column should be of type "Number" & "Decimal".

The most advance column type is that of a link between records. To try this feature we first need a second table. Create a new table using the "New Table" button, and call it "Fuel". This table will have a record for each type of fuel in the dataset. Open the "Fuel" table in a second window. Now we can change the column type of the "Primary fuel" column to a link. Open the column config menu with "[=]" and select "Link to other record(s)" and the "Fuel" table. This will create a new record for each fuel type in the column and a two-way link between both tables.

Links between the records in the Demo and Fuel table

Now that you have two screens open, you can also see the real-time collaboration in action. Any change will be synchronized to all users in the table. You can also undo or redo any change to a field or type of a column using the "Undo" and "Redo" buttons.

For linked records you can show values from all the linked records from the other table in a column. For example we can show all the capacities per fuel type in the "Fuel" table. Click the "+" icon next to the column "Link to Demo". As the column type select "Lookup", link as "Link to Demo" and "Capacity (MW)" column.

Finally there's the Formula column type. We'll calculate the total capacity per type of fuel. Create a new column with the "Formula" type and as input the lookup column with the capacities we just created. For the function choose SUM. Note that the "Capacity (MW)" column in the "Demo" table will need to have the type of a "Number", otherwise the formula will show "#ERROR" for every entry, because it can't SUM string values. [TODO insert javascript joke here]

Values lookup from linked records and a formula applied to each row

If you change a capacity value in the Demo table or add or remove a link, then the lookup and formula will automatically be updated.

Again, here's the link: https://mixgrid.thegeez.net