npm package: @schematize/client-db
Description: Schematize DB Client Library
Author: Benjamin Bytheway
Latest version: 0.6.3

# @schematize/client-db

A client-side database library that provides object-relational mapping (ORM) functionality for UML metamodel instances.  This package extends the Metamodel library with database persistence capabilities, allowing you to save, fetch, and manage instances with automatic change tracking and lazy loading.

## Features

- **Instance Management**: Create, save, delete, and track changes to UML instances
- **Lazy Loading**: Properties are loaded on-demand with caching and expiration
- **Change Tracking**: Automatic detection of dirty instances that need saving
- **Relationship Management**: Handle associations, links, and structured values
- **Query Interface**: Find instances by ID or with complex filters
- **Event System**: Listen to instance changes, saves, and deletions
- **Symbol-based Configuration**: Use symbols to configure credentials and behavior

## Dependencies

- `@schematize/refs`
- `@schematize/instance.js`
- `@schematize/metamodel`
- `@schematize/util-db`

## Exports

### connectionPools

Exported connection pools from `@schematize/util-db` for direct database access if needed.

## Installation

```bash
npm install @schematize/client-db
```

## Usage

### Basic Setup

```javascript
import { 
  attachClassifierMethods, 
  attachInstanceMethodsAndListeners,
  SYMBOL_CREDENTIALS,
  connectionPools
} from '@schematize/client-db';
import { resurrect } from '@schematize/metamodel';

// Load your UML metamodel
const package = await resurrect(metamodelJson);

// Attach database methods to classifiers
attachClassifierMethods({ package });

// Attach instance methods and event listeners
attachInstanceMethodsAndListeners();

// Configure database credentials for a classifier
UserClass[SYMBOL_CREDENTIALS] = {
  user: 'root',
  password: 'password',
};
```

### Finding Instances

```javascript
// Find all users
const users = await UserClass.find({
  filter: {
    symbol: '&&',
    operand: [
      {
        language: ['SQL'],
        body: ['status = "active"']
      }
    ]
  },
  limit: 50
});

// Find user by ID
const user = await UserClass.findById({
  __id__: 'user123',
  include: ['posts', 'profile']
});

// Find related instances
const userPosts = await user.findRelated({
  propertyName: 'posts',
  filter: {
    language: ['SQL'],
    body: ['published = true']
  }
});
```

### Saving Instances

```javascript
// Create new user
const user = new UserClass({
  name: 'John Doe',
  email: 'john@example.com'
});

// Save to database
await user.save();

// Update existing user
user.name = 'Jane Doe';
await user.save(); // Only saves changed attributes
```

### Lazy Loading

```javascript
// Load property on demand
const posts = await user.$('posts');

// Force reload (bypass cache)
const freshPosts = await user.$('posts', { force: true });
```

## API Reference

### Core Functions

#### attachClassifierMethods

Attaches `find` and `findById` methods to UML classifiers.

**Parameters:**
- `package` (Object): UML package containing classifiers

**Usage:**
```javascript
attachClassifierMethods({ package });
```

#### attachInstanceMethodsAndListeners

Attaches instance methods and sets up event listeners for change tracking.

**Usage:**
```javascript
attachInstanceMethodsAndListeners();
```

### Instance Methods

#### find

Finds instances of a classifier with optional filtering and includes.

**Parameters:**
- `__type__` (Object): UML classifier (defaults to `this`)
- `filterInternal` (Boolean): Filter out internal properties (default: true)
- `filteredProperties` (Object): Properties to filter from results
- `filter` (Object): Filter conditions using symbol/operand format
- `limit` (Number): Maximum number of instances to return
- `include` (Array): Related data to include (JSON:API style)
- `before` (Function): Optional callback function executed before the database query
- `log` (Function): Optional logging function for debugging
- `logLine` (Function): Optional function for logging individual query lines

**Returns:**
- `Promise<Array>`: Array of found instances

**Usage:**
```javascript
const instances = await UserClass.find({
  filter: {
    symbol: '&&',
    operand: [
      {
        language: ['SQL'],
        body: ['age >= 18']
      }
    ]
  },
  include: ['posts', 'profile'],
  limit: 100
});
```

#### findById

Finds a single instance by its ID.

**Parameters:**
- `__type__` (Object): UML classifier (defaults to `this`)
- `__id__` (String): Instance ID to find
- `filterInternal` (Boolean): Filter out internal properties (default: true)
- `filteredProperties` (Object): Properties to filter from results
- `filter` (Object): Additional filter conditions
- `include` (Array): Related data to include
- `before` (Function): Optional callback function executed before the database query
- `log` (Function): Optional logging function for debugging
- `logLine` (Function): Optional function for logging individual query lines

