Feature Request: Oracle Data Source Integration

[mustafaneguib]

Labels: enhancement, data-source, backend, frontend
Milestone: Q1 2026
Estimated Effort: 5 weeks (120 hours)
Priority: High

---

📋 Overview

Add Oracle database support to the Data Research Analysis platform, enabling users to connect Oracle 11g+ instances as data sources alongside existing PostgreSQL, MySQL, and MariaDB support.

🎯 Objectives

• Enable users to connect to Oracle databases via the UI
• Support schema introspection and table/column metadata retrieval
• Implement Oracle-to-PostgreSQL data type mappings
• Support data model building from Oracle tables
• Enable cross-source joins with Oracle data sources
• Maintain encryption and security standards

📝 Problem Statement

Users currently cannot connect Oracle databases to the platform, limiting adoption in enterprise environments where Oracle is prevalent. This feature will:

1. Expand market reach to Oracle-heavy enterprises
2. Enable comprehensive data analysis across heterogeneous database environments
3. Maintain architectural consistency with existing SQL database drivers

🏗️ Technical Approach

Follow the established architectural patterns used for PostgreSQL, MySQL, and MariaDB:

• Singleton Processor Pattern: Business logic in processors
• TypeORM Integration: Use TypeORM's Oracle driver (oracledb)
• Automatic Encryption: Leverage existing connectionDetailsTransformer
• Factory Pattern: Extend DBDriver.getDriver() for Oracle
• SSR Compatibility: Frontend guards for browser APIs

✅ Implementation Checklist

Phase 1: Backend Core (Week 1)

Type System

• Add ORACLE = 'oracle' to EDataSourceType enum (backend/src/types/EDataSourceType.ts)
• Update DRADataSource model enum to include Oracle (backend/src/models/DRADataSource.ts)

Driver Implementation

• Create OracleDataSource.ts singleton factory class (backend/src/datasources/OracleDataSource.ts)
  • Implement getDataSource() method with Oracle-specific configuration
  • Handle SID vs. Service Name connection options
  • Configure TypeORM DataSource with Oracle type
• Create OracleDriver.ts implementing IDBDriver interface (backend/src/drivers/OracleDriver.ts)
  • Implement singleton pattern
  • Implement initialize(), getConcreteDriver(), query(), close()
  • Implement connectExternalDB() for external Oracle connections
  • Implement getTablesColumnDetails() using Oracle system catalog (ALL_TAB_COLUMNS)
  • Implement getTablesRelationships() using Oracle constraints (ALL_CONSTRAINTS)

Factory Updates

• Add Oracle case to DBDriver.getDriver() factory method (backend/src/drivers/DBDriver.ts)
• Import OracleDriver in DBDriver.ts

Phase 2: Service Layer (Week 1-2)

Utility Service

• Add 'oracle' case to getDataSourceType() method (backend/src/services/UtilityService.ts)
• Implement parseOracleDataType() helper method
• Implement mapOracleToPostgreSQL() type conversion method
• Add Oracle data type mappings (VARCHAR2→VARCHAR, NUMBER→NUMERIC, CLOB→TEXT, BLOB→BYTEA, etc.)
• Update convertDataTypeToPostgresDataType() to handle Oracle types

Data Source Processor

• Update getTablesFromDataSource() to include Oracle in conditional check
• Verify connectToDataSource() handles Oracle (should work generically)
• Verify addDataSource() handles Oracle (should work generically)
• Test Oracle schema introspection flow

Phase 3: Database Migration (Week 2)

• Generate migration: npm run migration:generate -- --name=AddOracleDataSourceType
• Review generated migration file
• Update migration to use ALTER TYPE ... ADD VALUE IF NOT EXISTS 'oracle'
• Test migration in development environment
• Document rollback procedure (manual enum removal steps)
• Run migration: npm run migration:run
• Verify enum updated: SELECT enum_range(NULL::dra_data_sources_data_type_enum);

Phase 4: Frontend Implementation (Week 2-3)

Connection Page

