About Oracle NoSQL Database Visual Studio Code Extension

The Oracle NoSQL Database Cloud Service provides an extension for Microsoft Visual Studio Code which lets you connect to a running instance of Oracle NoSQL Database Cloud Service.

You can use Oracle NoSQL Database Visual Studio (VS) Code extension to:
  • View the tables in a well-defined tree structure with Table Explorer.
  • View information on columns, indexes, primary key(s), and shard key(s) for a table.
  • View column data in a well-formatted JSON Structure.
  • Create tables using form-based schema entry or supply DDL statements.
  • Drop tables.
  • Execute SELECT SQL queries on a table and view query results in tabular format.
  • Execute DML statements to update, insert, and delete data from a table.

This article has the following topics:

Installing Oracle NoSQL Database Visual Studio Code Extension

You can install the Oracle NoSQL Database VS Code extension in two ways. Install from the Visual Studio Marketplace for online installation or install from the VSIX package using *.vsix file for offline installation.

Before you can install the Oracle NoSQL Database Visual Studio (VS) Code extension, you must install Visual Studio Code. You can download Visual Studio Code from here.
  1. In Visual Studio Code, click the Extensions icon in the left navigation.


    Visual Studio Code Activity Bar

    Alternatively, you can open the Extensions view by pressing:
    • (Windows and Linux) Control + Shift + X
    • (macOS) Command + Shift + X.
  2. Search Oracle NoSQL Database Connector in the extension marketplace.
  3. Click Install on the Oracle NoSQL Database Connector extension
  1. Download the VSIX file for Oracle NoSQL Database from Oracle NoSQL Database Downloads site.
  2. In Visual Studio Code, click the Extensions icon in the left navigation.


    Visual Studio Code Activity Bar

    Alternatively, you can open the Extensions view by pressing:
    • (Windows and Linux) Control + Shift + X
    • (macOS) Command + Shift + X.
  3. In the Extensions view, Click the More Actions (...) menu and then click Install from VSIX....


    Install from VSIX Action

  4. Browse to the location where the *.vsix file is stored and click Install.

Connecting to Oracle NoSQL Database Cloud Service from Visual Studio Code

Oracle NoSQL Database Visual Studio (VS) Code extension provides two methods to connect to Oracle NoSQL Database Cloud Service or Oracle NoSQL Database Cloud Simulator.

