20 Advanced Features

This section outlines advanced capabilities of the Oracle Backend with Firebase APIs database service that go beyond basic document operations. These features enable developers to model complex data relationships, perform cross-collection queries, and enforce fine-grained access control using security rules.

Parent topic: Database

20.1 Join Collection

Join collections allow clients to define and query across multiple relational tables configured in the project. These joins are created and managed through the Oracle Backend with Firebase APIs Console and are exposed as logical collections that can be queried like any other document collection.

Parent topic: Advanced Features

20.1.1 Join Collection Setup and Prerequisites

A client can create a join query using the Console. Each join can further be used as a collection.

Prerequisites

  • The project must already have a relational database schema configured.

  • Each table involved in the join and used within the query must be part of the relational setup defined for the project in the Console.

  • Join definitions must be created using the Console UI.

How to Set Up

  • Define Join View in Console:

    • Navigate to the Join Collection setup section.

    • Specify the following input fields:

      • view_name: Name of the collection that will be used in the database operation (length >= 1 and <= 120)

      • table: Ordered list of tables involved, representing parent table name followed by child table names

      • columns: Fields to be used within the select clause. Each field includes:

        • col: column name

        • table: table name for which col is associated.

        • alias: alias for the field, used in the select clause

      • joins: Join type and array of join condition between each parent and child table

        • type:

          • INNER JOIN
          • JOIN
          • LEFT JOIN
          • RIGHT JOIN
          • FULL JOIN

        • parent_table: parent table name

        • child_table: child table name

        • conditions: This object represents conditions that will be concatenated in two round brackets ( condition )

          • logic: AND/OR, represents the type of operation

          • clauses: LHS and RHS represent the condition to join two tables

  • Publish Security Rule:

    • Each join must have a specific security rule to be published from the Console.
    • If a rule is not defined, all operations on the join collection will be rejected.

    • Define a rule for the join view path:

      {
  match /<view_name>/_docId {
  allow read: if request.auth is not null && 
                 resource.data.uuid = 'scott'
}

  • Verify access by ensuring that the rule is active, and test read access using the Console or SDK.

Parent topic: Join Collection

20.2 Collection Group Queries

A collection group allows you to query across all subcollections with the same name, regardless of where they exist in your document hierarchy. It’s different from a normal collection query, which only looks within a specific path. This is useful for querying deeply nested or distributed data structures.

Setup

  • Collection groups are created during index creation using the Console.

  • A dedicated security rule must be defined for each collection group.

Example 20-1 Rule for a Collection Group Named recipes: 

match /.*/recipes {
  allow read: if <expression>;
}

You can filter documents across all subcollections named recipes and query by field values or document identifiers.

Parent topic: Advanced Features

20.2.1 Collection Group Setup and Prerequisites

Prerequisites

  • Subcollections with the same name must exist across multiple parent documents.

  • A collection group index must be created using the Console.

How to Set Up

  • Navigate to the Indexing section.

  • Define a collection group index for the target subcollection name (For example, recipes).

  • Define the security rule. The following is an example:

    match /.*/recipes {
  allow read: if request.auth != null && resource.data.difficulty == "Easy";
}

  • Use the Console or SDK to run a query across the collection group and verify access.

Parent topic: Collection Group Queries

20.3 Standalone Duality View

A standalone duality view is an Oracle JSON relational duality view created in the project owner schema. It represents a relational hierarchy as one nested JSON document collection.

It differs from the Console's Relational-to-Collection Mapping. The Console mapping creates project-managed collections for the individual tables in a hierarchy. A standalone duality view defines the document shape directly and is accessed through the SDK duality-view API.

A standalone view is not automatically registered as a regular Oracle Backend with Firebase APIs collection. An application accesses the view with the SDK's duality-view collection API. See Managing Database Using CLI for the resource paths supported by the CLI.

Parent topic: Advanced Features

20.3.1 Create a Standalone Duality View

The root JSON object has two identifiers with different roles:

  • _id is the Oracle JSON relational duality-view document identifier. It maps to the root table's unique row identifier.
  • OID is the unique root-document identifier used by the current Oracle Backend with Firebase APIs duality-view API. SYS_MAKE_OID_FROM_PK derives it from the root primary key.

The employees array below is embedded in each department document. employeeNumber exposes the employee primary key for applications that need to identify an employee. For example, a JavaScript application creates the view reference with dualityViewCollection(db, 'department_dv'); document operations use the root department document's OID. The embedded employee array is part of that root document, so the SDK has no employee-level document reference to resolve. 

CREATE TABLE dept (
  deptno NUMBER(2) CONSTRAINT pk_dept PRIMARY KEY,
  dname  VARCHAR2(14),
  loc    VARCHAR2(13)
);

CREATE TABLE emp (
  empno    NUMBER(4) CONSTRAINT pk_emp PRIMARY KEY,
  ename    VARCHAR2(10),
  job      VARCHAR2(9),
  mgr      NUMBER(4),
  hiredate DATE,
  sal      NUMBER(7,2),
  comm     NUMBER(7,2),
  deptno   NUMBER(2) CONSTRAINT fk_deptno REFERENCES dept
);

CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW department_dv AS
SELECT JSON {
  '_id'            : d.deptno,
  'OID'            : SYS_MAKE_OID_FROM_PK(d.deptno),
  'departmentName' : d.dname,
  'location'       : d.loc,
  'employees'      : [
    SELECT JSON {
      'employeeNumber' : e.empno,
      'employeeName'   : e.ename,
      'job'            : e.job,
      'salary'         : e.sal
    }
    FROM emp e WITH INSERT UPDATE DELETE
    WHERE d.deptno = e.deptno
  ]
}
FROM dept d WITH INSERT UPDATE DELETE;

Security Rule

Publish a security rule for each operation that the application needs. _docId is the Oracle Backend with Firebase APIs document-path placeholder; it is separate from the JSON _id field. 

match /department_dv/_docId {
  allow read: if request.auth != null &&
              resource.data.departmentName == 'SALES';
}

The standalone duality-view API supports add, update, read, and delete operations. setDoc() is not supported for a duality view.

Parent topic: Standalone Duality View

20.4 Snapshot Reads

Snapshot reads allow clients to retrieve the state of a document or collection at a specific point in time. This is useful for:

  • Implementing audit trails

  • Supporting versioned data access

  • Performing time-based queries

Snapshot reads are supported by the underlying ORDS infrastructure and return complete document data for each read operation. The number of rows returned is determined by the document size, and pagination can be applied using limit clauses.

Parent topic: Advanced Features

20.4.1 Snapshot Reads Setup and Prerequisites

Prerequisites

  • Snapshot reads are supported by default through the ORDS infrastructure.

  • Pagination and limit clauses should be configured based on document size.

How to Use

  • Use limit, startAt, endAt, or startAfter clauses to paginate or filter based on time or document state.

  • Ensure that the security rules allow read access to the target documents.

Parent topic: Snapshot Reads

20.5 Managing Database Using CLI

Prerequisite

Ensure that the setup of the CLI for Oracle Backend with Firebase APIs is complete.

See Also:

Configure the CLI for detailed steps on how to set up the CLI for Oracle Backend with Firebase APIs

  • Get a Document: 
    fusabase database get <document path>
  • List Root Collections: 
    fusabase database list root
  • Read Query: 
    fusabase database query <collection path> fusabase database query --path=<config> fusabase database query --path=<query_config> --export=<filepath>
  • Add a Document: 
    fusabase database add --path=<config>
  • Update a Document: 
    fusabase database upd --path=<config>
  • Delete a Document: 
    fusabase database delete <document path> fusabase database delete
  • Index Management: 
    fusabase index create --path=<config> fusabase index list fusabase index drop <indexid>

Note:

The Oracle Backend with Firebase APIs CLI operates on project-managed collections and their published collection paths. A standalone duality view is accessed with the SDK's duality-view collection API. For an unregistered standalone view, a collection-only command such as fusabase database query /department_dv reports an invalid document or collection reference.

Parent topic: Advanced Features