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

# @schematize/util-db

Database utility functions for the Schematize platform.  This package provides comprehensive database operations for UML metamodel instances, including CRUD operations, relationship management, and secure SQL generation with automatic inheritance handling.

## Features

- **Instance Management**: Create, read, update, and delete UML instances
- **Relationship Handling**: Manage associations and links between instances
- **Inheritance Support**: Automatic handling of UML inheritance hierarchies
- **Secure SQL Generation**: SQL injection protection with parameterized queries
- **Filter Support**: Complex filtering with value specifications
- **Include Support**: Fetch related instances and associations
- **MySQL Integration**: Built on mysql2 with connection pooling

## Dependencies

- `@schematize/instance.js`: Instance management and utilities
- `@schematize/metamodel`: UML metamodel operations
- `@schematize/refs`: Reference utilities for cross-platform compatibility
- `mysql2`: MySQL database driver

## Installation

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

## Usage

```javascript
import { 
  fetchInstances, 
  saveInstance, 
  deleteInstance,
  fetchLinks,
  saveLink,
  deleteLink 
} from '@schematize/util-db';

// Fetch instances with filtering
const users = await fetchInstances({
  credentials: { host: 'localhost', user: 'root', hostRead: 'db_host_here' },
  classifier: UserClass,
  filter: { name: 'John' },
  limit: 10
});

// Save a new instance
const newUser = await saveInstance({
  credentials: dbCredentials,
  instance: new User({ name: 'Jane', email: 'jane@example.com' }),
  owner: currentUser
});
```

## API Reference

### Core Database Operations

#### credentials parameter

Common among each of these methods is the `credentials` parameter.  These are the details of the parameter: 

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
const credentials = {
  user: 'root',
  password: 'password',
  host: 'localhost'
};

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

// Credentials using environment variables
const 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


#### fetchInstances

Fetches instances of a classifier from the database with support for filtering, inheritance, and pagination.

##### Parameters

- `credentials` (Object): Database connection credentials
- `classifier` (Object): UML classifier to fetch instances for
- `filterInternal` (Boolean): Filter out internal properties (default: true)
- `filteredProperties` (Object): Properties to filter from results
- `filter` (Object): Filter conditions using symbol/operand format (e.g., `{ symbol: '&&', operand: [...] }`)
- `classifierFilters` (Object): Specific filters for individual classifiers using `{ language: ['SQL'], body: ['condition'] }` format
- `limit` (Number): Maximum number of instances to return
- `before` (Function): Hook function called before execution
- `returnType` (String): Return format - 'object' or 'instance' (default: 'object')
- `log` (Function): Logging function
- `logLine` (Function): Line-by-line logging function

##### Returns

- `Promise<Object>`: Fetched instances as objects or instances

##### Usage

```javascript
const instances = await fetchInstances({
  credentials: {
    user: 'root',
    password: 'password',
    hostRead: 'db_host_here'
  },
  classifier: UserClass,
  filter: {
    symbol: '&&',
    operand: [
      {
        language: ['SQL'],
        body: ['name LIKE "%John%"']
      },
      {
        language: ['SQL'],
        body: ['age >= 18']
      }
    ]
  },
  classifierFilters: {
    [UserClass.__id__]: {
      language: ['SQL'],
      body: ['status = "active"']
    }
  },
  limit: 50,
  returnType: 'object'
});
```

#### saveInstance

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

##### Parameters

- `credentials` (Object): Database connection credentials
- `instance` (Object): Instance to save
- `owner` (Object): Owner of the instance
- `returnType` (String): Return format (default: 'object')
- `expectExists` (Boolean): Whether instance should already exist
- `before` (Function): Hook function called before save
- `after` (Function): Hook function called after save
- `log` (Function): Logging function
- `logLine` (Function): Line-by-line logging function

##### Returns

- `Promise<Object>`: Saved instance

##### Usage

```javascript
const savedUser = await saveInstance({
  credentials: dbCredentials,
  instance: new User({
    name: 'John Doe',
    email: 'john@example.com',
    age: 30
  }),
  owner: currentUser,
  before: (instance) => {
    instance.createdAt = new Date();
  }
});
```

#### deleteInstance

Deletes an instance from the database, including all related links.

##### Parameters

- `credentials` (Object): Database connection credentials
- `instance` (Object): Instance to delete
- `before` (Function): Hook function called before deletion
- `log` (Function): Logging function
- `logLine` (Function): Line-by-line logging function

##### Returns

- `Promise<void>`: Promise that resolves when deletion is complete

##### Usage

```javascript
await deleteInstance({
  credentials: dbCredentials,
  instance: userToDelete,
  before: (instance) => {
    console.log(`Deleting user: ${instance.name}`);
  }
});
```

### Relationship Operations

#### fetchLinks

Fetches association links between instances.

##### Parameters

- `credentials` (Object): Database connection credentials
- `classifier` (Object): Association classifier to fetch links for
- `filteredProperties` (Object): Properties to filter from results
- `returnType` (String): Return format (default: 'object')
- `filter` (Object): Filter conditions
- `classifierFilters` (Object): Specific filters for classifiers
- `before` (Function): Hook function called before execution
- `log` (Function): Logging function
- `logLine` (Function): Line-by-line logging function

##### Returns

- `Promise<Object|Array>`: Fetched links

##### Usage

```javascript
const userRoles = await fetchLinks({
  credentials: dbCredentials,
  classifier: UserRoleAssociation,
  filter: {
    symbol: '&&',
    operand: [
      {
        language: ['SQL'],
        body: [`user__id__ = ${v(userId)}`]
      },
      {
        language: ['SQL'],
        body: ['role.name = "admin"']
      }
    ]
  }
});
```

#### saveLink

Saves an association link between instances.

