SemanticDB

SemanticDB translates its own LogicForm into SQL and executes it on a single core compute layer. Callers provide schemas, schema mappings, operators, and database connection information. SemanticDB creates the matching provider and owns its connection lifecycle.

Public API

import { Logicform } from 'semanticdb-v2';

const schemaMappings = [
  {
    schemaId: 'sales',
    connectionId: 'analytics',
    source: 'analytics.sales_detail',
    mappings: [],
    freshness: { mode: 'ttl', ttlSeconds: 300 },
  },
];

const response = await Logicform.execute(
  {
    version: '2.0',
    schema: 'sales',
    groupby: [{
      operator: '$dateBucket',
      pred: 'order_date',
      args: { granularity: 'month' },
    }],
    preds: [{ operator: '$sum', pred: 'amount', name: 'revenue' }],
  },
  {
    connections: [{
      id: 'analytics',
      kind: 'clickhouse',
      url: 'http://localhost:8123',
      username: 'default',
      password: process.env.CLICKHOUSE_PASSWORD,
      database: 'analytics',
    }],
    schemas,
    schemaMappings,
    operators,
    onProgress(event) {
      console.log(event.stage, event);
    },
  },
);

Every executable LogicForm, including LogicForm objects used as subqueries, must declare version. This SDK accepts semantic versions greater than or equal to 2.0; missing, malformed, or older versions fail with UNSUPPORTED_LOGICFORM_VERSION before Schema resolution or database access. A relation query shaped as { schema, query } is part of its parent LogicForm and inherits the parent's version.

PredItems without an explicit name receive a human-readable name during normalization. Generated names default to Chinese; pass locale: 'en-US' in ExecuteOptions for the built-in English names. Every operator owns its localized default names and receives the requested locale directly:

operators.register({
  name: '$weightedAverage',
  defaultName: ({ operandName, locale }) => locale.toLowerCase().startsWith('en')
    ? `Weighted average of ${operandName}`
    : `${operandName}加权平均值`,
  kind: 'aggregate',
  toSQL: ({ column }) => `weighted_avg(${column})`,
});

The same locale applies to preds, groupby, nested expressions, custom-function subqueries, and entity-population queries. A PredItem's normalized query is included in its generated name, for example 门店为上海的销售额合计. Operator-specific args also participate through each operator's defaultName(); period operators distinguish同比、环比 and other offsets. An explicitly authored name is preserved unchanged.

entity_id identifies one entity by hierarchy.property on hierarchy Schemas and by the unique type: 'ID' property on other Schemas. Normalization adds that identity filter unless the query already contains one, in which case the explicit query wins. The legacy value placeholder is preserved as entity context but never becomes a SQL filter. String IDs and safe-integer IDs are accepted and retain their original type. This also applies to relation queries shaped as { schema, query, entity_id }. representation is presentation metadata for frontend consumers and does not change normalization, planning, or execution. See Hierarchy plugin for Schema configuration, $hierarchyLevel, legacy compatibility, and its plugin boundary.

Compound capabilities can be supplied by external packages through ExecuteOptions.plugins. Plugins may own Schema metadata, legacy conversion, entity_id selection, and Operators while remaining scoped to one execution. See Register an external plugin.

Parent-child Schemas may declare parentProperty. $recursive filters a property to a root node and all descendants; object properties infer the tree Schema from ref. MySQL, Doris, StarRocks, and PostgreSQL compile this as a parameterized recursive CTE.

Top-level projections may set schema to read metrics from another registered Schema. SemanticDB splits those projections into independent provider queries, aligns shared query/groupby dimensions, and merges rows by the group keys before applying having, sort, limitBy, and pagination.

The progress stream emits started, validated, normalized, planned, lineage, compiled, executing, hydrating, result, and completed. Every event includes a cumulative partialResult; for example, compiled includes sqls, while result includes sqls and result. Failures emit failed before rejection and preserve the partial result accumulated so far.

Each sqls item keeps the executable statement and its parameters together, plus a complete SQL string for diagnostics:

interface ExecutedSql {
  type: 'semantic-resolution' | 'computation';
  explain: string;
  logicform: LogicformType;
  sql: string;
  parameters: readonly unknown[];
  displaySql: string;
}

semantic-resolution identifies supporting SQL used to resolve semantic input or output, such as time-watermark resolution and object entity population. computation identifies SQL that calculates the requested LogicForm result. sqls includes every physical SQL involved in the execution; an entry remains visible when its result is served by the query cache.

Providers execute only parameterized sql + parameters; SemanticDB never executes displaySql. Because displaySql contains literal values and may expose sensitive data, use it only in trusted debugging and observability channels.

Permissions

Pass table, row, and column permissions together through ExecuteOptions.permissions:

import type { Permissions } from 'semanticdb-v2';

const permissions = {
  orders: {
    rowPermissions: {
      订单状态: { $ne: '已删除' },
      所属部门: {
        schema: 'departments',
        query: {
          部门名称: { $in: ['华东销售部', '华南销售部'] },
        },
      },
    },
    columnPermissions: {
      whitelist: ['ID', '订单状态', '销售额', '所属部门', 'createdAt'],
    },
  },
  departments: {
    rowPermissions: {
      部门名称: { $in: ['华东销售部', '华南销售部'] },
    },
    columnPermissions: {
      whitelist: ['ID', '部门名称'],
    },
  },
} satisfies Permissions;

await Logicform.execute(logicform, {
  schemas,
  schemaMappings,
  connection,
  permissions,
});

When permissions is omitted, execution remains unrestricted. When it is present, its Schema keys are the table allowlist. rowPermissions is always combined with the caller query using AND, including internal time-watermark queries, related-table joins, and object population queries. columnPermissions.whitelist accepts property names or IDs and restricts caller projections, filters, grouping, operator inputs, and relation paths. A row policy may still use a non-whitelisted column as an internal filter. If an ID is not whitelisted, SemanticDB generates the executor-owned result-row _id instead of exposing the physical ID as that identity.

Component operators

Composite metrics are regular OperatorDefinition values with kind: 'component'. Their createComponent hook can return query components merged through a CTE or JavaScript. Register them in the same OperatorRegistry as scalar and aggregate operators. Operator names are free-form; the $ prefix is a convention used by built-in operators, not a requirement.

See Register an operator for the component contract.

The root entry point accepts a connections array of DatabaseConnection items. Every item has a unique id; set kind to mysql, doris, starrocks, clickhouse, snowflake, oracle, or postgresql, then add that driver's native connection fields directly on the item. Each Schema Mapping selects an item through connectionId. SemanticDB lazily creates and reuses the matching internal provider. Call await Logicform.close() during application shutdown to close every connection pool and session.

MySQL-compatible providers default to 30 connections, at most 10 idle connections, a 60-second idle timeout, and TCP keepalive. Native mysql2 pool options on the connection item override these defaults.

PostgreSQL connection

PostgreSQL uses the official pg pool and PostgreSQL-native $1, $2 parameters:

await Logicform.execute(logicform, {
  schemas,
  schemaMappings,
  connections: [{
    id: 'primary',
    kind: 'postgresql',
    connectionString: process.env.DATABASE_URL,
  }],
});

Doris and StarRocks connections

Doris and StarRocks expose MySQL-compatible protocols, so both use mysql2 connection fields:

const doris = { id: 'doris-main', kind: 'doris', host, port, user, password, database } as const;
const starrocks = { id: 'starrocks-main', kind: 'starrocks', host, port, user, password, database } as const;

They are independent providers with distinct kind values. Their currently compatible identifier, pagination, date, aggregate, string, regex, parameter, and display-SQL behavior comes from a shared MySQL-compatible SQL helper. Database-specific differences should be implemented by overriding the corresponding internal dialect capability.

Snowflake connection

Snowflake connection fields are passed to the official snowflake-sdk:

await Logicform.execute(logicform, {
  schemas,
  schemaMappings,
  connections: [{
    id: 'warehouse',
    kind: 'snowflake',
    account: process.env.SNOWFLAKE_ACCOUNT,
    username: process.env.SNOWFLAKE_USERNAME,
    password: process.env.SNOWFLAKE_PASSWORD,
    warehouse: process.env.SNOWFLAKE_WAREHOUSE,
    database: process.env.SNOWFLAKE_DATABASE,
    schema: process.env.SNOWFLAKE_SCHEMA,
    role: process.env.SNOWFLAKE_ROLE,
  }],
});

Oracle connection

Oracle uses the official oracledb driver in Thin mode and does not require Oracle Client libraries:

await Logicform.execute(logicform, {
  schemas,
  schemaMappings,
  connections: [{
    id: 'oracle-main',
    kind: 'oracle',
    user: process.env.ORACLE_USER,
    password: process.env.ORACLE_PASSWORD,
    connectString: process.env.ORACLE_CONNECT_STRING,
  }],
});

Semantic types

Schema.properties[].type is an extensible semantic type. SemanticDB supplies the built-in types and derives each property's primal_type while resolving the Schema. A supplied primal_type must match the registered type. isArray describes the value container and defaults to false; for example, tags are represented as type: 'category', isArray: true.

import { createSemanticTypeRegistry, Logicform } from 'semanticdb-v2';

const semanticTypes = createSemanticTypeRegistry();
semanticTypes.register({
  name: 'email',
  primalType: 'string',
  validateProperty(property) {
    if (property.isArray) throw new Error('email must be scalar');
  },
});

await Logicform.execute(logicform, { schemas, schemaMappings, connection, semanticTypes });

Registries are instance-local. Applications can reuse one registry across executions without introducing global mutable registration.

See Register a custom SemanticType for the complete external registration API, validation lifecycle, and Schema example.

SQL-derived properties

A Property can map to a trusted, provider-specific SQL expression instead of one physical column:

{
  id: 'net_amount',
  name: 'Net amount',
  type: 'currency',
  udf: { sql: 'COALESCE(amount, 0) - COALESCE(discount, 0)' },
}

udf.sql is used consistently for projection, filtering, grouping, operator input, and relation keys. In an event Schema, $TS expands to the quoted physical timestamp column. The expression is trusted model metadata, must match the active provider dialect, and cannot contain statements, comments, or parameter placeholders. Property UDFs do not support the legacy function or dependencies fields; application-side computed values belong outside this query SDK.

Query cache

All compiled provider queries, including the internal time-watermark query, can share one caller-owned query cache. Redis is published from semanticdb-v2/cache/redis; cache identity includes namespace, provider, SQL, and parameters. Physical expiration belongs to each Schema mapping's freshness policy. See Query cache and Redis.

When Redis is shared with other applications, pass an application-specific key prefix:

import { createRedisQueryCache } from 'semanticdb-v2/cache/redis';

const cache = await createRedisQueryCache({
  url: process.env.REDIS_URL,
  keyPrefix: process.env.REDIS_KEY_PREFIX,
});

Operators convert SQL expressions and declare the semantic type of their single output column. See LogicForm operators for the built-in syntax and composition rules, and Register an operator for the extension contract.

Schema and Property metadata use only id; neither defines sid. See the breaking-change migration checklist before updating an upstream Schema producer or downstream consumer.

Architecture

The execution pipeline is split into explicit layers:

LogicForm
  -> normalizer  validates and canonicalizes the request
  -> planner     builds a provider-independent LogicalPlan and lineage
  -> compiler    renders the LogicalPlan through the provider contract
  -> physical    executes SQL through a concrete database provider

executor is the application layer that coordinates the pipeline and publishes progress events. semantic-types validates and resolves Schema property types before normalization; schema owns Schema lookup and logical field mapping, while schema-mappings owns the physical source bindings. Each layer owns its types, while the provider contract and concrete database adapters live in physical. Physical adapters do not own normalization, planning, or compilation rules.

After physical execution, the executor's Result Hydrator runs SemanticType result hooks and batch-populates directly selected object properties. See Result Hydrator.

