@virlewood-open/universal-queries

Define filters once, convert them to SQL, JavaScript predicates, Dexie/IndexedDB, URL parameters, and natural language. One format, every backend.

Installation

npm install @virlewood-open/universal-queries

Quick Start

import { where, and, not } from '@virlewood-open/universal-queries';
import { toSQL } from '@virlewood-open/universal-queries/adapters/sql';
import { toPredicate } from '@virlewood-open/universal-queries/adapters/predicate';
import { toURLParams } from '@virlewood-open/universal-queries/adapters/url';
import { toNaturalLanguage } from '@virlewood-open/universal-queries/adapters/language';

// Define once
const filter = and(
  where('country').eq('Italy'),
  where('population').gt(100000),
  where('active').isTrue()
);

// SQL (PostgreSQL)
toSQL(filter);
// → { sql: '"country" = $1 AND "population" > $2 AND "active" IS TRUE', params: ['Italy', 100000] }

// SQL (MySQL)
toSQL(filter, { dialect: 'mysql' });
// → { sql: '"country" = ? AND "population" > ? AND "active" = TRUE', params: ['Italy', 100000] }

// SQL (DuckDB)
toSQL(filter, { dialect: 'duckdb' });
// → { sql: '"country" = ? AND "population" > ? AND "active" IS TRUE', params: ['Italy', 100000] }

// JavaScript predicate
const predicate = toPredicate(filter);
cities.filter(predicate);

// URL parameters
toURLParams(filter);
// → 'filter=eyJsb2dpYyI6ImFuZC...'

// Natural language
toNaturalLanguage(filter);
// → 'country equals "Italy" and population is greater than 100000 and active is true'

toNaturalLanguage(filter, 'it');
// → 'country è uguale a "Italy" e population è maggiore di 100000 e active è vero'

Operators (43)

Comparison

where('name').eq('Alice')           // equals (null-safe: emits IS NULL in SQL)
where('name').neq('Bob')            // not equals (null-safe: emits IS NOT NULL)
where('age').gt(18)                 // greater than
where('age').gte(18)                // greater than or equal
where('age').lt(65)                 // less than
where('age').lte(65)                // less than or equal
where('age').between(18, 65)        // between (inclusive)
where('age').notBetween(18, 65)     // not between
where('name').isDistinctFrom('x')   // NULL-safe not-equal (never returns NULL)
where('name').isNotDistinctFrom('x')// NULL-safe equal (never returns NULL)

Array Membership

where('status').in(['active', 'pending'])   // value is one of
where('status').notIn(['deleted'])          // value is not one of

in([]) returns FALSE, notIn([]) returns TRUE — both in SQL and predicates.

String Matching

// Case-sensitive
where('name').startsWith('Al')      // LIKE 'Al%'
where('name').endsWith('ice')       // LIKE '%ice'
where('name').contains('lic')       // LIKE '%lic%'

// Case-insensitive
where('name').istartsWith('al')     // PostgreSQL: ILIKE, MySQL: LOWER()+LIKE
where('name').iendsWith('ICE')
where('name').icontains('LIC')

Special LIKE characters (%, _, \) in values are automatically escaped.

Pattern Matching

where('path').like('/api/%')        // raw LIKE pattern (you control the wildcards)
where('path').ilike('/API/%')       // case-insensitive LIKE
where('path').notLike('/admin/%')
where('path').notILike('/ADMIN/%')

Regex

where('email').regex('^[a-z]+@')    // PostgreSQL: ~, MySQL: REGEXP
where('email').iregex('^[a-z]+@')   // PostgreSQL: ~*, MySQL: REGEXP
where('email').notRegex('^spam')    // PostgreSQL: !~, MySQL: NOT REGEXP

Null and Empty

where('field').isNull()             // null or undefined
where('field').isNotNull()          // not null
where('field').isEmpty()            // empty string, null, or undefined
where('field').isNotEmpty()         // has a non-empty value

isEmpty and isNotEmpty behave consistently across SQL and predicates — both treat null and '' as empty.

Boolean Tests

where('active').isTrue()            // PostgreSQL: IS TRUE, MySQL: = TRUE
where('active').isFalse()           // PostgreSQL: IS FALSE, MySQL: = FALSE

Array Column Operators

where('tags').arrayContains([1, 2])     // PostgreSQL: @>, MySQL: JSON_CONTAINS()
where('tags').arrayContainedBy([1,2,3]) // PostgreSQL: <@, MySQL: JSON_CONTAINS() reversed
where('tags').arrayOverlaps([1, 2])     // PostgreSQL: &&, MySQL: JSON_OVERLAPS()

