MiniliveStorage

A unified storage library that combines database, file metadata, and key-value functionality with subscription capabilities, all backed by SQLite.

Features

• Key-Value Store: Store and retrieve key-value pairs with optional expiry and wildcard search
• File Metadata Management: Track files with metadata including descriptions and search capabilities
• Raw SQL Access: Execute queries and schema operations with built-in security
• Migration System: Declarative JSON-based schema migrations with relationships
• Subscription System: Subscribe to real-time changes across all storage types
• SQLite Backend: All data stored in a single SQLite database file
• TypeScript Support: Fully typed API with comprehensive type definitions

Installation

npm install minilive-storage

Quick Start

import MiniliveStorage from 'minilive-storage';

// Initialize with SQLite database path
const storage = new MiniliveStorage('./myapp.db');
await storage.initialize();

// Or with audit logging
const storage = new MiniliveStorage('./myapp.db', (auditEvent) => {
  console.log('Operation:', auditEvent.type, auditEvent.action, auditEvent.details);
});
await storage.initialize();

// Key-Value operations
await storage.setKV('user:123', 'John Doe');
await storage.setKV('session:abc', 'active', { expiresIn: 3600 }); // expires in 1 hour

const users = await storage.getKV('user:*'); // wildcard search
console.log(users); // [{ key: 'user:123', value: 'John Doe', ... }]

// File metadata
const fileId = await storage.addFile(
  'document.pdf', 
  '/uploads/document.pdf',
  'application/pdf',
  1024,
  'Important document'
);

// Raw SQL - Note: User tables automatically get an 'id' field as primary key
await storage.schema('CREATE TABLE posts (title TEXT, content TEXT)');
await storage.query('INSERT INTO posts (title, content) VALUES (?, ?)', ['Hello World', 'My first post']);

// Subscriptions
const subId = storage.sub('kv', 'user:*', (event) => {
  console.log('User data changed:', event);
});

// Clean up
await storage.close();

API Reference

Initialization

const storage = new MiniliveStorage(dbPath: string, auditCallback?: AuditCallback);
await storage.initialize();

Audit Callback

The optional auditCallback parameter allows you to track all operations performed on the storage:

import { AuditEvent } from 'minilive-storage';

const auditCallback = (event: AuditEvent) => {
  console.log(`${event.type} ${event.action}:`, event.details);
  // Log to file, send to monitoring service, etc.
};

const storage = new MiniliveStorage('./app.db', auditCallback);

AuditEvent Interface:

interface AuditEvent {
  type: 'kv' | 'file' | 'db';           // Storage type
  action: 'create' | 'update' | 'delete' | 'read' | 'schema'; // Operation type
  timestamp: number;                     // Unix timestamp
  details: {
    // For DB operations
    query?: string;                      // SQL query
    params?: any[];                      // Query parameters
    // For file operations  
    uuid?: string;                       // File UUID
    filename?: string;                   // Original filename
    // For KV operations
    key?: string;                        // Key name
    value?: string;                      // Value content
    // Common
    affectedRows?: number;               // Number of records affected
    error?: string;                      // Error message if operation failed
  };
}

Key-Value Operations

// Set key-value pair
await storage.setKV(key: string, value: string, options?: SetKVOptions);

// Get by pattern (supports wildcards * and ?)
const records = await storage.getKV(pattern: string);

// Delete by pattern
const deletedCount = await storage.delKV(pattern: string);

// Search by value pattern, optionally filter by key pattern
const results = await storage.searchKV(valuePattern: string, keyPattern?: string);

File Operations

// Add file metadata
const fileId = await storage.addFile(
  originalFilename: string,
  path: string,
  mimetype?: string,
  size?: number,
  description?: string
);

// Get files by ID or pattern
const files = await storage.getFile(idOrPattern: string);

// Update file metadata
await storage.updateFile(id: string, updates: Partial<FileRecord>);

// Delete files by ID or pattern
const deletedCount = await storage.deleteFile(idOrPattern: string);

// Search files by description, optionally filter by key pattern
const files = await storage.searchFiles(descriptionPattern: string, keyPattern?: string);

// List files with pagination
const files = await storage.listFiles(limit?: number, offset?: number);

SQL Operations

// Schema operations (DDL)
await storage.schema(sql: string, params?: any[]);

// Data operations (DML) 
const result = await storage.query(sql: string, params?: any[]);

Automatic Table Management

