📦 longcelot-sheet-db

Google Sheets-backed Staging Database for Node.js

A schema-first, actor-aware database adapter that uses Google Sheets as the storage engine. Perfect for MVPs, prototypes, staging environments, and internal tools where cost and simplicity matter.

🎯 Purpose

Instead of running MySQL, PostgreSQL, or MongoDB for staging:

• Each user stores their data in their own Google Sheet
• Admin maintains a single centralized registry sheet
• Authentication powered by Google OAuth + optional password
• Developers define schemas that are automatically converted into sheet tables

✨ Features

• 📝 Schema-First Design: Define tables using a TypeScript DSL
• 🔐 Actor-Based Isolation: Each user role owns their own sheet
• 🔄 Auto CRUD: create, createMany, findMany, findOne, count, update, upsert, delete
• 🎭 Role-Based Permissions: Built-in security boundaries + cross-actor access matrix
• 🔑 Authentication: createAuthRouter wires Google Sign-In + JWT in one call; role-based registration policy
• 🛠️ CLI Tools: Initialize, authorize, generate, sync, validate, seed, migrate, drop/rename schema elements, mock-users
• 📊 Type-Safe: Full TypeScript support
• 💰 Cost-Free: No infrastructure costs for staging
• 🔒 Schema Integrity: Hash-based version tracking detects stale user sheets at runtime
• ♻️ Safe Migrations: sync --all-users pushes schema changes to every user sheet with rate-limit backoff
• 🚀 CI-Friendly: sync --token-file skips interactive OAuth prompt in CI/CD pipelines

🚀 Quick Start

Installation

# npm
npm install longcelot-sheet-db

# pnpm
pnpm add longcelot-sheet-db

# yarn
yarn add longcelot-sheet-db

# bun
bun add longcelot-sheet-db

Initialize Project

# npm
npx lsdb init

# pnpm
pnpm dlx lsdb init

# yarn
yarn dlx lsdb init

# bun
bunx lsdb init

This creates:

• lsdb.config.ts - Project configuration
• .env - Environment variables
• schemas/ - Schema directory

Set Up Google OAuth

This package requires Google OAuth2 to function — there is no way to skip it. OAuth is used for the backend to communicate with Google Sheets API.

