RAIDB - Production-Ready PostgreSQL-Compatible Database Engine

A complete, from-scratch database engine that provides PostgreSQL compatibility for both embedded and server deployments. Built for performance, reliability, and offline-first applications.

🚀 Features

Core Database Engine

• ✅ Full PostgreSQL SQL Compatibility - Complete SQL parsing and execution
• ✅ ACID Transactions - Multi-version concurrency control (MVCC) with isolation
• ✅ Advanced Schema Management - ALTER TABLE, constraints, foreign keys with referential actions
• ✅ Comprehensive Data Types - All PostgreSQL data types including JSONB, arrays, timestamps
• ✅ Query Optimization - Cost-based query planner with JOIN optimization
• ✅ Advanced Indexing - B-tree, hash, partial, and composite indexes
• ✅ Functions & Triggers - User-defined functions and trigger system
• ✅ Views & Subqueries - Complex query support with materialized views

Production Features

• ✅ PostgreSQL Wire Protocol - Full pg client library compatibility
• ✅ Clustering & Replication - Multi-node clustering with automatic failover
• ✅ Connection Pooling - Enterprise-grade connection management
• ✅ Schema Migrations - Version-controlled database migrations
• ✅ Encrypted Storage - AES-256-GCM encrypted file-based storage
• ✅ Real-time Sync - WebSocket-based synchronization between instances
• ✅ Performance Monitoring - Built-in metrics and query performance tracking

📦 Installation

npm install @rai/raidb

🏃 Quick Start

Embedded Mode (In-Process)

import { RAIDB } from '@rai/raidb';

// Initialize embedded database
const db = new RAIDB({
  dataPath: './my-app-data',
  encryptionKey: 'your-32-char-encryption-key-here',
  performance: {
    cacheSize: '256MB',
    walMode: true
  }
});

await db.initialize();

// Use standard PostgreSQL syntax
await db.query(`
  CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email TEXT UNIQUE NOT NULL,
    name TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
  )
`);

// Insert data with parameters
const result = await db.query(
  'INSERT INTO users (email, name) VALUES ($1, $2) RETURNING *',
  ['[email protected]', 'John Doe']
);

// Query with JOINs and complex operations
const users = await db.query(`
  SELECT u.*, COUNT(o.id) as order_count 
  FROM users u 
  LEFT JOIN orders o ON u.id = o.user_id 
  WHERE u.created_at > $1
  GROUP BY u.id
`, [new Date('2024-01-01')]);

Server Mode (PostgreSQL Compatible)

import { RAIDBServer } from '@rai/raidb/server';

// Start RAIDB as a PostgreSQL-compatible server
const server = new RAIDBServer({
  server: {
    host: '0.0.0.0',
    port: 5433,
    maxConnections: 100,
    workerThreads: 4
  },
  dataPath: './server-data',
  encryptionKey: 'your-encryption-key',
  authentication: {
    method: 'password',
    users: [
      { username: 'admin', password: 'secure-password', databases: ['myapp'] }
    ]
  },
  cluster: {
    enabled: true,
    nodes: [
      { id: 'node1', host: 'localhost', port: 5433, role: 'primary' },
      { id: 'node2', host: 'localhost', port: 5434, role: 'replica' }
    ]
  }
});

await server.start();
console.log('RAIDB Server running on port 5433');

// Now connect with any PostgreSQL client
// psql -h localhost -p 5433 -U admin -d myapp

Connect with Standard PostgreSQL Clients

// Using node-postgres (pg)
import { Client } from 'pg';

const client = new Client({
  host: 'localhost',
  port: 5433,
  user: 'admin',
  password: 'secure-password',
  database: 'myapp'
});

await client.connect();
const result = await client.query('SELECT * FROM users');
await client.end();

# Using psycopg2 (Python)
import psycopg2

conn = psycopg2.connect(
    host="localhost",
    port=5433,
    user="admin",
    password="secure-password",
    database="myapp"
)

cur = conn.cursor()
cur.execute("SELECT * FROM users")
rows = cur.fetchall()
conn.close()

