SQL Query Engine & Syntax Reference | Valstorm Docs

The Valstorm SQL Query Engine provides a secure, ANSI SQL-like query interface over multi-tenant MongoDB collections and relational schemas with specialized extensions for CRM and operational datasets.

1. Core Syntax Overview

The engine supports standard SQL clauses, including:

SELECT (Specific fields, *, or table.*)
FROM (Target object/collection)
JOIN ... ON ... (Multi-join support)
WHERE (Filters, including complex ( ), AND, OR logic)
ORDER BY (Sorting via ASC or DESC)
LIMIT and OFFSET (Pagination)

2. Special Context Keywords

The `ME` Keyword

Use ME in your WHERE clause to automatically filter records owned by or related to the currently authenticated user session.

SQL

SELECT id, name, email FROM contact WHERE owner = ME

The `PHONE:` Resolver

The PHONE: prefix allows you to search across all phone fields on an object simultaneously without manually chaining OR conditions. It supports both exact matches (=) and partial matches (LIKE).

SQL

-- Exact number search across all phone properties
SELECT name, phone FROM lead WHERE PHONE: = '+15551234567'

-- Area code prefix search
SELECT name, phone FROM company WHERE PHONE: LIKE '+1555%'

Automatic Lookup Resolution

When querying against a lookup or compound_lookup field (like company, owner, or created_by), you do not need to append .id. The engine automatically resolves WHERE company = '123' to WHERE company.id = '123'.

3. Dynamic Date & Time Keywords

The engine features an intelligent datetime parser that automatically translates date keywords into precise UTC time ranges (>= start_of_period AND <= end_of_period).

Standard Relative Ranges

Days: today, yesterday, tomorrow, this_day, last_day
Weeks: this_week, last_week, next_week
Months: this_month, last_month, next_month
Quarters: last_quarter, next_quarter
Years: this_year, last_year, next_year
Trailing/Future blocks: last_7_days, last_30_days, last_90_days, next_7_days, next_30_days, next_90_days

Parameterized Relative Ranges (N-based)

Specify the exact number of periods to look back or forward using colon syntax:

Days / Weeks: last_n_days:10, next_n_days:5, last_n_weeks:3
Months / Years: last_n_months:6, next_n_years:2
Hours / Minutes: last_n_hours:24, last_n_minutes:30
Specific Weekdays: last_n_mondays:3, next_n_fridays:2

Contextual Specific Ranges

Pass an ISO date string to query the surrounding period:

month_of:'2026-05-15' (Resolves to May 1st – May 31st, 2026)
week_of:'2026-05-15' (Resolves to the Sunday–Saturday week)

4. Working Query Examples

High-Value Leads Assigned to Current User

SQL

SELECT name, status, loan_amount 
FROM lead 
WHERE owner = ME AND created_date = this_week AND loan_amount > 500000 
ORDER BY loan_amount DESC

Contacts Opted into SMS This Month

SQL

SELECT first_name, last_name, phone 
FROM contact 
WHERE sms_opt_in = true AND created_date = this_month

SQL

SELECT lead.name, lead.status, company.name, company.industry 
FROM lead 
JOIN company ON lead.company = company.id 
WHERE lead.status = 'Preapproved' AND company.annual_revenue > 500000

SQL Query Engine