dexterquery

A React query builder for people who don't write query languages.

The user describes what they want in a sentence — "I want a list of Subscribers where their Last name contains Smith" — and you get back a MySQL WHERE predicate with the values kept out of the SQL as ? parameters.

I want a list of Subscribers
where their  [Last name ▾]   [contains ▾]     [Smith        ]
  and their  [Monthly spend ▾] [is at least ▾] [100       ]   ( + )

{ sql: "`LastName` LIKE ? AND `MonthlySpend` >= ?", params: ["%Smith%", 100] }

Install

npm install dexterquery

react and react-dom (18 or 19) are peer dependencies.

Simplest possible use

Two required props — what the list is called, and what columns exist. One callback to receive the query.

import { QueryBuilder } from 'dexterquery';
import 'dexterquery/styles.css';

export function Segment() {
  return (
    <QueryBuilder
      entity="Subscribers"
      columns={[
        { name: 'LastName', type: 'string' },
        { name: 'MonthlySpend', type: 'number', label: 'Monthly spend' },
        { name: 'IsActive', type: 'boolean', label: 'Active' },
        { name: 'SignedUpAt', type: 'timestamp', label: 'Signed up' },
      ]}
      onQueryChange={({ sql, params }) => console.log(sql, params)}
    />
  );
}

Don't forget the stylesheet — the component ships its own CSS and looks unstyled without it.

Running the query

sql is the predicate without the WHERE keyword, and '' when nothing has been filled in. That way it drops into a query that already has conditions of its own:

const [rows] = await db.query(
  `SELECT * FROM subscribers WHERE tenant_id = ?${query.sql ? ` AND (${query.sql})` : ''}`,
  [tenantId, ...query.params],
);

Values are always ? placeholders — user input never reaches the SQL string, so there is nothing to escape. % and _ typed into a "contains" box are escaped as literals rather than acting as wildcards.

Props

| prop | type | required | what it does | | --- | --- | --- | --- | | entity | string | yes | The list's name, shown verbatim on the first line. Plural — nothing is inflected. | | columns | Column[] | yes | The fields conditions can be built against. | | lead | string | no | Opening words before the entity name. Defaults to "I want a list of". | | onQueryChange | (q: MySqlQuery) => void | no | Fires whenever the predicate changes. | | onStateChange | (s: QueryState) => void | no | Fires whenever the user's working state changes, including unfinished rows. | | initialState | QueryState | no | A saved state to open with. Read once, on mount. | | onSuggest | SuggestionSource | no | Supplies value suggestions for text columns. | | transactions | TransactionsConfig | no | Offers conditions over each person's purchases. See Purchase conditions. | | readOnly | boolean | no | Shows the query without letting it be changed. See Read only. |

Columns

interface Column {
  name: string;      // the real column name; backtick-quoted in the SQL
  type: ColumnType;  // 'string' | 'number' | 'boolean' | 'date' | 'timestamp'
  label?: string;    // what the user sees; defaults to `name`
}

type is the important one. It decides which operators are offered, which input appears, and what SQL comes out.

• string — text box, LIKE matching, optional suggestions.
• number — numeric box that accepts decimals and negatives.
• boolean — no value slot; the operator says it all ("is true").
• date — a calendar day. Date picker, whole-day comparisons.
• timestamp — a point in time. Day-level operators use a date picker; is at or after / is at or before use a date-and-time picker with seconds.

Labels pick up a type word where it helps the sentence read — a boolean Active shows as "Active flag", a timestamp Signed up as "Signed up timestamp" — unless the label already says so.

Operators

Offered per column type. You never configure these; they follow from type.

| type | operators | | --- | --- | | string | contains · does not contain · is · is not · starts with · ends with · is empty · is not empty | | number | is · is not · is at least · is at most · is between · is empty · is not empty | | boolean | is true · is false · is empty | | date | is on · is on or after · is on or before · is in the last N days · is today (dd/mm/yy) · is today (dd/mm) · is empty · is not empty | | timestamp | all of the date ones, plus is at or after · is at or before |

Some behaviour worth knowing:

