This morning at the Developer Live event, our Executive Vice President Juan Loaiza announced the launch of Autonomous JSON Database (AJD). This is an exciting announcement because it represents the very first time we are offering a dedicated solution for JSON document collection persistence in the Oracle Cloud. It symbolizes our commitment to offering developers solutions that fit their needs and solves the problems they face when developing microservices and applications instead of trying to convince them to use a tool that might not be the best fit for them. Of course, like all good solutions, it is scalable and adaptable which means that it can transform into a full-blown Autonomous Transaction Processing database if your needs dictate that in the future. But of course, if you know me then you have likely come to this blog post to learn how to quickly get started using AJD, so let's take a look at how to use it. I promise that you'll be able to get an instance launched and data persisted quickly, so let's not waste any more time and get into the good stuff!

In this post, I'll show you how to create a brand new instance, connect up to it, create a collection and insert, query, and remove data from that collection. We'll do all of these things in less than 10 minutes and all without leaving your browser. 

To get started, log in to your Oracle Cloud console. From any page in the console, click on the 'Cloud Shell' icon in the header of the page to launch a new Cloud Shell.

Your Cloud Shell instance will take a minute or two to launch the first time, but once it launches it will remain open and ready as you navigate around the console. While you're waiting for the Cloud Shell to launch, collect the compartment OCID that you'd like to work with. That's all that we will need before we start creating our instance and performing persistence operations in it.

Once you have that OCID, start by running the following CLI command in Cloud Shell to launch the instance.

One of the (many) awesome things about Cloud Shell is that it comes with the OCI CLI pre-installed and configured so you can immediately start working with it in your tenancy without having to spend time on installation and configuration!

Create the instance, substituting your compartment OCID and a strong password of your choosing. We'll capture the JSON response so that we can grab information from it later on.

Note the new value (AJD) for launching an Autonomous JSON Database in the command above.

It will take a few minutes for your AJD instance to be created and provisioned. Next, we'll grab the newly created instance's OCID and store it in a variable.

We'll need to download our wallet to make a connection, so let's do that. Enter your own path and password instead of using mine!

We're already ready to connect to our instance. You might be wondering how we're going to do that from Cloud Shell? Well, last week we quietly rolled out a new tool added in the form of SQLcl. We'll use that tool to connect and do some basic CRUD operations. Let's get to it.

Launch SQLcl:

Point the tool at our wallet.

Connect with the admin user (enter the password when prompted).

Note: The next steps are purely optional. If you're just testing things out, feel free to skip them. But if you're planning on working a bit further and connecting up to an application, use these steps to create a schema.

Optionally, create a user and connect with that user. Enter your own username and password for this user.

Checkpoint

In just a few minutes we've created and connected to our AJD instance and we're ready to create a collection and perform some CRUD operations. You may be a bit confused right now - wondering how we are going to persist JSON documents in an Autonomous instance. The answer, of course, is SODA. 

Simple Oracle Document Access (SODA) is a set of NoSQL-style APIs that let you create and store collections of documents (in particular JSON) in Oracle Database, retrieve them, and query them, without needing to know Structured Query Language (SQL) or how the documents are stored in the database.

There are a number of SODA implementations which means you can work with it natively from your new and existing microservices:

• SODA for Java
• SODA for Node.JS
• SODA for Python
• SODA for REST
• SODA for C
• SODA for PL/SQL

There's one other option for SODA, and that's the SQLcl console that we're already connected to our instance with. Let's work through a full CRUD example very quickly below. You can always refer to the SQLcl documentation for SODA later if you get stuck.

JSON CRUD In 5 Minutes

To create a collection:

List all collections:

Insert a few docs into a collection:

Get all documents from a collection:

Get a document by key:

Search with query by example (QBE):

Count docs in collection:

Count docs with QBE:

Replace (update) an existing doc:

Confirm the replacement:

Remove (delete) a document:

View the table behind the JSON collection:

Use “vanilla” SQL to query the data (treating the JSON fields as columns):

JSON fields can also be used in your WHERE clause:

If you want to view the JSON document as a string, use json_serialize():

That's all it takes to create a JSON collection and insert, update and delete JSON documents to and from that collection. You're now ready to integrate your AJD instance into your new and existing microservices for full JSON document collection persistence in the Oracle Cloud.

Bonus: Create & Test a Node.JS App in Cloud Shell!

Since we're here, we might as well test out one of the SODA client libraries, so let's create a basic Node.JS application in Cloud Shell to work with our collection. Node and NPM are already installed, so we can create a directory and an application straight away at this point. But before we do that, let's do a tiny bit of admin work. We'll need our wallet unzipped and we have to set an environment variable to the location where we unzipped it. We'll also need to update the sqlnet.ora file to point at our wallet directory. We can accomplish all of this like so (again, update the path to the proper path for your Cloud Shell home directory):

Create a directory for the project and switch to it:

Create a new project and install oracledb:

Install the instantclient into your project directory and set the LD_LIBRARY_PATH:

Set some env vars:

In the project root, create and edit your index.js file (or whatever you chose for your entry point):

Save, close, and run it with:

Should produce output similar to this:

We can log back in with SQLcl and take a look at our collection to see what happened:

We can see that we now have 3 records and the newest record's ID matches the ID that we inserted with Node.JS. Let's get the newest one by ID and confirm that the content matches the content we persisted with Node.JS:

In just 5 more minutes, we created a Node.JS application to persist JSON documents in our AJD instance and confirmed those operations with SQLcl. 

Summary & Cleanup

If you'd like to destroy the test instance of Autonomous JSON Database that we created above, run the following:

In this post, we learned about Autonomous JSON Database (AJD), created an AJD instance, and performed CRUD operations using SQLcl and a basic Node.JS application from within Cloud Shell. To learn more about AJD, please refer to the documentation:

Read more about AJD here:

• https://docs.oracle.com/en/cloud/paas/autonomous-json-database/ajdug/autonomous-json-database.html

Photo by Susan Yin on Unsplash