• Create oracle.vue connection page (frontend/pages/projects/[projectid]/data-sources/connect/oracle.vue)
  • Copy structure from mysql.vue
  • Update labels: "Connect Oracle Data Source"
  • Set default port to 1521
  • Update field labels: "SID or Service Name", "Schema / Owner"
  • Implement testConnection() with data_source_type: "oracle"
  • Implement connectDataSource() with proper API calls
  • Add SSR guards (import.meta.client) for browser APIs

UI Integration

• Add Oracle icon to assets (frontend/assets/images/oracle.png)
  • Obtain Oracle logo (verify trademark compliance)
  • Optimize to PNG, square aspect ratio, transparent background
  • Target size: <150KB
• Update data source display page (frontend/pages/projects/[projectid]/data-sources/[datasourceid]/index.vue)
  • Import oracleImage
  • Add to icon mapping: 'oracle': oracleImage
  • Update type check to include 'oracle'

Phase 5: Testing (Week 3)

Unit Tests

• Create OracleDriver.test.ts (backend/src/__tests__/drivers/OracleDriver.test.ts)
  • Test singleton pattern
  • Test connection initialization
  • Test connection failure handling
  • Test schema query generation (getTablesColumnDetails())
  • Test relationship query generation (getTablesRelationships())
• Create UtilityService Oracle tests (backend/src/__tests__/services/UtilityService.test.ts)
  • Test parseOracleDataType() with various types
  • Test mapOracleToPostgreSQL() conversions
  • Test VARCHAR2, NUMBER, CLOB, BLOB, DATE mappings

Integration Tests

• Add Oracle test case to data-source-operations.integration.test.ts
• Create data-source-oracle.integration.test.ts
  • Test Oracle connection establishment
  • Test encrypted credential storage/retrieval
  • Test table retrieval
  • Test schema introspection
• Update data-source-lifecycle.test.ts with Oracle test case

E2E Tests

• Add Oracle data source creation test to data-source-lifecycle.test.ts
• Test full workflow: connect → test → save → retrieve tables

Frontend Tests

• Add Oracle SSR test to ssr-compatibility.nuxt.test.ts
• Run npm run validate:ssr and verify no SSR breaks

Phase 6: Dependencies & Docker (Week 3-4)

NPM Dependencies

• Add "oracledb": "^6.3.0" to backend/package.json
• Run npm install in backend directory
• Verify oracledb installs successfully

Docker Configuration

• Update backend Dockerfile to install Oracle Instant Client
  • Add libaio1 system dependency
  • Download and install Oracle Instant Client Basic
  • Configure LD_LIBRARY_PATH environment variable
• Test Docker build: docker-compose build backend
• Optional: Add Oracle XE test database service to docker-compose.yml
• Test full Docker stack startup

Phase 7: Documentation (Week 4)

• Create oracle-data-source-guide.md user documentation
  • Connection parameters (host, port, SID, schema, credentials)
  • Oracle-specific considerations (schema vs. database, SID vs. Service Name)
  • Required Oracle permissions (GRANT SELECT on system catalogs)
  • Data type mappings reference table
  • Troubleshooting section (TNS errors, ORA-XXXXX codes)
• Update README.md to list Oracle in supported data sources
• Update comprehensive-architecture-documentation.md with Oracle driver details
• Add Oracle to PlantUML class diagrams
• Document environment variables (.env example)

Phase 8: Final Validation (Week 4-5)

• Code review with focus on:
  • Adherence to singleton processor pattern
  • TypeScript ES module imports (.js extensions)
  • SSR compatibility (no unguarded browser APIs)
  • Encryption implementation
  • SQL injection prevention in schema queries
• Security audit:
  • Verify credentials encrypted in database
  • Test SQL injection in schema name parameters
  • Validate connection string sanitization
  • Review error messages for credential leakage
• Performance testing:
  • Measure connection establishment time
  • Test with large schemas (1000+ tables)
  • Verify connection pooling configuration
  • Test concurrent connections
