UCAST SQL

This package is a part of ucast ecosystem. It provides an interpreter that can translate ucast conditions into SQL query.

Installation

npm i @ucast/sql
# or
yarn add @ucast/sql
# or
pnpm add @ucast/sql

Getting Started

Interpret conditions AST

In order to interpret something, we need 2 things: interpreter and AST. It's really easy to create interpreter just pick operators you want to use or pass all of them:

import {
  createSqlInterpreter,
  eq,
  lt,
  lte,
  allInterpreters
} from '@ucast/sql';

const interpret = createSqlInterpreter({ eq, lt, lte });
// or
const interpret = createSqlInterpreter(allInterpreters);

interpret is a function that takes 2 parameters:

1. condition, the UCAST condition to interpret.
2. options, SQL dialect options with optional alias and entity metadata.

By default, it returns [sql, params]: a SQL predicate and its bound values. The caller adds the predicate to a query and passes the parameters to the database client. Each call starts with fresh parameters and relation aliases.

For the sake of an example, we will create AST manually using Condition from @ucast/core:

import { CompoundCondition, FieldCondition } from '@ucast/core';

// x > 5 && y < 10
const condition = new CompoundCondition('and', [
  new FieldCondition('gt', 'x', 5),
  new FieldCondition('lt', 'y', 10),
]);

Now, we can combine these 2 together to get SQL condition:

import { CompoundCondition, FieldCondition } from '@ucast/core';
import { createSqlInterpreter, allInterpreters, pg } from '@ucast/sql';

// x > 5 && y < 10
const condition = new CompoundCondition('and', [
  new FieldCondition('gt', 'x', 5),
  new FieldCondition('lt', 'y', 10),
]);
const interpret = createSqlInterpreter(allInterpreters);

const [sql, replacements] = interpret(condition, {
  ...pg
})

console.log(sql) // ("x" > $1 and "y" < $2)
console.log(replacements) // [5, 10]

Supported operators

allInterpreters registers these condition operators:

| Operator | SQL behavior | | --- | --- | | eq, ne | =, <>; null becomes is null, is not null | | lt, lte, gt, gte | <, <=, >, >= | | exists | is not null for a truthy value, is null otherwise | | in (also within), nin | in(...), not in(...), with one parameter per value | | mod | mod(field, divisor) = remainder, with both values bound | | regex | Dialect-specific regular expression comparison with the pattern bound | | and, or | Parenthesized conditions joined by and or or | | not, nor | Negated groups joined by and or or, respectively | | some, none, every | Correlated relation subqueries |

The exported function for in is named within. The relation functions are exported as someRelation, noneRelation, and everyRelation; allInterpreters registers them under some, none, and every.

Map model properties to database columns

Use metadata.toColumnName to translate a model property into a column name. Return an unescaped name; the composer escapes it and applies the query alias. Without this callback, field names are used as column names directly.

const columns: Record<string, string> = { displayName: 'display_name' };
const condition = new FieldCondition('eq', 'displayName', 'Alice');

const [sql, params] = interpret(condition, {
  ...pg,
  alias: 'users',
  metadata: {
    toColumnName: name => columns[name] ?? name,
  },
});

// sql: '"users"."display_name" = $1'
// params: ['Alice']

Each relation has its own metadata. Conditions use the related entity's mapping; join fields already identify physical database columns.

Conditions on related tables

Use some, none, and every to express conditions against related records:

• some checks that at least one related record matches the condition.
• none checks that no related records match the condition.
• every checks that no related record makes the negated condition true.

every uses SQL negation, including SQL's usual NULL behavior. Both none and every match when there are no related records.

When using the default composer, provide metadata.resolveRelation to describe how the current table connects to the related table. Use an alias that matches the outer query so the subquery can refer to its columns unambiguously.

import { FieldCondition } from '@ucast/core';
import { allInterpreters, createSqlInterpreter, pg } from '@ucast/sql';

const interpret = createSqlInterpreter(allInterpreters);
const condition = new FieldCondition(
  'some',
  'projects',
  new FieldCondition('eq', 'active', true),
);

const [sql, params] = interpret(condition, {
  ...pg,
  alias: 'users',
  metadata: {
    resolveRelation(name) {
      if (name !== 'projects') {
        return undefined;
      }

      return {
        ownField: 'id',
        relatedField: 'user_id',
        relatedTable: 'projects',
        metadata: {},
      };
    },
  },
});