• Dates work in whole days. "is on the 6th" matches any time that day, so a DATETIME column behaves the way the sentence reads. Written as range comparisons, so an index on the column is still usable.
• "is today (dd/mm)" matches the same day and month in any year — birthdays. 29 February falls back to the 28th in non-leap years.
• NULL follows the wording. "does not contain" and "is not" include rows where the column is NULL, which plain SQL would drop. "is empty" on text covers both NULL and ''.
• Unfinished rows contribute nothing rather than emitting broken SQL.

Grouping

Conditions live in groups. Within a group they share one connector — all must match, or any may. Groups are always ORed with each other.

(`LastName` LIKE ? AND `MonthlySpend` >= ?) OR `IsActive` IS TRUE

The user adds a row with the + at the end of the last row (hover it for and / or), and starts a second group with New group, which appears once two conditions are filled in. There are exactly two levels — no nesting — so the output never depends on operator precedence.

Value suggestions

Give the user a list of real values as they type. You supply the data; the component handles debouncing, cancelling, keyboard and rendering.

<QueryBuilder
  onSuggest={async ({ column, operator, value, signal }) => {
    const res = await fetch(
      `/api/values?column=${column.name}&op=${operator}&q=${encodeURIComponent(value)}`,
      { signal },
    );
    return res.json(); // string[]
  }}
  …
/>

• Calls are debounced by 250ms and only fire for text columns with something typed.
• Superseded calls are aborted — pass signal to fetch and they stop hitting your backend.
• A late reply from a stale request can never overwrite a newer one.
• At most 5 suggestions are shown (MAX_SUGGESTIONS).
• Suggestions are a hint, not a constraint: the user can type anything.
• If your source throws, the row carries on without suggestions.

Purchase conditions

Pass transactions and the field menu gains a second section, Their purchases, for conditions over each person's transactions rather than one of their fields:

I want a list of Members
where their  [Gender ▾]  [is ▾]  [F      ]
  and they   [spent ▾]   [at least ▾] $[500]  [in the last ▾] [180] days
      only items whose [Brand ▾] [is ▾] [Nike   ]
      + only items whose…   + only transactions whose…

<QueryBuilder
  entity="Members"
  columns={memberColumns}
  transactions={{
    transactionColumns: [{ name: 'Store', type: 'string' }],
    itemColumns: [{ name: 'Category', type: 'string' }, { name: 'Brand', type: 'string' }],
    currencySymbol: '$', // the default
  }}
  onStateChange={setState}
/>

• Three verbs. "made N transactions" counts them, "bought N items" sums item quantities, and "spent" sums money — the transactions' totals, or once an "only items whose…" line is added, just those items' line totals. "Spent $500 on Nike" means the Nike lines, not the baskets that held them.
• Comparisons: at least, at most, exactly, between. Windows: in the last N days, since a date, between two dates, or ever.
• Filter lines narrow which transactions or items count. All must hold. Under "made", an item line reads "only transactions with an item whose…".

A purchase condition is saved in the state like any other, with kind:

{
  "kind": "transactions",
  "column": null,
  "metric": "sum_line_total",
  "window": { "in_last_days": 180 },
  "where": [{ "scope": "item", "column": "Brand", "operator": "eq", "value": "Nike" }],
  "operator": "gte",
  "value": "500"
}

metric is count, sum_total_amount, sum_line_total or sum_quantity. window is null for all time; otherwise the keys present say which kind it is, so an unfinished one keeps its shape with blank values ({ "from": "", "to": "" }). Treat a window with blank values as unfinished, never as all time.

These are not in the SQL. The SQL is over the list's own table, and purchases live elsewhere, so onQueryChange leaves them out and sets omitted to how many finished ones it left out. Evaluate them from the state. If you run sql yourself, refuse while omitted is set — running it anyway selects more people than the user asked for.

Suggestions work on filter lines too. The request carries scope: 'header' for a transaction field or 'item' for an item field, so you can look values up in the right place.

On load, a purchase condition the builder can no longer show faithfully — a filter field that has gone, an unknown metric, purchases not enabled — is shown read-only, as it was configured, with a ⚠️ and a remove button. It takes no part in the state or the query until the user replaces it.

