gsheet-orm

Node.js/TypeScript ORM with a Prisma-style API (findMany, create, update, delete, where, include) that uses a Google Sheet as the database, authenticating via OAuth 2.0. No database server to set up — the spreadsheet is the database.

Includes a migrations flow with versioned history (migrate dev/deploy/prune, equivalent to Prisma's), an entity-relationship visualizer (erd), and an exit ramp for when the project outgrows what a spreadsheet can handle (export prisma — generates schema.prisma + data + a ready-to-run seed for a real database).

Full internals in Architecture below.

Getting started

Flow equivalent to Prisma's: setup-gcp/create provision the infrastructure (GCP project + spreadsheet — this doesn't exist in Prisma, which assumes the database already exists); gsheet/schema.ts + migrate dev/migrate deploy are the direct equivalent of schema.prisma + prisma migrate dev/deploy.

1. npm install @leotolotti/gsheet-orm
2. npx gsheet-orm setup-gcp — creates the Google Cloud project (see Google Cloud setup below).
3. Paste the Client ID/Client secret generated in the previous step into your .env:

   GOOGLE_CLIENT_ID=...
   GOOGLE_CLIENT_SECRET=...

4. npx gsheet-orm auth — authorizes your Google account and automatically saves GOOGLE_CLIENT_ID/GOOGLE_CLIENT_SECRET/ GOOGLE_REFRESH_TOKEN/GOOGLE_ACCOUNT_EMAIL to .env.
5. npx gsheet-orm create — creates the folder in Drive and the spreadsheet ("the database"), and saves GOOGLE_SHEET_ID to .env. Use --name "My Database" to pick a name; without it, defaults to GSHEET_DB.
6. Create gsheet/schema.ts exporting your models — see Migrations (schema).
7. npx gsheet-orm migrate dev — creates the tabs/headers in the spreadsheet from the schema.
8. Write your code with SheetClient/Schema/Model — see Usage.
9. Optional: npx gsheet-orm erd (visualize the database) and, when it's time to grow, npx gsheet-orm export prisma (see Export to Prisma).

Google Cloud setup (once)

Shortcut

npx gsheet-orm setup-gcp

Installs the gcloud CLI if missing (macOS via Homebrew; Linux via the official installer), runs gcloud auth login if no account is authenticated, creates the project and enables the Sheets API + Drive API automatically, and finishes by opening the browser on the right pages of the right project for the 3 steps that remain manual (OAuth consent screen, test user registration, and OAuth Client ID creation).

Manual

1. Create a project at https://console.cloud.google.com.
2. Enable the Google Sheets API (APIs & Services → Enable APIs).
3. Configure the OAuth consent screen at APIs & Services → Google Auth Platform → Branding (User type: External).
4. Under Google Auth Platform → Audience → Test users, add the Google account(s) that will authorize access. Without this, gsheet-orm auth returns Error 403: access_denied — every unverified External app stays in "Testing" status, and only accounts on that list can authorize (up to 100 accounts; "Internal" would skip this requirement, but it only exists for Google Workspace accounts with an organization).
5. Under Google Auth Platform → Clients → Create Client, choose the Desktop app type. Note down the Client ID and Client secret.
6. Share the spreadsheet (or leave it in the account) with the Google user who will authorize access — OAuth uses that user's identity, not a Service Account.

Generate the refresh token (once, local)

With GOOGLE_CLIENT_ID/GOOGLE_CLIENT_SECRET already in .env:

npx gsheet-orm auth

This opens the browser, asks for consent, and automatically saves to the .env of the current directory:

GOOGLE_CLIENT_ID=...
GOOGLE_CLIENT_SECRET=...
GOOGLE_REFRESH_TOKEN=...
GOOGLE_ACCOUNT_EMAIL=...

The refresh_token doesn't expire (unless revoked at https://myaccount.google.com/permissions) and is what the application uses at runtime — the interactive flow doesn't run again.

Note: for apps that haven't gone through Google's verification (i.e. still in "Testing" status — the default and expected setup for most gsheet-orm projects), Google automatically expires the refresh token after 7 days, regardless of usage. If you start getting invalid_grant: Token has been expired or revoked, just run npx gsheet-orm auth again.

