SQL Model Service

SQL backing for the travetto model module, with real-time modeling support for SQL schemas.

Install: @travetto/model-sql

npm install @travetto/model-sql

# or

yarn add @travetto/model-sql

This module provides the core SQL foundation for Data Modeling Support datastores. The current SQL client implementations include:

• MySQL Model Service - MySQL 8.0+
• PostgreSQL Model Service - Postgres 14+
• SQLite Model Service - SQLite (Node native DatabaseSync)

Note: Wider client support will roll out as usage increases.

Assumed Behavior & Schema Design

The SQL Model Service works quite a bit differently than the average Object Relationship Mapping in that it makes assertions about how data is stored in the database. The primary goal of the SQL support is not to handle every legacy relational database scenario, but to integrate with the Data Modeling Support structure while leveraging relational datastores to the best of their abilities.

The primary design maps each model class to a single table where simple fields (primitives, dates, enums) are mapped to individual columns, and complex fields (objects and arrays) are serialized and stored as native JSON document columns. Every table requires a primary key column (id), and indices are compiled directly to standard database columns or SQL functional expressions over JSON paths.

In development mode, storage modifications are applied dynamically in real time to match model definitions, minimizing manual database migrations.

Transactions

Transaction state is tracked seamlessly using Async Context. Methods can be wrapped using the Transactional decorator to run operations within a managed transaction.

Supported transaction modes include:

• required - Joins an active transaction if one exists, or starts a new top-level transaction.
• isolated - Begins a SAVEPOINT / nested transaction isolated from the surrounding context.
• force - Always creates a separate SAVEPOINT for nested operations.

Bulk Operations

Bulk operations (processBulk) batch insert, delete, and update statements for high performance. Bulk updates utilize standard ANSI SQL CASE ... WHEN constructs to update multiple records in a single database query across all SQL engines.

Supported Features

All SQL model service implementations derive from BaseSQLModelService and support:

• Bulk
• CRUD
• Expiry
• Indexed
• Query Crud
• Facet
• Suggest
• Query