8 Using Oracle Database API for MongoDB

Oracle Database API for MongoDB makes it possible to connect to Oracle Autonomous Database using MongoDB language drivers and tools.

Oracle Database API for MongoDB leverages the converged database capabilities of an Autonomous Database to manage multiple data types, including JSON data, within a single database. For example, these converged database capabilities allow you to use SQL to query or update JSON data.

See Oracle Database API for MongoDB for more information.

See About Autonomous JSON Database for more information.

See About Autonomous Database for Transaction Processing and Mixed Workloads for more information.

Configure Access for MongoDB

Oracle Database API for MongoDB enables you to use an Oracle Autonomous Database as the data store. You can create and configure a new Autonomous Database or modify the configuration of an existing Autonomous Database.

Configure a New Autonomous Database for MongoDB

Follow the steps in Provision an Autonomous Database, up to the point where you select your Network Access Type.

Description of adb_network_access_acl_provision.png follows
Description of the illustration adb_network_access_acl_provision.png

Configure an Existing Autonomous Database for MongoDB

Open the Oracle Cloud Infrastructure Console for your Autonomous Database instance.

Description of ajd_modification_for_new_mongodb_use.png follows
Description of the illustration ajd_modification_for_new_mongodb_use.png

In the Network section, next to Access Control List click the Edit link.

Note for setting the Network Access Type:

  • If your database Network Access Type is Private endpoint access only, you first must change to Allow secure access from everywhere using the Update Network Access from More Actions list in the Oracle Cloud Infrastructure Console. When switching to Allow secure access from everywhere, you will not be able to set the ACLs. After you change the network type, you can edit the ACLs.

Access Control List (ACL) Setup

See Configure Access Control Lists for an Existing Autonomous Database Instance for more information.

To configure your ACL for an IP address, you will need to obtain the public IP address. There are several ways to show your public IP address:
  • In the choose network access area, click Add My IP Address. This copies your IP address into the Values field.

  • After disabling any VPN, use the WhatIsMyIP website.
  • After disabling any VPN, use the curl command: curl -s https://ifconfig.me.

Note:

Public IP addresses may change. Any change to your public IP address will require a change in the ACL. If you are unable to access your database, your ACL should be something you check.

ACLs Types and Use Cases

ACL Type Use Case Comment
IP Address Local development laptops sharing the same public IP address Easiest way to get started. Any laptop connected on this LAN will have access to the database with the database credentials.
CIDR Block Local development laptop Using IPv4/32 notation
IP Addresses separated by commas Small number of local development laptops connected on distinct LANs (having distinct public IP addresses) Can be tedious to manage with 10+ laptops.
CIDR Block Local development laptops connected on the same subnet exposed to Internet (each laptop has its own public IP Address) Rely on CIDR Block notation. See calculator here for more information. Example: 89.84.109.0/24 gives 256 possible IP addresses from 89.84.109.0 to 89.84.109.255
VCN with CIDR Block For testing, production, or CI/CD pipeline hosted on OCI having their own VCN and Compute instances Assign OCI compartment per environment type.
Mixing IP Address and VCN with CIDR Block Local development laptop accessing a test Autonomous Database with connections from the testing environment or CI/CD pipeline A common configuration option for on-going development work.

User Management for MongoDB

Oracle Database API for MongoDB enables you to use an Oracle Autonomous Database as the data store. If you want or need to use an existing Autonomous Database for this purpose, here is the workflow.

Oracle Database API for MongoDB enables the mapping of Autonomous Database objects to MongoDB objects as follows:

MongoDB Object Oracle Autonomous Database Object
database schema
collection table
document document (in a column)

For example, you could create a collection using the Oracle Database API for MongoDB as follows:

  use scott;
  db.createCollection('fruit');

A table named FRUIT is created in the schema SCOTT.

When you connect to the Oracle Database API for MongoDB, you authenticate using an Autonomous Database username and password. This authenticated connection then accesses collections within the corresponding schema. This user must meet the following requirements:

Note:

The role DWROLE in the Autonomous Database contains these roles, among others.

Access to schemas not granted to the user is prohibited. For example, the user SCOTT can only access collections in the schema SCOTT. There is one exception. If the authenticated user has the Autonomous Database privileges CREATE USER, ALTER USER and DROP USER, that user can access any ORDS-enabled schema.

Additionally, a user with these privileges can implicitly create schemas. That is, when the user creates a collection in a database that does not exist, the schema will be created automatically. See Oracle Database API for MongoDB for more information.

Create a Test Autonomous Database User for MongoDB

The steps that follow use Database Actions to create a test user with the proper roles. You can also use Database Actions SQL or other SQL command line utilities to execute the SQL statements directly. See Create Users on Autonomous Database - Connecting with a Client Tool for more information.
  1. Open the Oracle Cloud Infrastructure Console for your Autonomous Database.
  2. Select Database Actions.
  3. From the Database Actions Launchpad, select Administration > Database Users.
  4. Select + Create User on the Database Users page.
  5. Enter a User Name and Password for your test user. Select Web Access, and set the Quota on tablespace DATA.
  6. Select the Granted Roles tab.
  7. In addition to the default roles, select and add the SODA_APP role for the user.
  8. Select the Create User button.
You can user this user, or a similarly authenticated user, for testing purposes. See Test Connection Using the Command Line for more information.

Connect MongoDB Applications to Autonomous Database

Connecting your MongoDB application to Autonomous Database includes several steps, depending upon your requirements.

Retrieve the Autonomous Database Connection String

Use the MongoDB Shell which is a command-line utility used to connect and query your data.
  1. To retrieve the connection string for your Autonomous Database instance, open Database Actions.

    See Access Database Actions as ADMIN for more information.

  2. On the Database Actions Launchpad, under Related Services, click Oracle Database API for MongoDB.
  3. On the Oracle Database API for MongoDB page click Copy.

    The Oracle Database API for MongoDB page shows two connection strings:

    • Copy the string with port 27017 if your driver supports the loadBalanced property.
    • Copy the string with port 27016 if your driver does not support the loadBalanced property.

Test Connection Using the Command Line

  1. Login as the test user. See Create a Test Autonomous Database User for MongoDB for more information.
    $ mongosh --tls --tlsAllowInvalidCertificates 'mongodb://TESTUSER:<PASSWORD>@<database URL>.
    <OCI region>.oraclecloudapps.com:27017/admin?authMechanism=PLAIN&authSource=$external&ssl=true&loadBalanced=false'
    Current Mongosh Log ID: 614c9e2a01e3575c8c0b2ec7
    Connecting to:         mongodb://TESTUSER:<PASSWORD>@<database URL>.<OCIregion>.oraclecloudapps.com:27017/admin?
    authMechanism=PLAIN&authSource=$external&tls=true&loadBalanced=false
    Using MongoDB:                   3.6.2
    Using Mongosh:                   1.0.7
    For mongosh info see: https://docs.mongodb.com/mongodb-shell/admin
    > show dbs
    testuser    0 B
    > 

    Note:

    Use URI percent-encoding to replace any reserved characters in your connection-string URI — in particular, characters in your username and password. These are the reserved characters and their percent encodings:

    ! # $ % & ' ( ) * +
    %21 %23 %24 %25 %26 %27 %28 %29 %2A %2B
    , / : ; = ? @ [ ]
    %2C %2F %3A %3B %3D %3F %40 %5B %5D

    For example, if your username is RUTH and your password is @least1/2#? then your MongoDB connection string to server <server> might look like this:

    'mongodb://RUTH:%40least1%2F2%23%3F@<server>:27017/ruth/ ...'

    Depending on the tools or drivers you use, you might be able to provide a username and password as separate parameters, instead of as part of a URI connection string. In that case you likely won't need to encode any reserved characters they contain.

    See also:

  2. Create a collection and insert documents into your collection.
    testuser> show collections
    
    testuser> db.createCollection( 'fruit' )
    { ok: 1 }
    testuser> show collections
    fruit
    testuser> db.fruit.insertOne( {name:"orange", count:42} )
    {
      acknowledged: true,
      insertedId: ObjectId("614ca31fdab254f63e4c6b47")
    }
    testuser> db.fruit.insertOne( {name:"apple", count:12, color: "red"} )
    {
      acknowledged: true,
      insertedId: ObjectId("614ca340dab254f63e4c6b48")
    }
    testuser> db.fruit.insertOne( {name:"pear", count:5} )
    {
      acknowledged: true,
      insertedId: ObjectId("614ca351dab254f63e4c6b49")
    }
    testuser>
  3. Query the collection using a SQL client such as Database Actions.
    SELECT
         f.json_document.name, 
         f.json_document.count, 
         f.json_document.color
    FROM fruit f;
    Description of database_actions_sql_for_mongodb_use.png follows
    Description of the illustration database_actions_sql_for_mongodb_use.png