console.log(sql);
// EXISTS (SELECT 1 FROM "projects" as "projects_ucast0" WHERE "users"."id" = "projects_ucast0"."user_id" AND "projects_ucast0"."active" = $1)
console.log(params); // [true]

Relation metadata contains:

• ownField: the join field on the current entity.
• relatedField: the join field on the related entity.
• relatedTable: the related table name.
• metadata: the related entity's column mapping and relation resolver. Use {} when neither is needed.

Nested relations use the related entity's metadata.resolveRelation. All scopes share the root parameter sequence, and generated relation aliases use a counter shared across the interpretation. An unresolved relation throws an error.

Join fields are physical database column names and bypass property mapping. For composite keys, provide matching arrays in ownField and relatedField. Many-to-many metadata also supplies through: { table, ownRelationField, relatedRelationField }, whose fields can likewise be matching arrays.

Relation metadata describes joins and column mappings. For domain-specific SQL, provide a custom composer and implement its relation handler.

SQL composers

createSqlInterpreter creates a composer for each interpretation call. The default string composer returns [sql, params]. To use another fragment representation or result shape, pass createSqlComposer in the interpreter options:

const interpret = createSqlInterpreter<SQL, SQL>(allInterpreters, {
  createSqlComposer: options => new DrizzleComposer(options),
});
const result = interpret(condition, pg);

SQL, and DrizzleComposer above represent your own types and implementation. The factory receives the SQL query options and returns a SqlComposer<Fragment>, which provides:

| Helper | Purpose | | --- | --- | | field(name) | Map, escape, and qualify a field | | param(conditionOrValue) | Bind a condition's value or a raw value | | manyParams(conditionOrValues) | Bind an array of values | | sql | Compose fragments with a tagged template | | raw(sql) | Create a fragment from trusted SQL syntax | | join(fragments, separator) | Join fragments using a fragment separator | | regexp(ignoreCase) | Return the dialect's regex operator and comparator | | relation(name) | Return a related composer and subquery(predicate) |

To return a different result shape, implement the optional result(fragment) method on the composer and specify its return type as the second generic argument. Without result, the interpreter returns the fragment directly.

createDefaultSqlComposer(options) is exported for extending the built-in string composer. A custom relation handler can create a child with createDefaultSqlComposer(childOptions, parentComposer) to share parameter binding. The interpreter compiles the related predicate before calling subquery(predicate). Place that predicate before additional bound filters in the generated SQL so positional placeholders follow parameter order.

Custom interpreter

To restrict supported operators, pass only the desired functions:

import { FieldCondition } from '@ucast/core';
import { createSqlInterpreter, eq, lt, gt, pg } from '@ucast/sql';

const interpret = createSqlInterpreter({ eq, lt, gt });
const condition = new FieldCondition('eq', 'x', true);

interpret(condition, pg);

To add a custom operator, create a function that receives a condition and a composer and returns a SQL fragment. A third argument provides interpret(condition, composer) for recursively interpreting child conditions. Let's create an operator that adds a condition on publishedAt:

import { DocumentCondition } from '@ucast/core';
import {
  type SqlOperator,
  createSqlInterpreter,
  allInterpreters,
  pg,
} from '@ucast/sql';

const isActive: SqlOperator<DocumentCondition<boolean>> = (node, { sql, field, raw, param }) => {
  const operator = node.value ? ' >= ' : ' < ';
  return sql`${field('publishedAt')}${raw(operator)}${param(new Date())}`;
};
const interpret = createSqlInterpreter({
  ...allInterpreters,
  isActive,
});
const condition = new DocumentCondition('isActive', true);
const [sql, params] = interpret(condition, pg);

console.log(sql); // "publishedAt" >= $1
console.log(params); // [new Date()]

Use param for values and field for identifiers; reserve raw for trusted SQL syntax. If condition names differ from registered operator names, pass getInterpreterName(condition, options) in the createSqlInterpreter options.

Integrations

This library provides sub-modules that allows quickly integrate SQL interpreter with popular ORMs:

Drizzle ORM

Install drizzle-orm alongside @ucast/sql:

npm i @ucast/sql 'drizzle-orm'

Supported versions are >=0.36.2 <1.0.0 and ^1.0.0-rc.1. Drizzle is an optional peer dependency: importing the SQL root or another adapter does not load it.

Use:

• @ucast/sql/drizzle-orm/v0.x for supported 0.x versions
• @ucast/sql/drizzle-orm/v1.x for supported 1.0.0

Each entry supports ESM, CommonJS and TypeScript. There is no automatic version selection.