• Run full test suite:
  • cd backend && npm test (all tests pass)
  • cd backend && npm run test:coverage (>80% coverage)
  • cd frontend && npm test (all tests pass)
  • cd frontend && npm run validate:ssr (all checks pass)
• User acceptance testing:
  • Test with real Oracle 11g instance
  • Test with Oracle 12c instance
  • Test with Oracle 19c instance
  • Verify schema introspection accuracy
  • Test data model building
  • Test cross-source joins with Oracle tables

📊 Acceptance Criteria

Functional Requirements

• ✅ Users can connect to Oracle databases via the UI
• ✅ Connection test validates Oracle credentials successfully
• ✅ Tables and columns are correctly retrieved from Oracle schemas
• ✅ Foreign key relationships are detected and displayed
• ✅ Oracle data types are correctly mapped to PostgreSQL equivalents
• ✅ Credentials are encrypted in the database using existing transformer
• ✅ Data models can be built from Oracle data sources
• ✅ Cross-source joins work with Oracle tables
• ✅ Schema names are validated and sanitized

Non-Functional Requirements

• ✅ No breaking changes to existing PostgreSQL/MySQL/MariaDB functionality
• ✅ All existing tests pass (backend: 31+ tests, frontend: 31+ SSR tests)
• ✅ Code coverage remains >80% (backend)
• ✅ SSR compatibility maintained (frontend)
• ✅ Connection pooling properly configured (2-10 connections)
• ✅ Performance comparable to other SQL drivers (<5s connection time)
• ✅ Security standards maintained (encryption, injection prevention)
• ✅ Code follows singleton processor pattern consistently
• ✅ TypeScript ES modules with .js extensions
• ✅ Documentation is complete and accurate

🗂️ Files to Create/Modify

New Files (7)

1. backend/src/drivers/OracleDriver.ts (~150 lines)
2. backend/src/datasources/OracleDataSource.ts (~30 lines)
3. backend/src/migrations/YYYYMMDDHHMMSS-AddOracleDataSourceType.ts (~20 lines)
4. frontend/pages/projects/[projectid]/data-sources/connect/oracle.vue (~280 lines)
5. frontend/assets/images/oracle.png (image file)
6. documentation/oracle-data-source-guide.md (user guide)
7. backend/src/__tests__/drivers/OracleDriver.test.ts (~100 lines)

Modified Files (9)

1. backend/src/types/EDataSourceType.ts (add enum value)
2. backend/src/models/DRADataSource.ts (add enum value)
3. backend/src/drivers/DBDriver.ts (add Oracle case)
4. backend/src/services/UtilityService.ts (add type conversions)
5. backend/src/processors/DataSourceProcessor.ts (add Oracle to conditionals)
6. frontend/pages/projects/[projectid]/data-sources/[datasourceid]/index.vue (add icon)
7. backend/package.json (add oracledb dependency)
8. docker/backend/Dockerfile (add Oracle Instant Client)
9. README.md (update supported databases list)

🔍 Technical Details

Oracle Connection Parameters

{
  type: "oracle",
  host: "oracle.example.com",
  port: 1521,
  username: "app_user",
  password: "secure_password",
  sid: "ORCL",  // OR serviceName: "xepdb1"
  schema: "APP_USER",  // Oracle schema (typically username)
}

Oracle Data Type Mappings

Oracle Type	PostgreSQL Type	Notes
VARCHAR2(n)	VARCHAR(n)	Variable-length string
NUMBER	NUMERIC	Decimal number
NUMBER(*,0)	INTEGER	Whole number
CLOB	TEXT	Large text
BLOB	BYTEA	Binary large object
DATE	TIMESTAMP	Date with time
TIMESTAMP	TIMESTAMP	High-precision timestamp
RAW(n)	BYTEA	Binary data
BINARY_FLOAT	REAL	32-bit float
BINARY_DOUBLE	DOUBLE PRECISION	64-bit float

Oracle System Catalog Queries

Tables & Columns:

SELECT 
    tc.TABLE_NAME AS table_name,
    tc.COLUMN_NAME AS column_name,
    tc.DATA_TYPE AS data_type,
    tc.DATA_LENGTH AS character_maximum_length
FROM ALL_TAB_COLUMNS tc
JOIN ALL_TABLES t ON tc.TABLE_NAME = t.TABLE_NAME AND tc.OWNER = t.OWNER
WHERE tc.OWNER = 'SCHEMA_NAME'
ORDER BY tc.TABLE_NAME, tc.COLUMN_ID

Foreign Keys:

SELECT
    ac.CONSTRAINT_NAME AS constraint_name,
    ac.TABLE_NAME AS local_table_name,
    acc.COLUMN_NAME AS local_column_name,
    r_ac.TABLE_NAME AS foreign_table_name,
    r_acc.COLUMN_NAME AS foreign_column_name
FROM ALL_CONSTRAINTS ac
JOIN ALL_CONS_COLUMNS acc ON ac.CONSTRAINT_NAME = acc.CONSTRAINT_NAME
JOIN ALL_CONSTRAINTS r_ac ON ac.R_CONSTRAINT_NAME = r_ac.CONSTRAINT_NAME
JOIN ALL_CONS_COLUMNS r_acc ON r_ac.CONSTRAINT_NAME = r_acc.CONSTRAINT_NAME
WHERE ac.CONSTRAINT_TYPE = 'R' AND ac.OWNER = 'SCHEMA_NAME'

⚠️ Risks & Mitigation

High Risk: Oracle Instant Client Installation

• Risk: Complex system dependency, platform-specific binaries
• Mitigation: Document thoroughly, provide pre-configured Docker image
• Alternative: Use Oracle Cloud Free Tier with managed connections

Medium Risk: Enum Migration Irreversibility

• Risk: PostgreSQL enum values cannot be easily removed
• Mitigation: Test extensively in dev/staging, backup before production
• Rollback: Manual procedure documented (drop data, recreate enum)

Medium Risk: Type Conversion Edge Cases

• Risk: Oracle has unique types (XMLTYPE, LONG, ROWID) that may not map cleanly
• Mitigation: Comprehensive test coverage, handle unknown types as TEXT
• Monitoring: Log unmapped types for future enhancement

Low Risk: Connection Timeout

• Risk: Oracle connections may be slower than PostgreSQL/MySQL
• Mitigation: Increase timeout to 60s, implement retry logic
• Monitoring: Track connection times, alert on anomalies

📅 Timeline

Week	Phase	Deliverables
1	Backend Core + Migration	OracleDriver, OracleDataSource, enum migration
2	Service Layer + Frontend Start	UtilityService updates, oracle.vue page
3	Testing + Docker	Unit/integration tests, Docker config
4	Documentation + Validation	User guide, code review, security audit
5	Final Testing + Deployment	UAT, performance testing, production prep

Total Estimated Effort: 5 weeks (1 FTE senior developer)

🔗 References

• TypeORM Oracle Documentation
• Oracle node-oracledb Documentation
• Oracle Data Types Reference
• Existing Implementation: PostgresDriver.ts
• Existing Implementation: MySQLDriver.ts

🎨 Related Issues

• #XXX - MySQL Data Source Implementation (completed)
• #XXX - MariaDB Data Source Implementation (completed)
• #XXX - MongoDB Data Source Implementation (completed)
• #XXX - Cross-Source Join Feature (related)

👥 Assignee

TBD - Senior Backend Developer with Oracle experience preferred

🏷️ Definition of Done

• All implementation checklist items completed
• All acceptance criteria met
• Code reviewed and approved by lead developer
• All tests passing (unit, integration, E2E)
• Documentation complete and reviewed
• Security audit completed with no critical findings
• Performance benchmarks meet standards
• Deployed to staging for UAT
• UAT completed successfully
• Production deployment approved
• Monitoring and alerting configured
• Release notes published

---

Estimated Story Points: 21
Complexity: High
Business Value: High
Technical Debt: None (follows existing patterns)