1. Go to Google Cloud Console
2. Create a project and enable Google Sheets API and Google Drive API
3. Create OAuth 2.0 credentials (Client ID and Client Secret)
4. Set redirect URI (e.g., http://localhost:3000/auth/callback)
5. Add your credentials to .env:

GOOGLE_CLIENT_ID=your_client_id
GOOGLE_CLIENT_SECRET=your_client_secret
GOOGLE_REDIRECT_URI=http://localhost:3000/auth/callback
ADMIN_SHEET_ID=your_admin_sheet_id

What if you have your own authentication?

• OAuth is strictly for backend-to-Google-Sheets communication
• Your app's existing authentication (JWT, sessions, etc.) remains untouched
• You map your user identity to lsdb context (see "Integrating into an Existing Project" below)

Authorize lsdb with Google

npx lsdb auth

Walks you through the consent screen and saves .lsdb-tokens.json to the project root. If GOOGLE_REDIRECT_URI points at localhost/127.0.0.1 and that port is free, lsdb auth opens your browser and catches Google's redirect automatically — no copying an authorization code out of the address bar. This step is optional (lsdb sync will run the same flow itself the first time it needs a token), but running it up front means auth happens once, on its own, instead of surprising you mid-sync.

Define a Schema

import { defineTable, string, number, date } from 'longcelot-sheet-db';

export default defineTable({
  name: 'bookings',
  actor: 'user',
  timestamps: true,
  columns: {
    booking_id: string().required().unique(),
    service: string().required(),
    date: date().required(),
    status: string().enum(['pending', 'confirmed', 'cancelled']).default('pending'),
    price: number().min(0),
  },
});

Use in Your Application

import { createSheetAdapter } from 'longcelot-sheet-db';
import bookingsSchema from './schemas/user/bookings';

const adapter = createSheetAdapter({
  adminSheetId: process.env.ADMIN_SHEET_ID,
  credentials: {
    clientId: process.env.GOOGLE_CLIENT_ID,
    clientSecret: process.env.GOOGLE_CLIENT_SECRET,
    redirectUri: process.env.GOOGLE_REDIRECT_URI,
  },
  tokens: userOAuthTokens,
});

adapter.registerSchema(bookingsSchema);

const userContext = adapter.withContext({
  userId: 'user_123',
  actor: 'user',
  actorSheetId: 'user-sheet-id',
});

await userContext.table('bookings').create({
  booking_id: 'bk_001',
  service: 'Consultation',
  date: new Date().toISOString(),
  price: 100,
});

const bookings = await userContext.table('bookings').findMany({
  where: { status: 'pending' },
  limit: 10,
});

📚 Core Concepts

Actors

Actors are data domains — they determine where data is stored (which Google Sheet and which table schemas apply). Each actor maps to a sheet ID via an environment variable:

// lsdb.config.ts
actors: [
  { name: "admin",  sheetIdEnv: "ADMIN_SHEET_ID" },
  { name: "user",   sheetIdEnv: "DEV_USER_SHEET_ID" },
  { name: "seller", sheetIdEnv: "DEV_SELLER_SHEET_ID" },
]

ActorConfig.role is accepted for backward compatibility but deprecated in favour of name — role reads as an RBAC role at the exact spot autocomplete shows it, which is the confusion this field exists to prevent. See Actors vs Application Roles and FAQ #2.

# .env
ADMIN_SHEET_ID=1ABCyourAdminSheetId
DEV_USER_SHEET_ID=1DEFyourDevUserSheetId    # optional for local dev
DEV_SELLER_SHEET_ID=1GHIyourDevSellerSheetId  # optional for local dev

• admin: Data stored in central admin sheet (always required)
• user / seller: Each actor gets a personal sheet at runtime; DEV_*_SHEET_ID values let you sync schemas during development without registering real users

lsdb init scaffolds all env vars automatically based on the actors you define.

Actors vs Application Roles

These two concepts are distinct — confusing them leads to wrong architecture decisions:

| Concept | What it controls | Dynamic? | Where defined | |---------|-----------------|----------|---------------| | Actor | Where data is stored (which Google Sheet, which schemas) | No — fixed in lsdb.config.ts | Config file | | App RBAC role | What a user can do (read orders, edit products, etc.) | Yes — rows in roles / role_permissions tables | Your app's DB layer |

The actor field in withContext() is the lsdb actor concept, not an RBAC role. If you need fine-grained permissions (e.g. "manager can approve but not delete"), build a roles + role_permissions table in the admin sheet and enforce it in your application layer — lsdb intentionally does not provide RBAC.

Field names follow the same rule everywhere actor identity appears — each was renamed away from role because the bare word role reads as an RBAC role at the point of writing the code, regardless of what the docs say:

| Location | Preferred field | Deprecated alias (still works, warns) | |---|---|---| | lsdb.config.ts actor entries | name | role | | withContext() | actor | role | | withContext() cross-actor target | targetActor | targetRole |

Modeling RBAC sub-roles (e.g. operation, finance, marketing) as separate actors is the most common version of this mistake — it usually means you need rows in a roles / role_permissions table inside one actor, not one actor config entry per sub-role.

Dev vs Production data model

In development, each actor type shares one sheet (DEV_SELLER_SHEET_ID for all sellers). In production, createUserSheet() creates one sheet per registered user. This means:

• Some bugs that only appear with per-user data isolation are invisible in dev.
• Use lsdb mock-users to create separate actor sheets that mirror the production topology for more realistic local testing.

Tip: Add a "Dev vs Production" section to your own README.md noting which tests cover per-user-sheet scenarios.

This same per-user isolation generalizes directly to the SQL adapters (Migration Path): each non-admin table gets an injected tenant_id column, and context.actorSheetId — a real spreadsheet ID on Sheets — becomes the opaque tenant_id value on Postgres/MySQL/Prisma. Admin tables have no tenant_id column at all, matching Sheets' single global admin sheet. See FAQ.md §13 for the full design rationale.

Schema DSL

Define tables using a fluent builder API:

{
  email: string().required().unique(),
  age: number().min(18).max(100),
  status: string().enum(['active', 'inactive']).default('active'),
  verified: boolean().default(false),
  metadata: json(),
}

Column Modifiers

• required() - Cannot be null
• unique() - Enforced uniqueness
• default(value) - Default value, applied on create() only (accepts arrays/objects for json() columns)
• min(n) / max(n) - Validation constraints
• enum([...]) - Allowed values
• pattern(regex) - Regex validation
• primary() - Primary key
• readonly() - Cannot be updated
• ref(table.column) - Foreign key reference
• index() - Create lookup index

CRUD Operations

const table = ctx.table('bookings');

// Create
await table.create({ service: 'Consultation', price: 100 });

// Bulk create — single API call
await table.createMany([
  { service: 'Consultation', price: 100 },
  { service: 'Follow-up', price: 50 },
]);

// Read
await table.findMany({
  where: { status: 'pending' },
  orderBy: 'date',
  order: 'desc',
  limit: 10,
  offset: 0,
});

await table.findOne({ where: { booking_id: 'bk_001' } });

// Count (without loading all rows)
const total = await table.count({ where: { status: 'pending' } });

// Update
await table.update({
  where: { booking_id: 'bk_001' },
  data: { status: 'confirmed' },
});

// Upsert (insert if not found, update if exists)
await table.upsert({
  where: { email: '[email protected]' },
  data: { role: 'admin', status: 'active' },
});

// Delete
await table.delete({ where: { booking_id: 'bk_001' } });

Context & Permissions

Every operation requires context:

const context = adapter.withContext({
  userId: 'user_123',
  actor: 'user',       // preferred — maps to the actor data domain
  actorSheetId: 'sheet-id',
});
// Note: role: is accepted for backward compatibility but deprecated in favour of actor:

Permissions are enforced automatically:

• Users can only access their own sheets
• Admin can access admin tables
• Cross-actor access is blocked

Schema Version Tracking

longcelot-sheet-db computes a SHA-256 hash of every table schema and compares it against the hash stored in the built-in schema_versions admin table. When withContext() is called for a non-admin user, the check runs in the background — every subsequent CRUD call awaits the result before proceeding.

Configure the behaviour in lsdb.config.ts:

export default {
  // ...
  onSchemaMismatch: 'warn',    // log warning and continue (default)
  // onSchemaMismatch: 'error',     // throw SchemaMismatchError
  // onSchemaMismatch: 'auto-sync', // sync the actor sheet automatically
};

And pass it through to the adapter:

import { createSheetAdapter } from 'longcelot-sheet-db';

const adapter = createSheetAdapter({
  adminSheetId: process.env.ADMIN_SHEET_ID,
  credentials: { clientId, clientSecret, redirectUri },
  tokens: oauthTokens,
  onSchemaMismatch: 'warn', // or 'error' or 'auto-sync'
});

| Mode | Behaviour | |------|-----------| | 'warn' | Log a warning to stderr and continue — safe for production rollouts | | 'error' | Throw SchemaMismatchError — useful in staging to hard-fail stale clients | | 'auto-sync' | Silently sync the actor sheet and update the version record before proceeding |

When you push a schema change, run lsdb sync --all-users to propagate it to every registered user sheet and update the version records in one go:

# Push schema changes to all user sheets
npx lsdb sync --all-users

# Preview what would change without applying
npx lsdb sync --all-users --dry-run

Integrating into an Existing Project

If you already have a working backend (e.g., Express, NestJS), you can safely inject longcelot-sheet-db without ripping out your framework:

# 1. Add the package
pnpm add longcelot-sheet-db

# 2. Initialize project (creates config and schemas directory)
npx lsdb init

# 3. Update your .env with Google OAuth credentials

# 4. Authorize lsdb with Google (saves .lsdb-tokens.json)
npx lsdb auth

# 5. Define your schemas in schemas/ directory

# 6. Sync schemas to Google Sheets
npx lsdb sync

# 7. Use in your backend code

How it works with your existing auth:

• Your app continues to use your existing authentication (JWT, sessions, cookies)
• When you need to access data, map your authenticated user to lsdb context:

// Your Express/NestJS route handler
app.get('/bookings', async (req, res) => {
  // Your existing auth provides user info
  const developerUser = req.user; // From your JWT/session

  // Map to lsdb context
  const userContext = adapter.withContext({
    userId: developerUser.id,        // Your app's user ID
    role: developerUser.role,         // 'student', 'teacher', etc.
    actorSheetId: developerUser.sheetId, // From lsdb user registry
  });

  const bookings = await userContext.table('bookings').findMany();
  res.json(bookings);
});

Google Sign-In & Auth Routes

For user-facing Google Sign-In, use createLoginOAuthManager (pre-configured with openid email profile scopes) and createAuthRouter to wire up the two required Express routes automatically.

Login-only roles (admin / manager)

import express from 'express';
import { createSheetAdapter, createAuthRouter } from 'longcelot-sheet-db';

const adapter = createSheetAdapter({ ... });

const adminAuth = createAuthRouter({
  adapter,
  jwtSecret: process.env.JWT_SECRET!,
  frontendUrl: process.env.FRONTEND_URL!,
  registrationPolicy: 'login-only', // user must already exist — no self-signup
  async onUser(profile, adapter) {
    const ctx = adapter.withContext({
      userId: 'auth',
      role: 'admin',
      actorSheetId: process.env.ADMIN_SHEET_ID!,
    });
    return await ctx.table('users').findOne({ where: { email: profile.email } });
  },
});

app.use(adminAuth.handler);
// Exposes: GET /auth/google  and  GET /auth/callback

Open registration (regular users)

const userAuth = createAuthRouter({
  adapter,
  jwtSecret: process.env.JWT_SECRET!,
  frontendUrl: process.env.FRONTEND_URL!,
  registrationPolicy: 'open', // allows self-signup (default)
  basePath: '/user',           // → /user/auth/google, /user/auth/callback
  async onUser(profile, adapter) {
    const ctx = adapter.withContext({
      userId: 'auth',
      role: 'admin',
      actorSheetId: process.env.ADMIN_SHEET_ID!,
    });
    let user = await ctx.table('users').findOne({ where: { email: profile.email } });
    if (!user) {
      // Auto-create the user and their sheet on first login
      const sheetId = await adapter.createUserSheet(profile.sub, 'user', profile.email);
      user = await ctx.table('users').findOne({ where: { email: profile.email } });
    }
    return user;
  },
});

app.use(userAuth.handler);

Registration policy summary:

| Policy | Behaviour | |--------|-----------| | 'open' | Any Google-authenticated user can access; onUser returning null lets them in with bare profile | | 'login-only' | onUser must return a non-null user; returns 401 if user is not found |

Using createLoginOAuthManager directly

If you prefer to wire up routes manually:

import { createLoginOAuthManager } from 'longcelot-sheet-db';

const oauth = createLoginOAuthManager({
  clientId: process.env.GOOGLE_CLIENT_ID!,
  clientSecret: process.env.GOOGLE_CLIENT_SECRET!,
  redirectUri: process.env.GOOGLE_REDIRECT_URI!,
});

// Step 1: redirect to Google
app.get('/auth/google', (req, res) => res.redirect(oauth.getAuthUrl()));

// Step 2: handle callback
app.get('/auth/callback', async (req, res) => {
  const tokens = await oauth.getTokens(req.query.code as string);
  const profile = await oauth.verifyToken((tokens as Record<string, string>).id_token);
  // ... lookup user, issue JWT
});

Why do we need user_id if we have sheet_id?

The sheet_id dictates the physical storage location on Google Drive — it exists only in the lsdb world. When you eventually graduate from Google Sheets to a production SQL database (MySQL, PostgreSQL), the sheet_id goes away entirely.

The user_id dictates the logical domain identity — it persists across all databases. This is your app's true primary key that ties your entire system together.

| Field | Purpose | Persists after migration | |-------|---------|--------------------------| | sheet_id | Physical location in Google Drive | No — Google Sheets only | | user_id | Logical user identity | Yes — becomes PK in SQL |

Migration example: When you export to Prisma, user_id becomes your primary key, while sheet_id is simply not included in the export.

🛠️ CLI Commands

All commands can be run with npx, pnpm dlx, yarn dlx, or bunx — or directly as lsdb <command> if installed globally.

Initialize Project

npx lsdb init
# pnpm dlx lsdb init
# yarn dlx lsdb init
# bunx lsdb init

Creates project structure and configuration files.

Authorize with Google

npx lsdb auth
# pnpm dlx lsdb auth
# yarn dlx lsdb auth
# bunx lsdb auth

npx lsdb auth --force   # re-authorize even if a valid token is already stored
npx lsdb login          # alias for auth

Runs the Google OAuth consent flow and saves .lsdb-tokens.json to the project root. Opens your browser automatically; when GOOGLE_REDIRECT_URI is localhost/127.0.0.1 and that port is free, a short-lived local server catches the redirect for you instead of asking you to paste the authorization code by hand. Falls back to the manual paste prompt whenever automatic capture isn't possible (non-local redirect URI, port already in use, timeout, or denied consent).

Run this once after init, before your first sync — though it's entirely optional: sync (and drop-table/drop-column/rename-column) trigger the same flow themselves the first time they need a token.

Generate Schema

npx lsdb generate bookings
# pnpm dlx lsdb generate bookings
# yarn dlx lsdb generate bookings
# bunx lsdb generate bookings

Interactive schema generator with prompts for columns and types.

Sync Schemas

npx lsdb sync
# pnpm dlx lsdb sync
# yarn dlx lsdb sync
# bunx lsdb sync

Creates missing sheets and adds missing columns (never deletes data). Iterates all actors defined in lsdb.config.ts and prints a per-actor status table:

Actor      │ Sheet ID                   │ Tables   │ Status
───────────┼────────────────────────────┼──────────┼────────────
admin      │ 1ABCyourAdminSheetId       │ 3        │ ✅ synced
student    │ 1DEFyourStudentSheetId     │ 5        │ ✅ synced
teacher    │ (not set)                  │ 4        │ ⚠ skipped

Actors whose sheet ID env var is not set are skipped with a warning (non-fatal).

--table <names> — restrict the sync to specific table(s) instead of every registered schema. Pass a single name or a comma-separated list. Large schemas can easily hit Google's per-user Sheets API quota when syncing everything at once — use --table to sync just the table(s) that actually changed:

npx lsdb sync --table bookings                    # sync one table
npx lsdb sync --table bookings,payments            # sync a few tables
npx lsdb sync --table bookings --all-users          # combine with --all-users

An unknown table name fails fast and lists exactly which name(s) weren't found, instead of silently skipping them. Every schema is still registered on the adapter regardless of --table — only which tables get synced is restricted, so cross-table lookups (e.g. --all-users' read of the admin users table) keep working.

