SheetDB

A TypeScript library that makes Google Sheets feel like a lightweight database.

SheetDB provides a typed table API over spreadsheet tabs, with runtime validation and an adapter boundary. It currently includes:

• SheetDB and Table classes
• typed schemas for rows
• Zod-based runtime validation
• an in-memory adapter for local demos and tests
• a Google Sheets adapter using service account auth
• Vitest tests, including skipped Google integration tests

Install

pnpm install

Local Demo

Run a short demo without Google credentials:

pnpm demo

This uses InMemorySheetAdapter and demonstrates insert, find, update, delete, and validation errors.

Real Google Sheets Example

Create a .env file:

GOOGLE_SHEETS_SPREADSHEET_ID="your-spreadsheet-id"
GOOGLE_SERVICE_ACCOUNT_JSON_BASE64="base64-encoded-service-account-json"

Load the .env file and run the example:

set -a
source .env
set +a

pnpm tsup example/waitlist.ts --format esm --out-dir .demo --silent
node .demo/waitlist.js

The Google Sheet must be shared with the service account client_email as an editor. See docs/auth.md for setup details.

GOOGLE_SERVICE_ACCOUNT_JSON is also supported as a fallback, but GOOGLE_SERVICE_ACCOUNT_JSON_BASE64 is preferred because it avoids multiline private key and shell escaping issues.

Usage

import { SheetDB } from "sheets-orm";

const db = new SheetDB({
  spreadsheetId: "your-spreadsheet-id",
  credentials: {
    client_email: "service-account@example.iam.gserviceaccount.com",
    private_key: "-----BEGIN PRIVATE KEY-----\n...\n-----END PRIVATE KEY-----\n",
  },
});

const waitlist = db.table("Waitlist", {
  id: "string",
  email: "string",
  name: "string",
  createdAt: "date",
  status: "string",
});

await waitlist.insert({
  id: "row_1",
  email: "ada@example.com",
  name: "Ada Lovelace",
  createdAt: new Date(),
  status: "pending",
});

const pendingRows = await waitlist.findMany({ status: "pending" });

await waitlist.updateById("row_1", { status: "approved" });
await waitlist.deleteById("row_1");

Rows, filters, and updates are validated against the table schema. Invalid data throws SheetDBValidationError with the table name and field-level details.

In-Memory Adapter

Use the in-memory adapter for tests or local flows:

import { InMemorySheetAdapter, SheetDB } from "sheets-orm";

const db = new SheetDB({
  spreadsheetId: "test",
  adapter: new InMemorySheetAdapter(),
});

Integration Tests

Google integration tests are skipped unless these environment variables are present:

GOOGLE_SHEETS_SPREADSHEET_ID="..."
GOOGLE_SERVICE_ACCOUNT_JSON_BASE64="..."

Then run:

pnpm test

Scripts

pnpm demo
pnpm test
pnpm test:watch
pnpm build

Project Structure

src/
  SheetDB.ts
  Table.ts
  adapters/
    GoogleSheetsAdapter.ts
    InMemorySheetAdapter.ts
  types.ts
  validation.ts
example/
  demo.ts
  waitlist.ts
docs/
  auth.md
tests/
  basic.test.ts
  google.integration.test.ts