Using Visual Studio Code Extension for Development

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.
  • Add new columns using form-based entry or supply DDL statements.
  • Drop Columns.
  • Create Indexes.
  • Drop Indexes.
  • 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.
  • Download the Query Result after running the SELECT query into a JSON file.
  • Download each row of the result obtained after running the SELECT query into a JSON file.

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 - 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 - 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 - 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.
  • Create Indexes.
  • Drop Indexes.
  • Add columns.
  • Drop Columns.
  • Insert data into table.
  • Execute SELECT SQL queries.

Explore tables, columns, indexes and keys

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

Perform DDL operations using Visual Studio Code

You can use Visual Studio Code to perform DDL operations.

Some of the DDL operations that can be performed from inside the Visual Studio Code plugin are:

CREATE TABLE

You can create the Oracle NoSQL Database table in two modes:
  • Simple DDL Input: You can use this mode to create the Oracle NoSQL Database table declaratively, that is, without writing a DDL statement.
  • Advanced DDL Input: You can use this mode to create the Oracle NoSQL Database table using a DDL statement.
  1. Hover over the Oracle NoSQL Database 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 Table Using Simple DDL Input Mode

    Table - Create an Oracle NoSQL Database Table

    Field Description
    Table Name: Specify a unique table name.
    Column Name Specify a column name for the primary key in your table.
    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 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.
    Column Type Select the data type for your 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.

DROP TABLE

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

CREATE INDEX

  • Locate the Table Explorer, and click the Refresh Schema to reload the schema.
  • Right click on the table where index need to be created. Choose Create Index.
  • Specify the name of the index and the columns to be part of the index.
  • Click Add Index.

DROP INDEX

  • Locate the Table Explorer, and click the Refresh Schema to reload the schema.
  • Click on the table where the index needs to be removed. The list of indexes are displayed below the column names.
  • Right click on the index to be dropped. Click Drop Index.
  • A confirmation window appears, click Ok to confirm the drop action.

ADD COLUMN

  • Locate the Table Explorer, and click the Refresh Schema to reload the schema.
  • Right click on the table where column needs to be added. Click Add columns.
  • Specify the name of the column and define the column with its properties - datatype, default value and whether it is nullable.
  • Click Add New Columns.

DROP COLUMN

  • Locate the Table Explorer, and click the Refresh Schema to reload the schema.
  • Expand the table where column needs to be removed.
  • Right click the column to be removed and choose Drop Column.
  • A confirmation window appears, click Ok to confirm the drop action.

Perform DML operations using Visual Studio Code

You can add data, modify existing data and query data from tables usingVisual Studio Code plugin.

Insert Data

  • Locate the Table Explorer, and click the Refresh Schema to reload the schema.
  • Right click on the table where a row needs to be inserted. Choose Insert Row.
  • In the Insert Row panel, enter the details for inserting a new row. You can INSERT a new ROW in two modes:
    • Simple Input : You can use this mode to insert the new row without writing a DML statement. Here a form based row fields entry is loaded, where you can enter the value of every field in the row.
    • Advanced JSON Input : You can use this mode to insert a new row into the table by supplying a JSON Object containing the column name and its corresponding value as key-value pairs.
  • Click Insert Row.

Modify Data - UPDATE ROW/DELETE ROW:

  • Locate the Table Explorer, and click the Refresh Schema to reload the schema.
  • Click on the table where data needs to be modified.
  • In the textbox on the right under SQL>, enter the SQL statement to fetch data from your table. Click > to run the query.
  • To view individual cell data separately, click the table cell.
  • To perform DML operations like Update and Delete Row, right-click on the particular row. Pick your option from the context-menu that appears.
    • Delete Row : A confirmation window appears, click Ok to delete the row.
    • Update Row : A separate HTML panel opens below the listed rows, containing the column names and its corresponding value in a form-based entry or provide the input as ON key-pair object. You can choose either of the two methods and supply new values.

      Note:

      In any row, PRIMARY KEY and GENERATED ALWAYS AS IDENTITY columns cannot be updated.

Executing SQL Queries for a Table

  • Locate the Table Explorer, and click the Refresh Schema to reload the schema.
  • Right click on the table and choose Browse Table.
  • In the textbox on the right under SQL>, enter the SELECT statement to fetch data from your table.
  • Click > to run the query. The corresponding data is retrieved from the table.
  • Right click on any row and click Download row in to JSON file. The single row gets downloaded into a JSON file.
  • Click Download Query Result to save the complete result of the SELECT statement as a JSON file.
  • Click Fetch All Records to retrieve all data from the table.

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