Add Oracle 23ai storage adapter

[bilalmohib]

Title

Add Oracle 23ai storage adapter

---

New Feature / Enhancement Checklist

• Report security issues confidentially.
• Any contribution is under this license.
• Before posting search existing issues.

Current Limitation

Parse Server currently supports MongoDB and PostgreSQL as storage backends through official adapters. Organizations using Oracle Database (particularly Oracle 23ai) cannot use Parse Server without implementing a custom adapter or migrating to a supported database. This limits adoption for enterprises that have standardized on Oracle Database infrastructure.

Feature / Enhancement Description

Add a fully functional Oracle 23ai storage adapter for Parse Server, modeled after the existing Postgres adapter. The adapter should leverage Oracle 23ai's native JSON support and Oracle Spatial capabilities to provide feature parity with the Postgres adapter.

Key Components:

1. OracleClient.js - Connection pool management using oracledb package with pg-promise-like interface

2. OracleConfigParser.js - URI parsing for Oracle connection strings (service name/SID, pooling, SSL)

3. QueryFormatter.js - Translation layer from pg-promise query syntax to Oracle bind variables

4. OracleStorageAdapter.js - Main adapter implementing StorageAdapter interface with:

   • Dynamic schema management with _SCHEMA table synchronization
   • Full CRUD operations (create, read, update, delete)
   • Comprehensive query support (comparisons, arrays, pointers, relations, regex, sorting, pagination)
   • Index creation and management
   • Transaction support
   • Spatial data support (GeoPoint, Polygon) using Oracle Spatial (SDO_GEOMETRY)

5. SQL Functions - Oracle-specific PL/SQL functions for array and JSON operations:

   • array_add, array_add_unique, array_remove
   • array_contains, array_contains_all, array_contains_all_regex
   • json_object_set_key

Data Type Mappings:

• String → VARCHAR2(4000)
• Number → NUMBER
• Boolean → NUMBER(1) (0/1)
• Date → TIMESTAMP WITH TIME ZONE
• Object → JSON (Oracle 23ai native JSON)
• Array → JSON (Oracle 23ai native JSON)
• Pointer → VARCHAR2(120)
• File → VARCHAR2(4000)
• GeoPoint → SDO_GEOMETRY
• Polygon → SDO_GEOMETRY

Design Principles:

• Leverage Oracle 23ai's native JSON support (no JSONB workarounds)
• Use Oracle Spatial for geographic data types
• Performance optimizations (EXISTS queries instead of COUNT(*))
• Consistent error handling with Oracle error code mapping
• Compatibility with existing Parse Server query building logic

Example Use Case

1. An enterprise has standardized on Oracle Database 23ai for their infrastructure
2. They want to use Parse Server for their mobile/backend-as-a-service needs
3. They configure Parse Server with the Oracle adapter:

   const api = new ParseServer({
     databaseURI: 'oracle://user:password@localhost:1521/XE',
     // ... other config
   });

4. Parse Server automatically creates tables and manages schemas in Oracle
5. All Parse Server features work seamlessly with Oracle as the backend:
   • User authentication and management
   • Object storage with full query capabilities
   • Relations and pointers
   • File storage metadata
   • Geographic queries (GeoPoint, Polygon)
   • Array operations
   • Complex queries with sorting, pagination, and filtering

Alternatives / Workarounds

1. Migrate to PostgreSQL or MongoDB - Not feasible for organizations with existing Oracle infrastructure and investments
2. Custom Adapter Development - Requires significant development effort and maintenance burden
3. Database Abstraction Layer - Adds complexity and may not support all Parse Server features
4. Use Parse Server with Oracle via ODBC/JDBC - Not officially supported and may have compatibility issues

3rd Party References

• PostgreSQL Adapter - The existing Postgres adapter serves as the reference implementation: https://github.com/parse-community/parse-server/tree/alpha/src/Adapters/Storage/Postgres
• Oracle 23ai Documentation - Native JSON support: https://docs.oracle.com/en/database/oracle/oracle-database/23/adjsn/
• Oracle Spatial Documentation - SDO_GEOMETRY for geographic data: https://docs.oracle.com/en/database/oracle/oracle-database/23/spatl/
• oracledb Node.js Driver - Official Oracle database driver: https://github.com/oracle/node-oracledb

Similar Implementations:

• MongoDB adapter (official)
• PostgreSQL adapter (official)
• Various community adapters for other databases (MySQL, etc.)

[parse-github-assistant]

🚀 Thanks for opening this issue!

[mtrezza]

@bilalmohib We don't integrate new storage adapters into the Parse Server repository. In fact, we have an open issue to remove the Postgres storage adapter; see the issue details why. If you look at our community projects you will find an existing Oracle storage adapter for Parse Server which has been developed recently by Oracle in cooperation with Parse Platform. Feel free to offer your adapter as well, but to ensure it works should model your repository after the existing Oracle repository to test your adapter with the test suite of Parse Server. Another option would be to contact Oracle to contribute to their adapter and improve it.