A Relational Spreadsheet | Hacker News Reader

A Relational Spreadsheet

kevinlynagh.com

172 pointsdustingetz73 comments

I want the same, I want it so much that I have been working on gasp gui code to do such, an activity I have found I am profoundly bad at.

But the theory is, I love the relational database, they are a sort of rigorous superset of the spreadsheet, and I have replaced all my spreadsheets with database tables, however while it is very hard to beat sql for rich comprehensive data transforms and analysis, ad-hoc data entry is very awkward. Most gui tools are focused on database administration and I want one for quick random edits. any hints?

Airtable provides an interesting mix of spreadsheet and database functionality.

I've tried a bunch, but many of the prettier ones don't have Linux versions. I've been using DBeaver (https://dbeaver.io/) for years now though - it's occasionally a little rough around the edges, but overall it's really amazing!

It uses jdbc drivers for database support, so it can handle basically anything.

If you're asking for a GUI for simple CRUD on an arbitrary table, I think that's pretty common. What database are you using, and what API are you using to access it? (Edit: and on what O/S?)

If one of the obstacles to data entry is normalization, perhaps an updatable view if your database supports it.

> I think that's pretty common.

Not as common as you might think. It is a hard thing to search for, and the ones I have been able to find all suck.

I wrote a PHP application a long time that allowed you to create a GUI for an 'arbitrary' SQL database table, using an XML schema to configure the appearance, validation etc. of the fields. Life got in the way, and I stopped development before it was ready for prime time. Fifteen years later, I went looking for something with similar functionality and was surprised at how few options there still were.

A bit more than 20 years ago Microsoft released a desktop product that automated producing a CRUD interface from an XML schema. It looked very nice but it was immediately withdrawn. I was puzzled at the time but I guess now they were threatened with a patent. This might explain why there weren't more products solving this obvious problem. Patents should have expired by now though.

> ad-hoc data entry is very awkward

I understand that a spreadsheet "row" often isn't going to be a normalized SQL table row and all that, but isn't mapping the spreadsheet cell ranges into such a schema technically the only problem here?

If you had a script that watches your spreadsheet for changes and can drop you into a SQL shell whenever you want, you'd have it both ways.

I wonder if you drag Notebooks a bit back toward spreadsheets if there’s a product there.

Check out NocoDB for an open alternative to AirTable.

Microsoft Access is this. It was quite popular in its heyday, but as time wore on, it turned out that people eventually either want scalable, real, full-blown databases, or infinitely flexible spreadsheets. Anything built with a product in that grey area in between will eventually want to go one way or the other.

We've seen the same phenomenon in word processing/desktop publishing. We've mostly seen people coalesce around Microsoft Word and its online counterpart/competitors or go full-on desktop publishing with InDesign. People have mostly moved away from lightweight desktop publishing programs for newsletters and the like.

Is SQLite a "scalable, real, full-blown database"? That's the scale that Access is going for. (Though it also totally works as a pure frontend to a "real" database connection.)

SQLite can be used to help build a "scalable, real, full-blown database" application. JET, the SQLite-like engine used by Access, could theoretically be used in the same way.

But the topic here is about GUIs which serve the middle-ground between the "scalable, real, full-blown database" applications and the lowly spreadsheet. The SQLite project offers nothing that is comparable to Access.

Access had the capability to use a full-blown SQL Server back end, while retaining 90% of the flexibility on the front end. For example, adding a column in Access would add a column in the database table!

We can have our cake and eat it too.

The mistake with Access was that instead of keeping a deathgrip on its legacy file-based roots, it ought to have "grown up" and become a web-native HTML5 app that uses SQL Server back-ends as the only option.

I still think there's a huge market for something like this.

Airtable does this, no? There’s just no SQL part. But they got the UI part down.

I’m so annoyed there’s no SQL API for Airtable… even if it was read only.

There were two problems with Access - it looked too complicated and it looked ugly.

It is still available even in the 365 package but I can't remember when I used it last time. It was long time ago.

> I want one for quick random edit.

DBeaver fills that need for me.

