Datasources

A datasource is a named connection to a specific database with specified credentials. You can define a datasource in one of three locations:

1. At the boxlang runtime level via your boxlang.json config file

2. For web applications, in your Application.bx via this.datasources

3. Inline, at query time, via the queryExecute() BIF, query or dbInfo component, etc

The datasource is then used to control the database's connection pool and allow the BoxLang engine to execute JDBC calls against it.

🗄️ What Database Vendors Are Supported?

The following database vendors are supported and available:

• Apache Derby

• HyperSQL

• MariaDB

• Microsoft SQL Server

• MySQL

• Oracle

• PostgreSQL

Each database we support comes with an installable BoxLang module which either

1. provides the necessary client dependencies for making JDBC connections to a running database server (MySQL, Postgres, etc.)

2. OR contains the database vendor itself, as in the case of Apache Derby or HyperSQL, which are both in-memory database.

To use any of these databases you'll need to install its BoxLang module to support JDBC connections to that datasource.

🔧 JDBC Built-In Functions

BoxLang provides several BIFs for working with databases. These are the core functions for executing queries, managing transactions, and working with database connections:

Function	Purpose	Category
queryExecute()	Execute SQL queries against datasources	Query Execution
isInTransaction()	Check if currently in a transaction	Transaction State
isWithinTransaction()	Alias for isInTransaction()	Transaction State
transactionCommit()	Commit current transaction	Transaction Control
transactionRollback()	Rollback current transaction or to savepoint	Transaction Control
transactionSetSavepoint()	Set a savepoint within a transaction	Transaction Control
preserveSingleQuotes()	Prevent string escaping in SQL	SQL Utilities

All JDBC BIFs are documented in detail in the JDBC Built-In Functions Reference.

🧩 JDBC Components

BoxLang provides component-based alternatives to BIFs for database operations:

Component	Purpose	Documentation
bx:query	Execute SQL queries	Query Component
bx:queryParam	Define parameterized query values	QueryParam Component
bx:transaction	Manage database transactions	Transaction Component
bx:storedProc	Execute stored procedures	StoredProc Component
bx:procParam	Define stored procedure parameters	ProcParam Component
bx:procResult	Capture stored procedure result sets	ProcResult Component
bx:dbInfo	Retrieve database metadata	DBInfo Component

Components and BIFs offer the same functionality - choose the style that fits your coding preference. Script syntax typically uses BIFs, while template syntax uses components.

⚠️ Make Sure to Specify a Driver

The datasource configuration struct should be defined exactly the same whether you are using an inline, ad-hoc datasource or configuring a datasource in your boxlang.json or Application.bx. Make sure you have a "driver" key defined OR the driver clearly denoted in the JDBC url:

Application.bx

this.datasources[ "testDB" ] = {
	"driver": "mysql",
	// OR:
	"url" : "jdbc:mysql://localhost:3306/test"
};

📝 Defining Datasources In boxlang.json

You can define a datasource at the BoxLang runtime level by placing it in your boxlang.json configuration file:

boxlang.json

  "datasources": {
      "theDerbyDB": {
      	"driver": "derby",
    	"connectionString": "jdbc:derby:memory:testDB;create=true"
      },
      "theMysqlDB": {
      	"driver": "mysql",
        "host": "${env.MYSQL_HOST:localhost}",
        "port": "${env.MYSQL_PORT:3306}",
        "database": "${env.MYSQL_DATABASE:myDB}",
        "username": "${env.MYSQL_USERNAME:root}",
        "password": "${env.MYSQL_PASSWORD}"
      }
  },

Note the use of BoxLang's environment variable replacement syntax for the datasource properties: ${env.MYSQL_HOST:localhost}. See Environment Variable Substitution for more info.

📝 Defining Datasources In Application.bx

For web runtimes, you can also define the datasources in the Application.bx, which is sometimes our preferred approach as the connections are versioned controlled and more visible than in the admin. You will do this by defining a struct called this.datasources. Each key will be the name of the datasource to register and the value of each key a struct of configuration information for the datasource. However, we recommend that you setup environment variables in order to NOT store your passwords in plain-text in your source code.

Application.bx

class{
    this.datasources = {
        // Driver Approach
        mysql = {
            database : "mysql",
            host : "localhost",
            port : "3306",
            driver : "MySQL",
            username : "root",
            password : "mysql",
            options : value
        },
        // URL approach
        mysql2 = {
            driver : "mysql",
            url : "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&useLegacyDatetimeCode=true",
            username : "",
            password : ""
        },
        // Shorthand Approach
        myDSN = {
            class : "com.mysql.jdbc.Driver",
            connectionString : "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&useLegacyDatetimeCode=true",
            username : "",
            password : ""
        },
        // Long Approach
        myDSN = {
            type : "mysql",
            database : "mysql",
            host : "localhost",
            port : "3306",
            username : "",
            password : ""
        }
    };
}

