Improves error logging in database queries (#53)

8 files changed, 119 insertions, 103 deletions

diff --git a/api/db/_addAuthenticator.ts b/api/db/_addAuthenticator.ts
index ad76037..9df50da 100644
--- a/api/db/_addAuthenticator.ts
+++ b/api/db/_addAuthenticator.ts
@@ -4,29 +4,31 @@ import { sql } from '@vercel/postgres';
 import { AUTHENTICATORS } from './_tables';
 
 export default async function addAuthenticator(authenticator: Authenticator) {
+  const query = `
+    INSERT INTO ${AUTHENTICATORS.name} (
+      ${AUTHENTICATORS.fields.id},
+      ${AUTHENTICATORS.fields.backedUp},
+      ${AUTHENTICATORS.fields.counter},
+      ${AUTHENTICATORS.fields.deviceType},
+      ${AUTHENTICATORS.fields.publicKey}
+      ${AUTHENTICATORS.fields.transports},
+      ${AUTHENTICATORS.fields.type},
+      ${AUTHENTICATORS.fields.userId}
+    ) VALUES (
+      ${authenticator.id},
+      ${authenticator.backedUp},
+      ${authenticator.counter},
+      ${authenticator.deviceType},
+      ${authenticator.publicKey}
+      { ${authenticator.transports.join(', ')} },
+      ${authenticator.type},
+      ${authenticator.userId}
+    );
+  `;
+
   try {
-    await sql.query(`
-      INSERT INTO ${AUTHENTICATORS.name} (
-        ${AUTHENTICATORS.fields.id},
-        ${AUTHENTICATORS.fields.backedUp},
-        ${AUTHENTICATORS.fields.counter},
-        ${AUTHENTICATORS.fields.deviceType},
-        ${AUTHENTICATORS.fields.publicKey}
-        ${AUTHENTICATORS.fields.transports},
-        ${AUTHENTICATORS.fields.type},
-        ${AUTHENTICATORS.fields.userId}
-      ) VALUES (
-        ${authenticator.id},
-        ${authenticator.backedUp},
-        ${authenticator.counter},
-        ${authenticator.deviceType},
-        ${authenticator.publicKey}
-        { ${authenticator.transports.join(', ')} },
-        ${authenticator.type},
-        ${authenticator.userId}
-      );
-    `);
+    await sql.query(query);
   } catch (err) {
-    throw new Error(`Failed to insert authenticator into database: ${JSON.stringify(authenticator)}.`, err);
+    throw new Error(`Failed to insert authenticator into database: ${JSON.stringify(authenticator)}. Query was ${query.replace(/\s+/g, ' ')}. ${err}`);
   }
-}
\ No newline at end of file
+}
diff --git a/api/db/_addUser.ts b/api/db/_addUser.ts
index ef72447..776391a 100644
--- a/api/db/_addUser.ts
+++ b/api/db/_addUser.ts
@@ -4,17 +4,19 @@ import { sql } from '@vercel/postgres';
 import { USERS } from './_tables';
 
 export default async function addUser(user: User) {
+  const query = `
+    INSERT INTO ${USERS.name} (
+      ${USERS.fields.id},
+      ${USERS.fields.displayName}
+    ) VALUES (
+      '${user.id}',
+      '${user.displayName}'
+    );
+  `;
+
   try {
-    await sql.query(`
-      INSERT INTO ${USERS.name} (
-        ${USERS.fields.id},
-        ${USERS.fields.displayName}
-      ) VALUES (
-        '${user.id}',
-        '${user.displayName}'
-      );
-    `);
+    await sql.query(query);
   } catch (err) {
-    throw new Error(`Failed to insert user into database: ${JSON.stringify(user)}.`, err);
+    throw new Error(`Failed to insert user into database: ${JSON.stringify(user)}. Query was ${query.replace(/\s+/g, ' ')}. ${err}`);
   }
-}
\ No newline at end of file
+}
diff --git a/api/db/_getAuthenticator.ts b/api/db/_getAuthenticator.ts
index 55c4b2f..e93023b 100644
--- a/api/db/_getAuthenticator.ts
+++ b/api/db/_getAuthenticator.ts
@@ -5,12 +5,14 @@ import Authenticator from '../types/Authenticator';
 export default async function getAuthenticator<
   T extends keyof Authenticator
 >(id: string, fields: T[]): Promise<Pick<Authenticator, T>> {
+  const query = `
+    SELECT ${fields.join(', ')}
+    FROM ${AUTHENTICATORS.name}
+    WHERE ${AUTHENTICATORS.fields.id} = '${id}';
+  `;
+
   try {
-    const result = await sql.query(`
-      SELECT ${fields.join(', ')}
-      FROM ${AUTHENTICATORS.name}
-      WHERE ${AUTHENTICATORS.fields.id} = '${id}';
-    `);
+    const result = await sql.query(query);
 
     const row = result.rows.at(0);
     if (!row) {
@@ -19,11 +21,11 @@ export default async function getAuthenticator<
 
     const authenticator: Partial<Pick<Authenticator, T>> = {};
     for (const field in fields) {
-    // Assume type conversion is already done by node-postgres
-    authenticator[field] = row[AUTHENTICATORS.fields[field]];
+      // Assume type conversion is already done by node-postgres
+      authenticator[field] = row[AUTHENTICATORS.fields[field]];
     }
     return authenticator as Pick<Authenticator, T>;
   } catch (err) {
-    throw new Error(`Failed to get authenticator with ID ${id} from database.`, err);
+    throw new Error(`Failed to get authenticator with ID ${id} from database. Query was ${query.replace(/\s+/g, ' ')}. ${err}`);
   }
-}
\ No newline at end of file
+}
diff --git a/api/db/_getAuthenticators.ts b/api/db/_getAuthenticators.ts
index c718989..1286b90 100644
--- a/api/db/_getAuthenticators.ts
+++ b/api/db/_getAuthenticators.ts
@@ -5,12 +5,14 @@ import Authenticator from '../types/Authenticator';
 export default async function getAuthenticators<
   T extends keyof Authenticator
 >(userId: string, fields: T[]): Promise<Pick<Authenticator, T>[]> {
+  const query = `
+    SELECT ${fields.join(', ')}
+    FROM ${AUTHENTICATORS.name}
+    WHERE ${AUTHENTICATORS.fields.userId} = '${userId}';
+  `;
+
   try {
-    const result = await sql.query(`
-      SELECT ${fields.join(', ')}
-      FROM ${AUTHENTICATORS.name}
-      WHERE ${AUTHENTICATORS.fields.userId} = '${userId}';
-    `);
+    const result = await sql.query(query);
 
     return result.rows.map((row) => {
       const authenticator: Partial<Pick<Authenticator, T>> = {};
@@ -21,6 +23,6 @@ export default async function getAuthenticators<
       return authenticator as Pick<Authenticator, T>;
     });
   } catch (err) {
-    throw new Error(`Failed to get authenticators for user with ID ${userId} from database.`, err);
+    throw new Error(`Failed to get authenticators for user with ID ${userId} from database. Query was ${query.replace(/\s+/g, ' ')}. ${err}`);
   }
-}
\ No newline at end of file
+}
diff --git a/api/db/_getCurrentChallenge.ts b/api/db/_getCurrentChallenge.ts
index ec82e4d..af428cb 100644
--- a/api/db/_getCurrentChallenge.ts
+++ b/api/db/_getCurrentChallenge.ts
@@ -2,12 +2,14 @@ import { QueryResultRow, sql } from '@vercel/postgres';
 import { CURRENT_CHALLENGES } from './_tables';
 
 export default async function getCurrentChallenge(userId: string): Promise<string | null> {
+  const query = `
+    SELECT ${CURRENT_CHALLENGES.fields.currentChallenge}
+    FROM ${CURRENT_CHALLENGES.name}
+    WHERE ${CURRENT_CHALLENGES.fields.userId} = '${userId}';
+  `;
+
   try {
-    const result = await sql.query(`
-      SELECT ${CURRENT_CHALLENGES.fields.currentChallenge}
-      FROM ${CURRENT_CHALLENGES.name}
-      WHERE ${CURRENT_CHALLENGES.fields.userId} = '${userId}';
-    `);
+    const result = await sql.query(query);
 
     if (!result.rows.length) {
       return null;
@@ -19,6 +21,6 @@ export default async function getCurrentChallenge(userId: string): Promise<strin
     // See https://github.com/brianc/node-postgres/blob/master/packages/pg/lib/result.js
     return row[CURRENT_CHALLENGES.fields.currentChallenge];
   } catch (err) {
-    throw new Error(`Failed to get current challenge for user with ID ${userId} from database.`, err);
+    throw new Error(`Failed to get current challenge for user with ID ${userId} from database. Query was ${query.replace(/\s+/g, ' ')}. ${err}`);
   }
-}
\ No newline at end of file
+}
diff --git a/api/db/_getUser.ts b/api/db/_getUser.ts
index 30a879e..1188927 100644
--- a/api/db/_getUser.ts
+++ b/api/db/_getUser.ts
@@ -5,13 +5,15 @@ import { USERS } from './_tables';
 export default async function getUser(
   userId: string
 ): Promise<Pick<User, 'id' | 'displayName'> | null> {
+  const query = `
+    SELECT
+      ${USERS.fields.displayName}
+    FROM ${USERS.name}
+    WHERE ${USERS.fields.id} = '${userId}';
+  `;
+
   try {
-    const result = await sql.query(`
-      SELECT
-        ${USERS.fields.displayName}
-      FROM ${USERS.name}
-      WHERE ${USERS.fields.id} = '${userId}';
-    `);
+    const result = await sql.query(query);
 
     if (!result.rows.length) {
       return null;
@@ -24,6 +26,6 @@ export default async function getUser(
       displayName: row[USERS.fields.displayName],
     };
   } catch (err) {
-    throw new Error(`Failed to get user with ID ${userId} from database.`, err);
+    throw new Error(`Failed to get user with ID ${userId} from database. Query was ${query.replace(/\s+/g, ' ')}. ${err}`);
   }
-}
\ No newline at end of file
+}
diff --git a/api/db/_setCurrentChallenge.ts b/api/db/_setCurrentChallenge.ts
index 0858a56..58031b1 100644
--- a/api/db/_setCurrentChallenge.ts
+++ b/api/db/_setCurrentChallenge.ts
@@ -2,34 +2,36 @@ import { sql } from '@vercel/postgres';
 import { CURRENT_CHALLENGES } from './_tables';
 
 export default async function setCurrentChallenge(userId: string, challenge: string) {
-  try {
-    const insert = `
-      INSERT INTO ${CURRENT_CHALLENGES.name} (
-        ${CURRENT_CHALLENGES.fields.userId},
-        ${CURRENT_CHALLENGES.fields.currentChallenge}
-      ) VALUES (
-        ${userId},
-        ${challenge}
-      );
-    `;
+  const insert = `
+    INSERT INTO ${CURRENT_CHALLENGES.name} (
+      ${CURRENT_CHALLENGES.fields.userId},
+      ${CURRENT_CHALLENGES.fields.currentChallenge}
+    ) VALUES (
+      ${userId},
+      ${challenge}
+    );
+  `;
+
+  const update = `
+    UPDATE TABLE ${CURRENT_CHALLENGES.name}
+    SET ${CURRENT_CHALLENGES.fields.currentChallenge} = ${challenge}
+    WHERE ${CURRENT_CHALLENGES.fields.userId} = '${userId}';
+  `;
 
-    const update = `
-      UPDATE TABLE ${CURRENT_CHALLENGES.name}
-      SET ${CURRENT_CHALLENGES.fields.currentChallenge} = ${challenge}
-      WHERE ${CURRENT_CHALLENGES.fields.userId} = '${userId}';
-    `;
+  const query = `
+    BEGIN
+      IF EXISTS (
+        SELECT ${CURRENT_CHALLENGES.fields.userId} FROM ${CURRENT_CHALLENGES.name}
+        WHERE ${CURRENT_CHALLENGES.fields.userId} = ${userId}
+      )
+        ${update};
+      ELSE
+        ${insert};
+  `;
 
-    await sql.query(`
-      BEGIN
-        IF EXISTS (
-          SELECT ${CURRENT_CHALLENGES.fields.userId} FROM ${CURRENT_CHALLENGES.name}
-          WHERE ${CURRENT_CHALLENGES.fields.userId} = ${userId}
-        )
-          ${update};
-        ELSE
-          ${insert};
-    `);
+  try {
+    await sql.query(query);
   } catch (err) {
-    throw new Error(`Failed to set current challenge for user with ID ${userId} in database.`, err);
+    throw new Error(`Failed to set current challenge for user with ID ${userId} in database. Query was ${query.replace(/\s+/g, ' ')}. ${err}`);
   }
-}
\ No newline at end of file
+}
diff --git a/api/db/_updateAuthenticator.ts b/api/db/_updateAuthenticator.ts
index 45d094c..a7e06e3 100644
--- a/api/db/_updateAuthenticator.ts
+++ b/api/db/_updateAuthenticator.ts
@@ -6,18 +6,20 @@ import { AUTHENTICATORS } from './_tables';
 export default async function updateAuthenticator<
   T extends keyof Omit<Authenticator, 'id'>
 >(id: string, updatedFields: Pick<Authenticator, T>): Promise<void> {
-  try {
-    const assignments = Object.entries(updatedFields)
-      .filter(([key]) => key !== 'id') // never update the ID
-      .map(([key, value]) => `${key}=${value}`)
-      .join(', ');
+  const assignments = Object.entries(updatedFields)
+    .filter(([key]) => key !== 'id') // never update the ID
+    .map(([key, value]) => `${key}=${value}`)
+    .join(', ');
+
+  const query = `
+    UPDATE ${AUTHENTICATORS.name}
+    SET ${assignments}
+    WHERE ${AUTHENTICATORS.fields.id} = '${id}';
+  `;
 
-    await sql.query(`
-      UPDATE ${AUTHENTICATORS.name}
-      SET ${assignments}
-      WHERE ${AUTHENTICATORS.fields.id} = '${id}';
-    `);
+  try {
+    await sql.query(query);
   } catch (err) {
-    throw new Error(`Failed to update authenticator with ID ${id} in database.`, err);
+    throw new Error(`Failed to update authenticator with ID ${id} in database. Query was ${query.replace(/\s+/g, ' ')}. ${err}`);
   }
 }