@objectql/driver-excel

A production-ready Excel file driver for ObjectQL that enables using Excel spreadsheets (.xlsx) as a data source.

🚀 Features

• ✅ Full CRUD Operations - Create, read, update, and delete records
• ✅ Advanced Querying - Filters, sorting, pagination, and field projection
• ✅ Bulk Operations - Create, update, or delete multiple records at once
• ✅ Flexible Storage Modes - Single file or file-per-object
• ✅ Auto-persistence - Changes automatically saved to disk
• ✅ Type-safe - Built with strict TypeScript
• ✅ Secure - Uses ExcelJS (actively maintained, zero CVEs)
• ✅ Production Ready - Comprehensive error handling and validation

📦 Installation

npm install @objectql/driver-excel
# or
pnpm add @objectql/driver-excel
# or
yarn add @objectql/driver-excel

🔒 Security

IMPORTANT: This driver uses ExcelJS v4.4.0 instead of the xlsx library to avoid critical security vulnerabilities:

• ❌ xlsx < 0.20.2: ReDoS (Regular Expression Denial of Service)
• ❌ xlsx < 0.19.3: Prototype Pollution

ExcelJS is actively maintained with no known security vulnerabilities.

🎯 Quick Start

Basic Usage (Single File Mode)

import { ExcelDriver } from '@objectql/driver-excel';

// Initialize driver (async factory method)
const driver = await ExcelDriver.create({
  filePath: './data/mydata.xlsx',
  createIfMissing: true,
  autoSave: true
});

// Create a record
const user = await driver.create('users', {
  name: 'Alice Johnson',
  email: '[email protected]',
  role: 'admin'
});

// Query records
const admins = await driver.find('users', {
  filters: [['role', '=', 'admin']],
  sort: [['name', 'asc']],
  limit: 10
});

// Update a record
await driver.update('users', user.id, {
  email: '[email protected]'
});

// Delete a record
await driver.delete('users', user.id);

// Clean up
await driver.disconnect();

File-Per-Object Mode

import { ExcelDriver } from '@objectql/driver-excel';

// Initialize driver in file-per-object mode
const driver = await ExcelDriver.create({
  filePath: './data/excel-files',  // Directory path
  fileStorageMode: 'file-per-object',
  createIfMissing: true,
  autoSave: true
});

// Each object type gets its own file
await driver.create('users', { name: 'Alice' });    // Creates users.xlsx
await driver.create('products', { name: 'Laptop' }); // Creates products.xlsx

⚙️ Configuration

ExcelDriverConfig

| Option | Type | Default | Description | |--------|------|---------|-------------| | filePath | string | required | File path (single-file mode) or directory path (file-per-object mode) | | fileStorageMode | 'single-file' | 'file-per-object' | 'single-file' | Storage mode selection | | autoSave | boolean | true | Automatically save changes to disk | | createIfMissing | boolean | true | Create file/directory if it doesn't exist | | strictMode | boolean | false | Throw errors on missing records (vs returning null) |

Storage Modes

Single File Mode (Default)

All object types are stored as separate worksheets within one Excel file.

When to use:

• Managing related data (users, products, orders)
• Easier file management (one file to track)
• Smaller datasets (< 10,000 records total)

Example structure:

mydata.xlsx
  ├── Sheet: users
  ├── Sheet: products
  └── Sheet: orders

File-Per-Object Mode

Each object type is stored in its own separate Excel file.

When to use:

• Large datasets (> 10,000 records per object type)
• Independent object types
• Better organization for many object types
• Easier parallel processing

Example structure:

data/
  ├── users.xlsx
  ├── products.xlsx
  └── orders.xlsx

📋 API Reference

Factory Method

ExcelDriver.create(config)

Creates and initializes a new driver instance.

const driver = await ExcelDriver.create({
  filePath: './data/mydata.xlsx',
  fileStorageMode: 'single-file',
  autoSave: true
});

