Queries

BoxLang provides the easiest way to query databases with powerful SQL execution and manipulation capabilities

A query is a BoxLang data type that represents tabular data from database operations or programmatic construction. It stores rows and columns of data along with metadata about the query execution and structure.

CFML became famous in its infancy because it was easy to query databases with a simple cfquery tag and no verbose ceremonious coding. BoxLang continues this tradition while adding modern features like functional programming operations, query metadata access, and flexible datasource configuration.

Java Interoperability: BoxLang queries implement Java's Collection<IStruct> interface, making them compatible with Java collection operations and frameworks. Each row can be treated as a Map<Key, Object> in Java code.

All BoxLang queries are passed to functions as memory references, not values. Keep that in mind when working with queries. There is also the passby=reference|value attribute to function arguments where you can decide whether to pass by reference or value.

💻 Queries in Code

Let's explore query creation and manipulation:

// Create a query programmatically
users = queryNew(
    "id,name,email,age",
    "integer,varchar,varchar,integer",
    [
        { id: 1, name: "Alice", email: "[e-mail]", age: 30 },
        { id: 2, name: "Bob", email: "[e-mail]", age: 25 },
        { id: 3, name: "Charlie", email: "[e-mail]", age: 35 }
    ]
);

// Access query properties
println( "Records: " & users.recordCount );
println( "Columns: " & users.columnList );
println( "First user: " & users.name[ 1 ] );

// Functional programming with queries
println( "--- Functional Operations ---" );

// Filter - get rows matching condition (returns new query)
adults = users.filter( ( row ) -> row.age >= 30 );
println( "Adults: " & adults.recordCount );

// Map - transform each row (returns array of transformed values)
emails = users.map( ( row ) -> row.email.ucase() );
println( "Emails: " & emails.toString() );

// Reduce - combine all rows into single value
totalAge = users.reduce( ( sum, row ) -> sum + row.age, 0 );
println( "Total age: " & totalAge );

// Each - perform action on each row
users.each( ( row, index ) => {
    println( "#index#: #row.name# (#row.age#)" );
} );

// Sort - order by column(s)
sortedUsers = users.sort( "age DESC, name ASC" );
println( "Sorted: " & sortedUsers.name.toList() );

Please note that all member functions can also be used as traditional query functions. However, member functions look much better for readability.

📚 Query Built-In Functions (BIFs)

BoxLang provides a comprehensive set of query BIFs organized by functionality. All query BIFs can be called as member methods on Query objects.

🔨 Creation & Conversion Functions

Function	Purpose	Example
queryNew()	Create new query	queryNew("id,name", "integer,varchar")
queryExecute()	Execute SQL query	queryExecute("SELECT * FROM users")
queryAddRow()	Add row(s)	queryAddRow(qry, {id:1, name:"John"})
queryAddColumn()	Add column	queryAddColumn(qry, "age", "integer")

🔍 Access & Retrieval Functions

Function	Purpose	Example
queryGetCell()	Get single cell value	queryGetCell(qry, "name", 1) → "John"
querySetCell()	Set single cell value	querySetCell(qry, "name", "Jane", 1)
queryGetRow()	Get row as struct	queryGetRow(qry, 1) → {id:1, name:"John"}
querySetRow()	Set entire row values	querySetRow(qry, 1, {name:"Jane"})
queryRowData()	Get row data (alias)	queryRowData(qry, 1)
queryColumnData()	Get column as array	queryColumnData(qry, "name") → ["John", "Jane"]
queryColumnArray()	Get column as array	queryColumnArray(qry, "name")

📊 Metadata Functions

Function	Purpose	Example
queryRecordCount()	Get row count	queryRecordCount(qry) → 10
queryColumnCount()	Get column count	queryColumnCount(qry) → 5
queryColumnList()	Get column names	queryColumnList(qry) → "id,name,email"
queryColumnExists()	Check if column exists	queryColumnExists(qry, "age") → true
queryKeyExists()	Check if key exists	queryKeyExists(qry, "name") → true
queryCurrentRow()	Get current row number	queryCurrentRow(qry) → 3
queryGetResult()	Get execution metadata	queryGetResult(qry) → {sql, executionTime, ...}

➕ Modification Functions

