Applications using the Oracle NoSQL Database use tables to store and access data. The Node.js SDK supports table and index creation and removal, reading, updating and deleting records, as well as queries. This guide provides an overview of these capabilities. For complete description of the APIs and available options, see the API reference.
Importing NoSQLClient class and other classes/types from the SDK is done differently depending on whether you are using TypeScript or JavaScript with ES6 modules, or if you are using JavaScript with CommonJS modules:
TypeScript or JavaScript with ES6 modules:
import { NoSQLClient } from 'oracle-nosqldb';
JavaScript with CommonJS modules:
const NoSQLClient = require('oracle-nosqldb').NoSQLClient;
We will use TypeScript/ES6 imports in the tutorial, but both types of imports are supported. For more information, see TypeScript Modules and Node.js ECMAScript Modules.
Class NoSQLClient represents the main access point to the service. To create instance of NoSQLClient you need to provide appropriate configuration information in the form of Config object. Alternatively, you may choose to store this information in a JSON configuration file and create NoSQLClient instance using the path (absolute or relative to the application's current directory) to that file.
Required configuration properties are different depending on what ServiceType is used by your application. Supported service types are Oracle NoSQL Cloud Service (CLOUD), Cloud Simulator (CLOUDSIM) and On-Premise Oracle NoSQL Database (KVSTORE). All service types require service endpoint or region and some require authentication/authorization information. Other properties are optional and default values will be used if not explicitly provided.
See Connecting an Application to Oracle NoSQL Database Cloud Service guide on how to configure and connect to the Oracle NoSQL Database Cloud Service as well as the Cloud Simulator.
See Connecting an Application to On-Premise Oracle NoSQL Database guide on how to configure and connect to the On-Premise Oracle NoSQL Database.
The first example below creates instance of NoSQLClient for the Cloud Service using configuration object literal. It also adds a default compartment and overrides some default timeout values in the configuration object:
import { NoSQLClient } from 'oracle-nosqldb';
let client = new NoSQLClient({
region: Region.US_ASHBURN_1,
timeout: 20000,
ddlTimeout: 40000,
compartment: 'mycompartment',
auth: {
iam: {
configFile: '~/myapp/.oci/config',
profileName: 'Jane'
}
}
});
The second example stores the same configuration in a JSON file config.json and uses it to create NoSQLClient instance:
config.json:
{
"region": "US_ASHBURN_1",
"timeout": 20000,
"ddlTimeout": 40000,
"compartment": "mycompartment",
"auth":
{
"iam":
{
"configFile": "~/myapp/.oci/config",
"profileName": "Jane"
}
}
}
Application code:
const NoSQLClient = require('oracle-nosqldb').NoSQLClient;
let client = new NoSQLClient('config.json');
Note that it may not be possible to store the configuration in a file if it has property values that cannot be represented as JSON types. In this case, use the object literal as in the first example above.
NoSQLClient instance is created synchronously, but most of the methods of NoSQLClient are asynchronous since network communication is required to access the service. Each of these methods returns Promise object that resolves with the result of corresponding operation or rejects with an error. You may use these methods with async/await or with Promise chains. The following sections show how to use some of the NoSQLClient methods with async/await.
Note that you may only need one NoSQLClient instance for your application, as it is safe to execute multiple concurrent operations on the same NoSQLClient instance. This would be equivalent to this class being thread-safe in other programming languages.
When you are done using NoSQLClient instance, you must call close method to release any resources it may hold. Failure to call this method may cause your application to hang on exit.
Most methods of NoSQLClient take an opt argument as an optional last argument. This is an options object with the format different for each operation (e.g. see TableDDLOpt, GetOpt, PutOpt, QueryOpt, etc). Each of these options will override corresponding option in Config, if exists, for the particular operation. If not specified, each option defaults to a corrensponding value in Config, if exists.
Learn how to create tables and indexes in the Oracle NoSQL Database.
Creating a table is the first step of developing your application. To create tables and execute other Data Definition Language (DDL) statements, such as creating, modifying and dropping tables as well as creating and dropping indexes, use method tableDDL.
Before creating a table, learn about:
Examples of DDL statements are:
/* Create a new table called users */
CREATE IF NOT EXISTS users (id INTEGER, name STRING, PRIMARY KEY (id));
/* Create a new table called users and with TTL value to of days */
CREATE IF NOT EXISTS users (id INTEGER, name STRING, PRIMARY KEY (id))
USING TTL 4 days;
/* Create a new index called nameIdx on the name field in the users table */
CREATE INDEX IF NOT EXISTS nameIdx ON users(name);
Table DDL statements are executed by tableDDL method. Like most other methods of NoSQLClient class, this method is asynchronous and it returns a Promise of TableResult. TableResult contains status of DDL operation such as its TableState, name, schema and its TableLimits. For more information, see TableResult. TableState is an enumeration that indicates the current state of the table.
As well as the statement to execute, tableDDL method takes opt optinal parameter in the form of TableDDLOpt. When you are creating a table, you must specify tableLimits as part of opt. TableLimits specifies maximum throughput and storage capacity for the table as the amount of read units, write units, and Gigabytes of storage.
Note that tableDDL method only launches the specified DDL operation in the underlying store and does not wait for its completion. The resulting TableResult will most likely have one of intermediate table states such as CREATING, DROPPING or UPDATING (the latter happens when table is in the process of being altered by ALTER TABLE statement, table limits are being changed or one of its indexes is being created or dropped).
When the underlying operation completes, the table state should change to ACTIVE or DROPPED (the latter if the DDL operation was DROP TABLE).
You may asynchronously wait for table DDL operation completion in one of the following ways:
import { NoSQLClient, TableState } from 'oracle-nosqldb';
.....
const client = new NoSQLClient('config.json');
async function createUsersTable() {
try {
const statement = 'CREATE TABLE IF NOT EXISTS users(id INTEGER, ' +
'name STRING, PRIMARY KEY(id))';
let result = await client.tableDDL(statement, {
tableLimits: {
readUnits: 20,
writeUnits: 10,
storageGB: 5
}
});
result = await client.forCompletion(result);
console.log('Table users created');
} catch(error) {
//handle errors
}
}
After the above call returns, result will reflect final state of the operation.
Alternatively, to use complete option, substitute the code in try-catch block above with the following:
const statement = 'CREATE TABLE IF NOT EXISTS users(id INTEGER, ' +
'name STRING, PRIMARY KEY(id))';
let result = await client.tableDDL(statement, {
tableLimits: {
readUnits: 20,
writeUnits: 10,
storageGB: 5
},
complete: true
});
console.log('Table users created');
You need not specify TableLimits for any DDL operation other than CREATE TABLE. You may also change table limits of the table after it has been created by calling setTableLimits method. This may also require waiting for the completion the operation in the same way as waiting for completion of operations initiated by tableDDL.
Add rows to your table.
When you store data in table rows, your application can easily retrieve, add to, or delete information from the table.
Method put is used to insert a single row into the table. It takes table name, row value and optional opt parameter in the form of PutOpt. This method can be used for unconditional and conditional puts to:
Each put method returns a Promise of PutResult containing information such as success status and resulting RowVersion.
Note that the property names in the provided row object should be the same as underlying table column names. See AnyRow.
For TypeScript users:
You may provide a row schema as the type parameter to put, putIfAbsent, putIfPresent, putIfVersion and other data-related methods.
This is strictly optional, but would allow TypeScript compiler to provide type hints for the returned PutResult, as well as type-check that the passed row object conforms to the specified schema. This is shown in the TypeScript example below.
To add rows to your table:
In JavaScript:
import { NoSQLClient } from 'oracle-nosqldb';
.....
const client = new NoSQLClient('config.json');
async function putRowsIntoUsersTable() {
const tableName = 'users';
try {
// Uncondintional put, should succeed
let result = await client.put(tableName, { id: 1, name: 'John' });
// Will fail since the row with the same primary key exists
result = await client.putIfAbsent(tableName, { id: 1, name: 'Jane' });
// Expected output: putIfAbsent failed
console.log('putIfAbsent ' + result.success ? 'succeeded' : 'failed');
// Will succeed because the row with the same primary key exists
res = await client.putIfPresent(tableName, { id: 1 , name: 'Jane' });
// Expected output: putIfAbsent succeeded
console.log('putIfPresent ' + result.success ?
'succeeded' : 'failed');
let version = result.version;
// Will succeed because the version matches existing row
result = await client.putIfVersion(tableName, { id: 1, name: 'Kim' },
version);
// Expected output: putIfVersion succeeded
console.log('putIfVersion ' + result.success ? 'succeeded' : 'failed');
// Will fail because the previous put has changed the row version, so
// the old version no longer matches.
result = await client.putIfVersion(tableName, { id: 1, name: 'June' },
version);
// Expected output: putIfVersion failed
console.log('putIfVersion ' + result.success ? 'succeeded' : 'failed');
} catch(error) {
//handle errors
}
}
In TypeScript:
import { NoSQLClient } from 'oracle-nosqldb';
.....
interface Person {
id: number;
name: string;
}
const client = new NoSQLClient('config.json');
async function putRowsIntoUsersTable() {
const tableName = 'users';
try {
// Uncondintional put, should succeed
// Type of result is PutResult<Person>
let result = await client.put<Person>(tableName,
{ id: 1, name: 'John' });
// Will fail since the row with the same primary key exists
result = await client.putIfAbsent<Person>(tableName,
{ id: 1, name: 'Jane' });
// Expected output: putIfAbsent failed
console.log('putIfAbsent ' + result.success ? 'succeeded' : 'failed');
// Will succeed because the row with the same primary key exists
res = await client.putIfPresent<Person>(tableName,
{ id: 1 , name: 'Jane' });
// Expected output: putIfAbsent succeeded
console.log('putIfPresent ' + result.success ?
'succeeded' : 'failed');
let version = result.version;
// Will succeed because the version matches existing row
result = await client.putIfVersion<Person>(tableName,
{ id: 1, name: 'Kim' }, version);
// Expected output: putIfVersion succeeded
console.log('putIfVersion ' + result.success ? 'succeeded' : 'failed');
// Will fail because the previous put has changed the row version, so
// the old version no longer matches.
result = await client.putIfVersion<Person>(tableName,
{ id: 1, name: 'June' }, version);
// Expected output: putIfVersion failed
console.log('putIfVersion ' + result.success ? 'succeeded' : 'failed');
} catch(error) {
//handle errors
}
}
Note that success results in false value only if conditional put operation fails due to condition not being satisfied (e.g. row exists for putIfAbsent, row doesn't exist for putIfPresent or version doesn't match for putIfVersion). If put operation fails for any other reason, the resulting Promise will reject with an error (which you can catch in async function). For example, this may happen if a column value supplied is of a wrong type, in which case the put will result in NoSQLArgumentError.
You can perform a sequence of put operations on a table that share the same shard key using putMany method. This sequence will be executed within the scope of single transaction, thus making this operation atomic. The result of this operation is a Promise of WriteMultipleResult. You can also use writeMany if the sequence includes both puts and deletes. For details, see the API Reference.
Using columns of type JSON allows more flexibility in the use of data as the data in the JSON column does not have predefined schema in the database. To put data into JSON column, provide either an object or a JSON string as the column value. Note that the field values inside JSON column must be of supported JSON types.
If using TypeScript, you can still specify row schema as described above, since it is used only on the client side by TypeScript compiler. The schema may be nested to in order describe expected shape of your JSON data.
Learn how to read data from your table.
You can read single rows using the get method. This method allows you to retrieve a record based on its primary key value. In order to read multiple rows in a single operation, see Use Queries, below.
You can set consistency of read operation using Consistency enumeration. By default all read operations are eventually consistent, using EVENTUAL. This type of read is less costly than those using absolute consistency, ABSOLUTE. This default consnstency for read operations can be set in the initial configuration used to create NoSQLClient instance using consistency property. You may also change it for a single read operation by setting consistency in the opt argument.
get method returns Promise of GetResult that contains the resulting row and its RowVersion. If the provided primary key does not exist in the table, the value of row property will be null.
Note that the property names in the provided primary key key object should be the same as underlying table column names. See AnyKey.
For TypeScript users:
You may provide a row schema as the type parameter to get and other data-related methods.
This is strictly optional, but would allow TypeScript compiler to provide type hints for the returned GetResult, as well as type-check the passed key, which is expected to contain any subset of fields from row schema having types allowed for primary key (see RowKey). This is shown in the TypeScript example below.
To use get method:
In JavaScript:
import { NoSQLClient, Consistency } from 'oracle-nosqldb';
.....
const client = new NoSQLClient('config.json');
async function getRowsFromUsersTable() {
const tableName = 'users';
try {
let result = await client.get(tableName, { id: 1 });
console.log('Got row: ' + result.row);
// Use absolute consistency
result = await client.get(tableName, 'users',
{ id: 1 }, { consistency: Consistency.ABSOLUTE });
console.log('Got row with absolute consistency: ' + result.row);
} catch(error) {
//handle errors
}
}
In TypeScript:
import { NoSQLClient, Consistency } from 'oracle-nosqldb';
.....
interface Person {
id: number;
name: string;
}
const client = new NoSQLClient('config.json');
async function getRowsFromUsersTable() {
const tableName = 'users';
try {
// result is of type GetResult<Person>
let result = await client.get<Person>(tableName, { id: 1 });
console.log('Got row: ' + result.row);
// Use absolute consistency
result = await client.get<Person>(tableName, 'users',
{ id: 1 }, { consistency: Consistency.ABSOLUTE });
console.log('Got row with absolute consistency: ' + result.row);
} catch(error) {
//handle errors
}
}
Learn about using queries in your application.
The Oracle NoSQL Database provides a rich query language to read and update data. See SQL For NoSQL Specification for a full description of the query language.
To execute a query use query method. This method returns returns Promise of QueryResult that contains an Array of resulting rows as well as continuation key.
The amount of data returned by the query is limited by the system default and could be further limited by setting maxReadKB property in the opt argument, which means that one invocation of query method may not return all available results. Likewise, the amount of data written by an update query is limited by the system default and can be further limited by setting maxWriteKB property in the opt argument, which means that one invocation of query may not finish all the updates or deletes specified by the query.
This situation is dealt with by using continuationKey property. Not-null continuation key means that more query results may be available. This means that queries should generally run in a loop, looping until continuation key becomes undefined. Note that it is possible for rows to be empty yet have non-null continuationKey, which means the query loop should continue. See query and QueryResult for details.
In order to receive all the results, call query in a loop. At each iteration, if non-null continuation key is received in QueryResult, set continuationKey property in the opt argument for the next iteration.
For TypeScript users:
You may provide a query result schema as the type parameter to query and queryIterable. Note that this may not be the same as table row schema (unless using SELECT * query), because the query may include projection, name aliases, aggregate values, etc.
This is strictly optional, but would allow TypeScript compiler to provide type hints for the returned rows in QueryResult. This is shown in the subsequent TypeScript examples below.
To use query method:
In JavaScript:
import { NoSQLClient } from 'oracle-nosqldb';
.....
const client = new NoSQLClient('config.json');
async function queryUsersTable() {
const opt = {};
try {
do {
const result = await client.query('SELECT * FROM users', opt);
for(let row of result.rows) {
console.log(row);
}
opt.continuationKey = result.continuationKey;
} while(opt.continuationKey);
} catch(error) {
//handle errors
}
}
In TypeScript:
import { NoSQLClient } from 'oracle-nosqldb';
.....
const client = new NoSQLClient('config.json');
interface Person {
id: number;
name: string;
}
async function queryUsersTable() {
const opt = {};
try {
do {
// result is of type QueryResult<Person>
const result = await client.query<Person>('SELECT * FROM users',
opt);
for(let row of result.rows) {
console.log(row);
}
opt.continuationKey = result.continuationKey;
} while(opt.continuationKey);
} catch(error) {
//handle errors
}
}
Another, more convenient way to iterate over query results is to use queryIterable API. This API returns an iterable object that you can iterate over with for-await-of loop. You do not need to manage continuation key if using this API. The following example is equivalent to the previous example:
In JavaScript:
import { NoSQLClient } from 'oracle-nosqldb';
.....
const client = new NoSQLClient('config.json');
async function queryUsersTable() {
const opt = {};
try {
for await(let result of client.queryIterable(
'SELECT * FROM users')) {
for(let row of result.rows) {
console.log(row);
}
}
} catch(error) {
//handle errors
}
}
In TypeScript:
import { NoSQLClient } from 'oracle-nosqldb';
.....
const client = new NoSQLClient('config.json');
interface Person {
id: number;
name: string;
}
async function queryUsersTable() {
const opt = {};
try {
// result is of type QueryResult<Person>
for await(let result of client.queryIterable<Person>(
'SELECT * FROM users')) {
for(let row of result.rows) {
console.log(row);
}
}
} catch(error) {
//handle errors
}
}
Note that you may also pass the same options as specified by QueryOpt to queryIterable same as to query, except continuationKey.
There are only few cases where you can make a single call to query without having to loop. These are the cases where the query can access at most one row and they include select, update and delete based on full primary key (that is where the where clause specifies equality based on complete primary key) as well as insert statement. In all other cases, the looping is required.
When using queries it is important to be aware of the following considerations:
Use prepare method to prepare the query. This method returns Promise of PreparedStatement object. Use set method to bind query variables. To run prepared query, pass PreparedStatement to the query or queryIterable instead of the statement string.
In JavaScript:
import { NoSQLClient } from 'oracle-nosqldb';
.....
const client = new NoSQLClient('config.json');
async function queryUsersTable() {
const statement = 'DECLARE $dept STRING; SELECT * FROM emp WHERE ' +
'dept = $dept';
try {
let prepStatement = await client.prepare(statement);
// Set value for $dept variable
prepStatement.set('$dept', 'Development');
for await(let result of client.queryIterable(prepStatement)) {
for(let row of result.rows) {
console.log(row);
}
}
// Set different value for $dept and re-execute the query
prepStatement.set('$dept', 'Marketing');
for await(let result of client.queryIterable(prepStatement)) {
for(let row of result.rows) {
console.log(row);
}
}
} catch(error) {
//handle errors
}
}
In TypeScript:
import { NoSQLClient } from 'oracle-nosqldb';
.....
interface Employee {
id: number;
firstName: string;
lastName: string;
dept: string;
}
const client = new NoSQLClient('config.json');
async function queryUsersTable() {
const statement = 'DECLARE $dept STRING; SELECT * FROM emp WHERE ' +
'dept = $dept';
try {
let prepStatement = await client.prepare(statement);
// Set value for $dept variable
prepStatement.set('$dept', 'Development');
for await(let result of client.queryIterable<Employee>(
prepStatement)) {
// result is of type QueryResult<Employee>
for(let row of result.rows) {
console.log(row);
}
}
// Set different value for $dept and re-execute the query
prepStatement.set('$dept', 'Marketing');
for await(let result of client.queryIterable<Employee>(
prepStatement)) {
// result is of type QueryResult<Employee>
for(let row of result.rows) {
console.log(row);
}
}
} catch(error) {
//handle errors
}
}
Learn how to delete rows from your table.
To delete a row, use delete method. Pass to it the table name and primary key of the row to delete. In addition, you can make delete operation conditional by specifying on a RowVersion of the row that was previously returned by get or put. You can pass it as matchVersion property of the opt argument: { matchVersion: my_version }. Alternatively you may use deleteIfVersion method.
delete and deleteIfVersion methods return Promise of DeleteResult, which is plain JavaScript object, containing success status of the operation. Inaddition, it may contain information about existing row if the delete operation failed due to version mismatch and returnExisting property was set to true in the opt argument.
Note that the property names in the provided primary key key object should be the same as underlying table column names. See AnyKey.
For TypeScript users:
You may provide a row schema as the type parameter to delete, deleteIfVersion and other data-related methods.
This is strictly optional, but would allow TypeScript compiler to provide type hints for the returned DeleteResult, as well as type-check the passed key, which is expected to contain any subset of fields from row schema having types allowed for primary key (see RowKey). This is shown in the TypeScript example below.
In JavaScript:
import { NoSQLClient } from 'oracle-nosqldb';
.....
const client = new NoSQLClient('config.json');
async function deleteRowsFromUsersTable() {
const tableName = 'users';
try {
let result = await client.put(tableName, { id: 1, name: 'John' });
// Unconditional delete, should succeed
result = await client.delete(tableName, { id: 1 });
// Expected output: delete succeeded
console.log('delete ' + result.success ? 'succeeded' : 'failed');
// Delete with non-existent primary key, will fail
result = await client.delete(tableName, { id: 2 });
// Expected output: delete failed
console.log('delete ' + result.success ? 'succeeded' : 'failed');
// Re-insert the row
result = await client.put(tableName, { id: 1, name: 'John' });
let version = result.version;
// Will succeed because the version matches existing row
result = await client.deleteIfVersion(tableName, { id: 1 }, version);
// Expected output: deleteIfVersion succeeded
console.log('deleteIfVersion ' + result.success ?
'succeeded' : 'failed');
// Re-insert the row
result = await client.put(tableName, { id: 1, name: 'John' });
// Will fail because the last put has changed the row version, so
// the old version no longer matches. The result will also contain
// existing row and its version because we specified returnExisting in
// the opt argument.
result = await client.deleteIfVersion(tableName, { id: 1 }, version,
{ returnExisting: true });
// Expected output: deleteIfVersion failed
console.log('deleteIfVersion ' + result.success ?
'succeeded' : 'failed');
// Expected output: { id: 1, name: 'John' }
console.log(result.existingRow);
} catch(error) {
//handle errors
}
}
In TypeScript:
import { NoSQLClient } from 'oracle-nosqldb';
.....
interface Person {
id: number;
name: string;
}
const client = new NoSQLClient('config.json');
async function deleteRowsFromUsersTable() {
const tableName = 'users';
try {
// putResult is of type PutResult<Person>
let putResult = await client.put<Person>(tableName,
{ id: 1, name: 'John' });
// Unconditional delete, should succeed
// deleteResult is of type DeleteResult<Person>
let deleteResult = await client.delete<Person>(tableName, { id: 1 });
// Expected output: delete succeeded
console.log('delete ' + deleteResult.success ?
'succeeded' : 'failed');
// Delete with non-existent primary key, will fail
deleteResult = await client.delete<Person>(tableName, { id: 2 });
// Expected output: delete failed
console.log('delete ' + deleteResult.success ?
'succeeded' : 'failed');
// Re-insert the row
putResult = await client.put<Person>(tableName,
{ id: 1, name: 'John' });
let version = putResult.version;
// Will succeed because the version matches existing row
deleteResult = await client.deleteIfVersion<Person>(tableName,
{ id: 1 }, version);
// Expected output: deleteIfVersion succeeded
console.log('deleteIfVersion ' + deleteResult.success ?
'succeeded' : 'failed');
// Re-insert the row
putResult = await client.put<Person>(tableName, { id: 1,
name: 'John' });
// Will fail because the last put has changed the row version, so
// the old version no longer matches. The result will also contain
// existing row and its version because we specified returnExisting in
// the opt argument.
deleteResult = await client.deleteIfVersion<Person>(tableName,
{ id: 1 }, version, { returnExisting: true });
// Expected output: deleteIfVersion failed
console.log('deleteIfVersion ' + deleteResult.success ?
'succeeded' : 'failed');
// Expected output: { id: 1, name: 'John' }
console.log(deleteResult.existingRow);
} catch(error) {
//handle errors
}
}
Note that similar to put operations, success results in false value only if trying to delete row with non-existent primary key or because of version mismatch when matching version was specified. Failure for any other reason will result in error.
You can delete multiple rows having the same shard key in a single atomic operation using deleteRange method. This method deletes set of rows based on partial primary key (which must be a shard key or its superset) and optional FieldRange which specifies a range of values of one of the other (not included into the partial key) primary key fields. For more information, see deleteRange and FieldRange.
Learn how to modify tables. You modify a table to:
Other than modifying table limits, you use tableDDL to modify a table by issuing a DDL statement against this table.
Examples of DDL statements to modify a table are:
/* Add a new field to the table */
ALTER TABLE users (ADD age INTEGER);
/* Drop an existing field from the table */
ALTER TABLE users (DROP age);
/* Modify the default TTL value*/
ALTER TABLE users USING TTL 4 days;
Table limits can be modified using setTableLimits method. It takes table name and new TableLimits as arguments and returns Promise of TableResult.
import { NoSQLClient, TableState } from 'oracle-nosqldb';
.....
const client = new NoSQLClient('config.json');
async function modifyUsersTableLimits() {
const tableName = 'users';
try {
let result = await client.setTableLimits(tableName, {
readUnits: 40,
writeUnits: 10,
storageGB: 5
});
// Wait for the operation completion using specified timeout and
// specified polling interval (delay)
await client.forCompletion(result, TableState.ACTIVE, {
timeout: 30000,
delay: 2000
});
console.log('Table limits modified');
} catch(error) {
//handle errors
}
}
As with table creation, when modifying a table, both tableDDL and setTableLimits may return before the actual operation is completed in the underlying store and the TableState upon return will most likely be intermediate state UPDATING. Before using the table again, you should wait for the operation completion in one of the ways described in Create Tables and Indexes section.
Note that forCompletion works by polling for the information about the operation at regular intervals. You may customize the wait timeout (see timeout) and the polling interval (see delay) by setting them in the opt argument as shown above (otherwise applicable default values will be used).
Learn how to delete a table or index that you have created in the Oracle NoSQL Database.
To drop a table or index, use the DROP TABLE or DROP INDEX DDL statement, for example:
/* Drop the table named users (implicitly drops any indexes on that table) */
DROP TABLE users;
/*
* Drop the index called nameIndex on the table users. Don't fail if the
* index doesn't exist
*/
DROP INDEX IF EXISTS nameIndex ON users;
These statements are executed by tableDDL method. As with table creation and modification, you may wait for the completion of the operation in the underlying store in one of the ways described in Create Tables and Indexes section.
import { NoSQLClient, TableState } from 'oracle-nosqldb';
.....
const client = new NoSQLClient('config.json');
async function dropNameIndexUsersTable() {
try {
let result = await client.tableDDL('DROP INDEX nameIndex ON users');
// Before using the table again, wait for the operation completion
// (when the table state changes from UPDATING to ACTIVE)
await client.forCompletion(result);
console.log('Index dropped');
} catch(error) {
//handle errors
}
}
async function dropTableUsers() {
try {
// Here we are waiting until the drop table operation is completed
// in the underlying store
let result = await client.tableDDL('DROP TABLE users', {
completion: true
});
console.log('Table dropped');
} catch(error) {
//handle errors
}
}
Asynchronous methods of NoSQLClient return Promise as a result and if an error occurs it results in the Promise rejection with that error. This error can be processed with .then and .catch statements of the promise chain as shown:
client.get('users', { id: 1})
.then(result => {
//process get result
})
.catch(error => {
//handle errors
});
or if using async function with async/await then regular try/catch statement may be used as shown in previous examples.
For synchronous methods such as NoSQLClient constructor errors are thrown as exceptions and can be processed using try/catch.
All errors used by the SDK are instances of NoSQLError or one of its subclasses. In addition to the error message, each error has errorCode property set to one of standard error codes defined by the ErrorCode enumeration. errorCode may be useful to execute conditional logic depending on the nature of the error.
For some error codes, specific subclasses of NoSQLError are defined, such as NoSQLArgumentError, NoSQLProtocolError, NoSQLTimeoutError, etc. NoSQLAuthorizationError may have one of several error codes depending on the cause of authorization failure.
In addition, errors may have cause property set to the underlying error that caused the current error. Note that the cause is optional and may be an instance of an error that is not part of the SDK.
See API reference for ErrorCode, NoSQLError and its subclasses for details.
In addition, error codes are split into 2 broad categories:
You can determine if the NoSQLError is retryable by checking retryable property. Its value is set to true for retryable errors and is false or undefined for non-retryable errors.
The driver will automatically retry operations on a retryable error. Retry handler determines:
RetryHandler is an interface with with 2 properties:
The driver is configured with default retry handler that will retry retryable errors depending on the operation type and whether number of retries reached preconfigured maximum. It will also use exponential backoff delay to wait between retries starting with preconfigured base delay. Maximum number of retries, base delay and other properties may be customized in RetryConfig object which is part of Config used to create NoSQLClient instance.
Alternatively, instead of using default RetryHandler you may choose to create your own RetryHandler and set it as handler property to use custom retry logic.
See documentation for RetryConfig and RetryHandler for details.
This section is relevant only for the Oracle NoSQL Database Cloud Service (CLOUD and CLOUDSIM) and not for the on-premise NoSQL Database (KVSTORE).
Programming in a resource-limited environment can be challenging. Tables have user-specified throughput limits and if an application exceeds those limits it may be throttled, which means an operation may fail with one of the throttle errors such as READ_LIMIT_EXCEEDED or WRITE_LIMIT_EXCEEDED. This is most common using queries, which can read a lot of data, using up capacity very quickly. It can also happen for get and put operations that run in a tight loop.
Even though throttling errors will be retried and using custom RetryHandler may allow more direct control over retries, an application should not rely on retries to handle throttling as this will result in poor performance and inability to use all of the throughput available for the table.
The better approach would be to avoid throttling entirely by rate-limiting your application. In this context rate-limiting means keeping operation rates under the limits for the table.
When you run your operations in a loop, rate-limiting may be as simple as adding constant delay between your operations in the loop (the delay, like operations themselves, should be done asynchronously). This applies to single row operations such as get and put, as well as to reading query results in a loop using continuation key, or calling deleteRange over a range of rows in a loop also using continuation key.
This approach may be improved by computing the delay based on how much throughput has been consumed by an operation. All data-related operation results such as GetResult, PutResult, DeleteResult, MultiDeleteResult, WriteMultipleResult, PreparedStatement and QueryResult include consumedCapacity property. ConsumedCapacity tells you how many write and read units, as well as write and read KB has been consumed by an operation. You may use this information to keep the throughput within the table limits.
For queries, another option would be to reduce the amount of data read in a single query call by setting maxReadKB. The same can be done for deleteRange operation by setting maxWriteKB.
Plain JavaScript objects (object literals) are used as rows for put operations and as primary keys for get and delete operations. The property names of these objects must match corresponding column names of the table. See AnyRow and AnyKey for more details.
In addition, the property values of rows and keys must be compatible with the underlying database types for corresponding columns. This means that there are mappings between Oracle NoSQL database types and JavaScript and Node.js types used by the SDK.
For details on Oracle NoSQL database types, see Supported Data Types.
For example, if the table was created using the following statement:
CREATE TABLE mytable(id INTEGER, name STRING, created TIMESTAMP,
address RECORD(street STRING, city STRING, zip INTEGER), PRIMARY KEY(id))
you may insert the following record using put:
let res = await client.put('mytable', {
id: 1,
name: 'myname',
created: new Date(),
address: {
street: '14 My Street',
city: 'Hometown',
zip: 12345
}
});
The mappings between JavaScript/Node.js types and the database types are described in detail in FieldValue.
Note that for some database types more than one JavaScript/Node.js type may be used on input. In general the system is permissive in terms of valid conversions among types and that any lossless conversion is allowed. For values returned by the driver (such as GetResult or QueryResult) there is a definite JavaScript/Node.js type for each database type. FieldValue describes data type mappings in both directions.
For TypeScript users:
All data-related methods such as get, put, delete, deleteRange, writeMany, query and their variants take an optional type parameter TRow that specifies table row schema (or query result schema for query operations). This allows typescript compiler to check that the input provided conforms to the schema and will also returned a typed version of the corresponding operation result. In addition, it will check that primary key fileds belong to the schema and of types supported for primary keys (see RowKey):
import { NoSQLClient, Consistency } from 'oracle-nosqldb';
.....
interface Person {
id: number;
name: string;
friends?: number[];
}
const client = new NoSQLClient('config.json');
async function getRow() {
const tableName = 'users';
try {
// result is of type GetResult<Person>
let result = await client.get<Person>(tableName, { id: 1 });
// @ts-expect-error result.row is possibly null
console.log(res.row.id); // Error
if (result.row) {
console.log(result.row.id); // OK
console.log(result.row.name); // OK
// @ts-expect-error result.row.friends is possibly undefined
console.log(result.row.friends[0]); // Error
if (result.row.friends && result.row.friends.length) {
console.log(result.row.friends[0]); // OK
}
// @ts-expect-error No property "address" in Person
console.log(result.row.address); // Error
// @ts-expect-error Wrong type for result.row.name
const n: number = result.row.name; // Error
// @ts-expect-error result.row is read-only
result.row = null; // Error
}
// @ts-expect-error Property "id2" does not exist in Person
result = await client.get<Person>(tableName, { id2: 1 }); // Error
result = await client.get<Person>(tableName,
// @ts-expect-error Unsupported type for primary key (array)
{ id: 1, friends: [ 1 ] }); // Error
} catch(error) {
//handle errors
}
}
Using TRow type parameter is optional. If not using TRow, the type of table rows, keys and query results is assumed to be any object with fields of type any. See AnyRow and AnyKey for more details.
If you are using Node.js SDK with On-Premise Oracle NoSQL Database, you may perform administrative operations on the store using adminDDL method. These are operations that don't affect a specific table. Examples of Data Definition Language (DDL) statements used with adminDDL include:
adminDDL method is similar to tableDDL method and returns a Promise of AdminResult which contains current operation state (which is either COMPLETE or IN_PROGRESS), operation id and operation output if any available.
Like for tableDDL method, AdminResult returned by adminDDL does not imply operation completion in the underlying store.
Similarly, you may asynchronously wait for admin DDL operation completion in one of the following ways:
Because some of admin DDL statements may include passwords, you may pass them as Buffer containing UTF-8 encoded statement string so that it can be erased afterwards to avoid keeping sensitive information in memory.
import { NoSQLClient, AdminState } from 'oracle-nosqldb';
.....
const client = new NoSQLClient('config.json');
async function createUser(userName, password) {
// password argument above is a Buffer
const statement = Buffer.concat(
Buffer.from(`CREATE USER ${userName} IDENTIFIED BY `),
password);
try {
let result = await client.adminDDL(statement);
await client.forCompletion(result);
console.log('User created');
} catch(error) {
// handle errors
} finally {
statement.fill(0); //erase the statement containing password
}
}
async function createNamespace(namespaceName) {
try {
// asynchronously wait for operation completion
const result = await client.adminDDL(
`CREATE NAMESPACE ${namespaceName}`, {
complete: true
});
console.log('Namespace created');
} catch(error) {
// handle errors
}
}
In addition, there are methods such as listNamespaces, listUsers and listRoles that return namespaces, users and roles, respectively, present in the store. These methods get this information by executing SHOW DDL commands (such as SHOW AS JSON NAMESPACES) via adminDDL and parsing the JSON output of the command which is returned as output.
Generated using TypeDoc