describeTransactionCondition(condition, config) gives the condition as one line of text ("spent at least $500 in the last 180 days, only items whose Brand is Nike"), for listing saved segments.

Saving and loading

onQueryChange gives you SQL to run. onStateChange gives you what the user has built, including rows they haven't finished — a serialisable snapshot to store alongside a saved segment.

{
  "groups": [
    {
      "match": "all",
      "conditions": [
        { "column": "LastName", "operator": "contains", "value": "Smith" },
        { "column": null, "operator": "contains" }
      ]
    }
  ]
}

Pass one back as initialState to open the builder on a saved query:

<QueryBuilder initialState={saved} columns={columns} entity="Subscribers" />

initialState is read once, on mount. To swap in a different saved query — or to load one that arrives from a fetch after the first render — give the component a key that changes with it, which is React's way of saying "this is a different editor now":

<QueryBuilder key={saved.id} initialState={saved.state} … />

Saved queries outlive schemas

A stored query can be older than the columns it was built against, so loading never trusts it blindly. initialState is passed through normalizeState, which is also exported if you want to check a state before storing it:

• a condition naming a column that no longer exists is dropped;
• an operator that does not apply to the column's current type is replaced with that type's default, and its value cleared;
• a value belonging to a slot the operator no longer has is dropped;
• a group left with no conditions is dropped;
• if nothing survives, you get one empty group, so the builder always has a row.

Read only

To show a saved query rather than edit it, add readOnly:

<QueryBuilder key={saved.id} initialState={saved.state} columns={columns} entity="Subscribers" readOnly />

Every menu and input is disabled and styled as plain words in the sentence, and nothing that adds, removes or regroups conditions is rendered: no "+", no "×", no and / or toggles, no "+ only … whose" and no "+ New group". Conditions that no longer apply are still shown, but cannot be removed. onQueryChange and onStateChange still fire, so a host can count or preview what is shown.

Styling

All appearance comes from dexterquery/styles.css. Every colour is a custom property. The stylesheet never declares them, it only reads them with its own light value as the fallback, so you set the ones you want and the rest stay as they are. Your values win however the stylesheets are ordered.

The three that cover most of a light theme:

:root {
  --dexterquery-fg: #16181d;      /* chosen values, suggestion text */
  --dexterquery-accent: #2f6feb;  /* the entity pill, links, focus rings */
  --dexterquery-border: #d5d9e0;  /* value boxes, stale rows */
}

For a dark theme, set the rest as well. These are the light values:

| property | light value | used for | | --- | --- | --- | | --dexterquery-color-scheme | light | native dropdowns and date pickers; set dark | | --dexterquery-text | #4a5060 | the sentence's own words, operators | | --dexterquery-connector | #808080 | "where their", "and they" | | --dexterquery-text-muted | #6b7180 | and/or pills, stale rows, "+ only … whose" | | --dexterquery-text-faint | #8a90a0 | "choose a field…", placeholders | | --dexterquery-text-fainter | #a6abb8 | the resting "+" and "×" | | --dexterquery-accent-soft | #eaf1fe | the entity pill, hovers, the active suggestion | | --dexterquery-accent-line | #c2d3f5 | accent borders on hover | | --dexterquery-control-bg | #f2f4f8 | field menus, the "or" between groups | | --dexterquery-control-hover-bg | #e7eaf1 | field menus on hover | | --dexterquery-input-bg | #fff | value boxes, the suggestion list | | --dexterquery-input-hover-border | #b9c0cc | value boxes on hover | | --dexterquery-surface | #fafbfc | a group's box, once there are two | | --dexterquery-surface-muted | #f4f5f7 | a stale (read-only) condition | | --dexterquery-border-soft | #e2e5eb | group boxes, and/or buttons, the suggestion list | | --dexterquery-tooltip-bg / -fg | #22252c / #fff | the ⚠️ tooltip | | --dexterquery-shadow | rgba(22, 24, 29, 0.16) | tooltip and suggestion list shadows | | --dexterquery-danger / -soft | #b3313d / #fdeced | the "×" on hover | | --dexterquery-guide | #cfd4dc | the guide lines linking each condition to its group |

