db.ts•6.53 kB
import { Client as PgClient } from "pg";
import mysql from "mysql2/promise";
import { DbConfig, HistoryEntry, recordHistory } from "./storage.js";
type TablePreview = { table: string; rows: unknown[] };
function resolveSsl(config: DbConfig) {
// Prefer sslMode if present; fall back to boolean ssl flag.
const mode = config.sslMode;
if (!mode) {
return config.ssl ? { rejectUnauthorized: false } : false;
}
if (mode === "disable") return false;
if (mode === "require") return { rejectUnauthorized: true };
if (mode === "no-verify") return { rejectUnauthorized: false };
return false;
}
function resolveMySqlSsl(config: DbConfig) {
const mode = config.sslMode;
if (!mode) {
return config.ssl ? { rejectUnauthorized: false } : undefined;
}
if (mode === "disable") return undefined;
if (mode === "require") return { rejectUnauthorized: true };
if (mode === "no-verify") return { rejectUnauthorized: false };
return undefined;
}
function normalizePgError(err: unknown, config?: DbConfig): Error {
if (err instanceof Error) {
const msg = err.message || "";
if (msg.includes("invalid response")) {
const info = config
? ` (host=${config.host}:${config.port}, sslMode=${config.sslMode ?? (config.ssl ? "no-verify" : "disable")})`
: "";
// Common when SSL setting or port/host is wrong.
return new Error(
`${msg}${info}. This usually means the host/port/SSL settings are incorrect or the target is not PostgreSQL.`,
);
}
if ((err as any).code === "ECONNREFUSED") {
return new Error(`${msg}. Check host/port and network reachability.`);
}
return err;
}
return new Error(String(err));
}
async function withPgClient<T>(config: DbConfig, fn: (client: PgClient) => Promise<T>): Promise<T> {
const client = new PgClient({
host: config.host,
port: config.port,
user: config.user,
password: config.password,
database: config.database,
ssl: resolveSsl(config),
});
await client.connect();
try {
// Enforce read-only session.
await client.query("SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY");
return await fn(client);
} catch (err) {
throw normalizePgError(err, config);
} finally {
await client.end();
}
}
async function withMySqlConn<T>(config: DbConfig, fn: (conn: mysql.Connection) => Promise<T>): Promise<T> {
const conn = await mysql.createConnection({
host: config.host,
port: config.port,
user: config.user,
password: config.password,
database: config.database,
ssl: resolveMySqlSsl(config),
});
try {
// Enforce read-only session for MySQL-compatible engines; fall back silently if unsupported.
try {
await conn.query("SET SESSION TRANSACTION READ ONLY");
} catch (e1) {
try {
await conn.query("SET SESSION tx_read_only = 1");
} catch {
console.warn("Could not set read-only mode for MySQL session; proceeding read-only by convention.");
}
}
return await fn(conn);
} finally {
await conn.end();
}
}
export async function listTables(config: DbConfig): Promise<string[]> {
if (config.kind === "postgres") {
return withPgClient(config, async (client) => {
const res = await client.query<{
table_name: string;
}>(
`
SELECT table_name
FROM information_schema.tables
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY table_name
`,
);
const names = res.rows.map((r) => r.table_name);
const history: HistoryEntry = {
dbName: config.name,
tables: names,
lastUsed: new Date().toISOString(),
};
recordHistory(history);
return names;
});
}
if (config.kind === "mysql") {
return withMySqlConn(config, async (conn) => {
const [rows] = await conn.execute<{ table_name: string }[]>(
`
SELECT table_name
FROM information_schema.tables
WHERE table_schema = ?
ORDER BY table_name
`,
[config.database],
);
const names = rows.map((r) => r.table_name);
const history: HistoryEntry = {
dbName: config.name,
tables: names,
lastUsed: new Date().toISOString(),
};
recordHistory(history);
return names;
});
}
throw new Error(`Unsupported DB kind: ${config.kind}`);
}
function assertSafeIdentifier(name: string) {
if (!/^[a-zA-Z0-9_]+$/.test(name)) {
throw new Error(`Unsafe identifier: ${name}`);
}
}
export async function previewTable(config: DbConfig, table: string, limit = 20): Promise<TablePreview> {
assertSafeIdentifier(table);
if (config.kind === "postgres") {
return withPgClient(config, async (client) => {
const res = await client.query(`SELECT * FROM ${table} LIMIT $1`, [limit]);
const history: HistoryEntry = {
dbName: config.name,
tables: [table],
lastUsed: new Date().toISOString(),
};
recordHistory(history);
return { table, rows: res.rows };
});
}
if (config.kind === "mysql") {
return withMySqlConn(config, async (conn) => {
const [rows] = await conn.query(`SELECT * FROM ?? LIMIT ?`, [table, limit]);
const history: HistoryEntry = {
dbName: config.name,
tables: [table],
lastUsed: new Date().toISOString(),
};
recordHistory(history);
return { table, rows: rows as unknown[] };
});
}
throw new Error(`Unsupported DB kind: ${config.kind}`);
}
export async function runSelect(config: DbConfig, sql: string): Promise<unknown[]> {
if (!sql.trim().toLowerCase().startsWith("select")) {
throw new Error("Only SELECT queries are allowed");
}
if (config.kind === "postgres") {
return withPgClient(config, async (client) => {
const res = await client.query(sql);
const history: HistoryEntry = {
dbName: config.name,
tables: [],
lastUsed: new Date().toISOString(),
};
recordHistory(history);
return res.rows;
});
}
if (config.kind === "mysql") {
return withMySqlConn(config, async (conn) => {
const [rows] = await conn.query(sql);
const history: HistoryEntry = {
dbName: config.name,
tables: [],
lastUsed: new Date().toISOString(),
};
recordHistory(history);
return rows as unknown[];
});
}
throw new Error(`Unsupported DB kind: ${config.kind}`);
}