🗄️ Schema Management & Migrations

Creating Migrations

import { MigrationManager } from '@rai/raidb/migrations';

const migrationManager = new MigrationManager({
  database: db, // Your RAIDB instance
  migrationsPath: './migrations'
});

// Create a new migration file
await migrationManager.createMigration('add-user-preferences', {
  up: `
    CREATE TABLE user_preferences (
      id SERIAL PRIMARY KEY,
      user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
      key TEXT NOT NULL,
      value JSONB,
      created_at TIMESTAMP DEFAULT NOW(),
      UNIQUE(user_id, key)
    );
    
    CREATE INDEX idx_user_preferences_user_id ON user_preferences(user_id);
    CREATE INDEX idx_user_preferences_key ON user_preferences USING gin(key);
  `,
  down: `
    DROP TABLE user_preferences;
  `
});

Running Migrations

// Apply all pending migrations
await migrationManager.migrate();

// Rollback last migration
await migrationManager.rollback();

// Get migration status
const status = await migrationManager.getStatus();
console.log(`Applied: ${status.applied.length}, Pending: ${status.pending.length}`);

Advanced Schema Operations

// ALTER TABLE with foreign key referential actions
await db.query(`
  ALTER TABLE orders 
  ADD CONSTRAINT fk_orders_user_id 
  FOREIGN KEY (user_id) REFERENCES users(id) 
  ON DELETE CASCADE 
  ON UPDATE RESTRICT
`);

// Add computed columns and constraints
await db.query(`
  ALTER TABLE products 
  ADD COLUMN price_with_tax DECIMAL GENERATED ALWAYS AS (price * 1.08) STORED,
  ADD CONSTRAINT check_positive_price CHECK (price > 0)
`);

// Create materialized views for performance
await db.query(`
  CREATE MATERIALIZED VIEW user_order_summary AS
  SELECT 
    u.id, 
    u.name,
    COUNT(o.id) as total_orders,
    SUM(o.total) as total_spent
  FROM users u
  LEFT JOIN orders o ON u.id = o.user_id
  GROUP BY u.id, u.name
`);

🔄 Synchronization & Replication

Real-time Sync Between Instances

import { SyncManager } from '@rai/raidb/sync';

// Configure synchronization
const syncManager = new SyncManager({
  instanceId: 'mobile-app-1',
  instanceName: 'Mobile App Instance',
  instanceType: 'mobile',
  endpoint: {
    type: 'websocket',
    host: 'sync.myapp.com',
    port: 8080,
    secure: true
  },
  behavior: {
    mode: 'bidirectional',
    autoSyncInterval: 30000, // 30 seconds
    realTimeSync: true,
    conflictResolution: 'last-write-wins'
  },
  security: {
    authMethod: 'token',
    token: 'your-sync-token'
  }
}, db);

await syncManager.initialize();

// Connect to server instance
await syncManager.connectToPeer({
  host: 'server.myapp.com',
  port: 8080,
  secure: true
});

// Sync specific tables
await syncManager.syncWithAllPeers(['users', 'orders', 'products']);

PostgreSQL Integration

import { PostgreSQLSync } from '@rai/raidb/postgres-sync';

// Sync with existing PostgreSQL database
const pgSync = new PostgreSQLSync({
  raidb: db,
  postgres: {
    host: 'localhost',
    port: 5432,
    user: 'postgres',
    password: 'password',
    database: 'production_db'
  },
  syncMode: 'bidirectional',
  tables: ['users', 'orders', 'products']
});

await pgSync.initialize();
await pgSync.startSync();

⚡ Performance & Configuration

Optimization Settings

const db = new RAIDB({
  dataPath: './data',
  encryptionKey: 'key',
  performance: {
    // Memory configuration
    cacheSize: '512MB',
    sharedBuffers: '256MB',
    
    // WAL configuration
    walMode: true,
    walSyncMode: 'normal', // or 'full' for maximum durability
    
    // Query optimization
    enableQueryCache: true,
    planCacheSize: 1000,
    
    // Indexing
    autoVacuum: true,
    autoAnalyze: true
  },
  
  // Connection limits
  maxConnections: 50,
  
  // Security
  encryptionLevel: 'maximum',
  keyDerivationRounds: 100000
});