Function	Purpose	Example
queryAddRow()	Add row(s)	queryAddRow(qry, 3) adds 3 empty rows
queryDeleteRow()	Delete row	queryDeleteRow(qry, 2)
queryInsertAt()	Insert row at position	queryInsertAt(qry, 2, {id:5})
queryRowSwap()	Swap two rows	queryRowSwap(qry, 1, 3)
queryAddColumn()	Add column	queryAddColumn(qry, "age", "integer")
queryDeleteColumn()	Delete column	queryDeleteColumn(qry, "age")
queryClear()	Remove all rows	queryClear(qry)
queryAppend()	Append another query	queryAppend(qry1, qry2)
queryPrepend()	Prepend another query	queryPrepend(qry1, qry2)

🔄 Functional Programming Functions

Function	Purpose	Example
queryEach()	Execute callback for each row	queryEach(qry, (row) -> println(row.name))
queryMap()	Transform each row	queryMap(qry, (row) -> row.name.ucase())
queryFilter()	Filter rows by condition	queryFilter(qry, (row) -> row.age > 18)
queryReduce()	Reduce to single value	queryReduce(qry, (sum, row) -> sum + row.age, 0)
queryEvery()	Test if all match	queryEvery(qry, (row) -> row.age >= 18)
querySome()	Test if any match	querySome(qry, (row) -> row.age > 50)
queryNone()	Test if none match	queryNone(qry, (row) -> row.age < 0)

📐 Manipulation Functions

Function	Purpose	Example
querySort()	Sort by column(s)	querySort(qry, "age DESC, name")
queryReverse()	Reverse row order	queryReverse(qry)
querySlice()	Extract portion of rows	querySlice(qry, 1, 10) → first 10 rows

🔧 Utility Functions

Function	Purpose	Example
queryColumnData()	Get column values	queryColumnData(qry, "name") → ["John", "Jane"]
queryRegisterFunction()	Register function for QoQ	queryRegisterFunction("myFunc", myUDF)

CFML Compatibility: valueList() and quotedValueList() are not native BoxLang BIFs. They only exist in CFML code, where the transpiler converts them to queryColumnData() calls. Calling them from BoxLang code will throw a Function 'valueList' not found error. Use queryColumnData() directly instead:

// CFML: valueList( qry, "name" )
queryColumnData( qry, "name" ).toList()
// => "John,Jane,Bob"

// CFML: quotedValueList( qry, "name" )
queryColumnData( qry, "name" ).map( ( value ) -> "'#value#'" ).toList()
// => "'John','Jane','Bob'"

🎯 Core Java Methods

The Query.java class provides essential methods for direct query manipulation:

Collection Interface Methods

Method	Purpose	Returns
size()	Get number of rows	int
isEmpty()	Check if query is empty	boolean
contains(Object)	Check if contains row data	boolean
iterator()	Get row iterator	Iterator<IStruct>
toArray()	Convert to array of row data	Object[]
toArrayOfStructs()	Convert to array of structs	Array
add(IStruct)	Add row as struct	boolean
remove(Object)	Remove row by object	boolean
clear()	Remove all rows	void

Query-Specific Methods

Method	Purpose	Returns
addRow(Object[])	Add row from array	int (row number)
addRow(Array)	Add row from Array	int
addRow(IStruct)	Add row from struct	int
addRows(int)	Add N empty rows	int (last row)
addEmptyRow()	Add one empty row	int
deleteRow(int)	Delete row at index	Query
swapRow(int, int)	Swap two rows	Query
getRow(int)	Get row as array	Object[]
getRowAsStruct(int)	Get row as struct	IStruct
getCell(Key, int)	Get cell value	Object
setCell(Key, int, Object)	Set cell value	Query

Column Methods

Method	Purpose	Returns
addColumn(Key, QueryColumnType)	Add column	Query
addColumn(Key, QueryColumnType, Object[])	Add column with data	Query
deleteColumn(Key)	Delete column	void
getColumn(Key)	Get QueryColumn object	QueryColumn
getColumnMeta(Key)	Get column metadata	IStruct
getColumnMeta()	Get all column metadata	IStruct
getColumnData(Key)	Get column data as array	Object[]
getColumnDataAsArray(Key)	Get column as BoxLang Array	Array
getColumnIndex(Key)	Get column position	int
getColumnList()	Get column names as string	String
getColumnArray()	Get column names as Array	Array
getColumnNames()	Get column names as Array	Array
hasColumn(Key)	Check if column exists	boolean
hasColumns()	Check if query has columns	boolean