If you get Error 403: access_denied, your account isn't on the consent screen's test users list — see step 4 of Google Cloud setup.

Create the spreadsheet (once, local)

With GOOGLE_REFRESH_TOKEN already in .env (previous step):

npx gsheet-orm create --name "My Database"

Without --name, defaults to GSHEET_DB. Creates a folder in Google Drive and a spreadsheet inside it (or reuses them if they already exist under that name — idempotent), and saves GOOGLE_SHEET_ID to .env. Requires the drive.file scope (access only to files created by the app itself), already included in auth. This only creates the empty "database" — the tabs/tables come in the next step (migrate).

Migrations (schema)

Equivalent to schema.prisma + prisma migrate dev/deploy, adapted for a spreadsheet: there's no real ALTER/DROP, so migrations only create tabs and columns — never remove or rename them (losing data in a spreadsheet with no transactions would be worse than the problem it solves).

gsheet/schema.ts

import { Schema, DataTypes } from '@leotolotti/gsheet-orm';

export const models = {
  Users: new Schema({
    id: DataTypes.UUID,
    name: DataTypes.String,
    email: { ...DataTypes.String, unique: true },
    createdAt: DataTypes.Date.createdAt(),
  }),
  Posts: new Schema({
    id: DataTypes.UUID,
    title: DataTypes.String,
    userId: DataTypes.String,
  }),
};

Each entry's key is the sheetName (tab name); the value is the same new Schema({...}) used in Usage — it's not a new language, just a convention for declaring everything in one place, like schema.prisma.

npx gsheet-orm migrate dev [--name slug]

Compares gsheet/schema.ts against the local history in gsheet/migrations/ (not against the live spreadsheet — same logic as Prisma's "shadow database"), generates gsheet/migrations/<timestamp>_<name>/migration.json with what changed, applies it to the spreadsheet (creates missing tabs/columns), and records it in a _gsheet_migrations tab on the spreadsheet itself (equivalent to the _prisma_migrations table). If nothing changed, it does nothing ("Nothing to migrate"). If a field was removed from the schema, it warns in the terminal but doesn't drop the column — manual cleanup is your call.

npx gsheet-orm migrate deploy

Applies migrations that already exist in gsheet/migrations/ but haven't been recorded as applied on this spreadsheet yet — never generates a new migration (equivalent to prisma migrate deploy; use in CI/prod, with gsheet/migrations/ checked into git).

npx gsheet-orm migrate prune [--yes]

Removes from the spreadsheet whatever isn't in gsheet/schema.ts: tabs outside the schema, columns outside the schema, empty columns to the right of the header (Sheets' default grid reserves columns with no content), and fully blank rows. Destructive — shows the plan and asks for confirmation before deleting anything (--yes skips the confirmation, for CI). Never touches a row with real data, even soft-deleted ones.

Relations (relations in gsheet/schema.ts)

Optional — only needed if you're going to use erd or export prisma, which need to know which tables relate to each other to draw the diagram / generate schema.prisma. It's declarative metadata (target by name, not by reference), separate from the relations config you pass to client.model() at runtime (that one uses target: () => Model, lazy — see Usage):

import { SchemaRelations } from '@leotolotti/gsheet-orm';

export const relations: SchemaRelations = {
  Users: {
    posts: { type: 'hasMany', target: 'Posts', foreignKey: 'userId' },
  },
  Posts: {
    user: { type: 'belongsTo', target: 'Users', foreignKey: 'userId' },
  },
};

Visualize the database (ERD)

npx gsheet-orm erd

Generates gsheet/erd.html — an entity-relationship diagram (tables, fields, types, and lines connecting the relations) from gsheet/schema.ts, in the style of DBeaver's ER view. Opens on its own in the browser; it's a static HTML file, no network dependency.

Export to Prisma

The idea behind gsheet-orm is to serve as a fast database for an MVP — no infra setup, no annoying schema migrations — and later, when the product grows, migrate to a real ORM/database. This command is the exit ramp:

npx gsheet-orm export prisma [--provider postgresql|mysql|sqlite]

Reads gsheet/schema.ts (models + relations) and the live spreadsheet, and generates:

