The Whole Spreadsheets as Databases Thing is Pretty Cool

Celý článek

A spreadsheet has always been a strong (if fairly literal) analogy for a database. A database has tables, which is like a single spreadsheet. Imagine a spreadsheet for tracking RSVPs for a wedding. Across the top, column titles like First Name, Last Name, Address, and Attending?. Those titles are also columns in a database table. Then each person in that spreadsheet is literally a row, and that's also a row in a database table (or an entry, item, or even tuple if you're really a nerd).

It's been getting more and more common that this doesn't have to be an analogy. We can quite literally use a spreadsheet UI to be our actual database. That's meaningful in that it's not just viewing database data as a spreadsheet, but making spreadsheet-like features first-class citizens of the app right alongside database-like features.

With a spreadsheet, the point might be viewing the thing as a whole and understanding things that way. Browsing, sorting, entering and editing data directly in the UI, and making visual output that is useful.

With a database, you don't really look right at it — you query it and use the results. Entering and editing data is done through code and APIs.

That's not to say you can't look directly at a database. Database tools like Sequel Pro (and many others!) offer an interface for looking at tables in a spreadsheet-like format:

What's nice is that the idea of spreadsheets and databases can co-exist, offering the best of both worlds at once. At least, on a certain scale.

We've talked about Airtable before here on CSS-Tricks and it's a shining example of this.

Airtable calls them bases, and while you can view the data inside them in all sorts of useful ways (a calendar! a gallery! a kanban!), perhaps the primary view is that of a spreadsheet:

If all you ever do with Airtable is use it as a spreadsheet, it's still very nice. The UI is super well done. Things like filtering and sorting feel like true first-class citizens in a way that it's almost weird that other spreadsheet technology doesn't. Even the types of fields feel practical and modern.

Plus with all the different views in a base, and even cooler, all the "blocks" they offer to make the views more dashboard-like, it's a powerful tool.

But the point I'm trying to make here is that you can use your Airtable base like a database as well, since you automatically have read/write API access to your base.

I talked about this more in my article How To Use Airtable as a Front End Developer. This API access is awesome from a read data perspective, to do things like use it as a data source for a blog. Robin yanked in data to build his own React-powered interface. I dig that there is a GraphQL interface, if it is third-party.

The write access is arguably even more useful. We use it at CodePen to do CRM-ish stuff by sending data into an Airtable base with all the information we need, then use Airtable directly to visualize things and do the things we want.

Airtable alternatives?

There used to be Fieldbook, but that shut down.

RowShare looks weirdly similar (although a bit lighter on features) but it doesn't look like it has an API, so it doesn't quite fit the bill for that database/spreadsheet gap spanning.

Zoho Creator does have an API and interesting visualization stuff built in, which actually looks pretty darn cool. It looks like some of their marketing is based around the idea that if you need to build a CRUD app, you can do that with this with zero coding — and I think they are right that it's a compelling sell.

Actiondesk looks interesting in that it's in the category of a modern take on the power of spreadsheets.

While it's connected to a database in that it looks like it can yank in data from something like MySQL or PostgreSQL, it doesn't look like it has database-like read/write APIs.

Can we just use Google Sheets?

The biggest spreadsheet tool in the sky is, of course, the Google one, as it's pretty good, free, and familiar. It's more like a port of Excel to the browser, so I might argue it's more tied to the legacy of number-nerds than it is any sort of fresh take on a spreadsheet or data storage tool.

Google Sheets has an API. They take it fairly seriously as it's in v4 and has a bunch of docs and guides. Check out a practical little tutorial about writing to it from Slack. The problem, as I understand it, is that the API is weird and complicated and hard, like Sheets itself. Call me a wimp, but this quick start is a little eye-glazing.

What looks like the most compelling route here, assuming you want to keep all your data in Google Sheets and use it like a database, is Sheetsu. It deals with the connection/auth to the sheet on its end, then gives you API endpoints to the data that are clean and palatable.

Plus there are some interesting features, like giving you a form UI for possibly easier (or more public) data entry than dealing with the spreadsheet itself.

There is also Sheetrock.js, an open source library helping out with that API access to a sheet, but it hasn't been touched in a few years so I'm unsure the status there.

I ain't trying to tell you this idea entirely replaces traditional databases.

For one thing, the relational part of databases, like MySQL, is a super important aspect that I don't think spreadsheets always handle particularly well.

Say you have an employee table in your database, and for each row in that table, it lists the department they work for.

ID  Name                  Department
--  --                    --
1   Chris Coyier          Front-End Developer
2   Barney Butterscotch   Human Resources   

In a spreadsheet, perhaps those department names are just strings. But in a database, at a certain scale, that's probably not smart. Instead, you'd have another table of departments, and relate the two tables with a foreign key. That's exactly what is described in this classic explainer doc:

To find the name of a particular employee's department, there is no need to put the name of the employee's department into the employee table. Instead, the employee table contains a column holding the department ID of the employee's department. This is called a foreign key to the department table. A foreign key references a particular row in the table containing the corresponding primary key.

ID  Name                  Department
--  --                    --
1   Chris Coyier          1
2   Barney Butterscotch   2 

ID  Department            Manager    
--  --                    --
1   Front-End Developers  Akanya Borbio
2   Human Resources       Susan Snowrinkle

To be fair, spreadsheets can have relational features too (Airtable does), but perhaps it isn't a fundamental first-class citizen like some databases treat it.

Perhaps more importantly, databases, largely being open source technology, are supported by a huge ecosystem of technology. You can host your PostgreSQL or MySQL database (or whatever all the big database players are) on all sorts of different hosting platforms and hardware. There are all sorts of tools for monitoring it, securing it, optimizing it, and backing it up. Plus, if you're anywhere near breaking into the tens of thousands of rows point of scale, I'd think a spreadsheet has been outscaled.

Choosing a proprietary host of data is largely for convenience and fancy UX at a somewhat small scale. I kinda love it though.

The post The Whole Spreadsheets as Databases Thing is Pretty Cool appeared first on CSS-Tricks.