Metadata & Duplication

Method	Purpose	Returns
getMetaData()	Get query metadata	IStruct
setMetadata(IStruct)	Set query metadata	Query
duplicate()	Shallow copy of query	Query
duplicate(boolean)	Shallow/deep copy	Query
duplicate(IBoxContext)	Context-aware copy	Query
toUnmodifiable()	Create immutable copy	UnmodifiableQuery

Advanced Methods

Method	Purpose	Returns
sort(Comparator<IStruct>)	Sort with comparator	void
sortData(Comparator<Object[]>)	Sort row arrays directly	void
truncate(long)	Keep only N rows	Query
insertQueryAt(int, Query)	Insert query at position	Query
fromResultSet(BoxStatement, ResultSet)	Create from JDBC ResultSet	Query (static)
fromArray(Array, Array, Object)	Create from arrays	Query (static)

📖 What is a Query?

A query is a request to a database representing the results' rows and columns. It returns a BoxLang query object containing a record set and other metadata information about the query. The query can ask for information from the database, write new data to the database, update existing information in the database, or delete records from the database. This can be done in several ways:

• Using the bx:query component in script: bx:query name="qItems" { SELECT * FROM table }
• Using the queryExecute() function: queryExecute("SELECT * FROM table")

Here's an example of using the default datasource in script syntax:

qItems = queryExecute(
    "SELECT QUANTITY, ITEM FROM CUPBOARD ORDER BY ITEM"
);

🔌 Datasource Configuration

BoxLang provides flexible datasource configuration options at multiple levels:

Configuration Locations

1. Global - boxlang.json (runtime-wide)
2. Application - Application.bx (per-application)
3. Inline - Query-level (ad-hoc connections)

Basic Datasource Structure

this.datasources[ "myDB" ] = {
    driver   : "mysql",
    host     : "localhost",
    port     : "3306",
    database : "myapp",
    username : "dbuser",
    password : "dbpass"
};

// OR with JDBC URL
this.datasources[ "myDB" ] = {
    url      : "jdbc:mysql://localhost:3306/myapp?useSSL=false",
    username : "dbuser",
    password : "dbpass"
};

Supported Database Drivers

Driver	Example URL
MySQL	jdbc:mysql://localhost:3306/mydb
PostgreSQL	jdbc:postgresql://localhost:5432/mydb
Microsoft SQL Server	jdbc:sqlserver://localhost:1433;databaseName=mydb
Oracle	jdbc:oracle:thin:@localhost:1521:XE
Derby	jdbc:derby:memory:mydb;create=true
H2	jdbc:h2:mem:mydb
HyperSQL	jdbc:hsqldb:mem:mydb

Connection Pool Options (HikariCP)

this.datasources[ "myDB" ] = {
    driver              : "mysql",
    host                : "localhost",
    port                : "3306",
    database            : "myapp",
    username            : "dbuser",
    password            : "dbpass",
    // Connection pool settings
    maximumPoolSize     : 10,
    minimumIdle         : 2,
    connectionTimeout   : 30000,
    idleTimeout         : 600000,
    maxLifetime         : 1800000,
    validationTimeout   : 5000
};

Default Datasource

// Set default datasource
this.defaultDatasource = "myDB";

// Queries use default if not specified
users = queryExecute( "SELECT * FROM users" );

Inline Datasource

// Ad-hoc datasource in query
users = queryExecute(
    "SELECT * FROM users",
    {},
    {
        datasource: {
            driver   : "mysql",
            host     : "localhost",
            database : "tempdb",
            username : "user",
            password : "pass"
        }
    }
);

https://github.com/ortus-boxlang/boxlang-docs/tree/v1.x/boxlang-language/datasources.mdJDBC & Databases

🔄 Iterating Over Queries

The query object can be iterated on like a normal collection through various looping constructs:

For-In Loop (Recommended)

// Simple iteration (row only)
for ( row in qItems ) {
    println( "There are #row.quantity# #row.item# in the pantry" );
}

// Destructuring syntax - get both row and index
for ( row, index in qItems ) {
    println( "#index#: There are #row.quantity# #row.item# in the pantry" );
}

New in 1.10.0: Loop destructuring syntax for (row, index in query) allows you to get both the row struct and its 1-based index in a single, clean declaration.

Each with Closure

