← back to Wallco Ai
REFACTOR-1a: extract lib/db.js (psqlQuery, pgEsc, psqlExecLocal)
3b6954e67b6afeaf03c8fd1887204501ca000c2e · 2026-05-23 11:56:52 -0700 · Steve Abrams
First of the four extractions in REFACTOR-1. Moved verbatim:
- DB_NAME, DB_URL constants
- PSQL_CMD platform-conditional string (Linux: sudo postgres, macOS: peer auth)
- psqlQuery(sql) — stdin-fed psql call
- pgEsc(v) — null/number/bool/string escaper for inline SQL
- psqlExecLocal(sql) — argv -c variant (JSON.stringify-wrapped)
server.js now imports them via:
const { DB_NAME, DB_URL, PSQL_CMD, psqlQuery, pgEsc, psqlExecLocal } =
require('./lib/db');
134 inline call sites of psqlQuery / pgEsc / psqlExecLocal unchanged —
the names land at module scope same as before. node --check passes;
parse confirms the imports resolve.
Behavior-identical, zero query-shape change. No movement on the deeper
question of whether to migrate from psql-CLI to pg.Pool (the db-perf
audit estimated 80-120ms savings per call × 134 sites — that lift is
deferred to REFACTOR-2 since it changes await semantics).
Next: lib/color.js (8 helpers), lib/http.js (maybe304), lib/parse.js
(parseDesignId — new utility).
Files touched
Diff
commit 3b6954e67b6afeaf03c8fd1887204501ca000c2e
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Sat May 23 11:56:52 2026 -0700
REFACTOR-1a: extract lib/db.js (psqlQuery, pgEsc, psqlExecLocal)
First of the four extractions in REFACTOR-1. Moved verbatim:
- DB_NAME, DB_URL constants
- PSQL_CMD platform-conditional string (Linux: sudo postgres, macOS: peer auth)
- psqlQuery(sql) — stdin-fed psql call
- pgEsc(v) — null/number/bool/string escaper for inline SQL
- psqlExecLocal(sql) — argv -c variant (JSON.stringify-wrapped)
server.js now imports them via:
const { DB_NAME, DB_URL, PSQL_CMD, psqlQuery, pgEsc, psqlExecLocal } =
require('./lib/db');
134 inline call sites of psqlQuery / pgEsc / psqlExecLocal unchanged —
the names land at module scope same as before. node --check passes;
parse confirms the imports resolve.
Behavior-identical, zero query-shape change. No movement on the deeper
question of whether to migrate from psql-CLI to pg.Pool (the db-perf
audit estimated 80-120ms savings per call × 134 sites — that lift is
deferred to REFACTOR-2 since it changes await semantics).
Next: lib/color.js (8 helpers), lib/http.js (maybe304), lib/parse.js
(parseDesignId — new utility).
---
lib/db.js | 55 +++++++++++++++++++++++++++++++++++++++++++++++++++++++
server.js | 31 ++++---------------------------
2 files changed, 59 insertions(+), 27 deletions(-)
diff --git a/lib/db.js b/lib/db.js
new file mode 100644
index 0000000..e9bc336
--- /dev/null
+++ b/lib/db.js
@@ -0,0 +1,55 @@
+// lib/db.js — Postgres helpers for server.js (non-marketplace surface).
+//
+// REFACTOR-1 (2026-05-23): extracted verbatim from server.js (lines 80-94 +
+// 1482-1487 in the pre-refactor file). Behavior identical — same execSync
+// path, same PSQL_CMD shape, same value escaping rules.
+//
+// Note: this module is intentionally not the pg.Pool client used by
+// src/marketplace/db.js — server.js's main surface still shells out to
+// psql via execSync. Migrating to pg.Pool is a separate refactor
+// (REFACTOR-2 in the original plan, post audit 2026-05-23) — the agent
+// estimated 80-120ms savings per call site × 134 call sites. For now,
+// preserve current behavior so this commit ships behavior-preserving.
+
+'use strict';
+const { execSync } = require('child_process');
+
+const DB_NAME = process.env.WALLCO_DB || 'dw_unified';
+const DB_URL = process.env.DATABASE_URL || DB_NAME;
+
+// On Linux (Kamatera) pm2 runs as root but PG has no 'root' role — sudo as
+// postgres. On macOS local trust auth works for the current user.
+const PSQL_CMD = (process.platform === 'linux')
+ ? `sudo -n -u postgres psql ${DB_NAME} -At -q`
+ : `psql ${DB_NAME} -At -q`;
+
+// Stdin-fed psql call. NEVER shell-pipe SQL as a -c "${sql}" argument —
+// double-quote injection. (SEC-1 fix in commit cf388a2 covers this for the
+// last remaining shell-pipe site.)
+function psqlQuery(sql) {
+ return execSync(PSQL_CMD, { input: sql, encoding: 'utf8' }).trim();
+}
+
+// PG value escaper for inline SQL — matches the project's existing inline-string style.
+function pgEsc(v) {
+ if (v === null || v === undefined) return 'NULL';
+ if (typeof v === 'number') return Number.isFinite(v) ? String(v) : 'NULL';
+ if (typeof v === 'boolean') return v ? 'TRUE' : 'FALSE';
+ return "'" + String(v).replace(/'/g, "''") + "'";
+}
+
+// Variant that uses the -c argv form — kept for cases where stdin isn't
+// the right path. SQL goes through JSON.stringify so embedded quotes are
+// shell-safe (the value is double-quoted by the shell, not interpolated).
+function psqlExecLocal(sql) {
+ return execSync(PSQL_CMD + ' -c ' + JSON.stringify(sql), { encoding: 'utf8' }).trim();
+}
+
+module.exports = {
+ DB_NAME,
+ DB_URL,
+ PSQL_CMD,
+ psqlQuery,
+ pgEsc,
+ psqlExecLocal,
+};
diff --git a/server.js b/server.js
index f54f911..04b7e0e 100644
--- a/server.js
+++ b/server.js
@@ -71,27 +71,9 @@ const ROOM_API = process.env.ROOM_API || 'http://45.61.58.125:3075/api/generate-
const ELEVEN_KEY = process.env.ELEVENLABS_API_KEY || '';
const ELEVEN_VOICE_ID = process.env.ELEVENLABS_VOICE_ID || '21m00Tcm4TlvDq8ikWAM'; // Rachel (default suggested)
// Local trust-auth works; the URL form in .env is for prod / remote use.
-// Stick with the bare DB name so peer auth handles it cleanly.
-const DB_NAME = process.env.WALLCO_DB || 'dw_unified';
-const DB_URL = process.env.DATABASE_URL || DB_NAME;
-
-// On Linux (Kamatera) pm2 runs as root but PG has no 'root' role — sudo as
-// postgres. On macOS local trust auth works for the current user.
-const PSQL_CMD = (process.platform === 'linux')
- ? `sudo -n -u postgres psql ${DB_NAME} -At -q`
- : `psql ${DB_NAME} -At -q`;
-
-function psqlQuery(sql) {
- return execSync(PSQL_CMD, { input: sql, encoding: 'utf8' }).trim();
-}
-
-// PG value escaper for inline SQL — matches the project's existing inline-string style.
-function pgEsc(v) {
- if (v === null || v === undefined) return 'NULL';
- if (typeof v === 'number') return Number.isFinite(v) ? String(v) : 'NULL';
- if (typeof v === 'boolean') return v ? 'TRUE' : 'FALSE';
- return "'" + String(v).replace(/'/g, "''") + "'";
-}
+// REFACTOR-1 (2026-05-23): DB helpers extracted to lib/db.js. Same execSync
+// path, same PSQL_CMD shape, same escaping rules — behavior-preserving.
+const { DB_NAME, DB_URL, PSQL_CMD, psqlQuery, pgEsc, psqlExecLocal } = require('./lib/db');
// Admin-token check for destructive room ops. Empty/unset token => admin features locked off.
const ADMIN_TOKEN = process.env.ADMIN_TOKEN || '';
@@ -1479,12 +1461,7 @@ app.get('/api/design/:id/download', async (req, res) => {
// POST /api/design/:id/rate body: { color_good?, style_good?, scale_good?, stars? }
// Localhost admin gate. Each field is optional — only what's sent gets
// updated. Boolean fields accept true/false/null (null = unset).
-function psqlExecLocal(sql) {
- // Was: spawnSync('psql', ['dw_unified', ...]) — broken on prod where pm2
- // runs as root and PG has no 'root' role. Switched to the same PSQL_CMD
- // path psqlQuery() uses, which sudos to postgres on Linux.
- return execSync(PSQL_CMD + ' -c ' + JSON.stringify(sql), { encoding: 'utf8' }).trim();
-}
+// REFACTOR-1: psqlExecLocal moved to lib/db.js (imported at top).
app.post('/api/design/:id/rate', express.json({ limit: '4kb' }), (req, res) => {
if (!isAdmin(req)) return res.status(404).json({ error: 'not found' });
const id = parseInt(req.params.id, 10);
← 28303dd PERF-3: cache /api/marketplace/status (30s in-process TTL)
·
back to Wallco Ai
·
REFACTOR-1b/c/d: extract lib/color.js, lib/http.js, lib/pars b940514 →