--all-users — after syncing dev actor sheets, reads all rows from the admin users table and pushes any missing columns/tables to every registered user sheet. Updates the schema_versions record for each one. Uses exponential backoff (1s → 32s) to handle Google Sheets API rate limits.

--dry-run — combine with --all-users to preview which user sheets are outdated without writing any changes:

npx lsdb sync --all-users           # apply
npx lsdb sync --all-users --dry-run # preview only

--token-file <path> — CI/CD-friendly: load a pre-stored tokens JSON file instead of triggering the interactive browser OAuth prompt. Inject the file from a CI secret:

# In GitHub Actions:
echo "$LSDB_TOKENS" > /tmp/tokens.json
npx lsdb sync --token-file /tmp/tokens.json

Validate Schemas

npx lsdb validate
# pnpm dlx lsdb validate
# yarn dlx lsdb validate
# bunx lsdb validate

Checks for:

• Duplicate table names
• Invalid modifiers
• Unknown actors
• Missing required fields

Seed Data

npx lsdb seed <seed-file>
# pnpm dlx lsdb seed seeds/admin.ts

Load initial or test data into your sheets.

Seed file formats — both are supported:

// Static export (simple)
export default {
  users: [
    { email: '[email protected]', role: 'admin', status: 'active' },
  ],
}