qItems.each( ( row, index ) => {
    println( "#index#: There are #row.quantity# #row.item# in the pantry" );
} );

Traditional Index Loop

for ( i = 1; i <= qItems.recordCount; i++ ) {
    println( "There are #qItems.quantity[ i ]# #qItems.item[ i ]# in the pantry" );
}

Array Notation Access

// Access specific row and column
firstItem = qItems[ "item" ][ 1 ];
secondQuantity = qItems[ "quantity" ][ 2 ];

// Loop with array notation
for ( i = 1; i <= qItems.recordCount; i++ ) {
    item = qItems[ "item" ][ i ];
    quantity = qItems[ "quantity" ][ i ];
    println( "Item: #item#, Quantity: #quantity#" );
}

Best Practice: Use for-in loops or each() for cleaner, more readable code. Reserve index-based loops for when you need precise position control.

🚀 Multi-Threaded Looping

BoxLang allows you to leverage the each() operations in a multi-threaded fashion. The queryEach() or each() functions allow for parallel and maxThreads arguments so the iteration can happen concurrently:

queryEach( qry, callback, parallel:boolean, maxThreads:numeric );
qry.each( callback, parallel:boolean, maxThreads:numeric );

🚀 Multi-Threaded Looping

BoxLang allows you to leverage each() operations in a multi-threaded fashion. The queryEach() and each() functions support parallel and maxThreads arguments so iteration can happen concurrently:

queryEach( qry, callback, parallel:boolean, maxThreads:numeric )
qry.each( callback, parallel:boolean, maxThreads:numeric )

Example:

users.each( ( row ) => {
    userService.process( row )
}, true, 20 )

Thread Safety Warning: When using parallel execution, ensure proper variable scoping and implement appropriate locking strategies. Thread concurrency requires careful attention to shared state and race conditions.

Limitation: This approach uses a single thread executor per execution and does not provide exception handling across threads. For production-grade parallel processing, consider BoxLang async programming features instead.

⚡ BoxLang Async Programming (Recommended for Parallel Operations)

For a more flexible approach to parallel programming, use BoxLang async programming constructs built on Java Concurrency and CompletableFuture.

Async Programming

🔒 Using Query Parameters (Preventing SQL Injection)

When using user input in queries, you must prevent SQL injection attacks. BoxLang provides query parameters for safe SQL execution.

Security Critical: Never concatenate user input directly into SQL strings. Always use query parameters.

Named Parameters (Recommended)

// Named parameter with automatic type binding
result = queryExecute(
    "SELECT quantity, item FROM cupboard WHERE item_id = :itemID",
    { itemID: { value: arguments.itemID, sqltype: "integer" } }
)

// Multiple named parameters
users = queryExecute(
    "SELECT * FROM users WHERE age >= :minAge AND status = :status",
    {
        minAge: { value: 18, sqltype: "integer" },
        status: { value: "active", sqltype: "varchar" }
    }
)

Positional Parameters

// Positional placeholders with ?
result = queryExecute(
    "SELECT quantity, item FROM cupboard WHERE item_id = ?",
    [ { value: arguments.itemID, sqltype: "integer" } ]
)

// Multiple positional parameters
users = queryExecute(
    "SELECT * FROM users WHERE age >= ? AND status = ?",
    [
        { value: 18, sqltype: "integer" },
        { value: "active", sqltype: "varchar" }
    ]
)

Using bx:queryParam Component