Supported dialects:

• PostgreSQL
• MySQL
• SQLite

Create an interpreter during application bootstrap, then pass a condition and a table's schema key to interpret it:

import { createDrizzleInterpreter } from '@ucast/sql/drizzle-orm/v0.x';
import { FieldCondition } from '@ucast/core';
import { and, eq } from 'drizzle-orm';
import { integer, sqliteTable, text } from 'drizzle-orm/sqlite-core';

const authors = sqliteTable('authors', {
  id: integer('id').primaryKey(),
  displayName: text('display_name').notNull(),
});
const interpret = createDrizzleInterpreter({ schema: { authors } });
const predicate = interpret(
  new FieldCondition('eq', 'displayName', 'Ada'),
  'authors'
);

// Use your configured Drizzle instance to construct and execute the query.
db.select().from(authors).where(and(eq(authors.id, 1), predicate));

interpret(condition, tableKey, options?) synchronously returns a Drizzle SQL predicate. Use it in SELECT, UPDATE, or DELETE queries, or combine it with native Drizzle conditions using and() and or(). You own query construction and execution; Drizzle renders the predicate and binds its parameters.

Table keys are inferred from the schema. Unknown table keys and unsupported table dialects throw when interpreting a condition.

Aliases and column names

Register original tables in the schema. Supply query aliases through the third argument, using the same alias in your Drizzle query:

import { alias } from 'drizzle-orm/sqlite-core';

const u = alias(authors, 'u');
const predicateForAlias = interpret(
  new FieldCondition('eq', 'displayName', 'Ada'),
  'authors',
  { alias: 'u' },
);
db.select().from(u).where(predicateForAlias);

Conditions use table property names: displayName above resolves to display_name. Native column references preserve Drizzle's naming, schema qualification, casing, and input encoders, including custom encoders. Unknown properties remain escaped literal identifiers; dots in field names do not introduce a relation path.

Relations

Relation operators (some/is, none/isNot, and every) resolve native Drizzle relation declarations and produce correlated EXISTS/NOT EXISTS subqueries. Include the tables and their declarations in the factory's schema:

import { relations } from 'drizzle-orm';

const posts = sqliteTable('posts', {
  id: integer('id').primaryKey(),
  authorId: integer('author_id').notNull(),
  title: text('title').notNull(),
});
const authorRelations = relations(authors, ({ many }) => ({
  posts: many(posts),
}));
const postRelations = relations(posts, ({ one }) => ({
  author: one(authors, {
    fields: [posts.authorId],
    references: [authors.id],
  }),
}));
const schema = { authors, posts, authorRelations, postRelations };
const interpret = createDrizzleInterpreter({ schema });

const condition = new FieldCondition('some', 'posts',
  new FieldCondition('eq', 'title', 'Getting started'));
db.select().from(authors).where(interpret(condition, 'authors'));

Here, Drizzle infers the authors.posts join from posts.author. Direct joins, inferred inverse joins, composite keys, named self relations, and nestedrelations are supported. Each nested condition uses the related table's columns and encoders.

| Operator | Meaning | | --- | --- | | some | At least one related row matches the nested condition | | none | No related row matches the nested condition | | every | No related row satisfies the SQL negation of the nested condition |

none and every match parents without related rows. every preserves SQL's usual NULL behavior.

Relations are normalized and validated once at interpreter creation. Register all tables referenced by your relation declarations, including declarations you do not use in a condition. Missing target tables, ambiguous or incomplete joins, and cross-dialect relations fail at startup. Unknown relation names in conditions fail during interpretation.

Express nested relations with nested condition nodes. For example, this follows authors.posts.author:

const hasPostByAda = new FieldCondition('some', 'posts',
  new FieldCondition('some', 'author',
    new FieldCondition('eq', 'displayName', 'Ada')));
db.select().from(authors).where(interpret(hasPostByAda, 'authors'));

Drizzle 0.x models many-to-many relations through a junction table. Register the junction table and its relation declarations, then traverse both declared steps with nested conditions in the same way. The interpreter does not invent a direct many-to-many relation.

Operators and values

The factory uses all supported operators by default. Pass an optional second argument to select or extend the interpreter set. These operators come from @ucast/sql:

import { eq , lt } from '@ucast/sql';

const interpretSelected = createDrizzleInterpreter(
  { schema: { authors } },
  { eq, lt },
);