JSON/JSONB Operators

where('meta').jsonContains({ role: 'admin' })  // PostgreSQL: @>, MySQL: JSON_CONTAINS()
where('meta').jsonContainedBy({ a: 1, b: 2 })  // PostgreSQL: <@, MySQL: JSON_CONTAINS() reversed
where('meta').jsonHasKey('email')               // PostgreSQL: ?, MySQL: JSON_CONTAINS_PATH()
where('meta').jsonHasAnyKey(['email', 'phone']) // PostgreSQL: ?|, MySQL: multiple JSON_CONTAINS_PATH OR
where('meta').jsonHasAllKeys(['email', 'phone'])// PostgreSQL: ?&, MySQL: multiple JSON_CONTAINS_PATH AND
where('meta').jsonPath('$.name')                // PostgreSQL: @?, MySQL: JSON_EXTRACT() IS NOT NULL

Full-Text Search

where('body').fullTextSearch('hello world')     // PostgreSQL: @@ to_tsquery(), MySQL: MATCH...AGAINST

Logical Operators

// AND — all must be true
and(
  where('country').eq('Italy'),
  where('active').eq(true)
)

// OR — any must be true
or(
  where('country').eq('Italy'),
  where('country').eq('Spain')
)

// NOT — negate any filter or group
not(where('active').eq(true))
not(and(where('country').eq('Italy'), where('active').eq(true)))

// Combine freely
and(
  where('active').isTrue(),
  or(where('country').eq('Italy'), where('country').eq('Spain')),
  not(where('status').eq('deleted'))
)

Full Queries

import { query } from '@virlewood-open/universal-queries';

const q = query()
  .where(and(where('country').eq('Italy'), where('active').eq(true)))
  .orderBy('name', 'asc')
  .orderBy('createdAt', 'desc')
  .limit(20)
  .offset(40)
  .build();

Adapters

SQL Adapter

import { toSQL, queryToSQL } from '@virlewood-open/universal-queries/adapters/sql';

// WHERE clause only
const { sql, params } = toSQL(filter);

// Full query
const result = queryToSQL(q, 'cities');
// → { fullQuery: 'SELECT * FROM "cities" WHERE ... ORDER BY ... LIMIT 20 OFFSET 40', sql, params }

// MySQL dialect
toSQL(filter, { dialect: 'mysql' });

// DuckDB dialect
toSQL(filter, { dialect: 'duckdb' });

Dialect notes — DuckDB uses ? placeholders (same as MySQL) and shares most of PostgreSQL's syntax: ILIKE, ~/~*/!~ for regex, IS TRUE/IS FALSE, IS DISTINCT FROM, BETWEEN. Where it differs, the adapter maps to DuckDB's native equivalents:

| Operator family | DuckDB output | |-----------------|---------------| | arrayContains / arrayContainedBy / arrayOverlaps | list_has_all / list_has_all (reversed) / list_has_any | | jsonContains / jsonContainedBy | json_contains (with reversed args for …ContainedBy) | | jsonHasKey / jsonHasAnyKey / jsonHasAllKeys / jsonPath | json_extract(col, '$.key') IS NOT NULL, chained with OR/AND for the multi-key variants | | fullTextSearch | Dropped by default (FTS needs the fts extension and a per-table index — no portable SQL to emit). Override with operators: { fullTextSearch: 'throw' } for strict behavior | | Geo operators | Unsupported by default; override per-operator if you have the spatial extension loaded |

Operator Overrides — control how unsupported operators behave per-dialect:

toSQL(filter, {
  dialect: 'mysql',
  onUnsupported: 'ignore',       // default for all unsupported operators
  operators: {
    arrayContains: 'fallback',   // use JSON_CONTAINS() workaround
    regex: 'throw',              // error if this operator is used
    fullTextSearch: 'ignore',    // silently drop this condition
  }
});

Three behaviors:

• 'fallback' (default) — use the best MySQL alternative
• 'ignore' — silently drop the condition
• 'throw' — throw a clear error

Predicate Adapter

import { toPredicate } from '@virlewood-open/universal-queries/adapters/predicate';

const predicate = toPredicate<City>(filter);

cities.filter(predicate);
cities.some(predicate);
cities.find(predicate);

Supports dot notation for nested objects:

where('address.city').eq('Rome')

Dexie Adapter

import { toDexie } from '@virlewood-open/universal-queries/adapters/dexie';

const collection = toDexie(db.cities, filter);
const results = await collection.toArray();

// With index hints
toDexie(db.cities, filter, { indexedFields: ['country', 'population'] });