We're building www.outerbase.com to support this use case. We're in the latest YC batch and are hoping to do a launch HN in the near future, but would love to hear your thoughts on what we've built if you are up for checking it out!

We are building a open source UI for Postgres DBs that should meet your use case: https://mathesar.org. We're doing our first release next week!

The UI looks pretty neat, but the install instructions are essentially "curl {url}/install.sh | bash -", which is kind of icky and runs a bunch of sudo commands. Since you're using Docker anyway, why not provide human-readable instructions?

Or, alternately, separate the config process from the install process. I'd be much more comfortable running config commands from a script inside the docker container, or as a first-run setup UI, or as a simple .yaml config.

It does look like something I'd like to try out later, though. Either after the install process is cleaned up a bit, or once I have time to read through the whole install.sh script.

Edit: In case it matters, my personal preference is to install things as linux packages instead of Docker, but I understand that this is a stretch for early-access software.

This is all good to know, thank you for the feedback!

Our current installation process was spun out of our local development setup, which isn't ideal.

Cleaning up installation is one of the top things on our to do list. This also includes documentation for setting up Mathesar without Docker. We'd like to do Linux packages as soon as we can too.

How large is the data you're working with? Airtable (expensive, with serious row limitations) and its open source alternatives (apparently great, but I've never used one) are basically what you're looking for. They allow you to create relationships pretty trivially. I largely use airtable for collaborative data entry work with 1-10 others, where I need to eventually throw the data into postgres. It works great. Though, it's hard to say where its limitations are until you're a bunch of hours into a project -- some features you'd expect to be easy can be extremely hard, or limited.