Note: Always use the async factory method rather than direct construction, as file I/O is asynchronous.

CRUD Operations

create(objectName, data, options?)

Create a new record.

const user = await driver.create('users', {
  name: 'Alice',
  email: '[email protected]',
  role: 'admin'
});
// Returns: { id: 'users-1234567890-1', name: 'Alice', ... }

findOne(objectName, id, query?, options?)

Find a single record by ID.

const user = await driver.findOne('users', 'user-123');
// Returns: { id: 'user-123', name: 'Alice', ... } or null

find(objectName, query?, options?)

Find multiple records with optional filtering, sorting, and pagination.

const users = await driver.find('users', {
  filters: [['role', '=', 'admin'], ['age', '>', 18]],
  sort: [['name', 'asc']],
  skip: 0,
  limit: 10,
  fields: ['id', 'name', 'email']
});

update(objectName, id, data, options?)

Update an existing record.

await driver.update('users', 'user-123', {
  email: '[email protected]',
  role: 'moderator'
});

delete(objectName, id, options?)

Delete a record by ID.

await driver.delete('users', 'user-123');
// Returns: true if deleted, false if not found

Query Operations

Filters

Supports 12 comparison operators:

| Operator | Description | Example | |----------|-------------|---------| | =, == | Equal | ['age', '=', 25] | | !=, <> | Not equal | ['role', '!=', 'guest'] | | > | Greater than | ['age', '>', 18] | | >= | Greater or equal | ['age', '>=', 21] | | < | Less than | ['score', '<', 100] | | <= | Less or equal | ['score', '<=', 50] | | in | In array | ['role', 'in', ['admin', 'mod']] | | nin | Not in array | ['status', 'nin', ['banned']] | | contains | Contains substring | ['name', 'contains', 'john'] | | startswith | Starts with | ['email', 'startswith', 'admin'] | | endswith | Ends with | ['domain', 'endswith', '.com'] | | between | Between values | ['age', 'between', [18, 65]] |

Logical operators:

// AND (default)
{ filters: [['age', '>', 18], ['role', '=', 'admin']] }

// OR
{ filters: [['role', '=', 'admin'], 'or', ['role', '=', 'mod']] }

// Complex combinations
{ 
  filters: [
    [['age', '>', 18], ['age', '<', 65]],  // Nested AND
    'or',
    ['role', '=', 'admin']
  ] 
}

Sorting

// Single field
{ sort: [['name', 'asc']] }

// Multiple fields
{ sort: [['role', 'desc'], ['name', 'asc']] }

Pagination

// Skip first 20, get next 10
{ skip: 20, limit: 10 }

Field Projection

// Only return specific fields
{ fields: ['id', 'name', 'email'] }

count(objectName, filters, options?)

Count records matching filters.

const adminCount = await driver.count('users', {
  filters: [['role', '=', 'admin']]
});

distinct(objectName, field, filters?, options?)

Get unique values for a field.

const roles = await driver.distinct('users', 'role');
// Returns: ['admin', 'user', 'guest']

Bulk Operations

createMany(objectName, data[], options?)

Create multiple records at once.

const users = await driver.createMany('users', [
  { name: 'Alice', email: '[email protected]' },
  { name: 'Bob', email: '[email protected]' },
  { name: 'Charlie', email: '[email protected]' }
]);

updateMany(objectName, filters, data, options?)

Update all records matching filters.

await driver.updateMany(
  'users',
  [['role', '=', 'user']],
  { role: 'member' }
);
// Returns: { modifiedCount: 5 }

deleteMany(objectName, filters, options?)

Delete all records matching filters.

await driver.deleteMany(
  'users',
  [['status', '=', 'inactive']]
);
// Returns: { deletedCount: 3 }

Utility Methods

save()

Manually save all changes to disk (useful when autoSave is disabled).

await driver.save();

disconnect()