Automatically uses indexed where() for the first indexable condition, applies remaining filters with predicates.

URL Adapter

import { toURLParams, fromURLParams, queryToURLParams, queryFromURLParams } from '@virlewood-open/universal-queries/adapters/url';

// Encode
const params = toURLParams(filter);
fetch(`/api/cities?${params}`);

// Decode
const filter = fromURLParams(window.location.search);

// Full query roundtrip
const encoded = queryToURLParams(q);
const decoded = queryFromURLParams(encoded);

Natural Language Adapter

import { toNaturalLanguage, queryToNaturalLanguage } from '@virlewood-open/universal-queries/adapters/language';

toNaturalLanguage(filter);
// → 'country equals "Italy" and population is greater than 100000'

toNaturalLanguage(filter, 'it');
// → 'country è uguale a "Italy" e population è maggiore di 100000'

// Same-field conditions are collapsed naturally
toNaturalLanguage(or(where('country').eq('Italy'), where('country').eq('Spain')));
// → 'country equals "Italy" or "Spain"'

// Custom formatters
toNaturalLanguage(filter, {
  fieldFormatter: (f) => f.replace(/_/g, ' '),
  valueFormatter: (v) => `[${v}]`,
});

// Full query
queryToNaturalLanguage(q);
// → 'country equals "Italy", sorted by name ascending, limit 20, starting from 40'

Supported languages: English (en), Italian (it).

Validation

Filters are plain JSON objects. When they come from external sources (APIs, databases, user input), you can validate them:

import { validateFilter, validateQuery } from '@virlewood-open/universal-queries';

// Standalone validation — throws a descriptive Zod error if invalid
const filter = validateFilter(JSON.parse(untrustedInput));
const query = validateQuery(JSON.parse(untrustedInput));

// Or validate inline in any adapter
toSQL(filter, { validate: true });
toPredicate(filter, { validate: true });
toURLParams(filter, { validate: true });
toNaturalLanguage(filter, { validate: true });

Validation is off by default (zero overhead). Turn it on at system boundaries where you receive filters you don't control.

Typed Field Definitions (defineFields)

For production APIs, you usually want to restrict which fields can be filtered and which operators are valid per field. defineFields gives you both compile-time type safety and runtime validation from a single definition:

import { defineFields } from '@virlewood-open/universal-queries';

const itemFilters = defineFields({
  name: 'string',
  age: 'number',
  active: 'boolean',
  type: { type: 'enum', values: ['A', 'B', 'C'] as const },
  metadata: 'json',
  tags: 'array',
  createdAt: 'date',
});

const { where, and, or, not, schema } = itemFilters;

Compile-Time Safety

The returned where function only exposes operators valid for each field's type:

where('name').contains('foo')     // ✅ string field → string operators
where('name').gt(5)               // ❌ TS error: gt not on StringWhereBuilder
where('age').gt(18)               // ✅ number field → number operators
where('age').contains('x')        // ❌ TS error: contains not on NumberWhereBuilder
where('type').eq('A')             // ✅ enum value
where('type').eq('D')             // ❌ TS error: 'D' not in ['A', 'B', 'C']
where('unknown').eq('x')          // ❌ TS error: 'unknown' is not a defined field

Runtime Validation

Use schema to validate incoming JSON at API boundaries:

// In your endpoint
app.post('/api/items', (req, res) => {
  try {
    const filter = itemFilters.schema.parse(req.body.filter);
    const { sql, params } = toSQL(filter);
    // ...
  } catch (err) {
    res.status(400).json({ error: 'Invalid filter' });
  }
});

The schema rejects:

• Unknown fields (field: 'unknown')
• Wrong operators for the field type (field: 'age', operator: 'contains')
• Invalid enum values (field: 'type', operator: 'eq', value: 'D')

Sharing Between Client and Server

Define once, use everywhere:

// shared/filters/items.ts
export const itemFilters = defineFields({
  name: 'string',
  type: { type: 'enum', values: ['A', 'B', 'C'] as const },
  metadata: 'json',
  age: 'number',
});

// client.ts — type-safe filter building
import { itemFilters } from '../shared/filters/items';
const { where, and } = itemFilters;

const filter = and(
  where('name').contains('foo'),
  where('metadata').jsonHasKey('theme'),
);
fetch('/api/items', { body: JSON.stringify({ filter }) });

// server.ts — runtime validation
import { itemFilters } from '../shared/filters/items';

app.post('/api/items', (req, res) => {
  const filter = itemFilters.schema.parse(req.body.filter);
  const { sql, params } = toSQL(filter);
  // ...
});

Field Types and Operators

