Playing With The MySQL 8 Document Store X DevAPI In Lucee CFML 5.3.8.201

Cyberdime
Published: March 21, 2022

After my blog was upgraded from MySQL 5.7 to MySQL 8.0.28, I read through the MySQL 8 release notes to see what new features I would have at my disposal. I already looked at using LATERAL derived tables and Common Table Expressions (CTE). And now, I want to look briefly at something that was called “Document Store” – MySQL’s document-oriented answer to the NoSQL (Not-Only SQL) band of database technologies. I found almost no examples of this on the web. So, what follows is just my trial-and-error experiment with MySQL 8’s Document Store and its new X DevAPI in Lucee CFML 5.3.8.201.

View this code in my ColdFusion MySQL 8 Document Store project on GitHub.

When I first started to Google for information about MySQL 8’s Document Store in Java (which I would be able to leverage from within ColdFusion), the only resources that I could find were an introductory post on the MySQL blog – Connector/J 8.0.11, the Face for Your Brand New Document-oriented Database – and the actual documentation for the X DevAPI User Guide. Everything else looked at the JavaScript interactive Shell. And, if we’re being honest, nothing integrates as seamlessly with a JSON-oriented API like JavaScript. So, that wasn’t really doing to help me all that much in Java.

I believe that part of the problem with my original researching was that I was looking for something that looked like SQL, but for documents. MySQL 5.7.8 introduced the JSON column type, which provides JSON-oriented functions for data-manipulation within SQL queries. So, I guess I was looking for something like that, only more “document-oriented”?

download the protobuf-java dependency from Maven.

If you’ve used other document-oriented databases, like MongoDB, the X DevAPI may look familiar. It uses a fluent API (a chainable API) that lets you build up “statements” that you can then execute. For example:

schema.createCollection( "contacts", true )
	.add( "{\"name\": \"Jen\"}" )
	.add( "{\"name\": \"Ted\"}" )
	.add( "{\"name\": \"Stan\"}" )
	.execute()
;
var contacts = schema.createCollection( "contacts", true )
	.find( "$.name = :nameParam" )
	.orderBy( "name" )
	.limit( 1 )
	.bind( "nameParam", "Jen" )
	.execute()
	.fetchAll()
;

I don’t have much experience with document-oriented databases; so, while this API is clearly inspired by standard SQL constructs, I don’t have much of an instinct for it yet. But, at least I am starting to understand what MySQL 8’s “Document Store” actually is.

the example code that the X DevAPI gives for that is simply bananas:

DbDoc doc = new DbDocImpl().add("field1", new JsonString().setValue("value 1")).add("field2", new JsonNumber().setValue("12345.44E22"))
         .add("field3", JsonLiteral.TRUE).add("field4", JsonLiteral.FALSE).add("field5", JsonLiteral.NULL)
         .add("field6",
                 new DbDocImpl().add("inner field 1", new JsonString().setValue("inner value 1")).add("inner field 2", new JsonNumber().setValue("2"))
                         .add("inner field 3", JsonLiteral.TRUE).add("inner field 4", JsonLiteral.FALSE).add("inner field 5", JsonLiteral.NULL)
                         .add("inner field 6", new JsonArray()).add("inner field 7", new DbDocImpl()))
         .add("field7", new JsonArray().addValue(new JsonString().setValue("arr1")).addValue(new JsonNumber().setValue("3")).addValue(JsonLiteral.TRUE)
                 .addValue(JsonLiteral.FALSE).addValue(JsonLiteral.NULL).addValue(new JsonArray()).addValue(new DbDocImpl()));

Now, I don’t want to be too judgemental…. but, what is this garbage! Can anyone imagine trying to build objects using this API?

It seems that the only reasonable approach in ColdFusion by way of the X DevAPI is going to be passing-in already-serialized data-structures:

add( String... jsonStrings )

This requires me to pass-in a Array of serialized Structs in ColdFusion:

.add([ serializeJson( my_struct ) ])

Using this approach, here’s a full example of what adding a document using the X DevAPI in Lucee CFML might look like:

NOTE: In this example, I’m using Lucee CFML’s ability to load JAR files on the fly in the createObject() function.

