Self-Host Proxy Server

┌─────────────────────┐         HTTPS         ┌──────────────────────┐
│   LunoDB Mobile     │◄──────────────────────►│   Your Proxy Server  │
│   (iOS/Android)     │     REST API           │   (Node.js)          │
└─────────────────────┘                        └──────────┬───────────┘
                                                          │
                                   ┌──────────────────────┼──────────────────────┐
                                   │                      │                      │
                             ┌─────▼─────┐          ┌─────▼─────┐          ┌─────▼─────┐
                             │   MySQL   │          │ PostgreSQL│          │  MariaDB  │
                             │  Server   │          │  Server   │          │  Server   │
                             └───────────┘          └───────────┘          └───────────┘

┌─────────────────────┐         HTTPS         ┌──────────────────────┐
│   LunoDB Mobile     │◄──────────────────────►│   Your Proxy Server  │
│   (iOS/Android)     │     REST API           │   (Node.js)          │
└─────────────────────┘                        └──────────┬───────────┘
                                                          │
                                               SSH Tunnel │ (Encrypted)
                                                          │
                                               ┌──────────▼───────────┐
                                               │   Bastion Host       │
                                               │   (SSH Server)       │
                                               └──────────┬───────────┘
                                                          │
                                               Private    │ Network
                                                          │
                                               ┌──────────▼───────────┐
                                               │   Database Server    │
                                               │   (MySQL/PostgreSQL) │
                                               └──────────────────────┘

{
  "type": "mysql",
  "host": "10.0.1.50",           // Private IP of database
  "port": 3306,
  "username": "db_user",
  "password": "db_password",
  "database": "mydb",
  "ssh": {
    "enabled": true,
    "host": "bastion.example.com", // SSH server hostname
    "port": 22,                    // SSH port (default: 22)
    "username": "ssh_user",        // SSH username
    "authType": "key",             // "password" or "key"
    "privateKey": "-----BEGIN RSA PRIVATE KEY-----\n...",
    "passphrase": "optional_key_passphrase"
  }
}

const { Client } = require('ssh2');
const mysql = require('mysql2/promise');
const net = require('net');

async function createSSHTunnel(sshConfig, dbConfig) {
  return new Promise((resolve, reject) => {
    const ssh = new Client();

    ssh.on('ready', () => {
      // Forward connection to database through tunnel
      ssh.forwardOut(
        '127.0.0.1',  // Source address
        0,            // Source port (auto-assign)
        dbConfig.host,
        dbConfig.port,
        async (err, stream) => {
          if (err) {
            ssh.end();
            return reject(err);
          }

          // Create database connection through the tunnel
          const connection = await mysql.createConnection({
            ...dbConfig,
            stream: stream,  // Use SSH stream instead of TCP
          });

          resolve({ connection, ssh });
        }
      );
    });

    ssh.on('error', reject);

    // Connect to SSH server
    const connectConfig = {
      host: sshConfig.host,
      port: sshConfig.port || 22,
      username: sshConfig.username,
    };

    if (sshConfig.authType === 'key') {
      connectConfig.privateKey = sshConfig.privateKey;
      if (sshConfig.passphrase) {
        connectConfig.passphrase = sshConfig.passphrase;
      }
    } else {
      connectConfig.password = sshConfig.password;
    }

    ssh.connect(connectConfig);
  });
}

// Usage in your connection endpoint
app.post('/proxy/connections', async (req, res) => {
  const { ssh, ...dbConfig } = req.body;

  try {
    let connection, sshClient;

    if (ssh?.enabled) {
      const tunnel = await createSSHTunnel(ssh, dbConfig);
      connection = tunnel.connection;
      sshClient = tunnel.ssh;
    } else {
      connection = await mysql.createConnection(dbConfig);
    }

    const sessionId = uuidv4();
    sessions.set(sessionId, {
      connection,
      sshClient,  // Store SSH client for cleanup
      lastActivity: Date.now()
    });

    res.json({ sessionId, /* ... */ });
  } catch (error) {
    if (error.message.includes('authentication')) {
      res.status(502).json({ error: 'SSH_AUTH_FAILED', message: error.message });
    } else {
      res.status(502).json({ error: 'SSH_CONNECTION_FAILED', message: error.message });
    }
  }
});

# MySQL - with headers (for SELECT queries)
MYSQL_PWD='password' mysql -h host -P 3306 -u user database -B -e 'SELECT * FROM users'

# MySQL - without headers (for internal queries like SHOW DATABASES)
MYSQL_PWD='password' mysql -h host -P 3306 -u user database -N -B -e 'SHOW DATABASES'

# PostgreSQL - with headers
PGPASSWORD='password' psql -h host -p 5432 -U user database -A -F $'\t' -c 'SELECT * FROM users'

