express-oracle-session-store

Oracle-based session store for express-session. Provides high-performance Oracle session storage with two implementations:

• OracleMinimalStore: implements required methods (get, set, destroy).
• OracleFullStore: extends OracleMinimalStore with recommended (touch) and optional methods (all, clear, length).

Features

• High performance with multiple optimizations:
  • Shared connection pool across instances
  • Lazy pool initialization (on first DB access)
  • Prepared statements using Oracle's statement cache
  • CLOB → String conversion for session data
  • Consistent connection handling with autoCommit
• Complete API implementation following express-session Store interface
• Graceful shutdown support with closePool() method
• Flexible configuration for table schemas and pool settings

Installation

npm install express-oracle-session-store

This package requires express-session and oracledb as peer dependencies.

Quick Start

const express = require('express');
const session = require('express-session');
const { OracleMinimalStore, OracleFullStore } = require('express-oracle-session-store');

// Create the session store with your Oracle DB credentials
const store = new OracleFullStore({
  user: 'DB_USER',
  password: 'DB_PASS',
  connectString: 'HOST:PORT/SERVICE',
  table: 'app_sessions',       // default: 'sessions'
  idColumn: 'session_id',      // default: 'session_id'
  dataColumn: 'session_data',  // default: 'session_data'
  expiresColumn: 'expires_at'  // optional TTL column
});

// Setup express with the session middleware
const app = express();

app.use(session({
  secret: 'your_secret_key',
  store,
  resave: false,
  saveUninitialized: false,
  cookie: { maxAge: 1000 * 60 * 60 } // 1 hour
}));

// Use in your routes
app.get('/', (req, res) => {
  req.session.views = (req.session.views || 0) + 1;
  res.send(`Views: ${req.session.views}`);
});

// When shutting down your application
process.on('SIGTERM', async () => {
  await OracleFullStore.closePool();
  process.exit(0);
});

app.listen(3000, () => console.log('Server listening on port 3000'));

Database Schema

Create the sessions table in Oracle:

CREATE TABLE sessions (
  session_id   VARCHAR2(128) PRIMARY KEY,
  session_data CLOB,
  expires_at   TIMESTAMP
);

Optionally, add an index on the expires_at column for performance:

CREATE INDEX idx_sessions_expires ON sessions(expires_at);

Configuration Options

| Option | Type | Default | Description | |--------|------|---------|-------------| | user | String | required | Oracle DB username | | password | String | required | Oracle DB password | | connectString | String | required | Connection string (HOST:PORT/SERVICE) | | table | String | sessions | Table name for storing sessions | | idColumn | String | session_id | Column name for session IDs | | dataColumn | String | session_data | Column name for JSON session data | | expiresColumn | String | none | Column name for TTL (timestamp) used by touch() | | pool.min | Number | 1 | Minimum number of connections in pool | | pool.max | Number | 10 | Maximum number of connections in pool | | pool.increment | Number | 1 | How many connections to create at once when needed |

API Reference

OracleBaseStore

Base class with shared functionality:

• static async closePool() - Close all connections in the pool gracefully

OracleMinimalStore

Implements the required methods for express-session compatibility:

• get(sid, callback) - Get session by ID
• set(sid, session, callback) - Update or insert session data
• destroy(sid, callback) - Delete session by ID

OracleFullStore

Extends OracleMinimalStore with additional functionality:

• touch(sid, session, callback) - Update session expiry time (requires expiresColumn)
• all(callback) - Get all sessions
• clear(callback) - Delete all sessions (uses TRUNCATE for performance)
• length(callback) - Count all sessions (uses RESULT_CACHE hint for performance)

Usage Examples

Basic Usage with Minimal Store

const { OracleMinimalStore } = require('express-oracle-session-store');

const store = new OracleMinimalStore({
  user: 'system',
  password: 'oracle',
  connectString: 'localhost:1521/XE'
});

// Use with express-session
app.use(session({
  store,
  secret: 'keyboard cat',
  resave: false,
  saveUninitialized: false
}));

Full Store with Custom Schema

const { OracleFullStore } = require('express-oracle-session-store');

const store = new OracleFullStore({
  user: 'webapp',
  password: 'secret',
  connectString: 'oracledb.example.com:1521/PROD',
  table: 'web_sessions',
  idColumn: 'sid',
  dataColumn: 'data',
  expiresColumn: 'expiry',
  pool: {
    min: 2,
    max: 20,
    increment: 2
  }
});

Connection Pool Management

The module uses a static connection pool shared across all store instances. To properly close connections when shutting down your application:

// Graceful shutdown example
async function shutdown() {
  console.log('Closing Oracle connection pool...');
  await OracleFullStore.closePool();
  console.log('Connections closed, exiting');
  process.exit(0);
}

// Listen for termination signals
process.on('SIGTERM', shutdown);
process.on('SIGINT', shutdown);

Performance Optimizations

This module includes several optimizations for Oracle:

1. Lazy Pool Initialization: The connection pool is only created when first needed, preventing blocking during application startup.

2. Prepared Statements: SQL queries are pre-built for each store instance, allowing Oracle to use its statement cache efficiently.

3. Automatic CLOB Handling: Session data is automatically converted to/from CLOBs without requiring extra processing code.

4. RESULT_CACHE Hint: The length() method uses Oracle's result cache for faster repeated counts.

5. TRUNCATE for clear(): Uses TRUNCATE TABLE instead of DELETE for much faster clearing of large session tables.

6. Single Shared Pool: All store instances share a single connection pool per process for optimal resource usage.

Error Handling

Connection and operation errors are logged to the console and passed to callbacks as required by express-session. The module includes proper connection cleanup in finally blocks to prevent connection leaks.

Testing

To run the tests (requires Oracle database):

# Set environment variables for test database
export TEST_ORACLE_USER=system
export TEST_ORACLE_PASSWORD=oracle
export TEST_ORACLE_CONNECT_STRING=localhost:1521/XE

# Run tests
npm test

License

MIT © [Your Name]