• prisma/schema.prisma — models, types, @unique, @id @default(uuid()), @default(now())/@updatedAt, and relations (@relation). Fields with required: true in your schema become NOT NULL; the rest (except the id) stay optional, because gsheet-orm doesn't validate requiredness at runtime — a seed failing on NOT NULL without that explicit marker wouldn't help anyone in this migration.
• prisma.config.ts — required since Prisma 7 (the connection URL no longer goes in schema.prisma's datasource).
• prisma/seed-data.json — a dump of all rows from all tables (including soft-deleted ones).
• prisma/seed.ts — a ready-to-run script (npx tsx prisma/seed.ts) that inserts that dump via @prisma/client, in the right order (whatever is referenced by belongsTo goes in before whatever references it).

Afterward: npm install -D prisma && npm install @prisma/client, set DATABASE_URL in .env, npx prisma migrate dev --name init, and run the seed. Review the generated schema.prisma first — it's a starting point, not the final schema (generic types, everything optional by default).

Usage

import { SheetClient, Schema, DataTypes, Infer } from '@leotolotti/gsheet-orm';

const UserSchema = new Schema({
  id: DataTypes.UUID,
  name: DataTypes.String,
  email: { ...DataTypes.String, unique: true },
  age: DataTypes.Number,
  isActive: { ...DataTypes.Boolean, default: true },
  createdAt: DataTypes.Date.createdAt(),
  updatedAt: DataTypes.Date.updatedAt(),
  deletedAt: DataTypes.Date.deletedAt(), // automatic soft delete
});

type User = Infer<typeof UserSchema>;

const client = new SheetClient({
  spreadsheetId: process.env.GOOGLE_SHEET_ID!,
  oauth: {
    clientId: process.env.GOOGLE_CLIENT_ID!,
    clientSecret: process.env.GOOGLE_CLIENT_SECRET!,
    refreshToken: process.env.GOOGLE_REFRESH_TOKEN!,
  },
});

const userModel = client.model<User>({
  sheetName: 'Users',
  schema: UserSchema,
});

const adults = await userModel.findMany(
  { age: { gte: 18 }, isActive: true },
  { limit: 10, sortBy: 'createdAt', sortOrder: 'desc' },
);

await userModel.create({ name: 'Ana', email: '[email protected]', age: 30 });
await userModel.update({ email: '[email protected]' }, { age: 31 });
await userModel.delete({ email: '[email protected]' });

Full example with relations (hasMany/belongsTo + include) in examples/basic-usage.ts (source repository).

The spreadsheet

• Each model points to a tab (sheetName) — npx gsheet-orm migrate dev creates the tab and the header row from gsheet/schema.ts (see Migrations).
• Row 1 of the tab has the schema's field names as headers (id, name, email, ...). Column order doesn't matter — the mapping is by header name, not by position.
• The sheetName passed to client.model({ sheetName, schema }) in your code must match the key used in gsheet/schema.ts — they describe the same "table" in two places (migration schema vs. runtime schema), like schema.prisma vs. the generated client.

where operators

equals, not, in, notIn, lt, lte, gt, gte, contains, startsWith, endsWith, plus the combinators AND, OR, NOT — same syntax as Prisma.

Limitations

• No real transactions; each operation is an isolated API call.
• In-memory filtering/sorting — fine up to tens of thousands of rows per tab (~50k is the practical ceiling), not beyond that.
• No indexes: every query scans the whole tab (mitigated by the cache).
• No coordination across multiple processes/instances (no locking) — cache is local to the process.
• unique and required are schema metadata, but aren't validated at runtime in this initial version.
• include attaches the relation at runtime, but findMany's return type is still T[]; to type the result, do the intersection manually (type UserWithPosts = User & { posts: Post[] }), as in the example.

Architecture

Layered overview

┌─────────────────────────────────────────────────────────────────┐
│  Your application                                                │
│  const client = new SheetClient({ spreadsheetId, oauth })        │
│  const User = client.model<User>({ sheetName: 'Users', schema }) │
└───────────────────────────┬───────────────────────────────────────┘
                            │
┌───────────────────────────▼───────────────────────────────────────┐
│  Model<T>  (client/Model.ts)                                       │
│  findMany · findFirst · findUnique · create · createMany           │
│  update · delete · upsert · count · include (relations)            │
└───────┬───────────────┬───────────────┬───────────────┬────────────┘
        │               │               │               │
┌───────▼──────┐ ┌──────▼───────┐ ┌─────▼──────┐ ┌───────▼────────┐
│ Query Engine │ │ Schema Layer │ │   Cache    │ │  Relations      │
│ where/sort/  │ │ DataTypes    │ │  Manager   │ │  hasMany/       │
│ paginate     │ │ Schema/Infer │ │  (TTL, mem)│ │  belongsTo      │
│ (query/)     │ │ serialize    │ │  (cache/)  │ │  (relations/)   │
└──────────────┘ └──────────────┘ └─────┬──────┘ └────────────────┘
                                        │
                          ┌─────────────▼──────────────┐
                          │      SheetsAdapter          │
                          │ readSheet · appendRow(s)    │
                          │ updateRow · batchUpdateRows │
                          │ deleteRows · retry/backoff  │
                          │        (adapter/)           │
                          └─────────────┬────────────────┘
                                        │  @googleapis/sheets (Sheets API v4)
                          ┌─────────────▼────────────────┐
                          │   GoogleOAuthProvider          │
                          │ authUrl · exchangeCode          │
                          │ automatic refresh                 │
                          │        (auth/)                    │
                          └─────────────┬────────────────────┘
                                        │  OAuth2Client (google-auth-library)
                                        ▼
                              The user's Google Sheet

Modules

1. auth/ — OAuth 2.0

• OAuthProvider.ts: wraps google-auth-library's OAuth2Client. Takes clientId, clientSecret, redirectUri, and at runtime an already-issued refreshToken. OAuth2Client renews the access token automatically on every call using the refresh token — no manual expiration handling needed.
• localOAuthServer.ts: spins up a temporary local HTTP server to capture the code from the OAuth consent redirect (used only by the CLI's interactive flow).
• Refresh token flow (once, done by a human):
  1. npx gsheet-orm auth → opens Google's consent URL.
  2. User signs in and authorizes the spreadsheets/drive.file scopes.
  3. Google redirects to http://localhost:53682/oauth2callback?code=....
  4. The CLI exchanges the code for tokens and saves the refresh_token straight to .env.
  5. That token is reused for the application's whole lifetime (doesn't expire, unless revoked — or after 7 days for unverified/"Testing" OAuth apps, see Google Cloud setup).

2. adapter/ — SheetsAdapter (raw spreadsheet access)

Isolates every call to the Google Sheets v4 API. Knows nothing about schema or types — works only with string[][].

• readSheet(sheetName) → { headers, rows } (row 1 = headers), with valueRenderOption: 'UNFORMATTED_VALUE' so numbers/booleans come back as real values instead of locale-formatted strings (e.g. "49,9" in a pt-BR spreadsheet, which would break Number()).
• appendRow / appendRows → INSERT_ROWS at the end of the tab.
• updateRow / batchUpdateRows → writes by row index (offset +2: +1 for the header, +1 because Sheets is 1-indexed).
• deleteRows / deleteColumns → batchUpdate with deleteDimension, bottom-to-top / right-to-left so removing multiple rows or columns in one call doesn't invalidate not-yet-processed indexes.
• createTab / deleteTab / listTabTitles — used by migrate and erd.
• withRetry → exponential backoff on 429 (rate limit) and 503.

This layer is the only one that knows about the Sheets API quota limit (default: 60 read/write requests per user per minute), which is why every read goes through the cache before reaching it.

3. schema/ — DataTypes, Schema, Infer, serialization

• DataTypes: String, Number, Boolean, JSON, UUID (auto id generation), Date.createdAt() / updatedAt() / deletedAt() (automatic fields, including soft delete).
• Schema: normalizes field definitions (accepts both { ...DataTypes.String, unique: true } and { type: DataTypes.String, unique: true }).
• Infer<typeof schema>: type utility that derives the entity's TypeScript interface from the schema — equivalent to the type generated by prisma generate, but resolved at compile time, with no code-generation step.
• serialize.ts: converts object → string[] (to write to the spreadsheet) and string[] → object (when reading), respecting each column's type (dates as ISO strings, booleans as TRUE/FALSE, JSON serialized into a single cell). An empty cell ("") only means null for types with no valid "empty" representation (number, boolean, date, json) — for string/uuid, "" round-trips as "", since the Sheets API does store and return it distinctly.

4. query/ — the query engine (where)

Sheets has no efficient native WHERE, so the strategy is: read the whole tab (or the cache), filter/sort/paginate in memory. That's fine for the target use case (spreadsheets up to tens of thousands of rows); beyond that, the adapter stops being a good fit.

• where.ts: implements the operators equals, not, in, notIn, lt, lte, gt, gte, contains, startsWith, endsWith, plus the AND / OR / NOT combinators, mirroring Prisma's syntax.
• types.ts: QueryOptions — limit, offset, sortBy, sortOrder, include.

5. cache/ — CacheManager

In-memory cache per tab, with a configurable TTL (cacheTTL, default 30s). Every write (create/update/delete) invalidates that tab's cache immediately, guaranteeing consistent reads after a write within the same process. This is what keeps normal usage from blowing through the API quota (several findMany calls in a row hit the cache, not the API).

6. client/ — Model and SheetClient

• SheetClient: entry point (equivalent to PrismaClient). Takes spreadsheetId + OAuth config, builds the SheetsAdapter and the CacheManager, and exposes .model<T>(config) to register each tab as a "table".
• Model<T>: implements the CRUD operations:
  • findMany(where?, options?), findFirst, findUnique, count
  • create, createMany (fills in id/createdAt/updatedAt/ default automatically)
  • update, delete (automatic soft delete if the schema has deletedAt; hard delete otherwise)
  • upsert

7. relations/ — hasMany / belongsTo

Relations are declared in each model's ModelConfig (a "lazy" reference to the related model, to allow circular references like User↔Post). When using include: { posts: true }, Model fetches the related tab once (batched), avoiding N+1 — it never makes one API call per row.

8. cli/ — tooling

• auth, setup-gcp, create: OAuth + Google Cloud project + Drive folder/spreadsheet provisioning (see Getting started).
• migrate/: the migrations engine — schemaLoader.ts (bundles the consumer's gsheet/schema.ts with esbuild, treating the published package as external so it resolves from the consumer's own node_modules), diff.ts (schema vs. local migration history, replay-based — same idea as Prisma's shadow database), store.ts (reads/writes gsheet/migrations/*/migration.json), apply.ts (applies operations to the live spreadsheet and records them in the _gsheet_migrations tab), plus the dev/deploy/prune commands.
• erd.ts: renders gsheet/schema.ts (models + relations metadata) as a static HTML entity-relationship diagram.
• export/: reads the schema and the live spreadsheet's data and generates a Prisma project (schema.prisma, prisma.config.ts, seed-data.json, seed.ts) — the exit ramp to a real database.

There's no code-generation step for the runtime library itself (no prisma generate equivalent) — types come from Infer at compile time, so there's no extra build step in the application's own flow.

Write flow (create)

app.create({ name, email })
  → Model.create
      → applies defaults/auto fields (id, createdAt)
      → loads headers (via cache or SheetsAdapter.readSheet)
      → serializes the object into a row (schema/serialize)
      → SheetsAdapter.appendRow (with retry)
      → CacheManager.invalidate(sheet)
      → returns the typed object

Read flow (findMany with include)

app.findMany({ age: { gte: 18 } }, { include: { posts: true } })
  → Model.load() → cache hit, or SheetsAdapter.readSheet + cache.set
  → excludes soft-deleted rows
  → query/where.matches per row
  → sort/offset/limit
  → resolveIncludes → 1 batched findMany on the related tab
  → returns T[]

Scripts

npm run build       # generates dist/ (cjs + esm + .d.ts) via tsup
npm run typecheck   # tsc --noEmit
npm test            # vitest — no tests yet (see Limitations)

Local library development

To test library changes in a real project without publishing on every change, point the consumer project at a local copy via file::

{
  "dependencies": {
    "@leotolotti/gsheet-orm": "file:../gsheet-orm"
  }
}

npm install creates a symlink (node_modules/@leotolotti/gsheet-orm -> ../gsheet-orm), so any change shows up in the project without reinstalling — just run npm run build in the library. .env, gsheet/schema.ts, and gsheet/migrations/ are configuration for each consumer project (each with its own spreadsheet) and shouldn't live in this repository.