# PostgreSQL - without headers (tuples only)
PGPASSWORD='password' psql -h host -p 5432 -U user database -t -A -F $'\t' -c 'SELECT 1'

// Example CLI output (tab-separated):
// id    name    status
// 1     John    active
// 2     Jane    \N

// Should be parsed to:
{
  "rows": [
    { "id": "1", "name": "John", "status": "active" },
    { "id": "2", "name": "Jane", "status": null }
  ],
  "fields": [
    { "name": "id", "type": "string" },
    { "name": "name", "type": "string" },
    { "name": "status", "type": "string" }
  ]
}

// Note: \N in MySQL output represents NULL values

// ❌ BAD: Simple string matching causes false positives
// This would match table names like "price_errors"
if (output.toLowerCase().includes('error')) {
  throw new Error('Query failed');
}

// ✅ GOOD: Use specific regex patterns
const errorPatterns = [
  /^ERROR \d+/m,              // MySQL: "ERROR 1045 (28000)..."
  /^error:/im,                 // Error prefix at line start only
  /Access denied for user/i,   // MySQL access denied
  /Connection refused/i,       // Connection error
  /Unknown database/i,         // MySQL unknown database
  /command not found/i,        // CLI not installed
  /^FATAL:/m,                  // PostgreSQL fatal errors
  /authentication failed/i,    // Auth failure
  /Can't connect to/i,         // MySQL connection error
];

for (const pattern of errorPatterns) {
  if (pattern.test(output)) {
    throw new Error(`Query error: ${output}`);
  }
}

{
  "dependencies": {
    "express": "^4.18.0",
    "mysql2": "^3.6.0",
    "pg": "^8.11.0",
    "uuid": "^9.0.0",
    "cors": "^2.8.5",
    "ssh2": "^1.15.0"
  }
}

const express = require('express');
const mysql = require('mysql2/promise');
const { v4: uuidv4 } = require('uuid');
const cors = require('cors');

const app = express();
app.use(cors());
app.use(express.json());

// In-memory session store (use Redis in production)
const sessions = new Map();
const SESSION_TIMEOUT = 30 * 60 * 1000; // 30 minutes

// Create session
app.post('/proxy/connections', async (req, res) => {
  try {
    const { type, host, port, username, password, database } = req.body;

    const connection = await mysql.createConnection({
      host, port, user: username, password, database
    });

    const sessionId = uuidv4();
    sessions.set(sessionId, {
      connection,
      config: req.body,
      lastActivity: Date.now()
    });

    // Get version
    const [rows] = await connection.query('SELECT VERSION() as version');

    res.json({
      sessionId,
      version: rows[0].version,
      expiresAt: new Date(Date.now() + SESSION_TIMEOUT).toISOString()
    });
  } catch (error) {
    res.status(502).json({
      error: 'CONNECTION_FAILED',
      message: error.message
    });
  }
});

// Execute query
app.post('/proxy/query/:sessionId/execute', async (req, res) => {
  const session = sessions.get(req.params.sessionId);

  if (!session) {
    return res.status(404).json({
      error: 'SESSION_NOT_FOUND',
      message: 'Session expired or not found'
    });
  }

  session.lastActivity = Date.now();

  try {
    const { query, params, database } = req.body;

    if (database) {
      await session.connection.changeUser({ database });
    }

    const startTime = Date.now();
    const [rows, fields] = await session.connection.query(query, params);

    res.json({
      rows: Array.isArray(rows) ? rows : [],
      fields: fields?.map(f => ({ name: f.name, type: f.type })) || [],
      affectedRows: rows.affectedRows || 0,
      executionTime: Date.now() - startTime
    });
  } catch (error) {
    res.status(400).json({
      error: 'QUERY_ERROR',
      message: error.message
    });
  }
});

// Disconnect
app.delete('/proxy/connections/:sessionId', async (req, res) => {
  const session = sessions.get(req.params.sessionId);

  if (session) {
    await session.connection.end();
    sessions.delete(req.params.sessionId);
  }

  res.json({ success: true });
});

// Session cleanup
setInterval(() => {
  const now = Date.now();
  for (const [id, session] of sessions) {
    if (now - session.lastActivity > SESSION_TIMEOUT) {
      session.connection.end();
      sessions.delete(id);
    }
  }
}, 60000);

app.listen(3000, () => console.log('Proxy running on port 3000'));

// API Key middleware
const apiKeyAuth = (req, res, next) => {
  const apiKey = req.headers['x-api-key'];

  if (!apiKey || apiKey !== process.env.PROXY_API_KEY) {
    return res.status(401).json({
      error: 'UNAUTHORIZED',
      message: 'Invalid or missing API key'
    });
  }

  next();
};

app.use('/proxy', apiKeyAuth);