Production-ready Excel file driver for importing, exporting, and using Excel spreadsheets as a data source

Excel Driver

A production-ready driver that enables using Excel spreadsheets (.xlsx) as a data source for ObjectQL. Perfect for data import/export, prototyping, and applications that need Excel file integration.

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

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.

Installation

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

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: 'alice@example.com',
  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: 'alice.new@example.com'
});

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

// Clean up
await driver.disconnect();

File-Per-Object Mode

// 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: 'alice@example.com',
  role: 'admin'
});
// Returns: { id: 'users-1234567890-1', name: 'Alice', ... }

`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']
});

`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

`update(objectName, id, data, options?)`

Update an existing record.

await driver.update('users', 'user-123', {
  email: 'newemail@example.com',
  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

Supported 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: 'alice@example.com' },
  { name: 'Bob', email: 'bob@example.com' },
  { name: 'Charlie', email: 'charlie@example.com' }
]);

`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();

mydata.xlsx
├── Sheet: users
│   ├── Row 1: id | name | email | role | created_at
│   ├── Row 2: user-1 | Alice | alice@example.com | admin | 2024-01-01...
│   └── Row 3: user-2 | Bob | bob@example.com | 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 | alice@example.com | admin
└── Row 3: user-2 | Bob | bob@example.com | user

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

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

Performance Considerations

Optimization Tips

Use batch operations: createMany(), updateMany() are faster than loops
Disable autoSave for bulk: Set autoSave: false, then call save() once
Choose appropriate mode:
- Single file: < 10,000 total records
- File-per-object: > 10,000 records per type
Limit field projection: Only request needed fields
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

Examples

Example 1: Data Import from Excel

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`);

Example 2: Generate Excel Reports

const driver = await ExcelDriver.create({
  filePath: './reports/sales-report.xlsx',
  autoSave: true
});

// Generate report data
const salesData = generateSalesData();

// Write to Excel
await driver.createMany('sales', salesData);

console.log('Report generated: sales-report.xlsx');

Best Practices

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

Driver Overview
File System Driver - Similar JSON-based storage
Data Import/Export Guide

Excel Driver

On this page