Monitoring & Metrics

// Built-in performance monitoring
const metrics = await db.getMetrics();
console.log({
  queriesPerSecond: metrics.qps,
  cacheHitRate: metrics.cacheHitRate,
  activeConnections: metrics.activeConnections,
  indexUsage: metrics.indexUsage
});

// Query performance analysis
const slowQueries = await db.getSlowQueryLog();
console.log('Slow queries:', slowQueries);

// Real-time query monitoring
db.on('query', (query, duration) => {
  if (duration > 1000) {
    console.log(`Slow query detected: ${query} (${duration}ms)`);
  }
});

🧪 Testing & Development

Running Tests

# Run all production readiness tests
npm test

# Run specific test categories
npm run test:core          # Core database engine
npm run test:server        # Server functionality
npm run test:integration   # Real-world scenarios
npm run test:performance   # Load testing

# Run individual test files
node tests/integration/real-world-server-test.js
node tests/performance/comprehensive-performance-benchmark.js

Development Mode

// Enable debug logging
const db = new RAIDB({
  dataPath: './dev-data',
  encryptionKey: 'dev-key',
  debug: {
    logLevel: 'debug',
    logQueries: true,
    logPerformance: true
  }
});

📚 API Reference

Core Database Operations

// Query execution
const result = await db.query(sql, parameters);
const { rows, rowCount, command } = result;

// Transaction management
const transaction = await db.beginTransaction();
try {
  await transaction.query('INSERT INTO users ...');
  await transaction.query('INSERT INTO orders ...');
  await transaction.commit();
} catch (error) {
  await transaction.rollback();
  throw error;
}

// Prepared statements
const stmt = await db.prepare('SELECT * FROM users WHERE id = $1');
const user = await stmt.execute([userId]);
await stmt.deallocate();

// Bulk operations
await db.bulkInsert('products', [
  { name: 'Product 1', price: 10.00 },
  { name: 'Product 2', price: 20.00 }
]);

Schema Management

// Database introspection
const tables = await db.getTables();
const columns = await db.getTableSchema('users');
const indexes = await db.getIndexes('users');
const constraints = await db.getConstraints('users');

// Dynamic schema operations
await db.createTable('dynamic_table', {
  id: 'SERIAL PRIMARY KEY',
  name: 'TEXT NOT NULL',
  data: 'JSONB'
});

await db.addColumn('users', 'phone', 'TEXT');
await db.addIndex('users', ['email'], { unique: true });

🔧 Configuration Options

Complete Configuration Reference

const config = {
  // Data storage
  dataPath: './data',                    // Data directory path
  encryptionKey: 'key',                  // Encryption key (required)
  
  // Performance tuning
  performance: {
    cacheSize: '256MB',                  // Memory cache size
    sharedBuffers: '128MB',              // Shared buffer pool
    walMode: true,                       // Write-ahead logging
    walSyncMode: 'normal',               // WAL sync mode
    enableQueryCache: true,              // Query result caching
    planCacheSize: 1000,                 // Query plan cache size
    autoVacuum: true,                    // Automatic space reclamation
    autoAnalyze: true,                   // Automatic statistics updates
    maxWorkerProcesses: 4                // Background worker processes
  },
  
  // Connection settings
  maxConnections: 100,                   // Maximum concurrent connections
  connectionTimeout: 30000,              // Connection timeout (ms)
  statementTimeout: 300000,              // Statement timeout (ms)
  
  // Security
  encryptionLevel: 'standard',           // 'standard' or 'maximum'
  keyDerivationRounds: 50000,            // PBKDF2 rounds
  
  // Synchronization
  sync: {
    enabled: true,                       // Enable sync capabilities
    instanceId: 'unique-id',             // Instance identifier
    instanceType: 'desktop',             // Instance type
    conflictResolution: 'last-write-wins' // Conflict resolution strategy
  },
  
  // Debugging
  debug: {
    logLevel: 'info',                    // Log level
    logQueries: false,                   // Log all queries
    logPerformance: false,               // Log performance metrics
    enableProfiler: false                // Enable query profiler
  }
};