// Dynamic export (receives process.env — great for CI or per-environment seeds)
export default async function(env: NodeJS.ProcessEnv) {
  return {
    users: [
      { email: env.SUPER_ADMIN_EMAIL, role: 'admin', status: 'active' },
    ],
  }
}

Flags:

• --skip-existing — skip rows where a unique column already matches (no error on re-seed)
• --upsert — update existing rows on unique conflict instead of throwing
• --all-actors — distribute seed data to all registered user sheets

npx lsdb seed seeds/admin.ts --skip-existing  # idempotent re-seed
npx lsdb seed seeds/admin.ts --upsert          # update on conflict

Doctor

npx lsdb doctor
# pnpm dlx lsdb doctor
# yarn dlx lsdb doctor
# bunx lsdb doctor

Runs environment and configuration health checks.

Status

npx lsdb status
# pnpm dlx lsdb status
# yarn dlx lsdb status
# bunx lsdb status

Shows all registered tables, actors, and their sheet IDs.

ER Diagram

npx lsdb erdiagram
# pnpm dlx lsdb erdiagram
# yarn dlx lsdb erdiagram
# bunx lsdb erdiagram

Generates ER-DIAGRAM.md (Mermaid erDiagram) mapping every registered table, its columns, and ref() relationships — offline, no Google Sheets calls. If the file already exists, prompts to overwrite, save under a different name, or cancel; pass --yes to overwrite non-interactively, or --output <file> to target a different path.

