Dirigible's Logodirigible

BYODS (Bring Your Own Data Source) in Dirigible - Part III: MongoDB custom data source


Dirigible welcomes Mongo DB onboard! Starting with version 2.2 Mongo DB is supported out-of-the-box

In previous blogs in the “BYODS in Dirigible” series we explored how data sources are integrated in general with most examples focusing on relational databases as options. But what about NoSQL?

Dirigible welcomes Mongo DB onboard! Starting with version 2.2 Mongo DB is supported out-of-the-box. You can explore it in the IDE and develop scripting services for it. Taking advantage of NoSQL document storage is now an entirely viable option. This is the first stage of our roadmap for onboarding NoSQL development. It uses JDBC as standard communication protocol and API. We are well aware that it is not native to NoSql development and is rather a “quick way in”. On next stage, we plan to explore the options to provision Apache TinkerPop as a well-recognized standard Graph API (to which Mongo DB also complies) to scripting services via the Injected API. We shall seek also for convenient ways to provide access to native Graph APIs of NoSql data stores with all pros and cons that go along with that.

But first things first. We shall now explore what we’ve got for Mongo DB developers in Dirigible 2.2.

Part III: MongoDB custom data source

What’s in the box? With a Mongo DB custom data source integrated in Dirigible Database tools, you can explore the related database instance list of collections and examine collection documents:

1
<img src="/img/posts/20160121-0/3-0.png"/>

As with any other relational data source, you can also execute queries and updates but hence, using Mongo’s native BSON-based query language:

1
<img src="/img/posts/20160121-0/3-1.png"/>

It is integrated also into the InjectedAPI and therefore in your scripting service you can request the data source by its name, get a connection and execute a statement using Mongo’s native query language, and iterate the result set (here, using the JDBC API. Read below for more options):

1
2
3
4
5
6
7
8
9
10
11
12
13
	var ds = $.getNamedDatasources().get(mongodb);
	var conn = ds.getConnection();
	try {
	    var stmt = conn.createStatement();
	    var rs = stmt.executeQuery({find:testCollection});
	    while (rs.next()) {
	        $.getResponse().getWriter().println(rs.getString(1)+':'+rs.getString(name) + '<br>');
	    }
	} finally {
	   conn.close();
	}

Key design notes

Onboarding a Mongo DB data source leverages exactly the same integration mechanism in Dirigible as any other (relational) data source. This feature has been discussed in detail in the previous BYODS blog series. The obvious advantage of this approach is that it follows an established path. That simplicity comes at the cost of a few reasonable prerequisites listed below.

JDBC API

JDBC is the standard API used by Dirigible internally to integrate data sources and by developers to use them. Therefore, you will need a JDBC compliant driver to provision access to a Mongo database. Its role is to reconcile the conceptual differences between the relational model centric JDBC API and the NoSQL document store world.

In Mongo, despite the name “Java driver” that you will find on Mongo DB’s site concerning Java clients, this has nothing to do with JDBC drivers. It is a Java client API. If you look around for available JDBC drivers for Mongo DB, they are not exactly abundant either. What’s more troublesome here is that virtually all available drivers actually try to translate between Mongo DB’s native query language and SQL. While this works perfectly well for us in terms of technical integration, it does not comply with our goal to make Mongo DB’s developers feel at home in Dirigible, because it would be fairly weird for them to write SQL to query a document database. To fill this gap and for the sake of this example we’ve prototyped a driver that can send native queries encoded as BSON to Mongo DB. It is available on Github.

The fine print? Once again, this driver is a prototype and as of the time of this writing there’s still nothing comparable (Meaning happily abusing the JDBC API as a standard protocol for Mongo DB but reusing its own query language). Show some love for it and we will further enhance it. The rest of the drivers out there translate to/from SQL, which will work for the InjectedAPI if you are happy with this approach, but not with the Dirigible database tools in the IDE’s Database perspective.

Query language