For the inline approach, you will use the struct definition, as you see in the Application.bx above and pass it into the bx:query or queryexecute call.

📝 Defining Inline Datasources

Finally, for smaller or simpler applications with few queries, you may find it useful to define your datasource at query time. So instead of giving the name of the datasource, it can be a struct definition of the datasource you want to connect to:

queryExecute(
  "SELECT * FROM Employees WHERE empid = ? AND country = ?", // sql
  [ 1, "USA" ], // params
  { // options
    datasource : {
      "driver": "mysql",
      "host": "${env.MYSQL_HOST:localhost}",
      "port": "${env.MYSQL_PORT:3306}",
      "database": "${env.MYSQL_DATABASE:myDB}",
      "username": "${env.MYSQL_USERNAME:root}",
      "password": "${env.MYSQL_PASSWORD}"
    }
  }
)

🎯 Default Datasource

You can also define a default datasource to allow you to omit the datasource completely from query calls.

To do this, you'll need to define a default datasource in one of two locations:

1. In your BoxLang runtime's boxlang.json config file via the defaultDatasource key

2. or, for web server runtimes, in a this.datasource variable in your Application.bx file

Defining a default datasource via boxlang.json

boxlang.json

   "defaultDatasource: {
      "driver": "mysql",
       "host": "${env.MYSQL_HOST:localhost}",
       "port": "${env.MYSQL_PORT:3306}",
       "database": "${env.MYSQL_DATABASE:myDB}",
       "username": "${env.MYSQL_USERNAME:root}",
       "password": "${env.MYSQL_PASSWORD}"
    },
   "datasources": {
      // You can still define additional datasources here! You're not limited to the default datasource
   },

Defining a default datasource via Application.bx

Application.bx

class{
    this.name = "myApp";

    // Default Datasource Name
    this.datasource = "pantry";

}

📦 Portable Datasources

You can also make your data sources portable from application to application or BoxLang engine to engine by using our CFConfig project. CFConfig allows you to manage almost every setting that shows up in the web administrator, but instead of logging into a web interface, you can manage it from the command line by hand or as part of a scripted server setup. You can seamlessly transfer config for all the following:

• CF Mappings

• Data sources

• Mail servers

• Request, session, or application timeouts

• Licensing information

• Passwords

• Template caching settings

• Basically any settings in the web based administrator

You can easily place a .cfconfig.json in the web root of your project, and if you start up a CommandBox server on any BoxLang engine, CFConfig will transfer the configuration to the engine's innards:

.cfconfig.json

{
    "datasources" : {
         "coldbox":{
             "host":"${DB_HOST}",
             "dbdriver":"${DB_DRIVER}",
             "database":"${DB_DATABASE}",
             "dsn":"jdbc:mysql://{host}:{port}/{database}",
             "custom":"useUnicode=true&characterEncoding=UTF-8&useLegacyDatetimeCode=true&autoReconnect=true",
             "port":"${DB_PORT}",
             "class":"${DB_CLASS}",
             "username":"${DB_USER}",
             "password":"${DB_PASSWORD}",
             "connectionLimit":"100",
             "connectionTimeout":"1"
         }
    }
}

Command Overview | CFConfig Documentationcfconfig.ortusbooks.com

⚙️ Datasource Configuration

All Configuration Properties

Property	Type	Default	Description
driver	String		Datasource driver to use. Corresponds with the boxlang JDBC driver module - see What Database Vendors Are Supported?
dbdriver	String		Alias for driver. Deprecated
class	String		Specify a custom or specific class to use as the database driver. Not recommended - use the correct driver instead.
custom	Struct		Struct of custom properties.
username	String		Database connection username.
password	String		Database connection password.
maxConnections	Integer	10	The maximum number of connections. Alias for Hikari's maximumPoolSize
minConnections	Integer	10	The minimum number of connections. Alias for Hikari's minimumIdle
connectionTimeout	Integer	1	Maximum time to wait for a successful connection, in seconds.
idleTimeout	Integer	600	The maximum number of idle time in seconds (10 Minutes = 600). Refers to the maximum amount of time a connection can remain idle in the pool before it is eligible for eviction.
maxLifetime	Integer	1800	This property controls the maximum lifetime of a connection in the pool. An in-use connection will never be retired, only when it is closed will it then be removed. 30 minutes by default = 1800 seconds.
keepaliveTime	Integer	600	This property controls how frequently HikariCP will attempt to keep a connection alive, in order to prevent it from being timed out by the database or network infrastructure. 10 Minutes = 600 seconds.
autoCommit	Boolean	true	The default auto-commit state of connections created by this pool.
registerMbeans	Boolean	true	Register mbeans for JMX connection monitoring support.

