Develop MongoDB Applications with Oracle Autonomous Database on Dedicated Exadata Infrastructure

Oracle Autonomous Database (ADB) offers powerful capabilities for developing applications with high-performance ACID transactions and robust security, including native support for JSON data. If you're a MongoDB developer, you can harness these capabilities within Autonomous Databases using the Oracle Database API for MongoDB.

The Oracle Database API for MongoDB enables seamless integration with Oracle Autonomous Database by utilizing MongoDB language drivers and tools. It takes advantage of the converged database capabilities of Autonomous Database, allowing you to manage multiple data types, including JSON, within a single database. This convergence also empowers you to leverage SQL for querying and updating JSON data.

In this blog post, we'll explore how to migrate a MongoDB collection and reap some of the benefits of ADB's converged capabilities. To use the MongoDB API with an Autonomous Database on Dedicated Exadata infrastructure (ADB-D)*, follow these steps:

1. Create or reuse and Autonomous Transaction Processing (ATP) on Dedicated Exadata infrastructure (ATP-D, both OCI and Cloud@Customer supported)

2. Install and configure the customer-managed Oracle REST Data Services (ORDS) with a version of 22.3 or later

3. Obtain the Autonomous Wallet, which can be downloaded from the "Database connection" tab of your ADB instance

4. Install Oracle JDK

*Autonomous Database Serverless already includes a version of ORDS with support for the Oracle Database API for MongoDB so no installation is needed

Once the prerequisites are in place, the installation process is straightforward. Unzip the downloaded ORDS package and locate the binary file. Running the binary initiates a guided process that prompts for necessary information, such as the ADMIN password and the wallet's full location. Execute the following command to begin the installation:

./ords install adb --interactive

By default, the MongoDB API is not enabled. To enable it, run the following command:

./ords config set mongo.enabled true

Finally, start ORDS:

./ords serve

Once started, the log will display the connection string for using the MongoDB API, which should resemble the following:

mongodb://[{user}:{password}@]localhost:27017/{user}?authMechanism=PLAIN&authSource=$external&ssl=true&retryWrites=false&loadBalanced=true

(For a deep dive into configuring any Oracle database to support the Oracle Database API for MongoDB see this blog post by Roger Ford)

Now, let's migrate a MongoDB collection into the Autonomous Database instance. Suppose we have a sample dataset called "inspections" with 81k documents and this specific structure:

We can generate the JSON document using mongoexport:

mongoexport --collection=inspections --db city --out=city_inspections.json

Next, use mongoimport to import the data into the Autonomous Database instance via the MongoDB API:

mongoimport --sslAllowInvalidCertificates --file city_inspections.json --uri 'mongodb://admin:password@localhost:27017/admin?authMechanism=PLAIN&authSource=$external&ssl=true&retryWrites=false&loadBalanced=true'

The data migration process is complete. Now, you can connect to the Autonomous Database instance using mongosh and verify that the collection and the number of documents are preserved:

mongosh --tlsAllowInvalidCertificates 'mongodb://admin:password@localhost:27017/admin?authMechanism=PLAIN&authSource=$external&ssl=true&retryWrites=false&loadBalanced=true'

You can execute your typical queries. For instance, let's find a specific certificate number:

db.city_inspections.find({ certificate_number: { $eq: 9278806 } })

Now, let's explore the migrated collection using SQL within the Autonomous Database. We'll use the Oracle SQL Developer command line (sqlcl) to run a SQL count and verify the number of rows:

SELECT COUNT(*) FROM city_inspections;

Autonomous Database allows you to view data in both JSON and relational formats. For example, you can execute a SQL query to display the ID and certificates for New York City:

SELECT id, certificates FROM city_inspections WHERE city = 'New York';

Additionally, if needed, you can retrieve data in JSON format:

SELECT JSON_OBJECT('id' VALUE id, 'certificates' VALUE certificates) FROM city_inspections WHERE city = 'New York';

Furthermore, you have the flexibility to perform JOIN operations between existing relational tables and JSON collections:

SELECT c.name, i.certificates FROM customers c JOIN city_inspections i ON c.customer_id = i.customer_id;

As demonstrated, migrating your MongoDB workloads to Autonomous Database - Dedicated allows you to leverage your MongoDB expertise while benefiting from the features of a converged database. You can continue using familiar tools and also take advantage of capabilities like querying JSON collections with SQL.

In conclusion, by integrating MongoDB applications with Oracle Autonomous Database, developers can harness the power of a dedicated Exadata infrastructure, native JSON support, ACID transactions, comprehensive security, and the ability to manage multiple data types within a single database. This seamless integration provides an opportunity to enhance existing MongoDB code and leverage the benefits of a converged database environment.

Source: oracle.com