open-sheets-orm

A type-safe ORM for Google Sheets with a Prisma-inspired developer experience.

A quick gotcha for the purists: this is technically clickbait. There's no relational database here, so "ORM" is a stretch. An "Object Sheet Mapper" (OSM) would be more accurate — but let's be honest, nobody would know what that means. We went with ORM because it instantly communicates the dev experience: define a schema, run queries, get typed results. Same vibes, different grid.

Features

• Schema-first approach — Define your data models with a fluent, type-safe API
• Automatic type coercion — String values from sheets are automatically parsed to numbers, booleans, dates, and JSON
• Query engine — findMany, findUnique, create, update, and delete operations
• Uniqueness constraints — Primary key and unique field enforcement
• Validation — Input validation with clear error messages
• Pluggable adapters — Abstract adapter layer for future spreadsheet providers
• CLI code generation — Generate a fully typed client from your schema with one command

Installation

npm install open-sheets-orm

pnpm add open-sheets-orm

yarn add open-sheets-orm

Google Sheets Setup

1. Create a Google Cloud Project

1. Go to the Google Cloud Console.
2. Create a new project (or select an existing one).
3. Enable the Google Sheets API under APIs & Services > Library.

2. Create a Service Account

1. Go to APIs & Services > Credentials.
2. Click Create Credentials > Service Account.
3. Fill in a name and click Done.
4. Under the new service account, go to the Keys tab.
5. Click Add Key > Create new key > JSON.
6. Download and save the JSON file securely.

3. Share Your Spreadsheet

Open the Google Spreadsheet you want to use and share it with the client email from the service account JSON (found under client_email). Give it Editor access.

Quick Start

import { schema, field, Database, GoogleSheetsAdapter } from 'open-sheets-orm';
import credentials from 'path/to/creds.json';

// 1. Define your schema
const mySchema = schema({
  User: {
    id: field.string().primaryKey(),
    email: field.string().unique(),
    name: field.string().optional(),
    age: field.number().optional().default(0),
  },
});

// 2. Create the adapter and connect
const adapter = new GoogleSheetsAdapter(
  credentials, // Service account JSON (Auth.JWTInput)
  'spreadsheet-id', // The ID from your spreadsheet URL
);
await adapter.connect();

// 3. Create the query engine
const db = new Database(mySchema, adapter);

// 4. Start querying
await db.create('User', { email: '[email protected]', name: 'Alice' });
const users = await db.findMany('User');
const alice = await db.findUnique('User', { email: '[email protected]' });

AutoGen Client

The CLI generates a fully typed client from your schema — no manual wiring needed.

1. Create a schema file

// schema.ts
import { schema, field } from 'open-sheets-orm';

const mySchema = schema({
  User: {
    id: field.string().primaryKey(),
    email: field.string().unique(),
    name: field.string().optional(),
    age: field.number().optional().default(0),
  },
  Post: {
    id: field.string().primaryKey(),
    title: field.string(),
    body: field.string().optional(),
    published: field.boolean().default(false),
  },
});

export default mySchema;

2. Run the generator

open-sheets-orm generate --schema ./schema.ts --output ./generated/client.ts

3. Use the generated client

import { SheetORMClient } from './generated/client';
import credentials from 'path/to/creds.json';

const client = new SheetORMClient({
  credentials,
  sheetId: 'your-spreadsheet-id',
  provider: 'google',
});

await client.connect();

// CRUD via typed delegates
await client.user.create({
  data: { email: '[email protected]', name: 'Alice' },
});

const users = await client.user.findMany({
  where: { name: 'Alice' },
  limit: 10,
});

const id = users[0].id;

const user = await client.user.findUnique({ id });

await client.user.update({
  where: { id },
  data: { name: 'Updated Name' },
});

await client.user.delete({ id });

// Same pattern for other models
await client.post.create({
  data: { title: 'Hello World', body: 'My first post' },
});

const posts = await client.post.findMany({
  where: { published: true },
});

What gets generated

| Output | Description | | ---------------- | ------------------------------------------------------------------------------------- | | Interfaces | One export interface per model with correct types | | Delegates | One class per model with typed findMany, findUnique, create, update, delete | | SheetORMClient | Wrapper class with dot-notation access (client.user, client.post) |

CLI Options

| Flag | Default | Description | | ---------- | ----------------------- | ------------------------------------ | | --schema | ./schema.ts | Path to your schema file | | --output | ./generated/client.ts | Output path for the generated client |

For more details, see the AutoGen Client guide.

Schema Reference

Field Types

| Factory | TypeScript Type | Cell Parsing | | ----------------- | --------------- | ----------------------------------------------- | | field.string() | string | Raw string value | | field.number() | number | Number(value) | | field.boolean() | boolean | true if value is "true", "TRUE", or "1" | | field.date() | Date | new Date(value) | | field.json() | unknown | JSON.parse(value) |

Field Modifiers

| Modifier | Effect | | ----------------- | ------------------------------------------------------------------------------------------------------- | | .primaryKey() | Marks as the primary key. Auto-generates a UUID on create if omitted from input. Enforces uniqueness. | | .unique() | Enforces uniqueness across all rows on create and update. | | .optional() | Field is not required on create. | | .default(value) | Used when the field is omitted from input data. |

Query Options

const results = await db.findMany('User', {
  where: { active: true }, // Filter conditions
  skip: 10, // Offset
  limit: 5, // Max records to return
});

Documentation

Full documentation is available at https://luisdlpr.github.io/open-sheets-orm.

Development

git clone https://github.com/luisdlpr/open-sheets-orm.git
cd open-sheets-orm
pnpm install

| Command | Description | | --------------- | ----------------- | | pnpm dev | Watch mode | | pnpm build | Build to dist/ | | pnpm test | Run tests | | pnpm lint | Lint with ESLint | | pnpm docs:dev | Local docs server |

Contributing

Contributions are welcome! Please open an issue first to discuss what you would like to change.

License

MIT