<cfscript>
	dbClient = javaNew( "com.mysql.cj.xdevapi.ClientFactory" )
		.init()
		.getClient(
			"mysqlx://root:password@doc_demo:33060",
			serializeJson({
				pooling: {
					enabled: true,
					maxSize: 25,
					maxIdleTime: 30000,
					queueTimeout: 10000
				}
			})
		)
	;
	try {
		dbSession = dbClient.getSession();
		dbSchema = dbSession.getSchema( "doc_demo" );
		addStatement = dbSchema
			.createCollection( "contacts", true )
			.add([
				serializeJson({
					name: "Sarah Smith"
				})
			])
		;
		results = addStatement.execute();
		newID = results.getGeneratedIds().first();
	} finally {
		dbSession?.close();
	}
	// ------------------------------------------------------------------------------- //
	// ------------------------------------------------------------------------------- //
	/**
	* I load the given class from the MySQL JAR files.
	* 
	* CAUTION: While the ColdFusion application already has the Connector/J JAR files, it
	* didn't have the ProtoBuf JAR files. I had to download those from Maven.
	*/
	public any function javaNew( required string className ) {
		var jarPaths = [
			expandPath( "./jars/mysql-connector-java-8.0.22.jar" ),
			expandPath( "./jars/protobuf-java-3.19.4.jar" )
		];
		return( createObject( "java", className, jarPaths ) );
	}
</cfscript>

Oh chickens! We’re not in Kansas anymore! That’s a whole heck of a lot of ColdFusion code for adding a single document. Compare that to what SQL would look like for something similar:

<cfquery result="insertResult">
	INSERT INTO
		contact
	SET
		name = <cfqueryparam value="Sarah Smith" sqltype="varchar" />
</cfquery>
<cfset newID = insertResult.generatedKey />

You can see just how much magic the CFQuery tag (and the queryExecute() function) are doing for us behind the scenes. When we move away from SQL statements and start using the X DevAPI, we have to do all that magic ourselves; including configuring the connection pool and using resources allocated by that connection pool.

To start experimenting with the MySQL 8 Document Store and this X DevAPI, I wanted to try and hide a lot of that complexity. To that end, I created a ColdFusion component – XDevApiHelper.cfc – that exposes simplified CRUD (Create, Read, Update, Delete) methods that encapsulate all the session management and serialization. This way, I can with vanilla ColdFusion data structures and not have to worry so much about the low-level Java interactions.

Of particular note in the following ColdFusion component are two methods:

  • withSession( closure ) – This method uses the common pattern of accepting an operator Function and then transparently managing a resource that is passed to the Function. In this case, the withSession() method pulls a resource out of the connection pool, passes it to the closure, and then safely returns the resource to the connection pool.

  • jsonResultToCFML( value ) – When we read documents out of the database using the X DevAPI, the results are a mish-mash of native ColdFusion data structures and JsonValue instances. In order to convert the result to something that can be used seamlessly with ColdFusion, I’m recursively mapping the result onto Structs, Arrays, and deserialized values.

All the CRUD methods are at the bottom and start with an x, as in xAdd(). I didn’t want these methods to conflict with any native ColdFusion built-in functions, like find().