npx lsdb erdiagram --output docs/schema.md
npx lsdb erdiagram --yes

🔐 Authentication

Google OAuth

import { createOAuthManager } from 'longcelot-sheet-db';

const oauth = createOAuthManager({
  clientId: process.env.GOOGLE_CLIENT_ID,
  clientSecret: process.env.GOOGLE_CLIENT_SECRET,
  redirectUri: process.env.GOOGLE_REDIRECT_URI,
});

const authUrl = oauth.getAuthUrl();

const tokens = await oauth.getTokens(code);

const payload = await oauth.verifyToken(idToken);

Sign-In Router (createAuthRouter)

import { createAuthRouter, verifyJwt } from 'longcelot-sheet-db';

const auth = createAuthRouter({
  adapter,
  jwtSecret: process.env.JWT_SECRET!,
  frontendUrl: process.env.FRONTEND_URL!,
  registrationPolicy: 'login-only',
  jwtExpiresInSeconds: 60 * 60 * 24, // default: 1 day
  tokenDelivery: 'fragment', // '#token=...' — never hits server access logs/Referer; default is 'query' for backward compatibility
  scopes: ['openid', 'email', 'profile'], // default: LOGIN_SCOPES (adds Sheets/Drive) — trim to identity-only to skip Google's sensitive-scope verification screen
  async onUser(profile, adapter) {
    const ctx = adapter.withContext({ userId: 'auth', actor: 'admin', actorSheetId: process.env.ADMIN_SHEET_ID! });
    return await ctx.table('users').findOne({ where: { email: profile.email } });
  },
});

app.use(auth.handler); // GET {basePath}/auth/google, GET {basePath}/auth/callback

// In your own auth middleware, verify the JWT this router issues:
const payload = verifyJwt(token, process.env.JWT_SECRET!); // null if invalid, expired, or tampered

The login flow is CSRF-protected with a signed state parameter and the issued JWT carries an exp claim — see OWASP-TOP-10.md → A01/A02/A07 for the reasoning.

scopes must include 'openid' — the callback needs an id_token to build the GoogleProfile passed to onUser; omitting it throws ValidationError when createAuthRouter() is called. See skills/auth-router/SKILL.md for when to trim scopes and what spreadsheets/drive.file being Google-classified sensitive scopes means for the consent screen.

Password Hashing

import { hashPassword, comparePassword, validatePasswordStrength } from 'longcelot-sheet-db';

const hash = await hashPassword('SecurePass123!');

const isValid = await comparePassword('SecurePass123!', hash);

const { valid, errors } = validatePasswordStrength('password');

📁 File Upload & Drive Storage

import { createSheetAdapter, DriveStorageAdapter } from 'longcelot-sheet-db';

const adapter = createSheetAdapter({
  ...,
  storage: new DriveStorageAdapter({ folder: 'uploads' }),
});

// Returns a URL that renders directly — no per-project conversion helper needed.
const avatarUrl = await adapter.upload(imageBuffer, { filename: 'avatar.jpg', mimeType: 'image/jpeg', public: true });
// 'https://drive.google.com/thumbnail?id=FILE_ID&sz=w1000'  → drop straight into <img src>

const videoUrl = await adapter.upload(videoBuffer, { filename: 'demo.mp4', mimeType: 'video/mp4', public: true });
// 'https://drive.google.com/file/d/FILE_ID/preview'          → drop straight into <iframe src>

const pdfUrl = await adapter.upload(pdfBuffer, { filename: 'invoice.pdf', mimeType: 'application/pdf', public: true });
// 'https://drive.google.com/file/d/FILE_ID/view'              → clickable open/preview link

// Deleting a file works with any URL adapter.upload() ever returned for it:
await adapter.deleteFile(avatarUrl);

The link format is chosen automatically from mimeType (image → thumbnail, video → embeddable preview, everything else → viewer link). If you need the raw downloadable file instead of a rendered preview (e.g. re-fetching bytes server-side), pass linkFormat: 'download'.

Already have links saved in the old uc?id= download format from before this behaviour shipped? Normalise them on read instead of writing your own conversion helper:

import { toDriveEmbedUrl } from 'longcelot-sheet-db';

