jeremybradbury · April 17, 2021 01:59 · Apr 17, 2021
diff --git a/upsert-row.js b/upsert-row.js
@@ -0,0 +1,29 @@
+// you'll likely want the pool creation in another file & imported here instead of a new pool
+const pool = new require("pg").Pool(); // left here for completeness
+// import { pool } from "../db"
+
+// usage example: `upsertRow("profile", {id:"999", bio: "I like to..." image: "https://..."})`
+const upsertRow = async (table, row = {}) => {
+  try {
+    const fields = Object.keys(row);
+    const values = Object.values(row);
+    let q = `INSERT INTO ${table} (${fields.reduce((x, y) => `"${x}", "${y}"`)})
+VALUES(${values.reduce((x, y) => `'${x}', '${y}'`)}) 
+ON CONFLICT ("id") DO UPDATE`;
+    for (let i = 0; i < fields.length; i++) {
+      if (fields[i].includes("id")) continue;
+      q += ` SET ${fields[i]} = EXCLUDED.${fields[i]}`;
+    }
+    // console.info("query",q); // uncomment for troubleshooting
+    ({
+      rows: [row], // extract first row into row variable
+    } = await pool.query(q));
+  } catch (err) {
+    console.error(
+      `upsertRow(${table},${JSON.stringify(row)})`
+    );
+    console.error(err)
+    return false;
+  }
+  return row;
+}