Finding a simple self-hosted, relational data tool
Stephen and his team manage a lot of documentary data such as statistics, geolocated records, reference images, and maps. As the founder of OurQuietPlaces, a website and app in-development that will help busy urbanites find moments of respite and cultural enrichment, he’s tasked with designing and maintaining the company’s data infrastructure and organizing research and publication. For the app’s core data, he’d found a system he liked using technical tools, database, and applications, self-hosted on several Linux servers, that met his tech-forward needs. But the rest of the team needed an easy-to-use, flexible and collaborative data tool to handle various types of data such as inventories, worksheets, publishing plans, and more. It wouldn’t be cost or time effective for Stephen to build GUIs on the hosted database to meet those needs. Members of the team needed to be empowered to create and edit their own documents.
The spreadsheet made sense, but quickly ran into problems. It was unstructured, there was no way to relate data in different tables, and complicated sheets became a nightmare to modify and share. Then they tried Airtable for its relational-spreadsheet hybrid approach, which worked well, but was expensive. Worse yet, when Stephen tried to move data off of Airtable, the experience was painful. Data could only be exported as CSV tables, they lost their relational structure, and some data types did not convert well, leading to data loss. He spent days piecing it all back together.
Burned by a bad experience, Stephen looked for a spreadsheet-database product that would not sequester company data, was easy to use, robust, and low cost. If it could be self-hosted, all the better. He tried dozens of startup products such as Retool and nocoDB, but rejected all those tools either because they were very recently developed and often buggy, limited in features, or increased his database overhead. His exhaustive research led him to Grist, which met and exceeded his expectations.
Specifically, here’s what I love about Grist:
It is open source and self-hostable using Docker.
It is opinionated, providing a totally new way to manage data that is quite disruptive, but convincing, even for database specialists.
Thanks to Grist’s use of SQLite, data is portable and not sequestered.
It is modest. Grist keeps it simple and does what it does superlatively.
It requires only a soft learning curve with lots of potential to dig deeper with outstanding documentation.
Today, OurQuietPlaces uses grist-core, the open source version of Grist, to manage its data inventory, hardware inventory, technical infrastructure (hosted apps, storage), publication schedule and writers’ assignments. Grist has been a key tool for this remote team of four who needed a real collaboration tool that made sharing easy.
We get asked all the time how Grist compares to Airtable. Both products straddle the line between spreadsheet and relational database, and have a variety of column types such as dates, formulas, and attachments. But there are key differences between Grist and Airtable. In fact, many Grist creators were Airtable users who made the switch to Grist to better meet their unique data needs. Grist is much more customizable, putting the creator in control of laying out their data in productive ways. Grist also has more robust formula support, a different philosophy when it comes to data ownership, granular data access permissions, and, to top it all off, a lower price point.
Highly Productive Layouts
Both Grist and Airtable make it possible to relate records across different tables. In Airtable related records pop-up in a card as a quick reference to related information. In Grist, you determine how to pull up data, in part or whole, and whether it’s a card or table.
It is possible to create many different views of the same data, which lets you build different productive dashboards for your work. For example, the grant application tracker in the Grist template gallery offers several convenient views of the same underlying data.
Dashboards can also summarize key figures in summary tables, and be used as a selector for underlying records. Dynamic charts update as you select and drill into data.
The ability to custom build your own highly productive layouts makes Grist a more flexible data tool for any project or business.
Grist and Airtable both offer columns of several types, such as Numeric, Text, Date, Choice, Reference, and Attachment. This is a database approach, and is a shared differentiator when compared to traditional spreadsheets. Both offer convenient widgets, such as a calendar picker for dates, and a dropdown with auto-complete for choices. For some widgets Airtable limits options — such as limiting choices to 7 colors — except on the highest-priced plan. In Grist, all widget options are available on all plans, including the full palette of choice colors.
For technical users with some coding ability, custom widgets open up more possibilities. Custom widgets can read and/or write data in your document and express it in new formats, such as building invoices or mailing labels. It can also pull in external data, such as a map, and pin locations stored in your document. Custom widgets are similar to Airtable’s Blocks SDK which enable Airtable users who write code to create their own custom apps.
Airtable super users are probably wondering how Grist compares to Airtable’s scripting apps. Scripting apps, available on the Pro plan which costs $20/user/month, allow users to do things like create multiple templates for a record or validate emails. Some of the things in the Scripting app marketplace are already possible in Grist, such as embedding Google Maps, randomizing values, or attaching multiple files to an attachment cell in bulk, but others automations are not yet available. Automations are something we plan to implement. Keep track of our progress in Grist’s product roadmap.
Grist, like Airtable, supports Excel-like functions such as IF(), CONCATENATE(), COUNT() and so on. Visit our help center to see the full list of functions supported by Grist. Creators comfortable with spreadsheet functions will quickly adapt to Grist functions. The autocomplete feature makes writing formulas easier and faster by reading your document’s structure and listing available fields that filter as you type.
Only Grist supports the full power of Python to create more powerful formulas that deepen data analysis and computations in Grist. For example, here’s a cool code encrypter and decrypter using Grist.
Double click on the formula columns (begin with = ) to see a multi-line Python formula.
This formula uses a lot of Python — it’s a lot for a spreadsheet, it’s only a little bit for a Python developer. Python developers can also import standard Python libraries. This empowers creators to build truly useful, custom apps, and expand Grist’s power far beyond what is possible in Airtable, Excel, or Google Sheets.
In Grist, data columns may also have associated formulas, called “trigger formulas” which set a value when specific events happen, such as adding a new record or updating a particular field. These can be used to set when a record was changed, or who changed it, as well as perform data cleaning.
We believe that you own your data and we shouldn’t get in the way of that ownership. That’s why there is no data lock-in with Grist. Airtable users cannot export their databases in full and must export each individual table as a CSV, which is time consuming and cumbersome. With Grist you can download the entire relational structure as a .grist file, which is a SQLite file that can be opened in other SQLite software. If you’d like to export tables as a spreadsheet, tables can be exported individually as a CSV, exported to Google Drive, or downloaded as an Excel workbook. Your documents are also automatically backed up, and snapshots can easily be exported in full. If you make a big mistake, you can also restore your data from a snapshot right in Grist.
It is also possible to self host Grist’s open source version, grist-core. The hosted Grist product is built from grist-core. We believe Grist being open source is important for all users of Grist, because a deep general-purpose data tool has a very long tail of features vital to someone somewhere. Open-source products can aggregate those features in a way closed-source products can not, benefiting everyone. Having the source open makes it easier to build extensions to handle crucial-but-unusual needs. This makes storing data in Grist a solid and durable bet. We expect people to host their Grist documents with us not because they are locked in, but because we offer great service at a good price!
Custom Access Permissions
Businesses often need the ability to decide who can see and modify which data. Excel and Google Sheets allow protecting certain ranges from modification. Airtable allows sharing filtered views of individual tables. But only Grist allows detailed filtering based on who is looking at the document.
Grist’s granular access permissions give you more control over your data, letting you set up rules that determine who can see or edit which data, down to each table, column, and row. You can assign members of your teams specific roles, and based on those roles, permit them to only view or edit parts of your Grist document.
For example, say you’re the head of HR at a small company and manage the company’s payroll. You may share the same document with each employee, and permit employees to only see records that pertain to them.
When it comes to pricing, Grist is appreciably less expensive than Airtable, too. Grist’s Team plan costs $8/user/month. Airtable has two premium options: Plus costs $10/user/mo and Pro costs $20/user/mo.
New creators often ask how many records can they store in a document. Airtable’s free plan only goes up to 1,200 rows. The Plus plan gets you to 5,000, and the Pro plan to 50,000. Grist limits are far more generous. The rule of thumb is that data is limited to 100,000 rows, although the exact limits depend on the total amount of data and the amount of computation done in formulas.
Moving beyond storage, Grist’s free plan is quite full-featured. For example, it is possible to import additional data into existing tables, create charts, and summarize data into key figures on both the free and team plans. In Airtable, additional data imports, charts, and summary tables (called pivot tables in Airtable) are part of the Pro plan at $20/user/month. Grist’s custom widgets, which are similar to Airtable’s Blocks SDK, are also available on the free and team plans, whereas in Airtable, Blocks SDK will be available on the free and plus plans until March 2022, thereafter they will only be available in the pro plan.
The table below captures features and pricing differences between Grist v. Airtable.
🟢 Grist Team = $8/mo/user 🟡 Airtable Plus = $10/mo/user 🟠 Airtable Pro = $20/mo/user
A Grist team site is analogous to a Plus or Pro workspace in Airtable. Within a team site, Grist allows organizing documents into folders called ‘workspaces’, which is not available on Airtable.
Grist documents are equivalent to Airtable’s bases.
Grist summary tables are equivalent to Airtable’s pivot tables.