Set them under whatever selects your dark theme:

html.dark {
  --dexterquery-color-scheme: dark;
  --dexterquery-fg: #f5f7fa;
  --dexterquery-input-bg: #1f1b2e;
  /* … */
}

Everything else is a .dexterquery-* class you can override.

Using the SQL builder without the UI

The query layer is exported on its own, if you have your own interface:

import { buildWhere, quoteIdentifier, isConditionComplete } from 'dexterquery';

buildWhere([
  {
    match: 'all',
    conditions: [
      { column: { name: 'LastName', type: 'string' }, operator: 'contains', value: 'Smith' },
    ],
  },
]);
// { sql: "`LastName` LIKE ?", params: ["%Smith%"] }

Also exported: OPERATORS, operatorsFor, operatorLabel, operatorSpec, defaultOperatorFor, columnDisplayName, COLUMN_TYPES, describeTransactionCondition, isTransactionConditionComplete, metricFor, verbOf, TRANSACTION_METRICS, AGGREGATE_OPERATORS, and the types Column, ColumnType, Condition, ConditionGroup, MySqlQuery, MatchMode, Operator, OperatorSpec, QueryState, SuggestionRequest, SuggestionSource, TransactionsConfig, TransactionMetric, TransactionWindow, TransactionFilter, TransactionVerb, AggregateOperator.

Limitations

• MySQL only. The output uses backtick quoting and MySQL date functions.
• One table in the SQL. No joins. Purchase conditions are described in the state only; evaluating them is the host's job.
• English only, with DD/MM/YYYY dates. Only lead is configurable copy.
• is today (dd/mm) can't use an index — it strips the year from every row.
• CURDATE() is the database server's today, which may not match the user's.

Documentation

A standalone reference page lives at docs/index.html — open it in a browser, or serve docs/ anywhere static. It is self-contained: no build step, no external requests.

Testing

170 tests, run with Vitest and Testing Library.

npm test           # ~5s
npm run coverage   # text + html report

| area | tests | holds to account | | --- | --- | --- | | mysql | 49 | every operator's SQL, wildcard escaping, NULL semantics, grouping, injection safety | | QueryBuilder | 31 | the whole component, driven by clicks and typing | | operators | 16 | the operator table and each type's menu | | SuggestInput | 16 | combobox behaviour, keyboard, ARIA | | ValueInput | 15 | each input kind, and what it refuses | | suggestions | 13 | debounce, abort, out-of-order replies | | state | 12 | save, load, schema drift | | types | 10 | display-name rules |

Coverage: 97.9% statements, 94.1% branches, 100% functions, 99.7% lines. What remains is defensive — guards the UI cannot reach.

The SQL layer is tested directly; the component is driven through the DOM the way a person drives it. That second part earned its keep: a datetime-local value normalises to 2026-08-06T02:50:31.000 once seconds are involved, and the parser was rejecting the fractional part — so a precise cut-off time was silently dropped from the query. Only visible by driving the real input.

Development

npm install
npm run dev        # playground at http://localhost:5173
npm test           # vitest
npm run test:watch
npm run build      # dist/ — ESM + CJS + .d.ts + CSS
npm run typecheck

Tests live next to what they cover, as *.test.ts(x). The SQL, operator, display-name and state modules are tested directly; QueryBuilder and the suggestion combobox are driven the way a user drives them, through the DOM.

The playground in dev/ imports the library by package name, aliased to src/, so edits hot-reload with no rebuild. It also has editable panels for the column schema and the demo suggestion data.

src/
  index.ts        public entry — everything exported here is public API
  components/     QueryBuilder, ConditionRow, ValueInput, SuggestInput
  mysql.ts        predicate building; the only place SQL is written
  operators.ts    the operator table: labels, arity, which types they apply to
  types.ts        Column, ColumnType, display names
  suggestions.ts  debounce/abort logic for the suggestion source
  state.ts        the serialisable snapshot shape
dev/              playground; never published