In addition to the above properties, you can include any Hikari configuration property you'd like in your datasource configuration:

"coldbox":{
    ...
   "connectionInitSql":"custom SQL statement to run on connection initialize...",
}

🏊 Datasource Connection Pooling

BoxLang uses HikariCP under the hood for connection pooling. Each datasource gets a dedicated connection pool. Use these configuration properties to adjust the pool size and behavior:

• maxConnections

• minConnections

• connectionTimeout

• idleTimeout

• maxLifetime

• keepaliveTime

Pool Statistics

BoxLang offers a number of pool statistics which can be retrieved from the datasource object. To do this, first find your datasource name from the list of registered datasources:

// get array of unique datasource names for all apps
writeDump( getBoxContext().getRuntime().getDatasourceService().getNames() );

Next, retrieve the datasource by its unique datasource name, and call .getPoolStats() on the result:

writeDump( getBoxContext().getRuntime().getDatasourceService().get( "app_12345_my_Datasource_Name" ).getPoolStats() );

This returns a struct of pool metadata including the following keys:

• pendingThreads

• idleConnections

• totalConnections

• activeConnections

• maxConnections

• minConnections

Find out what datasources you have defined by dumping out:

getBoxContext().getRuntime().getDatasourceService().getNames()

🔄 Database Transactions

BoxLang provides comprehensive transaction support for ensuring data integrity and consistency in database operations. Transactions guarantee ACID properties (Atomicity, Consistency, Isolation, Durability) across multiple database operations.

Transaction Basics

Use the transaction{} block or bx:transaction component to wrap multiple queries in a single transaction:

transaction {
    queryExecute( "UPDATE accounts SET balance = balance - 100 WHERE id = 1" );
    queryExecute( "UPDATE accounts SET balance = balance + 100 WHERE id = 2" );
    // Both queries commit together, or roll back together on error
}

Transaction Control

• Automatic Commit: Transactions automatically commit when the block completes successfully

• Automatic Rollback: Transactions automatically rollback when an exception occurs

• Manual Control: Use transactionCommit() and transactionRollback() for explicit control

• Savepoints: Use transactionSetSavepoint() to create rollback points within a transaction

• Nested Transactions: BoxLang supports nested transactions using the same connection

Isolation Levels

Control transaction isolation to balance consistency and performance:

transaction isolation="read_committed" {
    // Transaction operations here
}

Available isolation levels:

• read_uncommitted - Lowest isolation, highest performance

• read_committed - Default level, prevents dirty reads

• repeatable_read - Prevents non-repeatable reads

• serializable - Highest isolation, complete isolation from other transactions

For comprehensive transaction documentation, see JDBC Transactions.

🗂️ Stored Procedures

BoxLang supports executing stored procedures through the bx:storedProc component:

<bx:storedProc procedure="sp_GetEmployees" datasource="myDB" result="employees">
    <bx:procParam type="in" cfsqltype="varchar" value="Sales" />
    <bx:procParam type="out" cfsqltype="integer" variable="totalCount" />
    <bx:procResult name="resultSet" resultset="1" />
</bx:storedProc>

Or in script syntax:

bx:storedProce procedure="sp_GetEmployees" datasource="myDB" {
    bx:procParam type="in" cfsqltype="varchar" value="Sales";
    bx:procParam type="out" cfsqltype="integer" variable="totalCount";
    bx:procResult name="resultSet" resultset="1";
};

Key Features:

• IN/OUT/INOUT Parameters: Support for all parameter types

• Multiple Result Sets: Capture multiple result sets from a single procedure

• Return Values: Access stored procedure return codes

• NULL Handling: Proper NULL value support

Stored procedures are documented in detail in the StoredProc Component Reference.

🔍 Query of Queries (QoQ)

Query of Queries allows you to execute SQL queries against in-memory query objects, perfect for filtering and transforming result sets without additional database calls:

// Original database query
employees = queryExecute( "SELECT * FROM employees" );

// Query the result set in memory
salesTeam = queryExecute(
    "SELECT name, salary FROM employees WHERE department = ?",
    [ "Sales" ],
    { dbtype: "query" }
);

QoQ Features:

• Standard SQL Syntax: Use familiar SELECT, WHERE, ORDER BY, GROUP BY, JOIN operations

• No Database Round-trips: Queries execute entirely in memory

• Multi-Query Joins: Join multiple query objects together

• Aggregations: Support for COUNT, SUM, AVG, MIN, MAX functions

Query of Queries has some SQL limitations compared to full database engines. Complex queries may not be supported.

📚 Related Documentation

• Queries - Comprehensive query documentation

• JDBC Transactions - Detailed transaction management guide

• JDBC Built-In Functions - Complete BIF reference

• JDBC Components - Complete component reference

• Query Type Reference - Query object type documentation