All user-created tables are automatically prefixed with cus_ and receive an auto-generated id field as the primary key:

• Table Prefixing: Tables are automatically prefixed with cus_ (e.g., users becomes cus_users)
• Automatic ID: Every table gets an id TEXT PRIMARY KEY field automatically
• ID Format: IDs are auto-generated with format {table_prefix}{uuid} (e.g., user123e4567e89b12d3a456426614174000)
• System Tables: Direct access to system tables (sys_kv, sys_files) is not allowed through SQL operations

// Create a table - no need to define id field
await storage.schema('CREATE TABLE users (name TEXT, email TEXT)');

// Insert data - id is auto-generated if not provided
await storage.query('INSERT INTO users (name, email) VALUES (?, ?)', ['John', '[email protected]']);

// Query data - table name is automatically prefixed
const users = await storage.query('SELECT * FROM users'); // Actually queries cus_users
console.log(users[0].id); // e.g., "user123e4567e89b12d3a456426614174000"

// You can provide your own ID if it matches the format
const customId = 'user' + crypto.randomUUID().replace(/-/g, '');
await storage.query('INSERT INTO users (id, name, email) VALUES (?, ?, ?)', 
  [customId, 'Jane', '[email protected]']);

FlexQuery - Universal Query System

FlexQuery provides a unified JSON-based query interface that works across all storage types (KV, Files, and SQL tables) using a TypePersist-compatible format.

Key Features

• Universal Interface: Same query syntax works across KV store, file metadata, and SQL tables
• Type Safety: Full TypeScript support using existing migration field types
• Complex Queries: Support for AND/OR conditions, comparisons, sorting, pagination
• CRUD Operations: Universal insert, update, delete operations
• Schema Introspection: Get complete schema information across all storage types
• User-Friendly: Simple table names ($kv, $files, users) - no internal prefixes exposed

Basic Querying

// Query KV store
const kvRecords = await storage.flexquery({
  table: [{ 
    table: '$kv',
    query: { field: 'key', cmp: 'like', value: 'user:' }
  }],
  sort: [{ fieldId: 'created_at', direction: 'desc' }],
  limit: 10
});

// Query files with complex conditions
const imageFiles = await storage.flexquery({
  table: [{
    table: '$files',
    query: {
      And: [
        { field: 'mimetype', cmp: 'like', value: 'image/' },
        { field: 'size', cmp: 'lt', value: 1000000 }
      ]
    }
  }],
  field: { $files: ['id', 'original_filename', 'size'] }
});

// Query SQL table with pagination
const users = await storage.flexquery({
  table: [{ 
    table: 'users',
    query: { field: 'age', cmp: 'gte', value: 18 }
  }],
  limit: 10,
  page: 2
});

Universal CRUD Operations

// Insert data
const kvId = await storage.insert(
  { table: '$kv' },
  { key: 'user:123', value: 'John Doe' }
);

const fileId = await storage.insert(
  { table: '$files' },
  { 
    original_filename: 'avatar.jpg',
    path: '/uploads/avatar.jpg',
    mimetype: 'image/jpeg'
  }
);

const userId = await storage.insert(
  { table: 'users' },
  { name: 'John Doe', email: '[email protected]' }
);

// Update data with conditions
const updated = await storage.update(
  { table: 'users' },
  { status: 'active' },
  {
    table: [{ 
      table: 'users',
      query: { field: 'email', cmp: 'eq', value: '[email protected]' }
    }]
  }
);

// Delete data by IDs or query
const deleted = await storage.delete(
  { table: '$kv' },
  ['user:temp1', 'user:temp2'] // specific IDs
);

const deletedCount = await storage.delete(
  { table: 'users' },
  undefined, // no specific IDs
  {
    table: [{
      table: 'users',
      query: { field: 'status', cmp: 'eq', value: 'inactive' }
    }]
  }
);

Schema Information

// Get complete schema for all storage types
const schema = await storage.getSchema();

// Get specific tables
const specificSchema = await storage.getSchema(['$kv', 'users', 'products']);

// Inspect field types and definitions
console.log(schema.$kv.fields); // KV store fields
console.log(schema.users.type); // 'sql'
console.log(schema.users.fields); // User table fields

SQL Table Joins

FlexQuery supports hierarchical joins between SQL tables using foreign key relationships.

Join Requirements:

• Only SQL tables can be joined (no $kv or $files in joins)
• Foreign keys must follow camelCase naming: {parentTable}Id
• Example: users table → usersId foreign key in child table

