I recently blogged about a brand-new service in the Oracle Cloud - Database Tools. It's a safe, secure way to store your database credentials in the cloud and use them in your application or to connect to other services in the cloud like SQL Worksheets and SQLcl in Cloud Shell. If you haven't read that post, you definitely should do that. If you did read it, maybe you gave it a try and discovered that it is super helpful and easy! If you're especially curious, maybe you opened up the Developer Tools console in your browser while using a SQL Worksheet to see what's going on under the covers and noticed that your query statements are posted to a special endpoint that requires nothing more than a signed request to a URL that contains the DB Tools OCID to run the query against the specified connection. In this post, we're going to get creative and take advantage of that endpoint to run some queries.

We'll start by opening up a SQL worksheet and running a basic query. But before we do that, open up the Developer Tools in your browser and go to the 'Network' tab to make sure it is capturing requests.

Nothing complicated here. Just a basic query to prove that we're connected to the database and can run a simple query. Now, let's take a look at the HTTP call in the network tab to see what's going on.

It's a basic HTTP request to an endpoint that uses the following format:

https://sql.dbtools.us-phoenix-1.oci.oraclecloud.com/20201005/ords/ocid1.databasetoolsconnection.oc1.phx.../_/sql

The only 'variables' in this URL are the region ('us-phoenix-1') and the DB Tools Connection OCID, so we should be able to work with this! We can see an authorization header that contains the signature required for making REST calls to any OCI endpoint. The process for signing a request is outlined in the docs, but don't worry, we're not going to be signing the request manually (although you absolutely could if you wanted to). Finally, take a look at the request body.

Very Interesting! The request payload is an object/map that contains 4 keys: binds, limit, offset, and statementText. This looks like something that we can work with!

Running a Query via the OCI CLI

You've got the OCI CLI installed, right? Cool. Let's take a look at the CLI Reference documentation for dbtools and see how to run a query. Oh, wait. It's not documented there. That's OK - we can make this work. How? By making a raw request! Let's recreate the basic query above with the CLI and pipe it to JQ to pretty-print the results.

Which should give us something similar to this:

Whoa! That worked! We get all kinds of metadata back, and inside that result is our query result:

Fantastic! We can even get paginated results by passing offset and limit in the request body.

Which gives us:

What about bind variables? Just pass them as a list of objects (each containing a name for the bind variable, the data_type, and the value).

So we've established that it's fairly straightforward to use this endpoint - as long as the request is properly signed. So how might we use this from an SDK then? 

Running a Query via the OCI Java SDK

The good news is that most of the OCI SDKs include a method to sign a raw request to an OCI REST Endpoint. We'll look at the Java SDK below, but if you use Python or Node then you should check the SDK docs for a similar method. To get started, add a dependency for the OCI Java SDK 'common' module.

Now let's create a DatabaseToolsQueryRunner class and in the constructor create an instance of the BasicAuthenticationDetailsProvider and a RequestSigner (javadoc).

Create a run() method that will accept the SQL string.

Now we'll construct the URL, set some necessary variables, create and serialize the request body, and sign the request.

Next, we'll use HttpURLConnection to construct the request and set the headers.

Set the request body.

And then read the response and clean things up.

Now we can create an instance of our query runner class (passing in the connection OCID) and run a query!

If all went well, we'll get the following output:

Nice!! What about pagination? Just change the method signature to:

And pass them in the request body:

And call it:

Which produces:

Same thing goes for bind variables. 

And the request body:

And call it:

Which gives us:

Summary

If you don't mind me saying so, this has to be one of the coolest blog posts I've written in quite a while. Using Database Tools Connections in this manner to execute queries against a database is secure and powerful. It doesn't require a whole lot of external dependencies and uses simple REST calls instead of complex datasources and connection pools. This method can open up all kinds of opportunities for interacting with your database from clients that might not otherwise be able to easily interact (such as IoT devices or languages without official support). If you want to see more examples, check out this Gist from Kris Rice (which uses Jersey instead of the HttpURLConnection class and includes more awesome stuff like exporting to custom formats like XML, CSV, JSON, etc) or refer to the documentation.