**Returns:**
- `Promise<Object>`: Found instance or undefined

**Usage:**
```javascript
const user = await UserClass.findById({
  __id__: 'user123',
  include: ['posts', 'posts.comments']
});
```

#### save

Saves an instance to the database, handling both inserts and updates.

**Parameters:**
- `instance` (Object): Instance to save (defaults to `this`)
- `skipCompositeProperties` (Boolean): Skip saving composite properties.  When true, composite properties (owned relationships) will not be automatically saved during the save operation.  Useful when you want to handle composite saves manually or in a different order.  Default's to false.
- `skipStructuralValues` (Boolean): Skip saving structural values.  When true, structured values (complex objects that are part of the instance) will not be automatically saved.  Use this when you want to handle structured value saves separately.  Default's to false.
- `commitLinksChanged` (Boolean|Object): Commit added links to the database.  When true, commits all added association links.  When an object, should contain `associationEnds` array specifying which association ends to commit.
- `commitLinksRemoved` (Boolean|Object): Commit removed links to the database.  When true, commits all removed association links.  When an object, should contain `associationEnds` array specifying which association ends to commit.
- `parametersProvider` (Function): Optional function that provides additional parameters for cascading save operations.  When provided, this function is called for each nested instance being saved (e.g., structured values, composite properties) and should return an object with additional save parameters.  Useful for dynamically configuring save behavior for nested instances.  **Either/or:** If you pass `parametersProvider`, it overrides any values you passed directly for `skipCompositeProperties`, `skipStructuralValues`, `commitLinksChanged`, and `commitLinksRemoved`... use direct parameters or `parametersProvider` for those options, not both.
- `before` (Function): Optional callback function executed before the database operation
- `after` (Function): Optional callback function executed after the database operation
- `log` (Function): Optional logging function for debugging
- `logLine` (Function): Optional function for logging individual query lines

**Returns:**
- `Promise<void>`

**Usage:**
```javascript
await user.save({
  commitLinksChanged: true,
  commitLinksRemoved: {
    associationEnds: ['oldPosts']
  },
  parametersProvider: ({ instance }) => ({
    // Provide additional parameters for nested saves
    commitLinksChanged: true,
    commitLinksRemoved: true
  })
});
```

**Features:**
- **Change Tracking**: Only saves modified attributes
- **Cascading Saves**: Automatically saves composite properties
- **Link Management**: Handles association links and structured values
- **Event Dispatching**: Fires `client-db:change` events with `type: 'saved'` on successful save

#### delete

Deletes an instance from the database.

**Parameters:**
- `instance` (Object): Instance to delete (defaults to `this`)
- `before` (Function): Optional callback function executed before the database operation
- `after` (Function): Optional callback function executed after the database operation
- `log` (Function): Optional logging function for debugging
- `logLine` (Function): Optional function for logging individual query lines

**Returns:**
- `Promise<void>`

**Usage:**
```javascript
await user.delete({
  before: () => console.log('Deleting user...'),
  after: () => console.log('User deleted')
});
```

#### $

Lazy loads a property value from the database.

**Parameters:**
- `instance` (Object): Instance to load property for (defaults to `this`)
- `propertyName` (String): Name of property to load
- `force` (Boolean): Force reload, bypassing cache (default: false)
- `filter` (Object): Optional filter conditions for the related data
- `filterInternal` (Boolean): Filter out internal properties (default: true)
- `before` (Function): Optional callback function executed before the database query
- `log` (Function): Optional logging function for debugging
- `logLine` (Function): Optional function for logging individual query lines

**Returns:**
- `Promise<any>`: Property value

**Usage:**
```javascript
// Load posts property
const posts = await user.$('posts');

// Force reload
const freshPosts = await user.$('posts', { force: true });
```

**Features:**
- **Caching**: Properties are cached for 60 seconds
- **Promise-based**: Returns promises to prevent duplicate requests
- **Collection Handling**: Properly manages array properties
- **Change Integration**: Integrates with change tracking system

#### findRelated

Finds instances related to this instance through a specific property.

**Parameters:**
- `instance` (Object): Source instance (defaults to `this`)
- `__type__` (Object): Instance type (auto-detected)
- `propertyName` (String): Property name to follow
- `filterInternal` (Boolean): Filter out internal properties (default: true)
- `filteredProperties` (Object): Properties to filter from results
- `filter` (Object): Additional filter conditions
- `limit` (Number): Maximum number of instances to return
- `include` (Array): Related data to include
- `before` (Function): Optional callback function executed before the database query
- `log` (Function): Optional logging function for debugging
- `logLine` (Function): Optional function for logging individual query lines

**Returns:**
- `Promise<Object|Array>`: Related instance(s)