Test Connection Using a Node.js Application

  1. Download Node.js. If you have already downloaded or installed Node.js for your environment, you can skip this step.
    $ wget https://nodejs.org/dist/latest-v14.x/node-v14.17.5-linux-x64.tar.xz
  2. Extract the contents of the Node,js archive. If you have already downloaded or installed Node.js for your environment, you can skip this step.
    $ tar -xf node-v14.17.5-linux-x64.tar.xz
  3. Configure the PATH environment variable. If you have already downloaded or installed Node.js for your environment, you can skip this step.
    $ export PATH="`pwd`"/node-v14.17.5-linux-x64/bin:$PATH
  4. Test your connection with a Javascript example.
    1. Create a new directory.
      $ mkdir autonomous_mongodb
      $ cd autonomous_mongodb
      $ npm init –y
    2. Install mongodb dependency.
      $ npm install mongodb
    3. Create a JavaScript application named connect.js.
      const { MongoClient } = require("mongodb");
      const uri =
         "mongodb://TESTUSER:<PASSWORD>@<Database URI>.<OCI region>.oraclecloudapps.com:27017/admin?authMechanism=PLAIN&authSource=$external&ssl=true&loadBalanced=false";
      
      const client = new MongoClient(uri);
      
      async function run() {
        try {
              await client.connect();
      
              const database = client.db('admin');
              const movies = database.collection('movies');
      
              // Insert a movie
              const doc = { title: 'Back to the Future', 
                            year: 1985, genres: ['Adventure', 'Comedy', 'Sci-Fi'] }
              const result = await movies.insertOne(doc);
      
              // Query for a movie that has the title 'Back to the Future' :)
              const query = { title: 'Back to the Future' };
              const movie = await movies.findOne(query);
      
              console.log(movie);
        } finally {
           // Ensures that the client will close when you finish/error
           await client.close();
        }
      }
      run().catch(console.dir);
      

      Note:

      Use URI percent-encoding to replace any reserved characters in your connection-string URI — in particular, characters in your username and password. These are the reserved characters and their percent encodings:

      ! # $ % & ' ( ) * +
      %21 %23 %24 %25 %26 %27 %28 %29 %2A %2B
      , / : ; = ? @ [ ]
      %2C %2F %3A %3B %3D %3F %40 %5B %5D

      For example, if your username is RUTH and your password is @least1/2#? then your MongoDB connection string to server <server> might look like this:

      'mongodb://RUTH:%40least1%2F2%23%3F@<server>:27017/ruth/ ...'

      Depending on the tools or drivers you use, you might be able to provide a username and password as separate parameters, instead of as part of a URI connection string. In that case you likely won't need to encode any reserved characters they contain.

      See also:

    4. Run the example. The output should look similar to the following.
      $ node connect
      {
        _id: new ObjectId("611e3266005202371acf27c1"),
        title: 'Back to the Future',
        year: 1985,
        genres: [ 'Adventure', 'Comedy', 'Sci-Fi' ]
      }