Values are bound parameters. Comparisons and membership conditions use the resolved column's encoder; regular-expression patterns and modulo operands do not. Direct Drizzle SQL objects as condition values are rejected because Drizzle would otherwise render them as executable syntax. Use native Drizzle predicates or custom interpreters to compose SQL expressions.

Drizzle 1.x relation declarations

Use the matching entry and the release candidate's native defineRelations() declarations:

import { createDrizzleInterpreter } from '@ucast/sql/drizzle-orm/v1.x';
import { defineRelations } from 'drizzle-orm';

const relations = defineRelations({ authors, posts }, r => ({
  authors: {
    posts: r.many.posts({ from: r.authors.id, to: r.posts.authorId }),
    publishedPosts: r.many.posts({
      from: r.authors.id,
      to: r.posts.authorId,
      where: { title: { ne: 'Draft' } },
    }),
  },
  posts: {
    author: r.one.authors({ from: r.posts.authorId, to: r.authors.id })
  },
}));
const interpret = createDrizzleInterpreter({ schema: { authors, posts, relations } });

Native 1.x through relations are also supported. For example, with a declared authorRoles junction, use r.authors.id.through(r.authorRoles.authorId) as from and r.roles.id.through(r.authorRoles.roleId) as to. Predefined relation filters restrict the row set for all relation operators: every negates only the child condition. Rows outside the declared filter do not become counterexamples. The interpreter delegates join/filter construction to Drizzle's native relationToSQL.

Drizzle 0.x applies casing when rendering a predicate; the same predicate can be rendered with different dialect casing settings. In 1.0.0 release candidates use native table construction casing, such as sqliteTableCreator(name => name, 'snake_case'). The interpreter accepts neither a casing nor a dialect option.

Sequelize

For custom relation SQL, pass { createSqlComposer } as the second argument to createSequelizeInterpreter and implement the composer's relation handler.

import { interpret } from '@ucast/sql/sequelize';
import { CompoundCondition, FieldCondition } from '@ucast/core';
import { Model, Sequelize, DataTypes } from 'sequelize';

const sequelize = new Sequelize('sqlite::memory:');

class User extends Model {}

User.init({
  name: { type: DataTypes.STRING },
  blocked: { type: DataTypes.BOOLEAN },
  lastLoggedIn: { type: DataTypes.DATE },
}, { sequelize });

const condition = new CompoundCondition('and', [
  new FieldCondition('eq', 'blocked', false),
  new FieldCondition('lt', 'lastLoggedIn', new Date(Date.now() - 24 * 3600 * 1000)),
]);

const queryOptions = interpret(condition, User, { alias: User.name });
// Pass both the SQL literal and its bound replacements to Sequelize.
const users = await User.findAll(queryOptions);

interpret(condition, Model, options?) returns { where, replacements }. The optional third argument accepts alias and replacementMode ('numeric', the default positional ? placeholders, or 'named'). Named placeholders use an interpreter-local call counter; consumers combining predicates own replacement ordering and name collisions across interpreter instances. The default alias is Model.tableName; pass the query's model alias explicitly when it differs, as with User.findAll() above.

Objection.js

The adapter translates model properties and relations with objectionToSqlMetadata(Model). To customize that mapping, pass { createSqlComposer } as the second argument to createObjectionInterpreter. The factory receives query options including the model's SqlEntityMetadata; pass customized metadata to createDefaultSqlComposer. For custom relation SQL, implement the composer's relation handler.

import { interpret } from '@ucast/sql/objection';
import { CompoundCondition, FieldCondition } from '@ucast/core';
import { Model } from 'objection';
import Knex from 'knex';

Model.knex(Knex({ client: 'pg' }));

class User extends Model {
  static tableName = 'users';
}

const condition = new CompoundCondition('and', [
  new FieldCondition('eq', 'blocked', false),
  new FieldCondition('lt', 'lastLoggedIn', Date.now() - 24 * 3600 * 1000),
]);

// the next code produces:
// User.query()
//   .where('blocked', false)
//   .where('lastLoggedIn', '<', Date.now() - 24 * 3600 * 1000)
const query = User.query().where(interpret(condition, User));

interpret(condition, Model, options?) returns an Objection RawBuilder with bound values. Pass it to .where() or .andWhere() to compose it with other conditions. The model supplies the dialect and property/relation metadata. For aliased queries, pass the same alias explicitly:

const query = User.query().alias('u').where(interpret(condition, User, { alias: 'u' }));

MikroORM

This example uses the SQLite driver package, @mikro-orm/sqlite.