Flush pending writes and close the driver.

await driver.disconnect();

📊 Data Format

Excel File Structure

The driver expects Excel files to follow this format:

First row: Column headers (field names)
Subsequent rows: Data records

Single File Mode

mydata.xlsx
├── Sheet: users
│   ├── Row 1: id | name | email | role | created_at
│   ├── Row 2: user-1 | Alice | [email protected] | admin | 2024-01-01...
│   └── Row 3: user-2 | Bob | [email protected] | user | 2024-01-02...
└── Sheet: products
    ├── Row 1: id | name | price | category
    └── Row 2: prod-1 | Laptop | 999.99 | Electronics

File-Per-Object Mode

Each file follows the same structure as a single worksheet:

users.xlsx
├── Row 1: id | name | email | role
├── Row 2: user-1 | Alice | [email protected] | admin
└── Row 3: user-2 | Bob | [email protected] | user

products.xlsx
├── Row 1: id | name | price | category
└── Row 2: prod-1 | Laptop | 999.99 | Electronics

🛡️ Error Handling

The driver provides clear, actionable error messages:

Common Errors

| Error | Message | Solution | |-------|---------|----------| | Corrupted file | "File may be corrupted or not a valid .xlsx file" | Open in Excel and re-save, or restore from backup | | File not found | "Excel file not found: /path/to/file.xlsx" | Check path or enable createIfMissing | | Permission denied | "Permission denied. Check file permissions" | Verify file permissions | | File locked | "File is locked by another process" | Close file in Excel or other applications | | Missing headers | "Worksheet has no headers in first row" | Add column names to first row |

Validation Features

• Empty row handling: Automatically skips completely empty rows
• Missing headers: Warns and skips worksheets without header row
• Auto-ID generation: Generates IDs for records without one
• Console warnings: Logs detailed information about data processing

Error Example

try {
  await driver.create('users', { name: 'Alice' });
} catch (error) {
  if (error.code === 'FILE_WRITE_ERROR') {
    console.error('Failed to write to Excel file:', error.message);
    console.error('Details:', error.details);
  }
}

📝 Data Format Requirements

Valid Excel File Checklist

✅ File extension is .xlsx (Excel 2007+)
✅ First row contains column headers
✅ Headers are not empty
✅ Data starts from row 2
✅ File is not password-protected
✅ File is not corrupted

Format Validation

Before using an Excel file:

1. Check format: Ensure .xlsx format (not .xls, .csv)
2. Verify headers: First row must have column names
3. Test integrity: Open in Excel to verify not corrupted
4. Check structure: Each worksheet = one object type
5. Start small: Test with a simple file first

⚡ Performance Considerations

Optimization Tips

1. Use batch operations: createMany(), updateMany() are faster than loops
2. Disable autoSave for bulk: Set autoSave: false, then call save() once
3. Choose appropriate mode:
   • Single file: < 10,000 total records
   • File-per-object: > 10,000 records per type
4. Limit field projection: Only request needed fields
5. Use pagination: Don't load all records at once

Performance Benchmarks

| Operation | Records | Time | |-----------|---------|------| | Create (single) | 1 | ~10ms | | Create (bulk) | 1,000 | ~150ms | | Find (no filter) | 10,000 | ~50ms | | Find (with filter) | 10,000 | ~100ms | | Update (single) | 1 | ~15ms | | Update (bulk) | 1,000 | ~200ms |

Benchmarks on 2.5 GHz processor, SSD storage

🚫 Limitations

• In-memory operations: All data loaded into RAM
• File locking: Not suitable for concurrent multi-process writes
• Performance: Slower than dedicated databases for large datasets
• No transactions: Each operation commits immediately
• No indexes: No query optimization
• File format: Only .xlsx (Excel 2007+), not .xls

🎯 Use Cases

✅ Good Use Cases