I also work in [this space](https://tablam.org).

The tool that was great for this is FoxPro.

Is like access, but the genius thing is that it include a super-charged "repl" aka: the command window.

It allow to combine GUI + Help + Terminal in one single tool, so you can do

    use table
    browse // show a data table
    go first // you can navigate both by mouse/keyboard and by code!

[Laughs in pandas]

I don't want to be dismissive, this is nice work and it's clean and lightweight. But it might be good to look at existing solutions in this area - pandas was developed within the financial industry to solve exactly this sort of issue. If you need more topological flexibility there is xarray, and if you need spreadsheet type immediacy it's worth looking into Mito.

Rustaceans should look into pola.rs: https://github.com/pola-rs/polars

[Sobs in hours spent in pandas documentation]

Or, just use a relational database.

Alternatively, airtable and similar are basically relational databases that have some really neat features that let you create relationships by just copying/pasting data, or importing CSVs. They're limited in a lot of ways, but it solves a certain set of problems that can't be solved with code or excel.

I went through that as well; Wes McKinney is super-smart, but not the best teacher, and unfortunately his style of example has become a norm ion Stack Overflow etc.

  import pandas as pd

  df = pd.whaaargarrrrbllllll[(['what']['the']['fuck'), is.this['shit'], I, mean, seriously]

  (outputs)

  df[.astype('int64').fillna('spork')
  df.groupby[['uppers']['downers']['all arounders']].join(inner, child, trauma, (yes && no))

  df['confused'].very(simple['example']) # the thing being explained

I'm exaggerating, but not by much. Most examples in documentation or McKinney's tutorial work is presented as a complete small program in a REPL, and while that does make it easy to follow along by imitation, learning pandas feels like a painfully fragmented process at first. Also, tehre's a widespread assumption among pandas experts that people coming to pandas are already familiar with SQL, even more than Python in fact. I'm sure this reflects the initial user base and to bfair it's probably a true assumption for a lot of folk. But if you came from a more CS or scientific context rather than a database one, it's anotehr avoidable layer of confusion.

I can't recommend a book unfortunately - I just worked with McKinney's own materials and suffered for a while until things started to click. Once I realized what I found frustrating about the tutorial materials I began to realize that I could read it more selectively - and also that the code base is in constant flux. There are often 2 or 3 different ways to do the same thing, with different approaches being deprecated or promoted over time.

The year is 2025. You wake up after what feels like a long nap and you haven't touched pandas since you wrote this comment. In front of you is a pen and piece of paper with instructions to hand-write some pandas code to transform a collection of XLSX files of census data and municipal data into a choropleth representing deltas between the datasets. The pandas documentation is printed in-full on a bookshelf to your left. You have 20 minutes.

  import pandas as pd
  import geopandas as gpd

  census_df = pd.read_excel('census_data_yuge.xlsx').fillna(0)
  muni_df = pd.read_excel('muni_data_yuge.xlsx.').fillna(0)

  # assumes same criteria & column names in both
  census_df.drop(['address 1', 'address 2', 'zip'], inplace=True)
  muni_df.drop(['address 1', 'address 2', 'zip'], inplace=True)

  cities = gpd.DataFrame(muni_df.groupby(['city']).mean() - census_df.groupby(['city']).dropna().mean())
  gpd.plot(column='num_residents', cmap='bwr')

A fun exercise. I stuck to the time but score it as a C- because I cheated with geopandas (which I've never used but seems to do a lot of this out of the box) and gave myself unrealistically clean imaginary data. I have parsed census data once and remember it being a lot of work to tidy up before I even tried to answer my question.

mfw realizing the last line should be cities.plot... ToT

I was wondering the same, why not SQL?

> This leads to a lot of logical conditions, since to prevent double-counting each aggregate tuple’s conditions must assert both that the matching tuples matched and that the non-matching tuples didn’t.

I understand and know to handle edge cases with SQL, I'd have to learn that all over again with a custom language and its own unique quirks. If I was putting such an investment, I'd want it to be better established.

Unless of course it's to scratch an itch and this is a perfect way to start out and share. Nice work and good luck.

I work in finance and I hate pandas! Also worth pointing out that almost all big banks have their own functional languages baked into excel that allow not only relational operations and extend excel functions but also tapping into their inhouse analytics libraries written in C++.

The polars syntax is hella nice. It’s also much faster to boot.

I see your pandas (even the arrow based pandas 2.0) and raise you polars. ;)

I see your:

    (
        polars_df1
        .join(polars_df2, on=['state', 'county', 'timestamp'], suffix='_r')
        .with_column(
           ( pl.col('val') + pl.col('val_r')).alias('val')
        )
        .select(['state', 'county', 'timestamp', 'val'])
    )

and raise you:

    pandas_df1 + pandas_df2

Hmmm touché

Fwiw I actually do prefer polars for standard long format relational operations. But sometimes it’s just more convenient to work with data in other ways. Another example:

Polars:

    polars_df.with_column(
        pl.when(pl.col('timestamp').is_between(
            datetime('2023-03-01'),
            datetime('2023-03-31'),
            include_bounds=True
        )).then(pl.col('val') * 1.1)
        .otherwise(pl.col('val'))
        .alias('val')
    )

Pandas:

     pandas_df.loc['2023-03'] *= 1.1

Mito co-founder here - thanks for the mention! OP you can check out Mito here [1].

Tldr: Mito is a spreadsheet that you can use to edit pandas dataframes, from directly within a Jupyter notebook. For every edit you make, Mito generates the equivalent python code, allowing you to perform some tricky pandas operations with the ease of a spreadsheet.

In practice, most of our most active users _are_ in the financial industry. They turn to Mito because they’re transitioning from spreadsheets to a programming language (for one of many reasonsg, but doing so while not knowing how to program is challenging. For most people, the hard part of writing code is the actually process of writing code - so we give these folks a spreadsheet interface they already know.

Happy to answer any questions / hear any feedback!

P.S. We’re open core + source available. Check it out if that’s your thing [2]

[1] https://trymito.io [2] https://GitHub.com/mito-ds/monorepo

I tell everyone to start with Mito now. If I had had it around when I learned pandas I think I would have got comfortable in 6 weeks rather than 6 months.

Isn't Pandas just a Python clone of R dataframes?

I believe so, yes. I just came to it through Python first, I hate R and try never to think about it.

At my previous job, one of the accountants asked me on my first week if I could solve a spreadsheet problem they had involving payroll for it's ~250 employee's. As a 20 year database developer, manager and analyst, I was really hesitant, but after she showed me the amount of time it was taking her to do this every 2 weeks, I sort of felt obliged.

All of the data was from several flat files, it consisted of each employee's hours for the period, accrued sick time, vacation time, comp time, etc. She had to manually copy and paste this data into Excel, fix formatting issues, then use it to calculate several totals. It was then fed into another system (that would only accept Excel files for input), that would print the pay checks with all this data summarized.

This was clearly critical for the organization, and it was taking her a couple days to do manually. I begged her, and my supervisor, to let me do the whole thing in SQL Server or MySQL, but she wouldn't have it, because it HAD to be something she could adjust manually. I explained I could make it look and feel like Excel, but she wouldn't have it.

I realize to anyone in databases (including me), I should have fought harder to do it right. However, as a new employee it was more important to gain trust than show off new technologies. So I compromised.

I setup a system that imported all of the data into SQL Server, cleaned it up, then exported it to Excel. The tricky part was working with VBA, and creating recursive expressions in Excel during the export. It took me about a week of time, mostly because I hadn't used VBA in a decade. The end product, from her point of view, looked exactly like her original spreadsheet. She could then still manually fix any issues that came about before importing it into the paycheck printer.

In the end, it saved her 2 days a week, or 8 days a month or 96 days a year. I became her new best friend. I was always the first to get my paycheck, and never had any issues when I needed support from finance. Probably the smartest move I made early in that job. ;)

fwiw, one can import the contents of a list of excel files into one big excel file using some relatively vanilla vba without having to involve sql

I personally usually only involve a database if there's some significant amount of data involved and/or if it's significantly more efficient than the above

I don't get the hate on excel in this thread compared to rdbms's; i.e., just use a few vlookups for some joins which are usually sufficiently performant especially if you don't have that much data in which case you shouldn't be using excel in the first place... (though, if you're "clever" and want higher performance vlookups, just use it's often maligned indexed lookup feature more carefully to allow for both exact and indexed lookups which can be like >100x faster...)

The import wasn't the hard part, as you say, pretty vanilla. It was the export back to Excel that was time consuming. It involved recursive cell references, some that referenced other worksheets and one that reference another Excel file altogether.

When I tried a direct export, the system would interpret the cell references as values instead of parts of a formula. I had to parse each formula using CHR(). It also got tricky when I wanted to format the cell, say for currency. The final VBA script was barely 100 lines...

I may have some of this wrong, as it's been almost 9 years now.

Just throwing it around, but there is Apache Calcite, a DBMS without the Database part. It's an SQL parsing, logical & physical plan building framework. It's pretty much a DBMS without any database of its own. You can program up an adapter for "whatever you want" pretty much and query "whatever you want" with SQL.

What about MS Access. I used to use it years ago. It was amazing how simple it was to create and deploy an application based on relational data.

Northwind gives me shivers

Also using Access as a front end on top of MSSQL server.

Very nice piece of software, unfortunately Microsoft didn't port it to Mac.

I am loving using coda.io for small data. It's easy to create tables that reference each other. Tables are part of a document and you can write text and make them a little bit interactive with slider controls, etc.

Excel has get and transform that makes this easy + an odbc provider (you can query excel as an sql database).

A reminder for everyone excel web is actually entirely free and you can create stuff with https://excel.new

We spent a lot of time making it faster and rewrote a bunch of it ^^

(I'm just an engineer, I don't speak for my corporate overlords)

How's Excel web's feature parity?

excel.new has a "weird" TLD, no mention of Microsoft in the domain and asks me to auth with my Microsoft account before I can even see the site. If I came across this in the wild, I'd for sure think this is some phishing or generally bad website.

Agreed. That’s bad even for Microsoft.

What is wrong with excel.office.com with a version toggle?

excel.new is a shortcut to creating a new spreadsheet, not a brand new version of Excel

excel.office.com/new then

Those are all valid points, of course, and you can navigate to excel.office.com if you don't trust the OP (although it will also immediately redirect you to an auth window)

The .new TLD is pretty nifty. sheets.new gets you the same thing as excel.new, but for Google.

At least excel.office.com is on the official office.com domain, easy to verify.

Using X.new seems like a bad idea, there is no way for me to understand if it's actually a official project by the companies themselves or just a random developer who has set that up. Maybe one day it'll ask someone for auth, they don't notice it's at microsoftweb.com instead of microsoftonline.com and get phished.

Really hard to trust random domains when a simple excel.office.com/new would do just as fine.

That link took me through 6 redirects, at least 2 rendered flashes of various colors in the browser.

I love Excel, BTW :) just wanted to let you know in case you want to make it even faster (and easier to onboard presumably based on the domain).

Hi inglor! As someone who has been writing spreadsheets engines for the last 10+ years I am a big fan of how Excel has taken the lead in innovation in the last couple of years with dynamic formulas and the LAMBDA. So congratulations on your part of it! BTW, I must have been interesting to find an algorithm that can evaluate the new dynamic formulas. I would like to see one day more public information on spreadsheet evaluation algorithms. As the people from sourcetable[1] put it:

> Spreadsheets are one of the hardest things you can build — right up there with compilers.

I think I would like to see a "democratization" of the technology and find books, and lectures about it as popular as we find books and lectures about compilers.

As for the OP I find their take interesting, of course you can accomplish that with more established tools but it was a refreshing read and an interesting, if not inspiring, notation.

PS: Is it possible that there is a regression when computing sheets with lots of open ranges (like =SUM(D:D))

[1]: https://www.sourcetable.com/jobs

“ In trying to tidy up my finances I had to rebalance my portfolio, but even though I had all of my trades loaded into a spreadsheet, I found it difficult to aggregate them by ticker / account / category and calculate the actionable bit — the stocks and quantities I needed to buy/sell to meet a desired allocation.”

Is this not what a pivot table does?

that's exactly what a pivot table does

Ah nice memories, one of my first open source projects was to take a Google Sheet and convert it into a lightweight "database", basically parse the data and parse it into a big array of JSON data that could be used in any website:

const data = await driveDb('sheet-id'); console.log(data); // [{ name: 'John', age: 31 }, { name: 'Sarah', age: 27 }, ...]

https://github.com/franciscop/drive-db/

It was born very similarly to how the article describes it. For low-amount of data, a spreadsheet is IMHO a much better low-tech collaborative tool than a database.

Grist?

Yeah I was going to say the same. For the unfamiliar it's an open-source collaborative spreadsheet program that can be self-hosted and shared within your org. The sheets themselves are actually sqlite tables, which brings some constraints you might not get with traditional spreadsheet software.

I believe it also allows you to run python code for data processing and jobs

Grist is outstanding. Deserves much wider recognition as a 'better Airtable' for many use cases: https://www.getgrist.com/

Completely agreed - I've found it vastly more powerful than other tools, and handles 100k+ rows with speed, even loaded with Python code in each cell. I've been surprised how much I can get done with it, and its ability to display multiple tables on a single page for sequential filtering is powerful.

an interesting approach, how about doing the heavy lifting in an api backend and pushing/pulling data to the sheet using a custom formula? that way you would be able to leverage the full power of a db backend

The UI is kinda nice. I'd love something like that for plain old SQLite.

Excel’s get and transform makes this dead easy I think?

Bigquery is so deeply integrated with Sheets these days that you can establish live links from specified tabs to BQ (applying a SQL schema in transit), and vice versa, such that you can build a SQL layer into your spreadsheet “backend”. Bigquery is so cheap on data of that size that you’ll never realize significant cost, and provides a great query builder GUI on top.

I’m currently working on https://www.airsequel.com, which includes a spreadsheet interface for SQLite databases. By now I use it for most stuff I would have used Excel/Apple Numbers before.

The category names are replicated for the each recurring symbol, am I right? I thought you're gonna make it by giving them category IDs as we do in relational databases.

Reply on news.ycombinator.com