Spreadsheet ORM is a powerful, modern, and type-safe Object-Relational Mapping (ORM) library for using Google Spreadsheets as a database. Move beyond simple row/column manipulation and leverage database-like features such as schema management, migrations, and a fluent query builder.
- Type-Safe Schema Definition: Define your table structures in TypeScript, and enjoy full type safety and autocompletion.
- Powerful Query Builder: A fluent, chainable API for
SELECT,INSERT,UPDATE, andDELETEoperations. - Schema Synchronization: Keep your spreadsheet structure in sync with your code definitions, similar to database migrations.
- Automatic Type Inference: Automatically infer TypeScript types from your defined schemas for full end-to-end type safety.
- Modern API: Built with TypeScript and ES Modules, providing a clean and intuitive developer experience.
# Using Yarn
yarn add spreadsheet-orm
# Using NPM
npm install spreadsheet-ormFirst, you need Google Service Account credentials. For a detailed, step-by-step guide on how to get them, please see our Credentials Guide.
The workflow revolves around three main steps:
- Define Schemas: Use
defineTableto describe your tables (sheets). - Initialize Client: Create a client instance with your credentials and schemas.
- Sync & Query: Use the
schemaManagerto sync your schemas and thequeryBuilderto interact with data.
Here is a complete example of how to get started.
import {
createSpreadsheetClient,
defineTable,
fieldBuilder,
type InferTableType
} from "spreadsheet-orm";
// It's recommended to load credentials securely, e.g., from environment variables
import credentials from "./your-google-credentials.json";
// --- Step 1: Define Your Schemas ---
const Users = defineTable("Users", {
id: fieldBuilder.string().default("UUID()").build(), // Set a default value
name: fieldBuilder.string().build(),
email: fieldBuilder.string().build(),
age: fieldBuilder.number().optional().build(), // This field can be empty
createdAt: fieldBuilder.date().default(new Date()).build(),
});
const Posts = defineTable("Posts", (field) => ({
id: field.string().build(),
title: field.string().build(),
content: field.string().build(),
// Create a "foreign key" relationship to the Users table
authorId: field.reference(Users, "id").build(),
}));
// --- Step 2: Automatically Infer Types from Schemas ---
type User = InferTableType<typeof Users.fields>;
type Post = InferTableType<typeof Posts.fields>;
// Now you have full type safety!
// const newUser: User = { id: "1", name: "Jane Doe", email: "jane@example.com" };
// --- Step 3: Initialize the Client ---
const client = createSpreadsheetClient({
// Credentials
email: credentials.client_email,
privateKey: credentials.private_key,
spreadsheetID: "YOUR_SPREADSHEET_ID_HERE",
// Schemas
schemas: [Users, Posts],
// (Optional) Strategy for handling sheets that exist in code but not in the spreadsheet
onMissingSchema: "create", // 'create', 'ignore', or 'error'
});
// --- Step 4: Sync Schemas & Run Queries ---
async function main() {
// Sync schemas with the spreadsheet (like a database migration)
// 'smart' mode will create missing sheets and fix column order without losing data.
console.log("Syncing schemas...");
await client.schemaManager.sync({ mode: "smart" });
console.log("Sync complete!");
// Use the query builder for CRUD operations
console.log("Inserting new users...");
await client.queryBuilder
.insert(["1", "John Doe", "[email protected]", 30]).into("Users")
.and(["2", "Jane Smith", "[email protected]"]).into("Users") // Chain inserts with and()
.execute();
console.log("Fetching users...");
const allUsers = await client.queryBuilder.select().from("Users").execute();
console.log("All Users:", allUsers);
console.log("Fetching users older than 25...");
const filteredUsers = await client.queryBuilder
.select(["name", "email"])
.from("Users")
.where((row) => {
const ageIndex = Users.orderedColumns.indexOf("age");
// row[0] is the row index, so data columns start at row[1]
return Number(row[ageIndex + 1]) > 25;
})
.execute();
console.log("Filtered Users:", filteredUsers);
}
main().catch(console.error);Use defineTable to define the structure of a sheet. The second argument is an object where each value is a fieldBuilder chain ending with .build().
defineTable(sheetName, fields, [columnOrder])
The fieldBuilder provides methods for each data type:
string()number()boolean()date()reference(schema, fieldName): Creates a link to another table's field.
Each field builder can be chained with modifiers before calling .build():
.optional(): Marks the field as optional..default(value): Provides a default value for new entries if the schema is synced.
The schemaManager ensures your spreadsheet structure matches your code definitions.
sync({ mode }): Synchronizes the schemas.mode: 'strict': Throws an error if there are any discrepancies.mode: 'smart': (Recommended) Creates missing sheets and re-orders columns of existing sheets without data loss.mode: 'force': Overwrites existing sheets that don't match the schema, potentially causing data loss.mode: 'clean': Wipes all data and writes only the schema headers.
The query builder provides a fluent API for data manipulation.
-
SELECT:
// Select all columns await client.queryBuilder.select().from("Users").execute(); // Select specific columns and apply a filter await client.queryBuilder .select(["name", "email"]) .from("Users") .where(row => Number(row[3]) > 30) // filter by age (assuming age is the 3rd column) .execute();
-
INSERT:
const newRow = ["3", "Peter Jones", "[email protected]", 42]; await client.queryBuilder.insert(newRow).into("Users").execute();
-
UPDATE:
const updatedData = ["Peter Jones Jr.", "[email protected]", 43]; await client.queryBuilder .update(updatedData) .from("Users") .where(row => row[1] === "3") // where id is "3" .execute();
-
DELETE:
await client.queryBuilder .delete() .from("Users") .where(row => row[2] === "[email protected]") // where email matches .execute();
-
Chaining Queries (
and): You can chain multiple operations into a single batch request for better performance.await client.queryBuilder .insert(["4", "Alice", "[email protected]"]).into("Users") .and() .insert(["p1", "My First Post", "...", "4"]).into("Posts") .execute();
Contributions are welcome! Please feel free to submit a pull request or open an issue.
This project is licensed under the MIT License.