🏗️ Architecture

RAIDB Architecture
├── Core Engine
│   ├── SQL Parser & AST               ✅ Complete PostgreSQL syntax
│   ├── Query Planner & Optimizer     ✅ Cost-based optimization
│   ├── Transaction Manager (MVCC)    ✅ ACID compliance
│   ├── Storage Engine                ✅ Encrypted file storage
│   └── Index Manager                 ✅ B-tree, hash, partial indexes
│
├── Server Components
│   ├── PostgreSQL Wire Protocol      ✅ Full pg client compatibility
│   ├── Connection Manager            ✅ Pooling and session management
│   ├── Authentication Manager        ✅ Multi-method authentication
│   └── Cluster Manager               ✅ Multi-node clustering
│
├── Advanced Features
│   ├── Schema Migration System       ✅ Version-controlled migrations
│   ├── Function & Trigger System     ✅ User-defined functions
│   ├── View & Materialized Views     ✅ Complex query optimization
│   └── Synchronization Engine        ✅ Real-time instance sync
│
└── Monitoring & Management
    ├── Performance Metrics           ✅ Real-time monitoring
    ├── Query Performance Analyzer    ✅ Slow query detection
    ├── Health Check System           ✅ System health monitoring
    └── Administrative Tools          ✅ Database management

📈 Performance Benchmarks

Embedded Mode Performance

• Simple SELECT: ~2,000 ops/sec
• JOIN Queries: ~800 ops/sec
• INSERT Operations: ~1,500 ops/sec
• Complex Aggregations: ~400 ops/sec
• Transaction Processing: ~1,000 txn/sec

Server Mode Performance

• Concurrent Connections: 100+ simultaneous clients
• Query Throughput: ~300 QPS per core
• Replication Lag: <10ms typical
• Memory Usage: ~50MB base + cache size

📦 Local/Desktop Deployment

Building for Local Use

# Build standalone local package
npm run build:local

# Creates ./build/ directory with:
# - Compiled JavaScript (dist/)
# - Production dependencies (node_modules/)
# - Startup scripts (start.sh, start.bat)
# - Local configuration

Desktop App Distribution

# Package for all platforms
npm run package:desktop

# Creates platform-specific packages:
# - raidb-linux-v1.0.0.tar.gz
# - raidb-macos-v1.0.0.tar.gz  
# - raidb-windows-v1.0.0.zip

Real-World Desktop App Integration

For embedding RAIDB in desktop applications:

// In your Electron/Tauri/desktop app
import { RAIDB } from './raidb-local/dist/index.js';

const db = new RAIDB({
  dataPath: path.join(app.getPath('userData'), 'database'),
  encryptionKey: 'your-app-specific-key',
  performance: {
    cacheSize: '256MB',
    walMode: true
  }
});

await db.initialize();
// Database ready for use

Distribution Options

1. Bundled Approach - Include RAIDB files in your app installer
2. Download Approach - Download RAIDB package during first run
3. Embedded Approach - Compile RAIDB into your app binary

🚀 Deployment Scripts

Server Deployment (Docker)

npm run deploy         # Full deployment with verification
npm run deploy:quick   # Quick deployment

Local Deployment

npm run build:local    # Build for local use
npm run package:desktop # Create distribution packages

🤝 Contributing

We welcome contributions! Please see our Contributing Guide for details.

Development Setup

git clone https://github.com/your-org/raidb.git
cd raidb
npm install
npm run build
npm test

📄 License

MIT License - see LICENSE file for details.

🆘 Support

• Documentation: docs.raidb.com
• Issues: GitHub Issues
• Discord: Community Chat
• Email: [email protected]

RAIDB - Production-ready PostgreSQL-compatible database engine for modern applications.