In order to execute query or update statements from Dirigible, your back-end needs to be able to interpret a formal language that can be encoded in strings because that’s the input it will get. There are options here, but it would be best to re-use a query language if your database already has one. Developers who are already used to it will feel at home and make the best use of the database capabilities. Other options, but less desirable for the same reasons, are to translate to and from SQL or other suitable language. MongoDB has a concept of query language. Queries are BSON encoded documents that are input to the operation find. Our JDBC driver takes documents in that format as string input to its query operations in the JDBC API, converts internally to BSON documents and invokes the operation find on the Mongo DB Java client. The JDBC query operations string input therefore needs to be compliant with Mongo’s

1
find
operation input parameter specification.

Result sets

Results are returned as JDBC ResultSet, i.e. in a table form. The driver of choice should be capable of transforming internally to this form of results presentation from Mongo’s documents format.

These are all important considerations when implementing and using the result sets returned by queries.

Provisioning

The setup of a Mongo DB data source is no different from what we already did in Part I, so here we shall cut short and focus only on the details that you need to provide.

Step 1: Provision JDBC drivers classes

Get the JDBC driver source from Github and use Maven to build. Copy the build result in Tomcat’s lib directory.

Step 2: Bind a Data Source to JNDI

Edit Tomcat’s conf/context.xml to add a resource:

1
2
3
4
5
6
7
	<Resource name="jdbc/MongoDB" auth="Container"
				type="javax.sql.DataSource"
				driverClassName="io.dirigible.mongodb.jdbcMongodbJdbcDriver"
				url="jdbc:mongodb://127.0.0.1:5432"
				username="<YOUR_USER_HERE>" password="<YOUR_PASSWORD_HERE>"/>

Note: Remember to change the placeholders in this example with actual values. **** and **** are respectively the user name and password for a valid user of the database.

Step 3: Configure application reference

Add the following init parameter to the bridge servlet in the web.xml

1
2
3
4
5
6
	<init-param>
		<param-name>jndiCustomDataSource-mongodb</param-name>
		<param-value>java:comp/env/jdbc/MongoDB</param-value>
	</init-param>

Step 4: Register the data source

Go to Dirigible IDE Preferences, locate Data Sources and create a new one. Fill in the following details in dialog that pops up:

Finally, confirm all dialogs.

And that’s pretty much it. You should have a new data source by the name

1
mongodb
by now.

Putting it to use

Now that we’ve got a Mongo DB data source in Dirigible, put it to some good use.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
	/* globals $ */
	/* eslint-env node, dirigible */

	$.getResponse().setContentType("text/html; charset=UTF-8");
	$.getResponse().setCharacterEncoding("UTF-8");
	var out = $.getResponse().getWriter();

	var ds = $.getNamedDatasources().get("mongodb");
	var conn = ds.getConnection();
	try {
	    var stmt = conn.createStatement();
	    var rs = stmt.executeQuery({find:"testCollection"});
	    while (rs.next()) {
		var rsDoc = rs.getObject(-100);
		for(var prop in rsDoc){
	       		out.println(prop + ': ' + rsDoc[prop] + '<br>');
	}
	    }
	} finally {
	   conn.close();
	}

	out.flush();
	out.close();

In this code snippet we have several semantic blocks. First we open a writer to output some data from the service:

1
2
3
4
5
	$.getResponse().setContentType("text/html; charset=UTF-8");
	$.getResponse().setCharacterEncoding("UTF-8");
	var out = $.getResponse().getWriter();

Next, we get a connection to the Mongo DB data source that we setup on previous stage:

1
2
3
4
	var ds = $.getNamedDatasources().get("mongodb");
	var conn = ds.getConnection();

Then, we create a statement and execute it using the standard JDBC API but the native Mongo DB query language:

1
2
3
4
	var stmt = conn.createStatement();
	var rs = stmt.executeQuery({find:"testCollection"});

Now we are ready to iterate on the result set and output some results. Note how we use the standard JDBC API for iteration and the little trick that our Mongo DB JDBC driver is capable of with the

1
rs.getObject(-100);
statement. Once we get hold of the JSON document for the current iteration we use pure JavaScript and no JDBC to make some use of it:

1
2
3
4
5
6
7
8
	while (rs.next()) {
	var rsDoc = rs.getObject(-100);
		for(var prop in rsDoc){
	       		out.println(prop + ': ' + rsDoc[prop] + '<br>');
		}
	}

Finally, as good citizens we close all open resource streams.

Tweet