bx:query name="result" {
    writeOutput( "
        SELECT * FROM users
        WHERE age >= " )
    bx:queryParam value=18 sqltype="integer"
    writeOutput( " AND email LIKE " )
    bx:queryParam value="[e-mail]" sqltype="varchar"
}

📋 Available SQL Types

The sqltype parameter binds values to specific database types for security and query plan optimization:

Type	Description	Example
bigint	64-bit integer	-9223372036854775808 to 9223372036854775807
bit	Boolean/bit value	true / false
char	Fixed-length string	"ABC " (padded)
varchar	Variable-length string	"Hello World"
nchar	Fixed-length Unicode string	"ABC "
nvarchar	Variable-length Unicode string	"Hello 世界"
longvarchar	Long text	Long text content
longnvarchar	Long Unicode text	Long Unicode content
integer	32-bit integer	-2147483648 to 2147483647
smallint	16-bit integer	-32768 to 32767
tinyint	8-bit integer	-128 to 127
numeric	Fixed precision decimal	123.45
decimal	Fixed precision decimal	123.45
float	Floating point	123.456789
double	Double precision float	123.456789012345
real	Single precision float	123.456
money	Currency value	1234.56
money4	Small currency value	214748.3647
date	Date only	2025-12-09
time	Time only	14:30:00
timestamp	Date and time	2025-12-09 14:30:00
blob	Binary large object	Binary data
clob	Character large object	Large text
nclob	Unicode large object	Large Unicode text
sqlxml	XML data	<root><item /></root>
refcursor	Result set reference	Oracle REF CURSOR
idstamp	Unique identifier	UUID/GUID

The cf_sql_{type} syntax (e.g., cf_sql_varchar) is only supported when bx-compat-cfml is installed. Use the native type names (e.g., varchar) in all new code.

🏗️ Building Queries Programmatically

You can create and manipulate queries without database connections using BoxLang query construction functions:

Creating Empty Queries

// Create query with columns only
news = queryNew( "id,title", "integer,varchar" )


// Add rows one at a time
queryAddRow( news );
querySetCell( news, "id", 1 );
querySetCell( news, "title", "Dewey defeats Truman" );

queryAddRow( news );
querySetCell( news, "id", 2 );
querySetCell( news, "title", "Man walks on Moon" );

writeDump( news );

Creating Queries with Data

// Array of structs approach (recommended)
news = queryNew(
    "id,title",
    "integer,varchar",
    [
        { id: 1, title: "Dewey defeats Truman" },
        { id: 2, title: "Man walks on Moon" },
        { id: 3, title: "Apollo 11 Moon Landing" }
    ]
);

// Single struct approach
singleNews = queryNew(
    "id,title",
    "integer,varchar",
    { id: 1, title: "Dewey defeats Truman" }
);

// Array of arrays approach (positional)
newsArray = queryNew(
    "id,title",
    "integer,varchar",
    [
        [ 1, "Dewey defeats Truman" ],
        [ 2, "Man walks on Moon" ]
    ]
);

Manipulating Query Data

// Add multiple empty rows
queryAddRow( news, 5 ); // Adds 5 empty rows

// Add row from struct
queryAddRow( news, { id: 4, title: "New Event" } );

// Add column with data
queryAddColumn( news, "author", "varchar", [ "John", "Jane", "Bob", "Alice" ] );

// Delete rows and columns
queryDeleteRow( news, 2 ); // Delete row 2
queryDeleteColumn( news, "author" ); // Delete column

// Swap rows
queryRowSwap( news, 1, 3 ); // Swap positions

// Get and set specific cells
title = queryGetCell( news, "title", 1 );
querySetCell( news, "title", "Updated Title", 1 );

// Get row as struct
row = queryGetRow( news, 1 );
writeDump( row ); // { id: 1, title: "Updated Title" }

// Get column as array
titles = queryColumnData( news, "title" );
writeDump( titles ); // [ "Updated Title", "Man walks on Moon", ... ]

Method Chaining

// Build query fluently
users = queryNew( "id,name,age", "integer,varchar,integer" )
    .addRow( { id: 1, name: "Alice", age: 30 } )
    .addRow( { id: 2, name: "Bob", age: 25 } )
    .addRow( { id: 3, name: "Charlie", age: 35 } )
    .filter( ( row ) -> row.age >= 30 )
    .sort( "name ASC" );

🔍 Query of Queries (QoQ)

Query existing query objects using SQL without hitting the database. BoxLang's QoQ implementation is extremely fast - 5x faster than Lucee and 17x faster than Adobe ColdFusion.

// Create a query
users = queryNew(
    "id,firstname,lastname,age",
    "integer,varchar,varchar,integer",
    [
        { id: 1, firstname: "Han", lastname: "Solo", age: 35 },
        { id: 2, firstname: "Luke", lastname: "Skywalker", age: 28 },
        { id: 3, firstname: "Leia", lastname: "Organa", age: 28 }
    ]
);

// Query the query
youngUsers = queryExecute(
    "SELECT * FROM users WHERE age < :maxAge ORDER BY firstname",
    { maxAge: { value: 30, sqltype: "integer" } },
    { dbtype: "query" }
);

writeDump( youngUsers );

Advanced QoQ Features

BoxLang QoQ supports modern SQL features:

// ANSI JOINs
orders = queryNew( "orderID,userID,total", "integer,integer,decimal", [ ... ] );
users = queryNew( "userID,name", "integer,varchar", [ ... ] );

result = queryExecute( "
    SELECT u.name, o.total
    FROM users u
    INNER JOIN orders o ON u.userID = o.userID
    WHERE o.total > 100
", {}, { dbtype: "query" } );

// Subqueries
highValueCustomers = queryExecute( "
    SELECT * FROM users
    WHERE userID IN (
        SELECT userID FROM orders WHERE total > 1000
    )
", {}, { dbtype: "query" } );

// Aggregates with GROUP BY
summary = queryExecute( "
    SELECT age, COUNT(*) as count, AVG(age) as avgAge
    FROM users
    GROUP BY age
    HAVING count > 1
", {}, { dbtype: "query" } );

// CASE statements
categorized = queryExecute( "
    SELECT name, age,
        CASE
            WHEN age < 18 THEN 'Minor'
            WHEN age < 65 THEN 'Adult'
            ELSE 'Senior'
        END as category
    FROM users
", {}, { dbtype: "query" } );

For complete QoQ documentation including custom functions, bitwise operators, and performance tips, see Query of Queries.

Performance Tip: For simple filtering and sorting, use functional methods like queryFilter() and querySort() instead of QoQ. They are often faster and more type-safe.

📦 Alternative Return Types

You can return query results as arrays or structs instead of query objects - perfect for JSON APIs and modern frameworks.

Return as Array of Structs

// Each row becomes a struct in an array
users = queryExecute(
    "SELECT id, name, email FROM users",
    {},
    { returntype: "array" }
);

// Result: [
//     { id: 1, name: "Alice", email: "[e-mail]" },
//     { id: 2, name: "Bob", email: "[e-mail]" }
// ]

// Perfect for JSON APIs
return users.toJSON();

Return as Struct of Structs

// Use a column as the key for a struct of structs
usersById = queryExecute(
    "SELECT id, name, email FROM users",
    {},
    { returntype: "struct", columnkey: "id" }
);

// Result: {
//     "1": { id: 1, name: "Alice", email: "[e-mail]" },
//     "2": { id: 2, name: "Bob", email: "[e-mail]" }
// }

// Fast lookups by ID
alice = usersById[ 1 ];

Convert Existing Query

// Convert query object to array
qry = queryExecute( "SELECT * FROM users" );
arrayData = qry.toArrayOfStructs();

// Manual conversion with map
arrayData = qry.map( ( row ) -> {
    return { id: row.id, name: row.name };
} );

Best Practice: Use returntype="array" for REST APIs and JSON responses. It is cleaner and more compatible with JavaScript frameworks like React, Vue, and Angular.

🔄 Query Transformers — Custom Result Formatting

Since BoxLang 1.14.0. Query Transformers let you process query result sets natively and return exactly what you need — eliminating boilerplate post-processing.

BoxLang's queryExecute() and bx:query are locked into three hardcoded return types: query, array, and struct. Users who want tabular arrays, rich column descriptors, JSON, domain objects, or any other format must post-process results in a separate step — doubling memory and CPU for large queries. Adding new native return types for every use case is unsustainable.

Query Transformers solve this by allowing you to provide a transformer option that receives the raw query and execution metadata, giving you complete control over the result format.

Transformer input types:

1. Closure/Lambda — (query, metadata) => any or (query, metadata) → any
2. Class instance — any class with a transform(query, metadata) method
3. String — name of a registered transformer from this.queryTransformers in Application.bx

When transformer is provided, it takes precedence over returnType. The transformer receives two arguments:

• query — the raw Query object (.recordCount, .toArrayOfStructs(), .getData(), .getColumnNames(), .getColumnMeta(), etc.)
• metadata — a struct containing sql, parameters, executionTime, columnMetadata, and more

Why transformers? Instead of hardcoding every possible return format, transformers give you the flexibility to produce tabular arrays, domain objects, JSON payloads, enriched metadata structures, or any custom format — all in a single pass.

Inline Closure Transformers

Custom Struct with Metadata:

var result = queryExecute( "SELECT * FROM users", [], {
    datasource: "app",
    transformer: ( query, meta ) => {
        return {
            data: query.toArrayOfStructs(),
            total: query.recordCount,
            executedAt: now(),
            sql: meta.sql
        }
    }
} )
// => { data: [...], total: 42, executedAt: ..., sql: "SELECT..." }

Domain Objects:

var users = queryExecute( "SELECT * FROM users", [], {
    datasource: "app",
    transformer: ( query, meta ) => query.toArrayOfStructs().map( row => new User( row ) )
} )
// => [ User{...}, User{...}, ... ]

Tabular Format (Near Zero-Copy):

var tabular = queryExecute( "SELECT id, name, price FROM products", [], {
    datasource: "app",
    transformer: ( query, meta ) => {
        return {
            columns: query.getColumnNames(),
            data: query.getData().map( row => arrayNew( row ) )
        }
    }
} )
// => { columns: ["id","name","price"], data: [[1,"Widget",9.99],[2,"Gadget",19.99]] }

Rich Format with Column Descriptors:

var rich = queryExecute( "SELECT id, name, price, status FROM products", [], {
    datasource: "app",
    transformer: ( query, meta ) => {
        var colMeta = query.getColumnMeta()
        return {
            count: query.recordCount,
            columns: query.getColumnNames().map( name => {
                var info = colMeta[ name ]
                return {
                    name: name,
                    type: info.type,
                    nullable: info.nullable,
                    readOnly: info.readOnly,
                    decimals: info.decimals,
                    maxLength: info.maxLength
                }
            } ),
            data: query.getData().map( row => arrayNew( row ) )
        }
    }
} )
// => {
//   count: 3,
//   columns: [
//     { name: "id", type: "INTEGER", nullable: false, readOnly: true, ... },
//     { name: "name", type: "VARCHAR", nullable: false, readOnly: null, ... },
//     ...
//   ],
//   data: [[1,"Widget",9.99,"active"], ...]
// }

Class Instance Transformers

Create reusable transformer classes for complex formatting logic:

// RichTransformer.bx
class RichTransformer {
    function transform( query, metadata ) {
        var colMeta = query.getColumnMeta()
        return {
            count: query.recordCount,
            columns: query.getColumnNames().map( name => {
                var info = colMeta[ name ]
                return {
                    name: name, type: info.type,
                    nullable: info.nullable, readOnly: info.readOnly,
                    decimals: info.decimals, maxLength: info.maxLength
                }
            } ),
            data: query.getData().map( row => arrayNew( row ) )
        }
    }
}

// Usage
var transformer = new RichTransformer()
var result = queryExecute( sql, params, { transformer: transformer } )

Registered Transformers (Application.bx)

Register transformers globally in your application for reuse across the entire codebase:

// In Application.bx
this.queryTransformers = {
    "rich": new RichTransformer(),
    "tabular": ( query, meta ) => {
        return {
            columns: query.getColumnNames(),
            data: query.getData().map( row => arrayNew( row ) )
        }
    },
    "json": ( query, meta ) => serializeJson( query.toArrayOfStructs() ),
    "domainUsers": "models.transformers.UserTransformer"
}

// Usage anywhere in the app:
var rich    = queryExecute( sql, params, { transformer: "rich" } )
var tabular = queryExecute( sql, params, { transformer: "tabular" } )
var json    = queryExecute( sql, params, { transformer: "json" } )
var users   = queryExecute( sql, params, { transformer: "domainUsers" } )

Precedence: When transformer is provided, the returnType option is ignored. The transformer always takes precedence.

bx:query Component Support

Transformers also work with the bx:query component:

<bx:query name="result" datasource="app"
    transformer=(( q, m ) => serializeJson( q.toArrayOfStructs() ))>
    SELECT * FROM users
</bx:query>

JDBC Metadata Enhancement

As a prerequisite for rich column descriptors, QueryColumn now captures JDBC metadata that was previously discarded after the ResultSet was closed — exposed via query.getColumnMeta():

Property	Source	Description
nullable	ResultSetMetaData.isNullable()	Whether the column accepts NULL
readOnly	ResultSetMetaData.isReadOnly() / isAutoIncrement()	Whether the column is read-only
decimals	ResultSetMetaData.getScale()	Number of decimal digits (numeric types)
maxLength	ResultSetMetaData.getColumnDisplaySize()	Maximum column width (string types)

var colMeta = query.getColumnMeta()
for ( var name in query.getColumnNames() ) {
    var info = colMeta[ name ]
    println( "#name# — type: #info.type#, nullable: #info.nullable#, decimals: #info.decimals#" )
}

Transformer Resolution Order:

transformer input:
  ├── instanceof Function    → invoke( query, metadata )
  ├── Object with "transform" key → invoke transform( query, metadata )
  └── instanceof String      → lookup in this.queryTransformers
                                  └── resolves to Function, class instance, or class path
                                      └── resolved transformer is invoked

🏗️ QB - Query Builder Module

QB (Query Builder) is a powerful module for building database queries with a fluent, chainable API. It abstracts database differences and makes complex queries readable and maintainable.

Installation

box install qb

Features

• ✅ Fluent, chainable query building
• ✅ Database-agnostic (MySQL, PostgreSQL, MSSQL, Oracle, etc.)
• ✅ Complex joins, subqueries, and CTEs
• ✅ Query caching and pagination
• ✅ Raw expressions when needed
• ✅ Schema builder for migrations

Basic Usage

// Inject QB instance
property name="query" inject="Builder@qb";

// Build and execute queries
posts = query.from( "posts" )
    .whereNotNull( "published_at" )
    .whereIn( "author_id", [ 5, 10, 27 ] )
    .orderBy( "published_at", "DESC" )
    .limit( 10 )
    .get();

// Complex queries with joins
users = query.from( "users as u" )
    .join( "orders as o", "u.id", "=", "o.user_id" )
    .select( [ "u.name", "COUNT(o.id) as order_count" ] )
    .groupBy( "u.id" )
    .having( "order_count", ">", 5 )
    .get();

// Subqueries
topAuthors = query.from( "posts" )
    .whereIn( "author_id", function( q ) {
        q.select( "id" )
            .from( "users" )
            .where( "reputation", ">", 1000 );
    } )
    .get();

Insert, Update, Delete

// Insert
query.table( "users" )
    .insert( { name: "Alice", email: "[e-mail]" } );

// Update
query.table( "users" )
    .where( "id", 1 )
    .update( { email: "[e-mail]" } );

// Delete
query.table( "users" )
    .where( "status", "inactive" )
    .delete();

📖 Full Documentation: https://qb.ortusbooks.com/

Recommended: Use QB for complex queries and database migrations. It provides better testability and database portability than raw SQL.

⚙️ Query Options

BoxLang supports comprehensive query options for controlling execution behavior:

Core Options

Option	Type	Description	Example
result	String	Variable name to store query metadata	result: "queryResult"
maxRows	Integer	Limit number of rows returned	maxRows: 100
queryTimeout	Integer	Max execution time (seconds)	queryTimeout: 30
returnType	String	Return format: query, array, struct	returnType: "array"
columnKey	String	Key column for struct return type	columnKey: "id"
fetchSize	Integer	JDBC batch size for large results	fetchSize: 500
dbtype	String	Database type ("query" for QoQ)	dbtype: "query"

Caching Options

Option	Type	Description	Example
cache	Boolean	Enable query caching	cache: true
cacheKey	String	Unique cache identifier	cacheKey: "userList"
cacheProvider	String	Cache provider name	cacheProvider: "default"
cacheTimeout	Duration	Cache expiration time	cacheTimeout: "1h"

Example Usage

// Basic query with options
users = queryExecute(
    "SELECT * FROM users WHERE status = :status",
    { status: { value: "active", sqltype: "varchar" } },
    {
        maxRows: 50,
        queryTimeout: 10,
        returnType: "array",
        cache: true,
        cacheKey: "activeUsers",
        cacheTimeout: "30m"
    }
);

// Store metadata in result variable
queryExecute(
    "SELECT * FROM large_table",
    {},
    {
        result: "metadata",
        fetchSize: 1000,
        maxRows: 10000
    }
);

// Access metadata
writeDump( metadata.sql )
writeDump( metadata.executionTime )
writeDump( metadata.cached )

CFML Compatibility Options

When bx-compat-cfml is installed, these aliases are available:

CFML Option	BoxLang Equivalent
blockfactor	fetchSize
cacheID	cacheKey
cacheRegion	cacheProvider
cachedAfter	Converted to cacheTimeout
cachedWithin	cacheTimeout

Future Options: Support is planned for cachedWithin="request", timezone, psq, and lazy loading.

Unsupported: The following CFML options are not supported: username, password, debug, clientInfo, fetchClientInfo, ormoptions.