Queries

The query builder lets your users say which records they mean without writing a query: rules joined by AND or OR, groups inside groups, and NOT. Each field has a type, and each type offers the comparisons that make sense for it — between two numbers, in the last thirty days, any of these tags — with the right input for the value: a number, a date, a list to choose from.

The finished query comes out as SQL with parameters for six databases, a MongoDB filter, an OData $filter, JsonLogic, a function for filtering records in the page, or a plain sentence. The same conversions run on your server from bmx-query.mjs, so the server checks what the page sent with the same fields.

Find the customers

Forty customers and a filter over them. Change a rule, add one, group two with OR, or switch one off with its checkbox; the list underneath follows as you go. Drag a rule by its handle to move it into another group.

Check the query

Clear

Customers that match the filter
Name	Country	Age	Spent	Limit	Plan	Joined	Tags

Keyboard: every part of a rule is a field you Tab to. On a rule's handle, the Up and Down arrows move it, and the buttons beside it copy it, switch it off and remove it.

Get the code

<script src="/assets/bmx-components.min.js"></script>

<bmx-query-builder id="qy-customers" label="Customer filter" locale="en-GB" style="inline-size: 100%"></bmx-query-builder>

In your database's own language

The same query as SQL for ANSI, PostgreSQL, MySQL, SQL Server, SQLite or Oracle — with the values as parameters, never pasted into the text — and as a MongoDB filter, an OData $filter and JsonLogic. The tabs under the builder show each one as the query changes.

ANSI

PostgreSQL

MySQL

SQL Server

SQLite

Oracle

Get the code

<script src="/assets/bmx-components.min.js"></script>

<bmx-select id="qy-dialect" label="SQL for" value="postgres" style="inline-size: 12rem">
  <bmx-option value="ansi">ANSI</bmx-option>
  <bmx-option value="postgres">PostgreSQL</bmx-option>
  <bmx-option value="mysql">MySQL</bmx-option>
  <bmx-option value="sqlserver">SQL Server</bmx-option>
  <bmx-option value="sqlite">SQLite</bmx-option>
  <bmx-option value="oracle">Oracle</bmx-option>
</bmx-select>
<bmx-query-builder id="qy-orders" preview="sql mongo odata jsonlogic text" sql-dialect="postgres" label="Order filter" locale="en-GB" style="inline-size: 100%"></bmx-query-builder>

Read back as a sentence

With readonly, a saved query reads as words, for a summary beside a saved search or a report. This one is the filter at the top of the page, kept in step with it.

Get the code

<script src="/assets/bmx-components.min.js"></script>

<bmx-query-builder id="qy-read" readonly label="The customer filter, as words" locale="en-GB" style="inline-size: 100%"></bmx-query-builder>

An alert rule in a form

With a name, the query goes into its form as JSON. Here it must have at least one finished rule, AND is the only join offered, and groups go one level deep: the builder can be as simple as the job needs.

Get the code

<script src="/assets/bmx-components.min.js"></script>

<bmx-input name="title" label="Alert name" value="Busy production server" required="true" style="inline-size: 18rem; max-inline-size: 100%"></bmx-input>
<bmx-query-builder id="qy-alert" name="rule" required combinators="and" max-depth="2" label="Alert when" locale="en-GB" style="inline-size: 100%"></bmx-query-builder>

The same checks on your server

Every package carries dist/node/bmx-query.mjs: the builder's checks and conversions with nothing that needs a page, for Node, a bundle or a Web Worker. The server reads the query the page sent, with its own list of fields, so a reader can only ever ask for what the server allows.

import { normalizeQuery, validateQuery, queryToSql } from './dist/node/bmx-query.mjs';

const FIELDS = [
  { name: 'country', type: 'list', options: COUNTRIES },
  { name: 'spent', type: 'number' },
  { name: 'joined', type: 'date' },
];

const query = normalizeQuery(JSON.parse(request.body.filter), FIELDS);
if (validateQuery(query, FIELDS).length) throw new Error('The filter is not finished.');

const { sql, params } = queryToSql(query, FIELDS, { dialect: 'postgres' });
const rows = await db.query(`SELECT * FROM customers WHERE ${sql || 'TRUE'}`, params);