Gs-orm NPM | npm.io

Google Sheets ORM

A comprehensive ORM (Object-Relational Mapping) library that transforms Google Sheets into a powerful, flexible database with rich features and intuitive API.

🌟 Key Features

🗃️ Full ORM Capabilities
- Complete CRUD Operations
- Dynamic Schema Validation
- Advanced Querying
- Relationship Support
🔍 Powerful Query Capabilities
- Complex Filtering
- Sorting and Pagination
- Rich Comparison Operators
🔒 Built-in Data Validation
📊 Spreadsheet Management
🚀 Batch Operations
🕰️ Automatic Timestamps
🆔 Automatic ID Generation

Installation

npm install gs-orm

Comprehensive ORM Features

1. Model Definition and Schema Validation

const userModel = orm.defineModel('User', {
  // Detailed schema with comprehensive validation
  schema: {
    id: { 
      type: 'string', 
      required: true 
    },
    name: { 
      type: 'string', 
      required: true, 
      minLength: 2, 
      maxLength: 100 
    },
    email: { 
      type: 'string', 
      required: true,
      // Custom validation can be added
      validate: (email) => {
        const emailRegex = /^[^\s@]+@[^\s@]+\.[^\s@]+$/;
        return emailRegex.test(email);
      }
    },
    age: { 
      type: 'number', 
      min: 0, 
      max: 120,
      defaultValue: 0 
    },
    status: {
      type: 'string',
      defaultValue: 'active',
      // Enum-like validation
      validate: (status) => ['active', 'inactive', 'suspended'].includes(status)
    }
  },
  // Optional configuration
  primaryKey: 'id',      // Custom primary key
  timestamps: true       // Automatically manage createdAt/updatedAt
});

2. Relationship Management

// One-to-Many Relationship: Authors and Books
const authorModel = orm.defineModel('Author', {
  schema: {
    id: { type: 'string', required: true },
    name: { type: 'string', required: true },
    country: { type: 'string' }
  }
});

const bookModel = orm.defineModel('Book', {
  schema: {
    id: { type: 'string', required: true },
    title: { type: 'string', required: true },
    authorId: { type: 'string', required: true }, // Foreign Key
    publishYear: { type: 'number' },
    genre: { type: 'string' }
  }
});

// Many-to-Many: Books and Tags
const tagModel = orm.defineModel('Tag', {
  schema: {
    id: { type: 'string', required: true },
    name: { type: 'string', required: true }
  }
});

const bookTagModel = orm.defineModel('BookTag', {
  schema: {
    id: { type: 'string', required: true },
    bookId: { type: 'string', required: true },
    tagId: { type: 'string', required: true }
  }
});

// Example of using relationships
async function relationshipExample() {
  // Create an author
  const author = await authorModel.create({
    name: 'George Orwell',
    country: 'United Kingdom'
  });

  // Create a book for the author
  const book = await bookModel.create({
    title: '1984',
    authorId: author.id,
    publishYear: 1949,
    genre: 'Dystopian'
  });

  // Find books by an author
  const authorBooks = await bookModel.find({ 
    authorId: author.id 
  });
}

3. Advanced Querying

// Complex Querying with Multiple Operators
const results = await userModel.findAll({
  // Complex filtering
  where: {
    age: { 
      $gte: 18,     // Greater than or equal to 18
      $lte: 65      // Less than or equal to 65
    },
    status: { 
      $in: ['active', 'pending']  // In these statuses
    },
    name: { 
      $like: 'J%'   // Names starting with J
    }
  },
  // Sorting
  orderBy: { 
    age: 'desc',    // Sort by age descending
    name: 'asc'     // Then by name ascending
  },
  // Pagination
  limit: 10,        // Limit to 10 records
  offset: 20        // Skip first 20 records
});

4. Batch Operations

// Create multiple records in a single operation
const users = await userModel.createMany([
  { name: 'Alice', email: 'alice@example.com' },
  { name: 'Bob', email: 'bob@example.com' },
  { name: 'Charlie', email: 'charlie@example.com' }
]);

// Bulk update
const updatedUsers = await userModel.find({ status: 'inactive' });
const bulkUpdates = updatedUsers.map(user => ({
  ...user,
  status: 'archived'
}));
await userModel.createMany(bulkUpdates);

5. Sheets Management

const { SheetsManager } = require('gs-orm');

async function manageSpreadsheets() {
  const manager = new SheetsManager({ 
    credentials: require('./credentials.json') 
  });
  await manager.initialize();

  // Create a new spreadsheet
  const newSheet = await manager.createSheet(
    'Company Database', 
    ['Employees', 'Departments', 'Projects'],
    ['collaborator@company.com']
  );

  // List sheets in the spreadsheet
  const sheets = await manager.listSheets(newSheet.id);

  // Add a new sheet
  const budgetSheet = await manager.addSheet(newSheet.id, 'Budget');
}