Each layer exposes an index.ts boundary. Cross-layer imports should use that boundary rather than reach into another layer's implementation files.

See LogicForm to SQL pipeline for the responsibility and input/output contract of every stage.

The legacy test migration report records how the old project's test suite maps to this rewrite, including migrated behavior and explicit non-migration reasons.

Scalar object properties can be traversed with paths such as 门店_城市. The planner resolves these paths into provider-independent LEFT JOIN plans. See Object relations and LEFT JOIN.

MySQL test configuration

The test suite includes a real MySQL execution test. Configure it through the project .env:

cp .env.example .env
npm test

The supported variables are TEST_MYSQL_HOST, TEST_MYSQL_PORT, TEST_MYSQL_USER, TEST_MYSQL_PASSWORD, and TEST_MYSQL_DATABASE. The test creates an isolated table, verifies the returned aggregates, and removes the table afterward. If TEST_MYSQL_DATABASE is omitted, it creates and reuses the isolated semanticdb_v2_integration database.

PostgreSQL test configuration

Set TEST_POSTGRESQL_URL in .env to enable the real PostgreSQL integration test. It creates an isolated table in the configured database, verifies parameterized execution and LogicForm aggregation, and removes the table afterward.

Redis test configuration

The test suite also includes a real Redis integration test. Configure the connection in .env:

REDIS_URL=redis://localhost:6379/0
REDIS_KEY_PREFIX=semanticdb-v2

REDIS_KEY_PREFIX is the application namespace and should be unique when Redis is shared. The test appends its own process ID and UUID, verifies real expiry and source-index behavior, and removes its keys afterward. It is skipped only when REDIS_URL is absent; a configured but unreachable Redis server fails the test.

Snowflake test configuration

The ordinary test suite includes a real Snowflake provider and LogicForm execution test. Uncomment and fill the TEST_SNOWFLAKE_* variables in .env; ACCOUNT, USERNAME, PASSWORD, WAREHOUSE, and DATABASE are required, while SCHEMA and ROLE are optional. The test only reads INFORMATION_SCHEMA.TABLES. It is skipped when the required configuration is absent and fails when configured credentials cannot connect.

Oracle test configuration

The ordinary test suite includes real Oracle provider and LogicForm execution tests. Uncomment and fill TEST_ORACLE_USER, TEST_ORACLE_PASSWORD, and TEST_ORACLE_CONNECT_STRING in .env. Thin mode is the default. To test Thick mode, set TEST_ORACLE_MODE=thick and optionally set TEST_ORACLE_CLIENT_LIB_DIR and TEST_ORACLE_CONFIG_DIR. The tests query Oracle's DUAL table; they are skipped when required connection values are absent and fail when configured Oracle credentials, client libraries, or connectivity are invalid.

Debugging the pipeline

debug/debug.ts prepares an isolated MySQL table and executes one complete LogicForm pipeline against it. It uses the same TEST_MYSQL_* variables from .env as the integration tests.

In Trae or VS Code, open Run and Debug, select Debug SemanticDB pipeline, set a breakpoint on the Logicform.execute() call or inside src/, and start debugging. The same entry point can be run without the IDE:

npm run debug

Current scope

• ClickHouse, MySQL, Oracle, and Snowflake core compute providers
• Projections, conditional aggregates, and $sum, $count, $countDistinct, $avg, $min, $max
• Structured arithmetic, share-of-total, period comparison, period-boundary, date-bucket, cumulative period, trusted SQL expression, string, case, and window operators
• Declarative operators
• Grouping with hour/day/week/month/quarter/year time buckets
• $eq, $ne, $gt, $gte, $lt, $lte, $in, $nin, $exists, $contains, $regex
• $and and $or, HAVING, sorting, row-count and percentage pagination, and per-group limitBy
• Query-level and output-column lineage
• Multi-level scalar and array object relation paths, array groupby, and batched entity population
• Nested from LogicForm objects compiled as parameterized derived tables

Exports and metadata persistence are intentionally deferred. Directly selected array-object properties are populated without expanding the root result rows.