@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

npm install @schematize/util-db

Usage

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: '[email protected]' }),
  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

// 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

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

const savedUser = await saveInstance({
  credentials: dbCredentials,
  instance: new User({
    name: 'John Doe',
    email: '[email protected]',
    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

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

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

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

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

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): Starting instances to expand includes for (required)
• include (Array | 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

// 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

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

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

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:

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