Basic Two-Table Join:

// Create tables with foreign key relationship
await storage.schema(`CREATE TABLE users (name TEXT, email TEXT)`);
await storage.schema(`CREATE TABLE posts (title TEXT, content TEXT, usersId TEXT)`);

// Insert data
const userId = await storage.insert({ table: 'users' }, { name: 'John Doe', email: '[email protected]' });
await storage.insert({ table: 'posts' }, { title: 'My Post', content: 'Post content', usersId: userId });

// Join query
const results = await storage.flexquery({
  table: [
    { table: 'users' },
    { table: 'posts' }
  ]
});

// Result: Hierarchical structure with nested children
[
  {
    id: 'user123...',
    name: 'John Doe',
    email: '[email protected]',
    posts: [
      {
        id: 'post456...',
        title: 'My Post',
        content: 'Post content',
        usersId: 'user123...'
      }
    ]
  }
]

Three-Table Hierarchical Join:

// users -> posts -> comments
await storage.schema(`CREATE TABLE users (name TEXT)`);
await storage.schema(`CREATE TABLE posts (title TEXT, usersId TEXT)`);
await storage.schema(`CREATE TABLE comments (text TEXT, author TEXT, postsId TEXT)`);

const results = await storage.flexquery({
  table: [
    { table: 'users' },
    { table: 'posts' },
    { table: 'comments' }
  ]
});

// Result: Three-level nesting
[
  {
    name: 'John Doe',
    posts: [
      {
        title: 'My Post',
        comments: [
          { text: 'Great post!', author: 'Alice' },
          { text: 'Thanks for sharing', author: 'Bob' }
        ]
      }
    ]
  }
]

Join with WHERE Conditions:

// Apply conditions to any level of the join
const results = await storage.flexquery({
  table: [
    { 
      table: 'users',
      query: { field: 'country', cmp: 'eq', value: 'USA' }
    },
    { 
      table: 'posts',
      query: { field: 'status', cmp: 'eq', value: 'published' }
    }
  ]
});

Join with Field Selection:

// Select specific fields from each table while preserving join structure
const results = await storage.flexquery({
  table: [
    { table: 'users' },
    { table: 'posts' },
    { table: 'comments' }
  ],
  field: {
    users: ['name', 'country'],
    posts: ['title', 'status'],
    comments: ['text', 'author']
  }
});

// Result: Only selected fields, but join structure preserved
[
  {
    name: 'John Doe',
    country: 'USA',
    posts: [
      {
        title: 'My Post',
        status: 'published',
        comments: [
          { text: 'Great post!', author: 'Alice' }
        ]
      }
    ]
  }
]

Query Syntax Reference

Table Names:

• $kv - Key-value storage (single table queries only)
• $files - File metadata (single table queries only)
• tablename - SQL tables (e.g., users, products) - supports joins

Query Operators:

• eq / neq - Equals / Not equals
• gt / gte / lt / lte - Comparisons
• like / nlike - Contains / Not contains
• in / nin - In array / Not in array

Complex Conditions:

{
  And: [
    { field: 'age', cmp: 'gte', value: 18 },
    {
      Or: [
        { field: 'role', cmp: 'eq', value: 'admin' },
        { field: 'role', cmp: 'eq', value: 'moderator' }
      ]
    }
  ]
}

Migrations

The migration system provides a declarative way to define and evolve your database schema:

const migrations = [
  // Create users table
  {
    table: 'users',
    action: 'create',
    fields: [
      { name: 'email', type: 'Text', action: 'create', unique: true },
      { name: 'name', type: 'Text', action: 'create' },
      { name: 'created_at', type: 'CreatedAt', action: 'create' }
    ]
  },
  // Create posts table  
  {
    table: 'posts',
    action: 'create',
    fields: [
      { name: 'title', type: 'Text', action: 'create' },
      { name: 'content', type: 'Text', action: 'create' }
    ]
  },
  // Create relationship: user has many posts
  {
    connect: ['users', 'posts'],
    type: '1-n',
    action: 'create',
    cascade: ['delete']
  }
];

await storage.migrate(migrations);

See MIGRATIONS.md for detailed documentation on the migration system.

Subscriptions

// Subscribe to changes
const subscriptionId = storage.sub(
  type: 'kv' | 'file' | 'sql',
  pattern: string,
  callback: (event: SubscriptionEvent) => void,
  table?: string // for SQL subscriptions
);