##### Parameters

- `credentials` (Object): Database connection credentials
- `instance` (Object): Link instance to save
- `returnType` (String): Return format (default: 'object')
- `expectExists` (Boolean): Whether link should already exist
- `before` (Function): Hook function called before save
- `after` (Function): Hook function called after save
- `log` (Function): Logging function
- `logLine` (Function): Line-by-line logging function

##### Returns

- `Promise<Object>`: Saved link

##### Usage

```javascript
const userRole = await saveLink({
  credentials: dbCredentials,
  instance: new UserRoleAssociation({
    user: userInstance,
    role: adminRole,
    assignedBy: currentUser
  })
});
```

#### deleteLink

Deletes an association link between instances.

##### Parameters

- `credentials` (Object): Database connection credentials
- `instance` (Object): Link instance to delete
- `before` (Function): Hook function called before deletion
- `log` (Function): Logging function
- `logLine` (Function): Line-by-line logging function

##### Returns

- `Promise<void>`: Promise that resolves when deletion is complete

##### Usage

```javascript
await deleteLink({
  credentials: dbCredentials,
  instance: userRoleLink
});
```

### Related Data Operations

#### fetchRelated

Fetches instances related to a given instance through a specific property.

##### Parameters

- `credentials` (Object): Database connection credentials
- `instance` (Object): Source instance
- `propertyName` (String): Property name to fetch related instances for
- `returnType` (String): Return format (default: 'object')
- `filteredProperties` (Object): Properties to filter from results
- `filter` (Object): Additional filter conditions
- `limit` (Number): Maximum number of related instances
- `before` (Function): Hook function called before execution
- `log` (Function): Logging function
- `logLine` (Function): Line-by-line logging function

##### Returns

- `Promise<Object|Array>`: Related instances

##### Usage

```javascript
const userPosts = await fetchRelated({
  credentials: dbCredentials,
  instance: userInstance,
  propertyName: 'posts',
  filter: { published: true },
  limit: 20
});
```

#### fetchIncludes

Fetches related instances and association links for a given set of starting instances, following JSON:API include semantics (including intermediate resources for multi-part paths).

##### Parameters

- `credentials` (Object): Database credentials/execution context
- `instances` (Array<Object>): Starting instances to expand includes for (required)
- `include` (Array<string> | Object): Include paths as dot-notation strings (e.g. `['posts.author', 'posts.comments']`) or a pre-built include tree object
- `returnType` (any): Propagated to lower-level fetchers; controls return shaping
- `filteredProperties` (any): Optional sparse field handling passed through to fetchers
- `before` (Function): Hook invoked before execution
- `log` (Function): Logger
- `logLine` (Function): Line-level logger

##### Returns

- `Promise<{ links: Array<Object>, instances: Array<Object> }>`: Related association links and included instances

##### Usage

```javascript
// Given a set of already-fetched resources (e.g., posts),
// fetch related authors and comments (and each comment's author).
const { links, instances: included } = await fetchIncludes({
  credentials: dbCredentials,
  instances: postsArray,
  include: ['author', 'comments', 'comments.author'],
});
```

### SQL Generation Utilities

#### secureSQLCondition

Generates secure SQL conditions with parameterized queries to prevent SQL injection.

##### Parameters

- `condition` (String): SQL condition string to sanitize
- `translateSymbol` (Function): Function to translate symbols to safe values

##### Returns

- `String`: Sanitized SQL condition

##### Usage

```javascript
const safeCondition = secureSQLCondition(
  'name = "John" AND age > 18',
  (symbol) => ({ s: symbol.toUpperCase() })
);
```

#### valueSpecificationToSQL

Converts value specification objects to SQL conditions.

##### Parameters

- `vs` (Object): Value specification object
- `translateSymbol` (Function): Function to translate symbols

##### Returns

- `String`: SQL condition string

##### Usage

```javascript
const sqlCondition = valueSpecificationToSQL({
  and: [
    { name: { $eq: 'John' } },
    { age: { $gte: 18 } }
  ]
}, translateSymbol);
```

### Utility Functions

#### createError

Creates standardized error objects with status codes and error types.

##### Parameters

- `code` (String): Error code (default: 'ERROR_INTERNAL_SERVER_ERROR')
- `statusCode` (Number): HTTP status code
- `title` (String): Error title
- `message` (String): Error message
- `fatal` (Boolean): Whether error is fatal (default: false)

##### Returns

- `Error`: Error object with additional properties

##### Usage

```javascript
const error = createError({
  code: 'ERROR_USER_NOT_FOUND',
  statusCode: 404,
  title: 'User Not Found',
  message: 'The requested user does not exist',
  fatal: false
});
```

## Database Schema

The package expects a specific database schema that mirrors the UML metamodel:

- **Instance Tables**: One table per UML class with columns for each attribute
- **Link Tables**: Association tables for UML associations
- **Inheritance**: Handled through table joins based on UML generalization relationships
- **Primary Keys**: Uses `__id__` as the primary key for all instances
- **Foreign Keys**: Uses `__id__` references for relationships

## Security Features

- **SQL Injection Protection**: All queries use parameterized statements
- **Input Sanitization**: Automatic sanitization of user inputs
- **Access Control**: Support for owner-based access control
- **Audit Logging**: Built-in logging capabilities for all operations

## Error Handling

The package provides comprehensive error handling with standardized error codes:

```javascript
try {
  const result = await fetchInstances({
    credentials: dbCredentials,
    classifier: UserClass
  });
} catch (error) {
  if (error.code === 'ERROR_CLASSIFIER_REQUIRED') {
    // Handle missing classifier
  } else if (error.statusCode === 500) {
    // Handle server error
  }
}
```

## License

MIT

## Author

Benjamin Bytheway