JSFreeDB

Ship Faster with Google Sheets as a Database!

JSFreeDB is a JavaScript library that provides common and simple database abstractions on top of Google Sheets.

Features

1. Provide a straightforward key-value and row based database interfaces on top of Google Sheets.
2. Serve your data without any server setup (by leveraging Google Sheets infrastructure).
3. Support flexible enough query language to perform various data queries.
4. Manually manipulate data via the familiar Google Sheets UI (no admin page required).

For more details, please read our analysis on other alternatives and how it compares with FreeDB.

Table of Contents

• Protocols
• Getting Started
  • Installation
  • Pre-requisites
• Row Store
  • Querying Rows
  • Counting Rows
  • Inserting Rows
  • Updating Rows
  • Deleting Rows
  • Struct Field to Column Mapping
• KV Store
  • Get Value
  • Set Key
  • Delete Key
  • Supported Modes
• KV Store V2
  • Get Value
  • Set Key
  • Delete Key
  • Supported Modes

Protocols

Clients are strongly encouraged to read through the protocols document to see how things work under the hood and the limitations.

Getting Started

Installation

npm install @freeleh/jsfreedb

Pre-requisites

1. Obtain a Google OAuth2 or Service Account credentials.
2. Prepare a Google Sheets spreadsheet where the data will be stored.

Row Store

Let's assume each row in the table is represented by the Person interface.

Note that you can also represent the row using a normal JavaScript object. The object must follow the columns registered in the store.

interface Person {
    name: string;
    age: number;
}

import {
    Oauth2GoogleAuthClient,
    ServiceAccountGoogleAuthClient,
    GoogleSheetRowStore,
    GoogleSheetRowStoreConfig,
    GOOGLE_SHEETS_READ_WRITE,
} from '@freeleh/jsfreedb';

// If using Google Service Account.
const auth = ServiceAccountGoogleAuthClient.fromServiceAccountFile(
    "<path_to_service_account_json>",
    GOOGLE_SHEETS_READ_WRITE,
);

// If using Google OAuth2 Flow.
const auth = OAuth2GoogleAuthClient.fromFile(
    "<path_to_client_secret_json>",
    "<path_to_cached_credentials_json>",
    GOOGLE_SHEETS_READ_WRITE,
);

// Create a new row store.
const store = await GoogleSheetRowStore.create(
    auth,
    "<spreadsheet_id>",
    "<sheet_name>",
    new GoogleSheetRowStoreConfig(["name", "age"]),
);

Querying Rows

The returned value is an array of JavaScript objects (or TypeScript Record<string, any>) that represents the rows in the table.

// Select all columns for all rows
output = await store.select().exec();

// Select a few columns for all rows (non-selected fields will have default value)
output = await store.select("name").exec();

// Select rows with conditions
output = await store.select()
    .where("name = ? OR age >= ?", "freedb", 10)
    .exec();

// Select rows with sorting/order by
const ordering = [
    { column: "name", orderBy: "ASC" },
    { column: "age", orderBy: "DESC" }
];
output = await store.select()
    .orderBy(ordering)
    .exec();

// Select rows with offset and limit
output = await store.select()
    .offset(10)
    .limit(20)
    .exec();

Counting Rows

// Count all rows
const count = await store.count().exec();

// Count rows with conditions
const count = await store.count()
    .where("name = ? OR age >= ?", "freedb", 10)
    .exec();

Inserting Rows

await store.insert(
    { name: "freedb", age: 10 },
    { name: "another_row", age: 20 }
).exec();

Updating Rows

const colToUpdate: Record<string, any> = {
    name: "new_name",
    age: 12,
};

// Update all rows
await store.update(colToUpdate).exec();

// Update rows with conditions
await store.update(colToUpdate)
    .where("name = ? OR age >= ?", "freedb", 10)
    .exec();

Deleting Rows

// Delete all rows
await store.delete().exec();

// Delete rows with conditions
await store.delete()
    .where("name = ? OR age >= ?", "freedb", 10)
    .exec();

Struct Field to Column Mapping

In TypeScript, you can define interfaces or classes to represent your data structure. The column names in the Google Sheet should match the property names in your interface or class.

// This will map to the exact column name of "name" and "age".
interface Person {
    name: string;
    age: number;
}

// You can also use classes if you prefer
class PersonClass {
    constructor(
        public name: string,
        public age: number
    ) {}
}

KV Store

Please use KV Store V2 as much as possible, especially if you are creating a new storage.

import {
    OAuth2GoogleAuthClient,
    ServiceAccountGoogleAuthClient,
    GOOGLE_SHEETS_READ_WRITE,
    GoogleSheetKVStore,
    KVMode,
} from '@freeleh/jsfreedb';

// If using Google Service Account.
const auth = ServiceAccountGoogleAuthClient.fromServiceAccountFile(
    "<path_to_service_account_json>",
    GOOGLE_SHEETS_READ_WRITE
);

// If using Google OAuth2 Flow.
const auth = OAuth2GoogleAuthClient.fromFile(
    "<path_to_client_secret_json>",
    "<path_to_cached_credentials_json>",
    GOOGLE_SHEETS_READ_WRITE
);

const kv = await GoogleSheetKVStore.create(
    auth,
    "<spreadsheet_id>",
    "<sheet_name>",
    { mode: KVMode.AppendOnly }
);

Get Value

If the key is not found, a KeyNotFoundError will be thrown.

try {
    const value = await kv.get("k1");
    console.log(value);
} catch (error) {
    if (error instanceof KeyNotFoundError) {
        console.log("Key not found");
    } else {
        throw error;
    }
}

Set Key

await kv.set("k1", "some_value");

Delete Key

await kv.delete("k1");

Supported Modes

For more details on how the two modes are different, please read the protocol document.

There are 2 different modes supported:

1. Default mode.
2. Append only mode.

// Default mode
const kv = await GoogleSheetKVStore.create(
    auth,
    "<spreadsheet_id>",
    "<sheet_name>",
    { mode: KVMode.Default }
);

// Append only mode
const kv = await GoogleSheetKVStore.create(
    auth,
    "<spreadsheet_id>",
    "<sheet_name>",
    { mode: KVMode.AppendOnly }
);

License

This project is MIT licensed.