**Usage:**
```javascript
const userPosts = await user.findRelated({
  propertyName: 'posts',
  filter: {
    language: ['SQL'],
    body: ['published = true']
  }
});
```

#### findLinks

Finds association links from an instance.

**Parameters:**
- `instance` (Object): Source instance (defaults to `this`)
- `associationEnds` (Array): Association end names to search
- `before` (Function): Optional callback function executed before the database query
- `log` (Function): Optional logging function for debugging
- `logLine` (Function): Optional function for logging individual query lines

**Returns:**
- `Promise<Array>`: Array of association links

**Usage:**
```javascript
const links = await user.findLinks({
  associationEnds: ['userRoles', 'userGroups'],
  before: () => console.log('Finding links...')
});
```

#### commitLinksChanged

Commits link creations/updates for specified association ends on an instance.

**Parameters:**
- `instance` (Object): Source instance (defaults to `this`)
- `associationEnds` (Array<string>): Association end property names to commit
- `before` (Function): Optional callback function executed before the database operation
- `after` (Function): Optional callback function executed after the database operation
- `log` (Function): Optional logging function for debugging
- `logLine` (Function): Optional function for logging individual query lines

**Returns:**
- `Promise<void>`

**Usage:**
```javascript
await user.commitLinksChanged({
  associationEnds: ['posts', 'comments'],
  before: () => console.log('Committing links...')
});
```

#### commitLinksRemoved

Commits deletions for links that were removed on specified association ends.

**Parameters:**
- `instance` (Object): Source instance (defaults to `this`)
- `associationEnds` (Array<string>): Association end property names to commit
- `before` (Function): Optional callback function executed before the database operation
- `after` (Function): Optional callback function executed after the database operation
- `log` (Function): Optional logging function for debugging
- `logLine` (Function): Optional function for logging individual query lines

**Returns:**
- `Promise<void>`

**Usage:**
```javascript
await user.commitLinksRemoved({
  associationEnds: ['posts', 'comments'],
  before: () => console.log('Removing links...')
});
```
 

#### needsSave

Checks if an instance has unsaved changes and needs to be saved to the database.

**Parameters:**
- `instance` (Object): Instance to check (defaults to `this`)

**Returns:**
- `Boolean`: `true` if the instance needs to be saved, `false` otherwise

**Usage:**
```javascript
const user = new UserClass({ name: 'John' });
console.log(user.needsSave()); // true (new instance)

await user.save();
console.log(user.needsSave()); // false (clean instance)

user.name = 'Jane';
console.log(user.needsSave()); // true (has changes)
```

**What triggers needsSave:**
- **New instances**: Instances that haven't been saved yet
- **Changed attributes**: Modified primitive properties or enumerations
- **Structured values**: Added, changed, or removed composite properties
- **Association links**: Added or removed association relationships

**Features:**
- **Automatic tracking**: No manual intervention required
- **Comprehensive detection**: Covers all types of changes
- **Performance optimized**: Quick boolean check without side effects
- **Event integration**: Triggers `client-db:change` events with `type: 'unclean'` when changes are detected

### Symbols

#### SYMBOL_CREDENTIALS

Symbol for configuring database credentials on classifiers or packages.  By default each of the methods will try to resolve the credentials first by going to the classifier and if not there, it will check on the package of the classifier for the same Symbol.

**Usage:**
```javascript
// classifier
UserClass[SYMBOL_CREDENTIALS] = {
  user: 'root',
  password: 'password',
};
// package
AuthPackage[SYMBOL_CREDENTIALS] = {
  user: 'root',
  password: 'password',
};
```

The `credentials` object contains database connection information required to establish connections to your MySQL database. It supports both read and write database separation for improved performance and scalability.

##### Properties

- `user` (String, required): Database username for authentication
- `password` (String, required): Database password for authentication  
- `host` (String, optional): Primary database host (used as fallback for read/write hosts)
- `hostRead` (String, optional): Read-only database host for query operations
- `hostWrite` (String, optional): Write database host for insert/update/delete operations

##### Usage Examples

```javascript
// Basic credentials with single host
UserClass[SYMBOL_CREDENTIALS] = {
  user: 'root',
  password: 'password',
  host: 'localhost'
};

// Credentials with separate read/write hosts
UserClass[SYMBOL_CREDENTIALS] = {
  user: 'root', 
  password: 'password',
  hostRead: 'read-db.example.com',
  hostWrite: 'write-db.example.com'
};

// Credentials using environment variables
UserClass[SYMBOL_CREDENTIALS] = {
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  hostRead: process.env.DB_HOST_READ,
  hostWrite: process.env.DB_HOST
};
```

##### Connection Behavior

