alternatives.md

Alternatives Analysis

There are a few notable other alternatives that we believe are aiming to do something similar as FreeDB. We would like to try our best to summarise how these other alternatives work and how it compares with FreeDB.

If you find there is anything inaccurate about the analysis below, please feel free to let us know and we can discuss it further.

• Stein.
• Serve.
• SheetDB.
• sheetsql.
• gooss.
• drive-db (deprecated).

Why should you choose FreeDB?

Here is a quick summary of FreeDB compared to other available alternatives.

1. We do not need any backend server at all.

   • We aim for the minimum setup to use Google Sheets as your database.

2. All of our codes and protocols are available on GitHub.

   • Some alternatives do not share their codes publicly.
   • We publish the language agnostic protocols. This allows anyone to extend it to other languages as needed.

3. Conditions for retrieval, update, and deletion are more flexible.

   • We are using Google Sheets =QUERY() function.
   • We can handle all supported conditions by =QUERY() function.

4. Computations are all done through the Google Sheets API.

   • We are not performing any filtering, ordering, limiting, or offsetting in the library.
   • We use Google Sheets formula and run it through the Google Sheets APIs.
   • This allows bigger data size computation as well.
   • Essentially, we are offloading the computations into Google's servers.

5. Our library interfaces are designed to represent Google Sheets as a normal database.

   • We support both KV and ROW (similar to SQL) database.
   • We try to mimic the interfaces of a normal KV and SQL database (through a simple ORM model).

Caching

We are aware that our current implementation does not provide a caching mechanism yet. We see this as a potential future improvement that we are going to implement in the future version.

Analysis

Stein

Stein has a backend server as a proxy.

1. It provides both hosted and self-managed backend option.
2. Hosted backend offers higher capacity (more requests per month and more rows per sheet) at cost.
3. The backend server provides a few REST API that can be called directly or using the official JavaScript library.

This project provides the following operations.

1. Retrieve rows with conditions, limit, and offset.
2. Insert new rows.
3. Update rows with conditions and limit.
4. Delete rows with conditions and limit.

A few important implementation details to take note.

1. The conditions only support equality check (using JavaScript === check).
2. For retrieval, update, and deletion, the following is how it works:
   • Retrieve all rows from Google Sheet.
   • Perform the condition, offset, and limit operation in-memory.
   • Either return the matching rows or update/delete the matching rows.
3. The server has a dependency on a MongoDB instance to store client information.

Serve

Serve has a backend server as a proxy.

1. It only provides hosted backend option (currently it seems it is provided for free as of 24 August 2022).
2. It provides integrations with many data storages, not just Google Sheets (e.g. Airtable, MySQL, PostgreSQL, etc.).
3. There is no official client library provided, all REST APIs provided must be called manually.

Although Serve provides integrations with many different data storages, we are going to just focus on the Google Sheet capabilities.

This project provides the following operations for Google Sheet.

1. Retrieve all rows.
2. Insert new rows.
3. Update rows.
4. Delete rows.

A few important implementation details to take note.

1. Retrieving rows must include all rows. It does not support filtering, offsetting, or limiting.
2. No batch row insertion support as of 24 August 2022.
3. A row can only be updated as a full row (cannot update only selected columns).
4. If we want to update or delete a row, we must provide the row index.

SheetDB

SheetDB has a backend server as a proxy.

1. It only provides hosted backend option.
2. It provides both free and paid tier.

This project provides the following operations.

1. Retrieve rows with conditions, limit, offset, and order by a specific column.
2. Retrieve keys (similar to column names).
3. Count the number of rows.
4. Insert new rows.
5. Update rows with conditions and limit.
6. Delete rows with conditions and limit.
7. Delete duplicated rows within a sheet (the row must have the same exact content).
8. Retrieve specific cell values.
9. Insert new sheets.
10. Delete sheets.

A few important implementation details to take note.

1. Batch update is only supported in the paid version.
2. The project defines their own condition format for retrieval, deletion ,and update.
3. The project supports handlebars to display data directly in HTML.
4. The project supports data caching inside their hosted server (paid version only).
   • This means when the data is cached, changes in Google Sheet (directly changed) is not going to be reflected immediately.
   • However, if the data is updated/inserted/deleted via the APIs provided by SheetDB, the cache will be invalidated.

sheetsql

sheetsql is a TypeScript library that treats Google Sheets as a database. There is no backend server as a proxy.

This project provides the following operations.

1. Retrieve rows with conditions.
2. Insert new rows.
3. Update rows with conditions.
4. Delete rows with conditions.

A few important implementation details to take note.

1. The conditions only support string equality check.
2. For retrieval, update, and delete, the following is how it works:
   • Retrieve all rows from Google Sheet (data is cached in-memory).
   • Perform the condition operation in-memory.
   • Either return the matching rows or update/delete the matching rows.
3. The update and delete operations are done by calling the relevant Google Sheets API one-by-one for each row.
   • Google Sheets API actually supports batch update and delete.
4. The project supports data caching via in-memory caching.
   • This means when the data is cached, changes in Google Sheet (directly changed) is not going to be reflected immediately.
   • For write based operations (insert, update, and delete), both local and remote data will be updated.

gooss

gooss is a JavaScript library that reads data from Google Sheets.

The project only supports reading all rows and applying a client provided callback for each row.

A few important implementation details to take note.

1. It only supports full data retrieval.
2. It does not support condition matching.
3. It supports HTML templating using Underscore.js.

drive-db

drive-db is a JavaScript library that reads data from Google Sheets.

The project only supports reading all rows.

A few important implementation details to take note.

1. It only supports full data retrieval.
2. It does not support condition matching.
3. The project supports data caching via in-memory caching.
   • This means when the data is cached, changes in Google Sheet (directly changed) is not going to be reflected immediately.

Note that this project is already deprecated as it depends on Google Sheets v3 API.