OptiQL Query Language

SQL-like query language for exploring data across all entities — standard, custom, and dynamic objects

Overview

OptiQL is OptiCRM's built-in query language, inspired by Salesforce SOQL and Oracle ROQL. It lets you query any entity in the system using familiar SQL-like syntax — without writing raw SQL or knowing the database schema.

Access the interactive Query Console at Analytics > Query Console, or call the API directly at POST /api/query.

Quick Start

SELECT firstName, lastName, status
FROM Student
WHERE status = 'ACTIVE'
ORDER BY lastName ASC
LIMIT 20

This returns the first 20 active students sorted by last name. The query runs against the Student entity, which is a standard Prisma-backed table.

Syntax Reference

SELECT

Specify which fields to return. Use * for all fields.

-- Specific fields
SELECT firstName, lastName, email FROM Student
 
-- All fields
SELECT * FROM Enquiry
 
-- With aliases
SELECT firstName AS first, lastName AS last FROM Student
 
-- With aggregates
SELECT className, COUNT(id) AS total, AVG(attendanceRate) AS avgRate
FROM Student
GROUP BY className

FROM

Specify the entity to query. Use the entity's API name (PascalCase).

-- Standard entities
FROM Student
FROM Staff
FROM Enquiry
FROM Invoice
FROM Attendance
FROM Contact
FROM Guardian
FROM User
 
-- Finance entities
FROM FeeHead
FROM FeePlan
FROM Payment
FROM Receipt
FROM Expense
FROM Scholarship
FROM Concession
 
-- Dynamic objects (Transport, Library, Health, Hostel)
FROM Vehicle
FROM TransportRoute
FROM LibraryItem
FROM LibraryCopy
FROM LibraryIssue
FROM StudentHealthProfile
FROM HealthScreening
FROM SickBayVisit
FROM HostelRoom
FROM HostelAllotment
FROM GatePass
 