component
	output = false
	hint = "I provide methods that facilitate interacting with the X-DevAPI aspect of the MySQL 8 driver."
	{
	/**
	* I initialize the MySQL 8 X-DevAPI helper.
	*/
	public void function init(
		required string username,
		required string password,
		required string databaseServer,
		required string defaultSchema,
		required array jarPaths
		) {
		variables.defaultSchema = arguments.defaultSchema;
		variables.jarPaths = arguments.jarPaths;
		// Unlike with the CFQuery tag, we have to manage our own connection pool when
		// using the X-DevAPI. Or, at least, we have to instantiate it and let the client
		// take care of it.
		// --
		// https://dev.mysql.com/doc/x-devapi-userguide/en/connecting-connection-pool.html
		var pooling = {
			// Connection pooling enabled. When the option is set to false, a regular,
			// non-pooled connection is returned, and the other connection pool options
			// listed below are ignored. Default true.
			enabled: true,
			// The maximum number of connections allowed in the pool. Default 25.
			maxSize: 25,
			// The maximum number of milliseconds a connection is allowed to idle in the
			// queue before being closed. A zero value means infinite. Default 0.
			maxIdleTime: 30000,
			// The maximum number of milliseconds a request is allowed to wait for a
			// connection to become available. A zero value means infinite. Default 0.
			queueTimeout: 10000
		};
		// NOTE: The X-DevAPI uses a different PROTOCOL and PORT.
		variables.dbClient = javaNew( "com.mysql.cj.xdevapi.ClientFactory" )
			.init()
			.getClient(
				"mysqlx://#username#:#password#@#databaseServer#:33060",
				serializeJson({ pooling: pooling })
			)
		;
	}
	// ---
	// PUBLIC METHODS.
	// ---
	/**
	* I load the given class from the MySQL JAR files.
	* 
	* CAUTION: While the ColdFusion application already has the Connector/J JAR files, it
	* didn't have the ProtoBuf JAR files. I had to download those from Maven.
	*/
	public any function javaNew( required string className ) {
		return( createObject( "java", className, jarPaths ) );
	}

	/**
	* Many values that comes out of the X-DevAPI results seems to be a "JsonValue" class
	* instance. We need to convert those to native ColdFusion data structures. This
	* recurses through the given value and maps it onto native Structs, Strings, Numbers,
	* etc.
	*/
	public any function jsonResultToCFML( required any input ) {
		if ( isStruct( input ) ) {
			return input.map(
				( key, value ) => {
					return( jsonResultToCFML( value ) );
				}
			);
		} else if ( isArray( input ) ) {
			return input.map(
				( value, index ) => {
					return( jsonResultToCFML( value ) );
				}
			);
		} else if ( isInstanceOf( input, "com.mysql.cj.xdevapi.JsonValue" ) ) {
			return( deserializeJson( input.toString() ) );
		} else {
			return( input );
		}
	}

	/**
	* I parse the given _id value into its individual parts.
	* 
	* CAUTION: The date in the _id is NOT the date that the document was created - it's
	* the date that the SERVER was STARTED. If you want to know when a document was
	* created, you have to store that as a property on the document (it seems).
	*/
	public struct function parseID( required string id ) {
		// All aspects of the "_id" object are HEX-encoded numbers.
		// --
		// https://dev.mysql.com/doc/x-devapi-userguide/en/understanding-automatic-document-ids.html
		var prefix = inputBaseN( id.left( 4 ), 16 );
		var epochSeconds = inputBaseN( id.mid( 5, 8 ), 16 );
		var serial = inputBaseN( id.right( 16 ), 16 );
		// The "server startup" value is the Epoch Seconds. Let's convert that to a date
		// using Epoch milliseconds.
		var serverStartedAt = createObject( "java", "java.util.Date" )
			.init( epochSeconds * 1000 )
		;
		return({
			prefix: prefix,
			startedAt: serverStartedAt,
			serial: serial
		});
	}

	/**
	* I shutdown the database client, closing all session and disconnecting from the
	* MySQL database.
	*/
	public void function teardown() {
		dbClient?.close();
	}

	/**
	* I get a new Session from the Client's connection pool and pass it to the given
	* callback operator (along with the correct schema and a reference to this component).
	* The session is automatically returned to the pool once the operator has completed.
	* Any result returned from the operator is passed-back up to the calling context.
	*/
	public any function withSession(
		required function operator,
		string databaseSchema = defaultSchema
		) {
		try {
			var dbSession = dbClient.getSession();
			var dbSchema = dbSession.getSchema( databaseSchema );
			return( operator( dbSession, dbSchema, this ) );
		} finally {
			dbSession?.close();
		}
	}

	/**
	* I add the given document to the collection. Returns the generated ID.
	*/
	public string function xAdd(
		required string into,
		required struct value
		) {
		var results = withSession(
			( dbSession, dbSchema ) => {
				var addStatement = dbSchema
					.createCollection( into, true )
					.add([ serializeJson( value ) ])
				;
				return( addStatement.execute() );
			}
		);
		return( results.getGeneratedIds().first() );
	}

	/**
	* I return the documents that match the given where clause.
	*/
	public array function xFind(
		required string from,
		string where = "",
		struct params = {},
		numeric limit = 0
		) {
		var results = withSession(
			( dbSession, dbSchema ) => {
				var findStatement = dbSchema
					.createCollection( from, true )
					.find( where )
					.bind( params )
				;
				if ( limit ) {
					findStatement.limit( limit );
				}
				return( jsonResultToCFML( findStatement.execute().fetchAll() ) );
			}
		);
		return( results );
	}

	/**
	* I get the document with the given ID. If the document doesn't exist, an error is
	* thrown.
	*/
	public struct function xGetOne(
		required string from,
		required string id
		) {
		var results = withSession(
			( dbSession, dbSchema ) => {
				var dbDoc = dbSchema
					.createCollection( from, true )
					.getOne( id )
				;
				if ( isNull( dbDoc ) ) {
					throw(
						type = "Database.DocumentNotFound",
						message = "The document could not be found."
					);
				}
				return( jsonResultToCFML( dbDoc ) );
			}
		);
		return( results );
	}

	/**
	* I remove the document with the given ID. Returns the number of documents affected by
	* the operation.
	*/
	public numeric function xRemoveOne(
		required string from,
		required string id
		) {
		var itemCount = withSession(
			( dbSession, dbSchema ) => {
				var result = dbSchema
					.createCollection( from, true )
					.removeOne( id )
				;
				return( result.getAffectedItemsCount() );
			}
		);
		return( itemCount );
	}

	/**
	* I update documents that match the given where clause. Returns the number of
	* documents affected by the operation.
	*/
	public numeric function xUpdate(
		required string from,
		required string where,
		required struct set,
		struct params = {}
		) {
		var itemCount = withSession(
			( dbSession, dbSchema ) => {
				var modifyStatement = dbSchema
					.createCollection( from, true )
					.modify( where )
					.patch( serializeJson( set ) )
					.bind( params )
				;
				return( modifyStatement.execute().getAffectedItemsCount() );
			}
		);
		// NOTE: If the targeted object exists but the operation didn't actually change
		// any of the properties, this count will be zero.
		return( itemCount );
	}
}

