database ts

db.ts

// USAGE:

async function getUser(userId: string): Promise<User> {
  return getSqlClient(async (SQL, poolClient) => {
    // await poolClient.query()
    const result = await SQL`SELECT * FROM users WHERE user_id = ${userId}`
    return result.rows[0]
  })
}

async function createUser(name: string): Promise<string> {
  return getSqlClient(async (SQL, client) => {
    try {
      await client.query('BEGIN')
      const result = await SQL`INSERT INTO users(name) VALUES(${name}) RETURNING user_id`
      
      // ...more queries

      await client.query('COMMIT')
      return result.rows[0].user_id
    } catch (e) {
      await client.query('ROLLBACK')
      throw e
    }
  })
}


///////////////////////////////////////////////////////////////////////////////////


import { Pool, PoolClient, QueryResult } from "pg";

import config = require("../config");
import sql = require("sql-template-strings");

if (!config.db) throw new Error("Cannot load config.db");

export const pool = new Pool(config.db);

export async function getClient<T>(
  cb: (client: PoolClient) => Promise<T>
): Promise<T> {
  const client = await pool.connect();

  try {
    return await cb(client);
  } catch (e) {
    console.error(e);
    throw e;
  } finally {
    client.release();
  }
}

export async function getSqlClient<T>(
  cb: (
    SQL: (strings: any, ...values: any[]) => QueryResult,
    client: PoolClient
  ) => Promise<T>
): Promise<T> {
  const client = await pool.connect();

  try {
    const x = function() {
      return client.query(sql.SQL.apply(null, arguments as any));
    };

    return await cb(x as any, client);
  } finally {
    client.release();
  }
}