- If `hostRead` is provided, read operations (queries) will use this host
- If `hostWrite` is provided, write operations (inserts/updates/deletes) will use this host  
- If only `host` is provided, all operations will use this host
- If neither `hostRead` nor `hostWrite` is provided, `host` will be used for all operations
- The connection uses MySQL2 with connection pooling and optimized settings for the Schematize platform

#### SYMBOL_NEW

Internal symbol tracking whether an instance is new (not yet saved).

#### SYMBOL_ATTRIBUTES

Internal symbol storing changed attributes.

#### SYMBOL_LINKS_CHANGED

Internal symbol tracking changed association links.

#### SYMBOL_LINKS_REMOVED

Internal symbol tracking removed association links.

#### SYMBOL_LINK

Internal symbol used to store link references in resource objects during link commit operations.

#### SYMBOL_UNLINKED

Internal symbol tracking whether an instance has been unlinked from an association.

#### SYMBOL_DESTROYED

Internal symbol tracking whether an instance has been destroyed.

#### SYMBOL_STRUCTURED_VALUES_CHANGED

Internal symbol tracking changed structured values.

#### SYMBOL_STRUCTURED_VALUES_REMOVED

Internal symbol tracking removed structured values.

#### SYMBOL_OWNER

Internal symbol storing owner information for composite instances.

#### SYMBOL_PROPERTY_EXPIRES

Internal symbol tracking property cache expiration times.

### Constants

#### EXPIRES

Default cache expiration time in milliseconds (60 seconds by default): `60000`

### Event System

The library provides a comprehensive event system for tracking instance changes:

#### Instance Events

- **`change`**: Fired when instance properties change
- **`client-db:change`**: Fired for client-db specific changes
  - `type: 'unclean'`: Instance marked as dirty
  - `type: 'saved'`: Instance successfully saved
- **`instance`**: Fired when new instances are created
- **`destroy`**: Fired when instances are destroyed
- **`get`**: Fired when properties are accessed

#### Collection Events

- **`change`**: Fired when collection items are added/removed

**Usage:**
```javascript
// Listen to instance changes
user.on('change', (event) => {
  console.log('Instance changed:', event.detail);
});

// Listen to save events
user.on('client-db:change', (event) => {
  if (event.detail.type === 'saved') {
    console.log('Instance saved successfully');
  }
});
```

## Advanced Features

### Change Tracking

The library automatically tracks changes to instances:

```javascript
const user = new UserClass({ name: 'John' });
console.log(user.needsSave()); // true (new instance)

await user.save(); // Initial save
console.log(user.needsSave()); // false (clean)

user.name = 'Jane'; // Automatically marked as dirty
console.log(user.needsSave()); // true (has changes)
await user.save(); // Only saves the changed 'name' attribute
```

**Change Detection:**
The `needsSave()` method checks for:
- New instances that haven't been saved
- Modified primitive attributes and enumerations
- Added, changed, or removed structured values
- Added or removed association links

This enables efficient saving by only sending changed data to the database.

**Event Integration:**
```javascript
// Listen for change events
user.on('client-db:change', (event) => {
  if (event.detail.type === 'unclean') {
    console.log('Instance has unsaved changes');
  } else if (event.detail.type === 'saved') {
    console.log('Instance saved successfully');
  }
});
```

### Composite Properties

Handles composite (owned) properties with automatic cascading:

```javascript
const user = new UserClass({ name: 'John' });
const profile = new ProfileClass({ bio: 'Developer' });

user.profile = profile; // Composite relationship
await user.save(); // Automatically saves profile too
```

### Lazy Loading with Caching

Properties are loaded on-demand and cached:

```javascript
// First access - loads from database
const posts = await user.$('posts');

// Subsequent access - uses cache (within 60 seconds)
const cachedPosts = await user.$('posts');

// Force reload - bypasses cache
const freshPosts = await user.$('posts', { force: true });
```

### Association Management

Automatic handling of bidirectional associations:

```javascript
const user = new UserClass({ name: 'John' });
const role = new RoleClass({ name: 'admin' });

// Setting one side automatically updates the other
user.roles = [role]; // role.users will include user
```

## Error Handling

The library provides comprehensive error handling:

```javascript
try {
  const user = await UserClass.findById({ __id__: 'nonexistent' });
} catch (error) {
  console.error('Error:', error.message);
}
```

## Performance Considerations

- **Lazy Loading**: Properties are only loaded when accessed
- **Change Tracking**: Only modified attributes are saved
- **Caching**: Properties are cached for 60 seconds by default
- **Batch Operations**: Multiple saves are batched when possible
- **Connection Pooling**: Uses connection pooling from `@schematize/util-db`

## License

MIT

## Author

Benjamin Bytheway