| Type | Allowed operators | |------|------------------| | string | eq, neq, in, notIn, startsWith, endsWith, contains, istartsWith, iendsWith, icontains, like, ilike, notLike, notILike, regex, iregex, notRegex, isEmpty, isNotEmpty, isNull, isNotNull, isDistinctFrom, isNotDistinctFrom | | number | eq, neq, gt, gte, lt, lte, between, notBetween, in, notIn, isNull, isNotNull, isDistinctFrom, isNotDistinctFrom | | boolean | eq, neq, isTrue, isFalse, isNull, isNotNull, isDistinctFrom, isNotDistinctFrom | | date | eq, neq, gt, gte, lt, lte, between, notBetween, in, notIn, isNull, isNotNull, isDistinctFrom, isNotDistinctFrom | | enum | eq, neq, in, notIn, isNull, isNotNull, isDistinctFrom, isNotDistinctFrom | | json | jsonContains, jsonContainedBy, jsonHasKey, jsonHasAnyKey, jsonHasAllKeys, jsonPath, isNull, isNotNull | | array | arrayContains, arrayContainedBy, arrayOverlaps, isNull, isNotNull | | geo | geoNear, geoWithinBox, geoWithin, geoContains, geoIntersects, geoDistance |

Custom Operator Restrictions

Restrict specific fields to a subset of operators:

const strictFilters = defineFields({
  name: { type: 'string', operators: ['eq', 'neq', 'contains'] },
  status: { type: 'enum', values: ['active', 'inactive'] as const },
});

Standalone Schema (without typed builder)

If you only need runtime validation without the typed builder:

import { createFilterSchema } from '@virlewood-open/universal-queries';

const schema = createFilterSchema({
  email: 'string',
  role: { type: 'enum', values: ['admin', 'user'] as const },
});

schema.parse(req.body.filter);

Composing and Storing Filters

Filters are plain JSON — no class instances, no special prototypes:

// Store in a database
const filter = and(where('country').eq('Italy'), where('active').eq(true));
await db.saveFilter(JSON.stringify(filter));

// Load and use directly — no deserialization needed
const stored = JSON.parse(await db.getFilter());
cities.filter(toPredicate(stored));
toSQL(stored);

Compose stored filters with new conditions:

const savedFilter = JSON.parse(storedJson);
const combined = and(savedFilter, where('active').eq(true));

Merge multiple stored filters:

const roleFilter = JSON.parse(savedRoleFilter);
const regionFilter = JSON.parse(savedRegionFilter);
const merged = and(roleFilter, regionFilter, where('name').icontains(searchTerm));

This works because and(), or(), and not() accept any object matching the Filter shape.

Using with TanStack Table

toPredicate returns a standard (item) => boolean function, so it plugs directly into TanStack Table:

import { where, and, type Filter } from '@virlewood-open/universal-queries';
import { toPredicate } from '@virlewood-open/universal-queries/adapters/predicate';
import { toSQL } from '@virlewood-open/universal-queries/adapters/sql';
import { toURLParams, fromURLParams } from '@virlewood-open/universal-queries/adapters/url';
import { toNaturalLanguage } from '@virlewood-open/universal-queries/adapters/language';

// Your filter state — universal, not tied to any UI
const [filter, setFilter] = useState<Filter>(
  and(where('country').eq('Italy'), where('population').gt(1000000))
);

// Client-side filtering
const filteredRows = data.filter(toPredicate(filter));

// Server-side filtering
const response = await fetch(`/api/cities?${toURLParams(filter)}`);

// On the server
const filter = fromURLParams(req.query);
const { sql, params } = toSQL(filter);

// Shareable URLs
window.history.pushState({}, '', `?${toURLParams(filter)}`);

// Show active filters to the user
toNaturalLanguage(filter);
// → 'country equals "Italy" and population is greater than 1000000'

Your filter is the single source of truth. TanStack Table handles the UI, Universal Queries handles what the filter means — and that meaning flows to any backend, any URL, any human-readable label.

Filter Object Structure

Filters are plain JSON objects:

// Condition
{ field: 'country', operator: 'eq', value: 'Italy' }

// Group
{ logic: 'and', filters: [
  { field: 'country', operator: 'eq', value: 'Italy' },
  { field: 'active', operator: 'isTrue' }
]}

// Negation
{ not: { field: 'active', operator: 'eq', value: true } }

This makes them serializable, inspectable, and storable as JSON in any database.

Demo

Run the interactive demo locally:

npm run demo

Then open http://localhost:3000/examples/demo/ to see every operator converted to SQL, predicates, URL params, and natural language in real time.

License

MIT