import { createMikroORMInterpreter } from '@ucast/sql/mikro-orm';
import { CompoundCondition, FieldCondition } from '@ucast/core';
import { EntitySchema, MikroORM } from '@mikro-orm/sqlite';

class User {
  id!: number;
  blocked!: boolean;
  name!: string;
  lastLoggedIn = new Date();
}

const userSchema = new EntitySchema({
  class: User,
  properties: {
    id: { type: 'number', primary: true },
    blocked: { type: 'boolean' },
    name: { type: 'string' },
    lastLoggedIn: { type: 'Date' },
  },
});

const condition = new CompoundCondition('and', [
  new FieldCondition('eq', 'blocked', false),
  new FieldCondition('lt', 'lastLoggedIn', new Date(Date.now() - 24 * 3600 * 1000)),
]);

async function main() {
  const orm = await MikroORM.init({
    entities: [userSchema],
    dbName: ':memory:',
  });

  const interpret = createMikroORMInterpreter({
    metadata: orm.getMetadata(),
    platform: orm.em.getPlatform(),
  });
  const em = orm.em.fork();

  // Compose the raw condition with a native filter and a query alias.
  const qb = em.createQueryBuilder(User, 'u').select('*')
    .where({ name: 'Alice' })
    .andWhere(interpret(condition, User));
  console.log(qb.getQuery(), qb.getParams());
  await orm.close();
}

main().catch(console.error);

createMikroORMInterpreter({ metadata, platform }, operators?) configures entity lookup and the SQL dialect once. Operators default to allInterpreters; pass createSqlComposer in the configuration to customize SQL composition.

interpret(condition, Entity) accepts a MikroORM entity class or entity schema and returns a raw filter object containing the predicate and its bound values. Use it with em.find(User, interpret(condition, User)), compose it with native filters using $and/$or, or pass it to a query builder's .where()/.andWhere(). MikroORM supplies the root alias automatically, including for correlated relation subqueries. Create a fresh condition for each query because MikroORM consumes raw condition keys.

This replaces the previous query-builder argument and return value. The standalone interpret export is removed; create an interpreter with your ORM's metadata and platform instead.

TypeORM

interpret(condition) returns a native TypeORM Brackets predicate. Pass it to query-builder where, andWhere, or orWhere methods. This replaces the previous interpret(condition, queryBuilder) interface, which modified the builder.

Translation happens when the predicate is added to a query. The query builder provides its alias, entity metadata, and database dialect; no metadata storage needs to be configured on createTypeOrmInterpreter. Each application generates fresh parameter names of the form :ucast_<call>_<index>, including across interpreter instances. Predicates can be reused with different query aliases and combined without overwriting each other's parameters. Reserve the ucast_ parameter prefix for the adapter.

This integration supports query builders, not repository find options.

The adapter translates entity metadata with typeormToSqlMetadata(metadata). Pass createSqlComposer to createTypeOrmInterpreter and provide custom SqlEntityMetadata to createDefaultSqlComposer to extend that mapping. Composite relation joins and owning or inverse many-to-many joins use their physical database columns.

This example uses the better-sqlite3 driver package.

import { interpret } from '@ucast/sql/typeorm';
import { CompoundCondition, FieldCondition } from '@ucast/core';
import { DataSource, EntitySchema } from 'typeorm';

class User {
  id!: number;
  blocked!: boolean;
  name!: string;
  lastLoggedIn = new Date();
}

const userSchema = new EntitySchema({
  name: 'User',
  target: User,
  columns: {
    id: { type: Number, primary: true, generated: true },
    blocked: { type: Boolean },
    name: { type: String },
    lastLoggedIn: { type: Date },
  },
});

const condition = new CompoundCondition('and', [
  new FieldCondition('eq', 'blocked', false),
  new FieldCondition('lt', 'lastLoggedIn', new Date(Date.now() - 24 * 3600 * 1000)),
]);

async function main() {
  const conn = await new DataSource({
    type: 'better-sqlite3',
    database: ':memory:',
    entities: [userSchema]
  }).initialize();

  const qb = conn.createQueryBuilder(User, 'u')
    .where('u.name = :name', { name: 'Alice' })
    .andWhere(interpret(condition));
  console.log(qb.getQuery(), qb.getParameters());
  await conn.destroy();
}

main().catch(console.error);

TypeScript Support

Written in TypeScript and supports type inference for supported ORMs.

Want to help?

Want to file a bug, contribute some code, or improve documentation? Excellent! Read up on guidelines for contributing

License

Apache License, Version 2.0