How to Integrate Replit with Oracle Database

1# replit.nix — Add libaio for Oracle Instant Client compatibility
2{ pkgs }: {
3  deps = [
4    pkgs.python312
5    pkgs.python312Packages.pip
6    pkgs.libaio         # Required by Oracle Instant Client
7    pkgs.libnsl         # Required for Oracle on Linux
8  ];
9
10  # Set LD_LIBRARY_PATH if you upload Oracle Instant Client manually
11  # env = {
12  #   LD_LIBRARY_PATH = "/home/runner/${REPL_SLUG}/instantclient_21_13";
13  # };
14}

1# Verify Oracle secrets are present at startup
2import os
3
4required = ['ORACLE_USERNAME', 'ORACLE_PASSWORD', 'ORACLE_HOST', 'ORACLE_PORT', 'ORACLE_SERVICE_NAME']
5for key in required:
6    val = os.environ.get(key)
7    if not val:
8        print(f'MISSING: {key} — add it in Replit Secrets (lock icon 🔒)')
9    else:
10        masked = val if key != 'ORACLE_PASSWORD' else '*' * len(val)
11        print(f'OK: {key} = {masked}')

1# oracle_server.py — Oracle Database connection for Replit (Python)
2import os
3import oracledb  # python-oracledb package
4from flask import Flask, jsonify, request as flask_request
5
6# Connection details from Replit Secrets
7USERNAME = os.environ['ORACLE_USERNAME']
8PASSWORD = os.environ['ORACLE_PASSWORD']
9HOST = os.environ['ORACLE_HOST']
10PORT = os.environ.get('ORACLE_PORT', '1521')
11SERVICE = os.environ['ORACLE_SERVICE_NAME']
12
13# Build DSN string
14DSN = f'{HOST}:{PORT}/{SERVICE}'
15
16# Optional: Enable thick mode with local Instant Client
17# Uncomment and set path if you have Instant Client uploaded to your Repl:
18# oracledb.init_oracle_client(lib_dir='/home/runner/YOUR_REPL_NAME/instantclient_21_13')
19
20# Create connection pool at startup
21# min/max connections — adjust based on your query load
22pool = oracledb.create_pool(
23    user=USERNAME,
24    password=PASSWORD,
25    dsn=DSN,
26    min=2,
27    max=10,
28    increment=1
29)
30
31app = Flask(__name__)
32
33@app.route('/health')
34def health():
35    try:
36        with pool.acquire() as conn:
37            cursor = conn.cursor()
38            cursor.execute('SELECT SYSDATE FROM DUAL')
39            result = cursor.fetchone()
40        return jsonify({'status': 'connected', 'server_time': str(result[0])})
41    except Exception as e:
42        return jsonify({'status': 'error', 'error': str(e)}), 500
43
44@app.route('/api/query', methods=['POST'])
45def run_query():
46    body = flask_request.get_json()
47    sql = body.get('sql')
48    params = body.get('params', {})
49    
50    if not sql:
51        return jsonify({'error': 'sql field required'}), 400
52    
53    # IMPORTANT: Only allow parameterized queries — never string-concatenate user input
54    try:
55        with pool.acquire() as conn:
56            cursor = conn.cursor()
57            cursor.execute(sql, params)
58            
59            if cursor.description:
60                columns = [col[0] for col in cursor.description]
61                rows = cursor.fetchmany(1000)  # Limit rows returned
62                results = [dict(zip(columns, row)) for row in rows]
63                return jsonify({'results': results, 'count': len(results)})
64            else:
65                conn.commit()
66                return jsonify({'rowcount': cursor.rowcount})
67    except Exception as e:
68        return jsonify({'error': str(e)}), 500
69
70if __name__ == '__main__':
71    app.run(host='0.0.0.0', port=3000)

1// oracle_server.js — Oracle Database connection for Replit (Node.js)
2const oracledb = require('oracledb');
3const express = require('express');
4
5const HOST = process.env.ORACLE_HOST;
6const PORT = process.env.ORACLE_PORT || '1521';
7const SERVICE = process.env.ORACLE_SERVICE_NAME;
8const USERNAME = process.env.ORACLE_USERNAME;
9const PASSWORD = process.env.ORACLE_PASSWORD;
10
11if (!HOST || !SERVICE || !USERNAME || !PASSWORD) {
12  throw new Error('Missing Oracle credentials in Replit Secrets (lock icon 🔒)');
13}
14
15// Node.js oracledb thin mode (no Instant Client required for basic usage)
16// For thick mode with Instant Client:
17// oracledb.initOracleClient({ libDir: '/home/runner/YOUR_REPL_NAME/instantclient_21_13' });
18
19// Set result format to objects (key-value pairs instead of arrays)
20oracledb.outFormat = oracledb.OUT_FORMAT_OBJECT;
21
22let pool;
23
24async function initPool() {
25  pool = await oracledb.createPool({
26    user: USERNAME,
27    password: PASSWORD,
28    connectString: `${HOST}:${PORT}/${SERVICE}`,
29    poolMin: 2,
30    poolMax: 10,
31    poolIncrement: 1
32  });
33  console.log('Oracle connection pool created');
34}
35
36const app = express();
37app.use(express.json());
38
39app.get('/health', async (req, res) => {
40  let conn;
41  try {
42    conn = await pool.getConnection();
43    const result = await conn.execute('SELECT SYSDATE AS server_time FROM DUAL');
44    res.json({ status: 'connected', server_time: result.rows[0].SERVER_TIME });
45  } catch (err) {
46    res.status(500).json({ status: 'error', error: err.message });
47  } finally {
48    if (conn) await conn.close();
49  }
50});
51
52app.post('/api/query', async (req, res) => {
53  const { sql, params = {} } = req.body;
54  if (!sql) return res.status(400).json({ error: 'sql field required' });
55  
56  let conn;
57  try {
58    conn = await pool.getConnection();
59    const result = await conn.execute(sql, params, { maxRows: 1000 });
60    res.json({ results: result.rows, rowsAffected: result.rowsAffected });
61  } catch (err) {
62    res.status(500).json({ error: err.message });
63  } finally {
64    if (conn) await conn.close();
65  }
66});
67
68// Initialize pool then start server
69initPool().then(() => {
70  app.listen(3000, '0.0.0.0', () => console.log('Oracle Node.js server running on port 3000'));
71}).catch(err => {
72  console.error('Failed to create Oracle pool:', err);
73  process.exit(1);
74});