// Unsubscribe
storage.unsub(subscriptionId: string);

// Get active subscriptions
const active = storage.getActiveSubscriptions();

Database Schema

The library automatically creates three system tables:

sys_kv - Key-Value Store

• key (TEXT PRIMARY KEY)
• value (TEXT NOT NULL)
• expires_at (INTEGER) - Unix timestamp
• created_at (INTEGER)
• updated_at (INTEGER)

sys_files - File Metadata

• id (TEXT PRIMARY KEY) - UUID
• original_filename (TEXT NOT NULL)
• path (TEXT NOT NULL)
• mimetype (TEXT)
• size (INTEGER)
• description (TEXT)
• created_at (INTEGER)
• updated_at (INTEGER)

sys_migrations - Database Migrations

• id (INTEGER PRIMARY KEY AUTOINCREMENT)
• migration (TEXT NOT NULL) - JSON migration data
• created_at (INTEGER) - Unix timestamp

Wildcard Patterns

The library supports shell-style wildcards:

• * - matches any number of characters
• ? - matches exactly one character

Examples:

• user:* - matches user:123, user:abc, etc.
• file?.txt - matches file1.txt, filea.txt, etc.

Expiry and Cleanup

Key-value pairs can have optional expiry times. A background worker automatically cleans up expired keys every 60 seconds.

await storage.setKV('temp', 'data', { expiresIn: 300 }); // expires in 5 minutes

Subscriptions

Subscribe to real-time changes across all storage types:

// KV subscriptions
storage.sub('kv', 'user:*', (event) => {
  console.log(`KV ${event.type}:`, event.data);
});

// File subscriptions  
storage.sub('file', '*', (event) => {
  console.log(`File ${event.type}:`, event.data);
});

// SQL subscriptions (table-specific)
storage.sub('sql', 'posts', (event) => {
  console.log(`SQL ${event.type} on posts:`, event.data);
}, 'posts');

Error Handling

The library throws descriptive errors for invalid operations:

try {
  await storage.schema('SELECT * FROM users'); // Invalid - not a schema operation
} catch (error) {
  console.error(error.message); // "Schema function only accepts DDL operations"
}

try {
  await storage.query('SELECT * FROM sys_kv'); // Invalid - system table access
} catch (error) {
  console.error(error.message); // "Access to system table 'sys_kv' is not allowed"
}

try {
  await storage.schema('CREATE TABLE users (id TEXT PRIMARY KEY, name TEXT)'); // Invalid - user-defined id
} catch (error) {
  console.error(error.message); // "Cannot define an id column. It will be created automatically."
}

Audit Logging Examples

Track all database operations for compliance, debugging, or monitoring:

import MiniliveStorage, { AuditEvent } from 'minilive-storage';
import fs from 'fs';

// Log to file
const auditLogger = (event: AuditEvent) => {
  const logEntry = {
    timestamp: new Date(event.timestamp).toISOString(),
    operation: `${event.type}:${event.action}`,
    ...event.details
  };
  fs.appendFileSync('./audit.log', JSON.stringify(logEntry) + '\n');
};

const storage = new MiniliveStorage('./app.db', auditLogger);

// All operations will be logged
await storage.setKV('user:123', 'John Doe');
// Logs: {"timestamp":"2023-12-01T10:30:00.000Z","operation":"kv:create","key":"user:123","value":"John Doe","affectedRows":1}

await storage.addFile('doc.pdf', '/uploads/doc.pdf');  
// Logs: {"timestamp":"2023-12-01T10:30:01.000Z","operation":"file:create","uuid":"550e8400-e29b-41d4-a716-446655440000","filename":"doc.pdf","affectedRows":1}

await storage.query('SELECT * FROM posts WHERE title LIKE ?', ['%Hello%']);
// Logs: {"timestamp":"2023-12-01T10:30:02.000Z","operation":"db:read","query":"SELECT * FROM posts WHERE title LIKE ?","params":["%Hello%"],"affectedRows":1}

Security and Compliance Use Cases:

• Track data access for GDPR/compliance audits
• Monitor database schema changes
• Log file uploads and modifications
• Debug performance issues
• Detect unauthorized access patterns

Error Tracking:

const errorAuditLogger = (event: AuditEvent) => {
  if (event.details.error) {
    console.error(`Operation failed: ${event.type}:${event.action}`, event.details.error);
    // Send to error tracking service
  }
};

License

MIT