const embeddable = toDriveEmbedUrl(row.avatar_url, 'image');

See UploadOptions and adapter.upload() in API.md for the full option/return reference, and FAQ.md §14 for why the raw uc?id= link never rendered reliably.

Organizing uploads into (sub)folders

folder accepts a /-separated path — each segment is created (or reused) as a real nested Drive folder, so a handful of upload categories become a handful of real subfolders:

await adapter.upload(contractBuffer, { filename: 'contract.pdf', mimeType: 'application/pdf', folder: 'my-app/contracts' });
await adapter.upload(avatarBuffer, { filename: 'avatar.jpg', mimeType: 'image/jpeg', folder: 'my-app/avatars' });
await adapter.upload(invoiceBuffer, { filename: 'invoice-042.pdf', mimeType: 'application/pdf', folder: 'my-app/invoices' });

Per-actor upload placement — same Drive/isolation model as sheets

When adapter.upload()/deleteFile() is called on a withContext()-scoped adapter, the file follows the exact same placement rule as that actor's sheet (createUserSheet()):

• Shared/dev model (no tokenStore/actorTokens for that actor) — file goes to the shared admin Drive, under driveFolder.root/subfolders[actor]/<folder> if driveFolder is configured.
• Actor-owned model (tokenStore has tokens for that userId, or createUserSheet() was given actorTokens) — file goes to that actor's own Drive, not the admin's, mirroring where their sheet lives.
• No withContext()? Uploads behave exactly as before — flat, Drive-root-relative folder, no per-actor placement.

const adapter = createSheetAdapter({
  adminSheetId,
  credentials,
  tokens,
  driveFolder: { root: 'My App', subfolders: { seller: 'Sellers' } },
  tokenStore, // optional — actors with stored tokens get their own Drive; everyone else shares the admin's
  storage: new DriveStorageAdapter({ folder: 'uploads' }),
});

const sellerCtx = adapter.withContext({ userId: 'seller_1', actor: 'seller', actorSheetId: sellerSheetId });

// Shared model: My App/Sellers/uploads/... in the admin's Drive.
// Actor-owned model (tokenStore has tokens for seller_1): My App/Sellers/uploads/... in
// seller_1's own Drive instead — same place their sheet was created.
await sellerCtx.upload(listingImage, { filename: 'listing.jpg', mimeType: 'image/jpeg' });

See Per-actor upload placement mirrors createUserSheet() in API.md for the full resolution rules.

📋 Sheet Structure

Central Admin Sheet

• users - User registry with actor_sheet_id per user
• credentials - Authentication data
• schema_versions - Schema hash per (actor_sheet_id, table_name) — used for mismatch detection

User-Owned Sheets

Each user gets their own sheet with tables based on their role:

user-sheet-123
  ├── profile
  ├── bookings
  ├── payments
  └── settings

Sheet Formatting

Every tab created or extended by sync / syncSchema() / createUserSheet() is formatted automatically — no config needed:

• Auto-fit columns — header and data columns are resized to fit their content.
• Header row styling — a light fill color, frozen by default so it stays visible while scrolling.
• Data validation dropdowns — boolean() columns get a dropdown restricted to TRUE/FALSE (or 1/0, see below); string().enum([...]) columns get a dropdown of the allowed values. Both guard against invalid manual edits directly in the sheet.

Override the defaults via sheetStyle on createSheetAdapter():

const adapter = createSheetAdapter({
  // ...
  sheetStyle: {
    headerColor: '#E8F0FE',     // optional, falls back to this built-in default
    freezeHeader: true,         // default: true
    freezeFirstColumn: false,   // default: false
    booleanFormat: 'TRUE_FALSE', // default: 'TRUE_FALSE', or '1_0'
  },
});

Override per column instead of project-wide with boolean({ format: '1_0' }) — useful when one table needs to match an external system's convention without changing every other table.

Read Caching & Rate Limits

findMany(), findOne(), count(), update(), and delete() all read through an in-memory cache in SheetClient, enabled by default with a 2-second TTL — repeated or concurrent reads of the same tab within that window are served from cache or de-duplicated into a single Sheets API call instead of one call each. This exists because Google's default quota (60 read requests/min/user) is easy to exhaust once more than a couple of concurrent users are hitting the same tables — see FAQ.md #11 for the incident that motivated it.

Every write invalidates the cache for the tab it touched, so a read right after a write always sees fresh data. Tune or disable it via cache on createSheetAdapter():

const adapter = createSheetAdapter({
  // ...
  cache: { ttlMs: 5000 }, // default: 2000ms; set enabled: false to disable
});

🎓 Complete Example

Coming Soon!

🔄 Migration Path

When you're ready for production, swapping createSheetAdapter for a real database is a config/factory change, not a CRUD-call rewrite — SheetAdapter and the SQL adapters below all implement the same DatabaseAdapter/TableOperations contract, so adapter.withContext({...}).table('products').create({...}) reads identically either way. See FAQ.md §13 for the tenancy design behind this.

import { createDatabaseAdapter } from 'longcelot-sheet-db';

// Picks the engine from $DB_DRIVER ('sheets' | 'postgres' | 'mysql'), or pass driver explicitly.
// A dev's .env points at sheets; CI/production's env points at postgres/mysql — zero code branching.
const adapter = createDatabaseAdapter();
adapter.registerSchemas(schemas);