You can either provide a config file with the connection information or fill in the connection information in the specific fields. If you are using a Node.js driver and already have connection details saved in a file, use the Connect via Config File option to connect to the Oracle NoSQL Database Cloud Service. Otherwise, if you are creating a new connection, use the Fill in Individual Fields option.
  1. In Visual Studio Code, click the Oracle NoSQL DB view in the Activity Bar.


    Visual Studio Code Activity Bar

  2. Open the Oracle NoSQL DB Show Connection Settings page from the Command Palette or the Oracle NoSQL DB view in the Activity Bar.
    • Open from Command Palette
      1. Open the Command Palette by pressing:
        • (Windows and Linux) Control + Shift + P
        • (macOS) Command + Shift + P
      2. From the Command Palette, select OracleNoSQL: Show Connections Settings.

        Tip:

        Enter oraclenosql in the Command Palette to display all of the Oracle NoSQL DB commands you can use.


        Oracle NoSQL DB Show Connections Settings Command

    • Open from Oracle NoSQL DB View
      1. Expand the TABLE EXPLORER pane in the left navigation if it's collapsed.
      2. Click Add Connection to open the Oracle NoSQL DB Show Connection Settings page.


      Oracle NoSQL DB TABLE EXPLORER

  3. In the Show Connection Settings page, click Cloud or CloudSim to connect to Oracle NoSQL Database Cloud Service or Oracle NoSQL Database Cloud Simulator.


    NoSQL DB Connection Settings for Oracle NoSQL Database Cloud Service

  4. Enter the connection information.

    Table 5-4 Cloud Connection Parameters

    Field Description Sample Value
    Region: Select the Region identifier of the Oracle NoSQL Database Cloud Service endpoint. us-ashburn-1
    Configuration File: Browse to the location where the OCI configuration file is stored. /home/user/security/config/oci.config
    Profile: Name of the configuration profile to be used to connect to the Oracle NoSQL Database Cloud Service.

    If you do not specify this value, the field defaults to the DEFAULT profile.

    ADMIN_USER
    Compartment: The name or OCID of the compartment for your Oracle NoSQL Database Cloud Service schema.

    If you do not provide any value, the field defaults to the root compartment.

    You create compartments in Oracle Cloud Infrastructure Identity and Access Management (IAM). See Setting Up Your Tenancy and Managing Compartments in Oracle Cloud Infrastructure Documentation.

    • Compartment name

      mycompartment

    • Compartment name qualified with its parent compartment

      parent.childcompartment

    • Compartment OCID

      ocid1.tenancy.oc1...<unique_ID>

    Tenant OCID: Tenancy's OCID for your Oracle NoSQL Database Cloud Service. See Where to Get the Tenancy's OCID and User's OCID in Oracle Cloud Infrastructure Documentation ocid1.tenancy.oc1..<unique_ID>
    User OCID: User's OCID for your Oracle NoSQL Database Cloud Service. See Where to Get the Tenancy's OCID and User's OCID in Oracle Cloud Infrastructure Documentation. ocid1.user.oc1..<unique_ID>
    Fingerprint: Fingerprint for the private key that was added to this user.

    The fingerprint of the signing key is created while generating and uploading the API Signing Key. See How to Get the Key's Fingerprint in Oracle Cloud Infrastructure Documentation.

    12:34:56:78:90:ab:cd:ef:12:34:56:78:90:ab:cd:ef
    Private Key File: Browse to the location where private key is stored. See How to Generate an API Signing Key to generate the signing key with an optional pass phrase. /home/user/.oci/oci_api_key.pem
    Passphrase: Passphrase you specified when creating the private key. The pass phrase of the signing key is created while generating and uploading the API Signing Key. See How to Get the Key's Fingerprint in Oracle Cloud Infrastructure Documentation.

    Required, only if the key is encrypted.

     
    Compartment: The name or OCID of the compartment for your Oracle NoSQL Database Cloud Service schema.

    If you do not provide any value, the field defaults to the root compartment.

    You create compartments in Oracle Cloud Infrastructure Identity and Access Management (IAM). See Setting Up Your Tenancy and Managing Compartments in Oracle Cloud Infrastructure Documentation.

    • Compartment name

      mycompartment

    • Compartment name qualified with its parent compartment

      parent.childcompartment

    • Compartment OCID

      ocid1.tenancy.oc1...<unique_ID>

    Table 5-5 CloudSim Connection Parameters

    Field Description Sample Value
    Endpoint: Service Endpoint URL of the Oracle NoSQL Database Cloud Simulator instance. http://myinstance.cloudsim.com:8080

    If you do not specify the value, it defaults to http://localhost:8080.

    Tenant Identifier: Unique identifier to identify the tenant. Tenant01

    If you do not specify the value, it defaults to TestTenant.

  5. Click Connect.
  6. Click Reset to clear the saved connection details from the workspace.
  1. Create the config file, for example, config.json or a file with the JSON object. The config file format for connecting to Oracle NoSQL Database Cloud Service or Oracle NoSQL Database Cloud Simulator is as shown below.

    Table 5-6 Configuration Templates

    Oracle NoSQL Database Cloud Service Oracle NoSQL Database Cloud Simulator
    Configuration template to connect using OCI configuration file
    {
     "region": "<region-id-of-nosql-cloud-service-endpoint>",
     "compartment": "<oci-compartment-name-or-id>",
     "auth":
      {
       "iam":
        {
         "configFile": "<path-to-OCI-config-file>", 
         "profileName": "<oci-credentials-profile-name>"
        }
      }
    }
    Configuration template to connect using IAM authentication credentials
    {
     "region": "<region-id-of-nosql-cloud-service-endpoint>",
     "compartment": "<oci-compartment-name-or-id>",
     "auth":
      {
       "iam":
        {
         "tenantId": "<tenancy-ocid>",
         "userId": "<user-ocid>",
         "fingerprint": "<fingerprint-for-the-signing-key>",
         "privateKeyFile": "<path-to-the-private-key>",
         "passphrase": "<passphrase-of-the-signing-key>"
         }
       }
    }
    {
     "endpoint": "http://myinstance.cloudsim.com:8080",
     "auth" : "Bearer<tenant-id>"
    }
  2. Open the Command Palette by pressing:
    • (Windows and Linux) Control + Shift + X
    • (macOS) Command + Shift + X
  3. From the Command Palette, select Oracle NoSQL: Connect via Config File.

    Tip:

    Enter oraclenosql in the Command Palette to display all of the Oracle NoSQL DB commands you can use.


    Connect Via Config File

  4. Browse to the location where the *.config file is stored and click Select.

Managing Tables Using Visual Studio Code Extension

Once you connect to your deployment using Oracle NoSQL Database Visual Studio (VS) Code extension, use the TABLE EXPLORER located on the left navigation to:
  • Explore your tables, columns, indexes, primary keys, and shard keys.
  • Create new tables.
  • Drop existing tables.
  • Execute SELECT SQL queries and DML statements on a table.

Oracle NoSQL DB TABLE EXPLORER

When you expand an active connection, Oracle NoSQL Database VS Code shows the tables in that deployment.
  • Click the table name to view its columns, indexes, primary key(s), and shard key(s). The column name displays along with its data type.
  • You can refresh the schema or table at any time to re-query your deployment and populate Oracle NoSQL Database Cloud Service with the most up-to-date data.
    • In the TABLE EXPLORER, locate the connection and click the Refresh icon to reload the schema. Alternatively, you can right-click the connection and select Refresh Schema.


      Oracle NoSQL DB VS Code Extension Refresh Schema

    • In the TABLE EXPLORER, locate the table name and click the Refresh icon to reload the table. Alternatively, you can right-click the table name and select Refresh Table.


      Oracle NoSQL DB VS Code Refresh Table