-- Custom objects (any object you've created in Object Manager)
FROM YourCustomObject

Entity names are case-sensitive and use PascalCase — exactly as they appear in the Object Manager. Use Student not students or STUDENT.

WHERE

Filter results with conditions. Supports multiple operators and AND/OR logic.

Comparison Operators

-- Equals
WHERE status = 'ACTIVE'
 
-- Not equals
WHERE status != 'INACTIVE'
WHERE status <> 'INACTIVE'
 
-- Greater/less than
WHERE amount > 10000
WHERE dob < '2010-01-01'
 
-- Greater/less than or equal
WHERE score >= 75
WHERE dueDate <= '2026-03-31'

String Matching

-- Contains (LIKE)
WHERE firstName LIKE 'Arav'
WHERE notes LIKE 'scholarship'

List Matching

-- IN list
WHERE stage IN ('APPLIED', 'DOCS_VERIFIED', 'ASSESSMENT')
 
-- NOT IN list
WHERE status NOT IN ('INACTIVE', 'SUSPENDED')

Null Checks

-- Is null
WHERE guardianEmail IS NULL
 
-- Is not null
WHERE mobile IS NOT NULL

Range

-- Between (inclusive)
WHERE amount BETWEEN 5000 AND 50000
WHERE dob BETWEEN '2010-01-01' AND '2015-12-31'

Combining Conditions

-- AND (both must be true)
WHERE status = 'ACTIVE' AND className = 'Class 10'
 
-- OR (either can be true)
WHERE source = 'WEBSITE' OR source = 'REFERRAL'
 
-- Multiple conditions
WHERE status = 'ACTIVE' AND gender = 'FEMALE' AND bloodGroup = 'O+'

GROUP BY

Aggregate results by one or more fields. Must be used with aggregate functions in SELECT.

-- Single grouping
SELECT className, COUNT(id) AS studentCount
FROM Student
WHERE status = 'ACTIVE'
GROUP BY className
 
-- With multiple aggregates
SELECT feeHeadName, COUNT(id) AS invoiceCount, SUM(totalAmount) AS totalDemand, SUM(paidAmount) AS totalCollected
FROM Invoice
GROUP BY feeHeadName
 
-- Multi-level grouping
SELECT className, gender, COUNT(id) AS count
FROM Student
GROUP BY className, gender

ORDER BY

Sort results. Default is ascending.

-- Ascending (default)
ORDER BY lastName ASC
 
-- Descending
ORDER BY createdAt DESC
 
-- Multiple sort fields
ORDER BY className ASC, rollNumber ASC

LIMIT

Restrict the number of rows returned. Default is 100 if not specified.

LIMIT 50
LIMIT 500

Aggregate Functions

Function	Description	Example
`COUNT(field)`	Count of rows	`COUNT(id) AS total`
`SUM(field)`	Sum of numeric values	`SUM(paidAmount) AS collected`
`AVG(field)`	Average of numeric values	`AVG(score) AS avgScore`
`MIN(field)`	Minimum value	`MIN(dob) AS youngest`
`MAX(field)`	Maximum value	`MAX(amount) AS highest`

SELECT COUNT(id) AS total, SUM(totalAmount) AS demand, SUM(paidAmount) AS collected,
       AVG(totalAmount) AS avgInvoice, MAX(totalAmount) AS largest
FROM Invoice
WHERE status != 'WAIVED'

Comments

-- This is a comment
SELECT firstName, lastName -- inline comments work too
FROM Student

Querying Standard Entities

Standard entities have typed Prisma fields. Use field names exactly as defined.

Student Fields

SELECT firstName, lastName, studentCode, dob, gender, bloodGroup, status, createdAt,
       className, sectionName, rollNumber, academicYear,
       guardianName, guardianRelation, guardianMobile, guardianEmail
FROM Student

Staff Fields

SELECT employeeCode, userName, userEmail, departmentName, designationName,
       joinDate, contractType, employmentStatus,
       panNumber, bankAccountNo, bankName, bankIfsc,
       emergencyContactName, emergencyContactPhone
FROM Staff

Enquiry Fields

SELECT refNumber, firstName, lastName, email, mobile, dob,
       gradeApplying, programmeInterest, source, stage,
       followUpDate, assignedToName, notes,
       utmSource, utmCampaign, utmMedium
FROM Enquiry

Invoice Fields

SELECT invoiceNo, studentName, feeHeadName,
       amount, lateFee, discount, totalAmount, paidAmount,
       dueDate, status, notes, createdAt
FROM Invoice

Attendance Fields

SELECT studentId, date, status, period, source, note
FROM Attendance

Querying Dynamic Objects

Dynamic objects (Transport, Library, Health, Hostel, and any custom objects) use JSONB storage. Query them using their field API names.

Example: Library Items

SELECT title, author, isbn, publisher, totalCopies, availableCopies
FROM LibraryItem
WHERE availableCopies > 0
ORDER BY title ASC

Example: Vehicles

SELECT registrationNo, type, make, model, capacity, isActive
FROM Vehicle
WHERE isActive = true
ORDER BY registrationNo ASC

Example: Gate Passes

SELECT studentId, type, purpose, outDate, status
FROM GatePass
WHERE status = 'PENDING'
ORDER BY outDate DESC

Example: Health Screenings

SELECT studentId, screeningType, date, height, weight, bmi, referralRequired
FROM HealthScreening
WHERE screeningType = 'ANNUAL'
ORDER BY date DESC
LIMIT 50

Querying Custom Objects

If you've created custom objects in the Object Manager, query them using their API name and field API names.

-- If you created a "Course" custom object with fields: name, code, credits, department
SELECT name, code, credits, department
FROM Course
WHERE credits >= 3
ORDER BY name ASC
 
-- Custom fields on standard entities use the custom_ prefix
SELECT firstName, lastName, custom_houseTeam, custom_sportPreference
FROM Student
WHERE custom_houseTeam = 'Blue Eagles'

API Usage

Endpoint

POST /api/query
Content-Type: application/json
Authorization: (session cookie)

Request

{
  "query": "SELECT firstName, lastName, status FROM Student WHERE status = 'ACTIVE' LIMIT 10"
}

Success Response

{
  "data": {
    "columns": [
      { "key": "firstName", "label": "firstName", "type": "text" },
      { "key": "lastName", "label": "lastName", "type": "text" },
      { "key": "status", "label": "status", "type": "select" }
    ],
    "rows": [
      { "firstName": "Aarav", "lastName": "Sharma", "status": "ACTIVE" },
      { "firstName": "Diya", "lastName": "Patel", "status": "ACTIVE" }
    ],
    "summary": null,
    "rowCount": 2
  },
  "parsed": {
    "sources": [{ "entity": "Student" }],
    "fields": [{ "field": "firstName" }, { "field": "lastName" }, { "field": "status" }],
    "filters": [{ "field": "status", "operator": "equals", "value": "ACTIVE" }],
    "limit": 10
  },
  "meta": {
    "entity": "Student",
    "rowCount": 2,
    "executedAt": "2026-04-14T..."
  }
}

Error Response

{
  "error": "Parse error: Expected KEYWORD but got \"FORM\"",
  "position": 35
}

Query Console UI

The interactive Query Console at Analytics > Query Console provides:

Dark-themed editor with monospace font
Ctrl+Enter to execute queries
7 example queries to get started quickly
Results table with sticky headers, row numbers, and formatting
Copy to clipboard — tab-separated for pasting into spreadsheets
CSV download — exports results as a CSV file
Execution time and row count displayed
Parse error highlighting with position indicator
Syntax reference card at the bottom of the page

Common Query Patterns

Counts and Summaries

-- Total students by status
SELECT status, COUNT(id) AS count
FROM Student
GROUP BY status
 
-- Revenue by fee head
SELECT feeHeadName, SUM(paidAmount) AS revenue, COUNT(id) AS invoices
FROM Invoice
WHERE status IN ('PAID', 'PARTIAL')
GROUP BY feeHeadName
ORDER BY revenue DESC

Finding Records

-- Students without guardians
SELECT firstName, lastName, studentCode
FROM Student
WHERE guardianName IS NULL
 
-- Overdue invoices over ₹10,000
SELECT invoiceNo, studentName, totalAmount, dueDate
FROM Invoice
WHERE status = 'OVERDUE' AND totalAmount > 1000000
ORDER BY totalAmount DESC

Date Filtering

-- Attendance this week
SELECT studentId, date, status
FROM Attendance
WHERE date >= '2026-04-07' AND date <= '2026-04-14'
 
-- Recent enquiries
SELECT firstName, lastName, stage, source, createdAt
FROM Enquiry
ORDER BY createdAt DESC
LIMIT 20

Limitations

No subqueries — WHERE id IN (SELECT ...) is not supported
No JOIN — cross-entity joins are parsed but executed as single-entity queries (JOIN support is planned)
No HAVING — filter aggregated results using application logic
No UNION — combine results from multiple queries in application code
100 row default — specify LIMIT explicitly for larger result sets (max 1000)
Read-only — OptiQL is for querying only, not INSERT/UPDATE/DELETE