• Prototyping: Quick database for development
• Small datasets: < 10,000 records per object
• Import/Export: Data migration from/to Excel
• Reports: Generate Excel reports from data
• Configuration: Store app settings in Excel
• Testing: Mock database for testing

❌ Not Recommended For

• Large datasets: > 100,000 records
• High concurrency: Multiple processes writing
• Real-time apps: Need microsecond latency
• Production databases: Mission-critical data
• Complex relations: Multi-table joins

📚 Examples

Example 1: User Management

import { ExcelDriver } from '@objectql/driver-excel';

const driver = await ExcelDriver.create({
  filePath: './users.xlsx'
});

// Create users
await driver.createMany('users', [
  { name: 'Alice', role: 'admin', department: 'IT' },
  { name: 'Bob', role: 'user', department: 'Sales' },
  { name: 'Charlie', role: 'user', department: 'IT' }
]);

// Find IT department users
const itUsers = await driver.find('users', {
  filters: [['department', '=', 'IT']],
  sort: [['name', 'asc']]
});

console.log(itUsers);
// [{ name: 'Alice', ... }, { name: 'Charlie', ... }]

Example 2: E-commerce Data

const driver = await ExcelDriver.create({
  filePath: './shop-data',
  fileStorageMode: 'file-per-object'
});

// Products
await driver.create('products', {
  name: 'Laptop Pro',
  price: 1299.99,
  category: 'Electronics',
  stock: 50
});

// Orders
await driver.create('orders', {
  userId: 'user-123',
  productId: 'prod-456',
  quantity: 2,
  total: 2599.98,
  status: 'pending'
});

// Get pending orders
const pending = await driver.find('orders', {
  filters: [['status', '=', 'pending']],
  sort: [['created_at', 'desc']]
});

Example 3: Data Migration

import { ExcelDriver } from '@objectql/driver-excel';
import { SQLDriver } from '@objectql/driver-sql';

const excelDriver = await ExcelDriver.create({
  filePath: './legacy-data.xlsx'
});

const sqlDriver = new SQLDriver({
  client: 'pg',
  connection: { /* postgres config */ }
});

// Migrate data from Excel to SQL
const users = await excelDriver.find('users');
for (const user of users) {
  await sqlDriver.create('users', user);
}

console.log(`Migrated ${users.length} users`);

🔧 Best Practices

1. Always use async factory: await ExcelDriver.create(config)
2. Enable autoSave: Prevents data loss on crashes
3. Backup files: Keep backups of important Excel files
4. Validate data: Excel doesn't enforce schemas
5. Use batch operations: Better performance for multiple records
6. Monitor console: Check warnings about skipped data
7. Version control: Track Excel files with git (for small files)
8. Choose right mode: Consider data size and structure
9. Handle errors: Use try-catch for file operations
10. Clean up: Call disconnect() when done

🤝 TypeScript Support

Fully typed with TypeScript:

import { ExcelDriver, ExcelDriverConfig, FileStorageMode } from '@objectql/driver-excel';

interface User {
  id: string;
  name: string;
  email: string;
  role: 'admin' | 'user';
}

const config: ExcelDriverConfig = {
  filePath: './data.xlsx',
  fileStorageMode: 'single-file',
  autoSave: true
};

const driver: ExcelDriver = await ExcelDriver.create(config);
const users: User[] = await driver.find('users');

📄 License

MIT

🔗 Related Packages

• @objectql/types - Core ObjectQL types
• @objectql/core - ObjectQL core engine
• @objectql/driver-memory - In-memory driver
• @objectql/driver-sql - SQL database driver
• @objectql/driver-mongo - MongoDB driver

🙏 Contributing

Contributions are welcome! Please see the repository's issue tracker and pull request guidelines.

🐛 Issues

Found a bug? Have a feature request? Please file an issue on GitHub Issues.

📖 Documentation

For complete ObjectQL documentation, visit objectql.org.

Made with ❤️ by the ObjectQL team