// Or construct a specific engine directly:
import { createSheetAdapter, createPostgresAdapter, createMySQLAdapter, createPrismaAdapter } from 'longcelot-sheet-db';

const dev = createSheetAdapter({ adminSheetId, credentials, tokens });
const prod = createPostgresAdapter({ connectionString: process.env.DATABASE_URL });
const prodMysql = createMySQLAdapter({ connectionString: process.env.DATABASE_URL });

// Prisma: pass an already-`prisma generate`'d client — this package never runs Prisma codegen
// itself (see FAQ.md §13). Run `lsdb migrate --prisma` + `prisma generate` first.
import { PrismaClient } from '@prisma/client';
const prodPrisma = createPrismaAdapter({ client: new PrismaClient() });

Install only the driver you target — pg/mysql2 are optional peer dependencies, lazily required only inside createPostgresAdapter()/createMySQLAdapter(), so npm install longcelot-sheet-db alone never pulls either in:

npm install longcelot-sheet-db
npm install pg        # only if targeting Postgres
npm install mysql2    # only if targeting MySQL
npm install prisma @prisma/client   # only if targeting Prisma

Which migrate command do I need?

| Goal | Command | |------|---------| | Copy table structure only (schema / DDL) | lsdb migrate --prisma or --sql | | Apply that DDL to a live Postgres/MySQL database | lsdb migrate --sql --apply --connection-string $DATABASE_URL | | Copy structure + admin sheet row data | lsdb migrate-data | | Copy structure + all user-sheet row data | lsdb migrate-data --all-users | | Run the data cutover now, no generated script | lsdb migrate-data --run --connection-string $DATABASE_URL --driver postgres | | Preview any of the above without writing/executing | add --dry-run |

Schema export (structure only)

# Export to Prisma schema
npx lsdb migrate --prisma --output ./prisma

# Export to SQL DDL (CREATE TABLE statements)
npx lsdb migrate --sql --output ./migrations

# Export AND apply directly to a live database — idempotent, safe to rerun
npx lsdb migrate --sql --apply --connection-string postgres://user:pass@host/db
npx lsdb migrate --sql --apply --driver mysql --connection-string $DATABASE_URL

# Prisma: shells out to `prisma migrate deploy` (needs an existing migrations/ folder —
# run `prisma migrate dev` once locally first)
npx lsdb migrate --prisma --apply

--driver is inferred from the connection string's scheme (postgres:///postgresql:// → postgres, mysql:// → mysql) when omitted. --dry-run prints the statements that would run without executing them.

Data export (row data → production DB)

# Generate a migrate-data.js script (admin sheet only)
npx lsdb migrate-data

# Admin sheet + all registered user sheets
npx lsdb migrate-data --all-users

# Preview without writing
npx lsdb migrate-data --all-users --dry-run

# Only migrate specific tables (comma-separated) instead of everything
npx lsdb migrate-data --table users,credentials,setup

# Run the cutover now, in-process — no generated script, no insertRow() stub to fill in
npx lsdb migrate-data --run --all-users --connection-string $DATABASE_URL --driver postgres

# Partial cutover: run now, but only for a specific set of tables
npx lsdb migrate-data --run --table users,credentials,setup --connection-string $DATABASE_URL --driver postgres

Without --run, migrate-data generates a migrate-data.js script — replace the insertRow() stub with your real DB client and run it once. With --run, the cutover happens immediately against createPostgresAdapter/createMySQLAdapter (not Prisma — see FAQ.md §13 for why), upserting every row by _id so reruns are safe by default — no separate --upsert flag needed. --token-file works the same as it does for sync --token-file, for CI/CD.

--table <names> restricts the migration to specific tables instead of everything — pass a single name or a comma-separated list (e.g. --table users,credentials,setup) when a use case only needs a subset of the data moved. Works with both the script-generation path and --run; an unknown table name fails fast and lists exactly which name(s) weren't found instead of silently skipping them.

Note: lsdb export and lsdb export-data are deprecated — use lsdb migrate and lsdb migrate-data instead. In standard tooling (Prisma Migrate, Rails, Flyway), "migrate" means schema-only DDL changes, so the schema/DDL export command is now the one named migrate; the row-data export command is migrate-data.

Reference CI/CD pipeline

--apply/--run are built to be invoked unattended from a deploy pipeline — same secrets convention as sync --token-file ($DATABASE_URL / a secret manager entry for the connection string, a stored tokens file for the Sheets read side), idempotent by default, --dry-run on both for a gated diff-review step. A typical GitHub Actions job:

# In GitHub Actions:
jobs:
  deploy:
    steps:
      - uses: actions/checkout@v4
      - run: pnpm install --frozen-lockfile && pnpm build

      # Schema: apply to staging, smoke test, then production only on a version tag —
      # mirrors this package's own `publish` job's `if: startsWith(github.ref, 'refs/tags/v')` gate.
      - run: npx lsdb migrate --sql --apply --connection-string ${{ secrets.STAGING_DATABASE_URL }}
      - run: <your smoke test against staging>
      - if: startsWith(github.ref, 'refs/tags/v')
        run: npx lsdb migrate --sql --apply --connection-string ${{ secrets.PRODUCTION_DATABASE_URL }}

      # Data cutover — run once (or repeatedly during a dual-write transition window, see below),
      # not on every deploy.
      - if: startsWith(github.ref, 'refs/tags/v')
        run: |
          npx lsdb migrate-data --run --all-users \
            --connection-string ${{ secrets.PRODUCTION_DATABASE_URL }} \
            --driver postgres \
            --token-file token.json

