Note: The BI SQL API is accessible only to accounts on the Pro Plan
Overview
The BI SQL API provides a PostgreSQL-compatible interface to query Boxly's semantic layer. This guide covers everything you need to get started, including connection setup, query patterns, supported operations, and best practices specific to your analytics infrastructure.
Important: The data that you receive is not live. It is refreshed daily around 1 - 2 AM UTC.
Note: The SQL API is provided as a convenience for BI tools that cannot use the HTTP Query API. If possible, we recommend using the Query API.
The Query API is more natural and well documented for Cube.js - the engine that powers Boxly’s semantic layer.
Connection Details
For connection instructions, please log into app.boxly.ai and view the “SQL Data Access” section in “Settings”.
SQL Commands Reference
SELECT - Retrieve Tables Information
SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';
SELECT - Retrieve Data
Syntax
SELECT select_expr [, ...] FROM from_item [CROSS JOIN join_item ON join_criteria]* [WHERE where_condition] [GROUP BY grouping_expression] [HAVING having_expression] [LIMIT number] [OFFSET number];
Example 1 - Count Users by Account
SELECT COUNT(*) as total_users, account_id FROM eb_pub_users GROUP BY account_id ORDER BY total_users DESC;
Example 2 - Active Users with Details
SELECT id, username, email, created_at FROM eb_pub_users WHERE is_active = true AND is_deleted = false LIMIT 100;
Example 3 - Get Leads from Last 120 Days
SELECT id, name, email, phone_number, created_at FROM eb_pub_leads WHERE created_at >= NOW() - INTERVAL '30 days' ORDER BY created_at DESC;
JOINS
Cube SQL API does NOT support traditional ON clause joins. Use CROSS JOIN with table names.
Correct Pattern
In the Query we add in names of the tables we want to join along with alias, Our data model will take care on which column it needs to join and will provide the results. Only Cross Join is supported. The Join will always be applied as Left Join.
SELECT leads.id, leads.name, accounts.account_name FROM eb_pub_leads as leads CROSS JOIN eb_pub_accounts as accounts;
Wrong Pattern
In the Query if we add in the traditional way of joining using ON clause, the query will fail. You cannot use any other join apart from Cross Join.
SELECT leads.id, leads.name, accounts.account_name FROM eb_pub_leads as leads Inner JOIN eb_pub_accounts as accounts on leads.iaccount_id = accounts.id;
Example 1 - Leads with Box Information
SELECT leads.name, leads.email, boxes.box_name, boxes.box_id, leads.created_at FROM eb_pub_leads as leads CROSS JOIN eb_pub_boxes as boxes ORDER BY leads.created_at DESC LIMIT 50;
Example 2 - Multiple Joins
select leads_milestone.lead_id, leads.name, milestones.milestone_name, leads_milestone.value from eb_pub_lead_milestones as leads_milestone cross join eb_pub_leads as leads cross join eb_pub_milestones as milestones cross join eb_pub_boxes as box cross join eb_pub_box_stages as stages;
Best For
Works seamlessly with BI tools like Tableau, Superset, Metabase, Power BI.
Provides consistent metrics access for any SQL client, ideal for metrics stores serving diverse consumers.
Facilitates self-service analytics for business users.
Limitations
Only cross joins are supported; other join types (inner, left, right) are not supported in SQL API queries.
You cannot query beyond 15 dimensions at a single point in a SQL SELECT statement.
The SQL API does not support arbitrary raw SQL; queries must map to Cube's cube model and measures.
Query pushdown is limited and not all SQL features or complex queries are supported.
SQL Functions & Operators
COMPARISON OPERATORS
Function
Function Description
<
Returns TRUE if the first value is less than the second
>
Returns TRUE if the first value is greater than the second
<=
Returns TRUE if the first value is less than or equal to the second
>=
Returns TRUE if the first value is greater than or equal to the second
=
Returns TRUE if the first value is equal to the second
<> or !=
Returns TRUE if the first value is not equal to the second
COMPARISON PREDICATES
Function
Function Description
BETWEEN
Returns TRUE if the first value is between the second and the third
IS NULL
Test whether value is NULL
IS NOT NULL
Test whether value is not NULL
MATHEMATICAL FUNCTIONS
Function
Function Description
ABS
Absolute value
CEIL
Nearest integer greater than or equal to argument
DEGREES
Converts radians to degrees
EXP
Exponential (e raised to the given power)
FLOOR
Nearest integer less than or equal to argument
LN
Natural logarithm
LOG
Base 10 logarithm
LOG10
Base 10 logarithm (same as LOG)
PI
Approximate value of π
POWER
a raised to the power of b
RADIANS
Converts degrees to radians
ROUND
Rounds v to s decimal places
SIGN
Sign of the argument (-1, 0, or +1)
SQRT
Square root
TRUNC
Truncates to integer (towards zero)
STRING FUNCTIONS AND OPERATORS
Function
Function Description
||
Concatenates two strings
BTRIM
Removes the longest string containing only characters in characters from the start and end of string
BIT_LENGTH
Returns number of bits in the string (8 times the OCTET_LENGTH)
CHAR_LENGTH or CHARACTER_LENGTH
Returns number of characters in the string
LOWER
Converts the string to all lower case
LTRIM
Removes the longest string containing only characters in characters from the start of string
OCTET_LENGTH
Returns number of bytes in the string
POSITION
Returns first starting index of the specified substring within string, or zero if it's not present
RTRIM
Removes the longest string containing only characters in characters from the end of string
SUBSTRING
Extracts the substring of string
TRIM
Removes the longest string containing only characters in characters from the start, end, or both ends of string
UPPER
Converts the string to all upper case
OTHER STRING FUNCTIONS
Function
Function Description
ASCII
Returns the numeric code of the first character of the argument
CONCAT
Concatenates the text representations of all the arguments
LEFT
Returns first n characters in the string, or when n is negative, returns all but last ABS(n) characters
REPEAT
Repeats string the specified number of times
REPLACE
Replaces all occurrences in string of substring from with substring to
RIGHT
Returns last n characters in the string, or when n is negative, returns all but first ABS(n) characters
STARTS_WITH
Returns TRUE if string starts with prefix
PATTERN MATCHING
Function
Function Description
LIKE
Returns TRUE if the string matches the supplied pattern
REGEXP_SUBSTR
Returns the substring that matches a POSIX regular expression pattern
DATA TYPE FORMATTING FUNCTIONS
Function
Function Description
TO_CHAR
Converts a timestamp to string according to the given format
DATE/TIME FUNCTIONS
Function
Function Description
DATE_ADD
Add an interval to a timestamp with time zone
DATE_TRUNC
Truncate a timestamp to specified precision
DATEDIFF
Returns the difference between the date parts of two date or time expressions
EXTRACT
Retrieves subfields such as year or hour from date/time values
LOCALTIMESTAMP
Returns the current date and time without time zone
NOW
Returns the current date and time with time zone
CONDITIONAL EXPRESSIONS
Function
Function Description
CASE
Generic conditional expression
COALESCE
Returns the first of its arguments that is not NULL
NULLIF
Returns NULL if both arguments are equal, otherwise returns the first argument
GREATEST
Select the largest value from a list of expressions
LEAST
Select the smallest value from a list of expressions
GENERAL-PURPOSE AGGREGATE FUNCTIONS
Function
Function Description
AVG
Computes the average (arithmetic mean) of all the non-NULL input values
COUNT
Computes the number of input rows in which the input value is not NULL
MAX
Computes the maximum of the non-NULL input values
MIN
Computes the minimum of the non-NULL input values
SUM
Computes the sum of the non-NULL input values
ROW AND ARRAY COMPARISONS
Function
Function Description
IN
Returns TRUE if a left-side value matches any of right-side values
NOT IN
Returns TRUE if a left-side value matches none of right-side values
ER Diagram
Tables Information
eb_pub_accounts Stores account (sub-organisation) information including industry type, location settings, and organisation configuration.
name
type
description
id
UUID
Primary key. Unique identifier for the account record.
created_at
TIMESTAMP
Timestamp when the account was created.
updated_at
TIMESTAMP
Timestamp when the account was last updated.
account_id
UUID
Account ID
account_name
VARCHAR(255)
Account's name
eb_pub_box_stages Stores stage definitions within boxes that represent different steps in the lead pipeline.
name
type
description
id
UUID
Primary key. Unique identifier for the stage.
created_at
TIMESTAMP
Timestamp when the stage was created.
updated_at
TIMESTAMP
Timestamp when the stage was last updated.
account_id
UUID
Account ID for user
stage_id
UUID
Stage ID
box_id
UUID
ID of the associated box
stage_name
VARCHAR(255)
Name of the box stage
milestone_id
UUID
Associated milestone ID
is_milestone_activated
BOOLEAN
Flag if the milestone for this stage is activated
eb_pub_boxes Stores boxes (lead containers/pipelines) that organise leads within an account.
name
type
description
id
UUID
Primary key. Unique identifier for the box.
created_at
TIMESTAMP
Timestamp when the box was created.
updated_at
TIMESTAMP
Timestamp when the box was last updated.
box_id
UUID
Unique identifier for the box
box_name
VARCHAR(255)
Name of the box
account_id
UUID
Account ID for user
eb_pub_call_log Stores call log records for leads including call duration, outcome, and notes.
