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.
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
Erp. Sheetsu is dead. Richard O’Dwyer wrote to tell me he has an alternative service: sheet2api. I’ll replace the screenshot and links below with that.
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.
No server or DB maintenance. Easy IAM and encryption. Replication of SaaS data in Google Sheets makes perfect sense. Flatly.io makes Google Sheets and Excel Online easy to use as data repos, without coding.
(Disclosure: I’m the founder.)
Actually, there’s no difference at all between using a spreadsheet as database and using a database as database (as long as you are only using a single table). Guess, how the spreadsheet is storing the data? Right, in a database. So you’re using a database with an API and a frontend – which is equally true for conventional databases.
However, databases do a lot of work for you beyond storing and retrieving data. For example they ensure, that a write operation is never done only partially (due to crashes for example), they have a pretty sophisticated system under the hood to ensure smart caching of frequently searched tables and efficient searching of the database and much more.
I really see no benefit in using a spreadsheet over an actual database.
If you’re using spreadsheets, your still using databases – just bad ones (for the purpose).
As a second note: If you use a 3rd party spreadsheet service, better make damn sure that your contact with its provider sufficiently ensures the privacy of your users (Google Sheets is pretty certainly a no-go).
That will probably mean that most free spread sheet services are off the table, at least if you want to offer your service in the EU (having to comply to the GDPR as a result) or care about your users.
There’s also Coda (https://coda.io/). It seems really promising. I already use it for my personal organization and little projects.
Hi Chris – I’m a MSSQL developer by trade – but surprisingly, I don’t hate this idea :) I could see it being especially useful for data in a web app that is not updated via the interface: reference tables, etc. This would make it easier for a non-tech to update this data when necessary, vice needing someone that knows SQL just to make basic updates. Good article.
I’m approaching this issue from the opposite end in some ways. I have reasonably static CSVs of varying sizes (200-6000 entries) and wondering at what point I turn them into a DB, the online spreadsheet approach seems like a good one to consider.
Great overview of what’s available. HubSpot cms has something like this as well https://knowledge.hubspot.com/articles/kcs_article/cos-general/how-to-edit-hubdb-tables
One downside of hubdb is you can’t populate rows with a form like you can in airtable.
Awesome article. Very interesting to see how people are using alternative methods of data storage. At the University of York, we were using Google’s Fusion Tables (yes, even though they were a beta product!) as a quick alternative to host simple, tabular data.
SELECT id,ddc as DDC , ddcgroup as Beschreibung FROM $gf->table order by ddc;
The resulting set of rows and a bunch of meta data is then passed to the client to be presented in my spread sheet liek table , where I can manipulate the data in real time.
This is very convenient.
Yes! I’ve been working a while with something that you may like. It’s a self hosted spreadsheet CMS/database editor for MySql databases. Kind of a mix between MySql and Airtable, but with the goal to keep it simple.
I just made it public https://github.com/jenstornell/squares See the screenshot and you will get the idea. It’s still in a minefield/beta state, but it has most of the basics there already.
Link is dead :(
Databox is on fire these days, and guess what their “most requested” data source integration is? :)
To be more pedantic, you might reference “employees” table yet again, instead of having manager name in “departments” table :)
I built an HTML/PHP web site for my company’s Trade Show and I’m using JSON to pull data out of a published Google Sheet and then styling with CSS…. it works pretty well for what I need to use it for. I think I found the initial idea somewhere on stackexchange.com
Using spreadsheets as a replacement for a database seems like a terrible idea. It’s rare that you’ll only ever need a single table within any app/website if you’re using a DB, but using spreadsheet tables ensures that you’re guaranteed poor performance on any table joins as there’s no concept of table indexes to join by. Likewise, there are no such indexes for searching, so that’s yet another performance hit.
Then you have further performance issues because you’re severely limited on any kind of column type hinting. In-fact, it’s completely possible for spreadsheet columns to contain a mix of strings, numbers, dates, formulae, that you cannot realistically set a columns data type.
Functionally you lose out on transactions, which can ensure that any modification queries complete as a set or not at all, instead of partial modifications which can result in a broken data set. Lastly you lose out on some very powerful functionality built into the majority of databases; no stored procedures to help protect against malicious queries, so all that logic needs to be replicated in your application.
There might some use for spreadsheets as databases, but I cannot seem to think of a single one.
Newer player: https://stackby.com/