When does "ship to production" actually happen? Two options:

• Dual-write-then-flip (recommended default) — both stores stay populated during a transition window: keep writing to Sheets as usual, rerun migrate-data --run periodically to keep the SQL side current (safe — always idempotent), then flip DB_DRIVER=postgres (via createDatabaseAdapter()) at deploy time once you're confident. Rollback is just flipping the env var back, since Sheets was never stopped.
• One-shot cutover — run migrate-data --run once, flip the driver, and stop writing to Sheets entirely. Simpler, but harder to roll back from: the SQL adapters never auto-create/alter schema at runtime (§16.2), so there's no automatic "flip back and resync" path once Sheets stops being the source of truth.

See FAQ.md §13 for the full tenancy/cutover design rationale.

🗑️ Dropping & Renaming Schema Elements

lsdb sync is deliberately additive-only — it creates tabs and appends missing columns, but never removes or renames anything, so it can never lose data on its own. Once a table or column is no longer needed (or needs a better name), use these commands to update the schema file and the live Google Sheet together:

# Drop table(s) — deletes the schema file and the Google Sheet tab
npx lsdb drop-table bookings
npx lsdb drop-table                    # interactive checkbox — pick any number of tables
npx lsdb drop-table --all-users        # also drop from every registered user's personal sheet

# Drop column(s) from a table
npx lsdb drop-column bookings notes
npx lsdb drop-column bookings          # interactive checkbox over that table's columns
npx lsdb drop-column                   # interactive: pick the table, then its columns

# Rename a column — updates the header cell in place, existing row data is preserved
npx lsdb rename-column bookings notes remarks
npx lsdb rename-column                 # interactive: pick table, pick column, type new name

All three:

• Print a plan and ask for confirmation before touching anything (skip with --yes)
• Support --dry-run to preview without making changes
• Support --all-users to also apply the change to every registered user's personal sheet (reads actor_sheet_id from the admin users table, same as sync --all-users)
• Support --token-file <path> for CI
• Refuse to touch reserved auto-generated columns (_id, _created_at, _updated_at, _deleted_at); drop-column also refuses to drop a table's primary key column (drop the whole table instead)
• Warn — but don't block — if another table's ref() points at the table/column being dropped or renamed, since ref() strings in other schema files aren't rewritten automatically

rename-column is the safe way to fix a column name: it edits the Google Sheet header cell in place instead of dropping and re-adding the column, so every existing row keeps its data. This matters most for production, where a bad rename that instead did drop+re-add would silently wipe that column's data across every registered user's sheet the next time it synced.

⚡ Performance

• Suitable for hundreds to low thousands of rows
• Not suitable for millions of rows
• Read operations: ~200-500ms
• Write operations: ~300-700ms

🔒 Security

• bcrypt password hashing (10 rounds), with a guard against silent truncation past bcrypt's 72-byte input limit
• Auth JWTs are short-lived (exp claim, 1 day by default) and verifiable via the exported verifyJwt() — see Authentication
• OAuth login flow is CSRF-protected via a signed state parameter
• .lsdb-tokens.json (OAuth refresh token) is written with 0o600 permissions — note: this is plaintext on disk, restricted by file permissions, not encrypted at rest; treat it as a credential
• lsdb init scaffolds .gitignore entries for .env/.lsdb-tokens.json so secrets aren't committed by default
• Sheets private by default — access control depends on who the underlying spreadsheet is shared with, see OWASP-TOP-10.md → A01
• Role/permission validation on every cross-actor request (accessControl.ts)
• SQL adapters use parameterized queries exclusively — no SQL injection risk via the CRUD API

**For the full picture — what's fixed, what's a documented limitation, and what's on you to handle as the integrating developer or your end users — see OWASP-TOP-10.md, a category-by-category OWASP Top 10 review of this package including concrete answers to "an end user's Google account gets compromised" and "someone edits the Sheet directly instead of through the API."

📦 Architecture

Developer Backend
      ↓
longcelot-sheet-db SDK
      ↓
Google OAuth2 → Google Sheets API
      ↓
Central Admin Sheet
      ↓
User-Owned Sheets

🎯 Use Cases

Perfect for:

• ✅ MVPs and prototypes
• ✅ Staging environments
• ✅ Internal tools
• ✅ School/small business apps
• ✅ Proof of concepts

Not suitable for:

• ❌ Production at scale
• ❌ High-performance applications
• ❌ Real-time analytics
• ❌ Millions of records

🤝 Contributing

Contributions welcome! This package is designed to be:

• Simple over clever
• Explicit over implicit
• Safe over fast

📄 License

MIT

🙏 Acknowledgments

Built on:

• Google Sheets API
• googleapis
• bcryptjs
• commander
• inquirer

Note: This is a staging database solution. For production workloads, migrate to MySQL, PostgreSQL, or MongoDB.