There’s a lot of code there. But, it greatly simplifies the interactions in the calling context.

view all of this code over in GitHub, including the downloaded JAR files for MySQL Connector/J and ProtoBuf.

First, I defined my ColdFusion application component, Application.cfc, that instantiates and caches my XDevApiHelper.cfc component. Of special note here is that I also have to implement the onApplicationEnd() event handler so that I can teardown the connection pool when the application shuts down. With CFQuery, we never have to worry about this. But, when we start managing the connection pool ourselves, this becomes important. If we don’t call .close() on the Client, those threads continue to be kept alive in MySQL.

component
	output = false
	hint = "I define the application settings and event handlers."
	{
	// Define the application settings.
	this.name = "MySqlXDevApiTesting";
	this.applicationTimeout = createTimeSpan( 0, 1, 0, 0 );
	this.sessionManagement = false;
	// ---
	// LIFE-CYCLE METHODS.
	// ---
	/**
	* I get called once to initialize the application state.
	*/
	public void function onApplicationStart() {
		application.xDevApi = new XDevApiHelper(
			username = "root",
			password = "password",
			databaseServer = "127.0.0.1",
			defaultSchema = "doc_demo",
			jarPaths = [
				expandPath( "./jars/mysql-connector-java-8.0.22.jar" ),
				expandPath( "./jars/protobuf-java-3.19.4.jar" )
			]
		);
	}

	/**
	* I get called once to teardown the application state.
	*/
	public void function onApplicationEnd( required struct applicationScope ) {
		applicationScope.xDevApi?.teardown();
	}

	/**
	* I get called once to initialize the request state.
	*/
	public void function onRequestStart() {
		// If the reset flag exists, re-initialize the ColdFusion application.
		if ( url.keyExists( "init" ) ) {
			// It's important that we call the applicationStop() function so that our
			// onApplicationEnd() method is called and we can clean-up the database
			// connections being held-open by X DevAPI before we create a new connection
			// pool in the onApplicationStart() event handler.
			applicationStop();
			location( url = cgi.script_name, addToken = false );
		}
	}
}

For the CRUD-oriented CFML pages, I’m not going to go into any detail – there’s really nothing much happening. These page are not intended to be elegant; nor are they intended to be an example of best practices. This was just an experiment to get more familiar with the MySQL 8 X DevAPI – take all of this code with a grain of salt.

For this exploration, a “Contact” has:

  • name – String
  • phoneNumber – String
  • tags – Array of string
  • isBFF – Boolean

Documents being pulled-back and listed from the MySQL document store in Lucee CFML.

Source: www.bennadel.com