Executing SQL Queries and DML Statements for a Table

  1. Right-click the target table and select Browse Table.
  2. In the SQL window, specify the SELECT query in the text box and click
    Oracle NoSQL DB VS Code Execute Query

    . The query result is displayed in tabular format. You can adjust the number of rows per page. You can also execute DML statements to update, insert, and delete data from a table.
  3. To view individual cell data separately, click the table cell.

Dropping a Table

  1. Right-click the target table.
  2. Click Drop Table.
  3. Click Yes to drop the table.

Creating an Oracle NoSQL Database Cloud Service Table from Visual Studio Code

You can create the Oracle NoSQL Database Cloud Service table in two modes:
  • Simple DDL Input: You can use this mode to create the Oracle NoSQL Database Cloud Service table declaratively, that is, without writing a DDL statement.
  • Advanced DDL Input: You can use this mode to create the Oracle NoSQL Database Cloud Service table using a DDL statement.
  1. Hover over the Oracle NoSQL Database Cloud Service connection to add the new table.
  2. Click the Plus icon that appears.
  3. In the Create Table page, select Simple DDL Input.


    Create an Oracle NoSQL Database Cloud Service Table Using Simple DDL Input Mode

    Table 5-7 Create an Oracle NoSQL Database Cloud Service Table

    Field Description
    Read capacity (Read Units) Enter the number of read units. See Estimating Capacity to learn about read units.
    Write capacity (Write Units) Enter the number of write units. See Estimating Capacity to learn about write units.
    Disk storage (GB) Specify the disk space in gigabytes (GB) to be used by the table. See Estimating Capacity to learn about storage capacity.
    Table Name: Specify a table name that is unique within your tenancy.Table names must conform to Oracle NoSQL Database Cloud Service naming conventions. See Service Limits.
    Column Name Specify a column name for the primary key in your table. See Service Limits to learn about column naming requirements.
    Column Type Select the data type for your primary key column.
    Set as Shard Key Select this option to set this primary key column as shard key. Shard key is to distribute data across the Oracle NoSQL Database Cloud Service cluster for increased efficiency, and to position records that share the shard key locally for easy reference and access. Records that share the shard key are stored in the same physical location and can be accessed atomically and efficiently.
    Remove Click this button to delete an existing column.
    + Add Primary Key Column Click this button to add more columns while creating a composite (multi-column) primary key.
    Column Name Specify the column name. Ensure that you conform to column naming requirements described in Service Limits.
    Column Type Select the data type for your primary key column.
    Default Value (optional) Specify a default value for the column.

    Note:

    Default values can not be specified for binary and JSON data type columns.
    Not Null Select this option to specify that a column must always have a value.
    Remove Click this button to delete an existing column.
    + Add Column Click this button to add more columns.
    Unit Select the unit (Days or Hours) to use for TTL value for the rows in the table.
    Value Specify expiration duration for the rows in the table. After the number of days or hours, the rows expire automatically, and are no longer available. The default value is zero, indicating no expiration time.

    Note:

    Updating Table Time to Live (TTL) does not change the TTL value of any existing data in the table. The new TTL value applies only to those rows that are added to the table after this value is modified and to the rows for which no overriding row-specific value has been supplied.
  4. Click Show DDL to view the DDL statement formed based on the values entered in the fields in the Simple DDL input mode. This DDL statement gets executed when you click Create.
  5. Click Create.
  1. Hover over the Oracle NoSQL Database Cloud Service connection to add the new table.
  2. Click the Plus icon that appears.
  3. In the Create Table page, select Advanced DDL Input.


    Create an Oracle NoSQL Database Cloud Service Table Using Advanced DDL Input Mode

    Table 5-8 Create Table for Oracle NoSQL Database Cloud Service

    Field Description
    Read capacity (Read Units) Enter the number of read units. See Estimating Capacity to learn about read units.
    Write capacity (Write Units) Enter the number of write units. See Estimating Capacity to learn about write units.
    Disk storage (GB) Specify the disk space in gigabytes (GB) to be used by the table. See Estimating Capacity to learn about storage capacity.
    DDL Statement Specify the create table DDL statement. See SQL Reference for Oracle NoSQL Database.
  4. Click Create.

Removing a Connection

Oracle NoSQL Database Connector provides two methods to remove a connection from Visual Studio (VS) Code.

You can:
  • Remove a connection with the Command Palette, or
  • Remove a connection from the Oracle NoSQL DB view in the Activity Bar.

Note:

Removing a connection from Visual Studio Code deletes the persisted connection details from the current workspace.
  1. Expand the TABLE EXPLORER pane in the left navigation if it's collapsed.
  2. Right-click the connection you want to remove, then click Remove Connection.


Table Explorer

  1. Open the Command Palette by pressing:
    • (Windows and Linux) Control + Shift + P
    • (macOS) Command + Shift + P
  2. From the Command Palette, select OracleNoSQL: Remove Connection.

    Tip:

    Enter oraclenosql in the Command Palette to display all of the Oracle NoSQL DB commands you can use.


    Remove Connection