Local postgress & proxy server as Docker containers

Readme.md

Database proxy

docker-compose up -d

client.ojs

db.query("select * from pg_tables;");

db = new OfflineDatabaseClient({
  proxy: "http://localhost:3000/"
});

class OfflineDatabaseClient {
  constructor(config) {
    Object.defineProperties(this, {
      _db: {
        value: { exec: this.exec, ...config }
      }
    });
  }
  async exec(sql, params) {
    var resp = await fetch(this.proxy, {
      body: JSON.stringify({
        sql: sql,
        params: params
      }),
      method: "POST"
    });
    var json = await resp.json();
    return json.data;
  }
  async query(sql, params) {
    return (await this._db).exec(sql, params);
  }
  async queryRow(sql, params) {
    return (await this.query(sql, params))[0];
  }
  async explain(sql, params) {
    const rows = await this.query(`EXPLAIN QUERY PLAN ${sql}`, params);
    const text = rows.map(row => row.detail).join("\n");
    const pre = document.createElement("PRE");
    pre.className = "observablehq--inspect";
    pre.appendChild(document.createTextNode(text));
    return pre;
  }
  async describe(object) {
    if (object !== undefined) {
      const [row] = await this.query(`SELECT * FROM '${object}' LIMIT 1`);
      const value = Object.entries(row).map(([column_name, value]) => ({
        column_name,
        data_type:
          typeof value === "string"
            ? "character varying"
            : typeof value === "number"
            ? "integer"
            : undefined,
        column_default: null,
        is_nullable: "YES"
      }));
      const table = html`
        <table>
          <thead>
            <tr>
              <th>column_name</th>
              <th>data_type</th>
              <th>column_default</th>
              <th>is_nullable</th>
            </tr>
          </thead>
          <tbody>
            ${value.map(
              ({ column_name, data_type, column_default, is_nullable }) => html`
              <tr>
                <td>${column_name}</td>
                <td>${data_type}</td>
                <td>${column_default}</td>
                <td>${is_nullable}</td>
              </tr>
            `
            )}
          </tbody>
        </table>
      `;
      table.value = value;
      return table;
    } else {
      const rows = await this.query(
        `SELECT name FROM sqlite_master WHERE type = 'table'`
      );
      const table = html`
        <table>
          <thead>
            <tr>
              <th>name</th>
            </tr>
          </thead>
          <tbody>
            ${rows.map(
              ({ name }) => html`
              <tr>
                <td>${name}</td>
              </tr>
            `
            )}
          </tbody>
        </table>
      `;
      table.value = [{ table_schema: "public", table_name: "names" }];
      return table;
    }
  }
};

docker-compose.yml

version: "3.7"
services:
  postgres:
    image: postgres:9.6.23-alpine3.14
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
    ports: 
      - '5432:5432'
    volumes:
      - proxy-db:/var/lib/postgresql/data
      

  local-proxy:
    build:
      context: .
    depends_on:
      - postgres
    ports:
      - '3000:80'
        
volumes:
  proxy-db:

Dockerfile

FROM node:erbium-alpine3.14

WORKDIR /usr/src/app
COPY . .
RUN npm install

EXPOSE 80
CMD node index.js

index.js

const { json } = require("micro");
const { Pool } = require("pg");
const { parse } = require("url");
const QueryStream = require("pg-query-stream");
const JSONStream = require("JSONStream");
const Cursor = require("pg-cursor");
const express = require("express");
const cors = require("cors");
const app = express();
const port = 80;

async function query(req, res) {
  const { sql, params } = await json(req);
  const pool = new Pool({
    connectionString: "postgresql://postgres:postgres@postgres:5432",
    ssl: false,
  });
  const client = await pool.connect();
  try {
    const queryStream = new QueryStream(sql, params);
    const stream = await client.query(queryStream);

    await new Promise((resolve, reject) => {
      stream
        .on("end", resolve)
        .on("error", reject)
        .pipe(JSONStream.stringify(`{"data":[`, ",", "]"))
        .pipe(res, { end: false });
    });

    const schema = {
      type: "array",
      items: {
        type: "object",
        properties: queryStream.cursor._result.fields.reduce(
          (schema, { name, dataTypeID }) => (
            (schema[name] = dataTypeSchema(dataTypeID)), schema
          ),
          {}
        ),
      },
    };
    res.end(`,"schema":${JSON.stringify(schema)}}`);
  } finally {
    client.release();
  }
}

// https://www.postgresql.org/docs/9.6/datatype.html
const array = ["null", "array"],
  boolean = ["null", "boolean"],
  integer = ["null", "integer"],
  number = ["null", "number"],
  object = ["null", "object"],
  string = ["null", "string"];

function dataTypeSchema(dataTypeID) {
  switch (dataTypeID) {
    // https://github.com/brianc/node-pg-types/blob/master/lib/textParsers.js#L166
    case 20: //parseBigInteger // int8
      return { type: string, bigint: true };
    case 21: //parseInteger // int2
    case 23: //parseInteger // int4
    case 26: //parseInteger // oid
      return { type: integer };
    case 700: //parseFloat // float4/real
    case 701: //parseFloat // float8/double
      return { type: number };
    case 16: //parseBool
      return { type: boolean };
    case 1082: //parseDate // date
    case 1114: //parseDate // timestamp without timezone
    case 1184: //parseDate // timestamp
      return { type: string, date: true };
    case 600: //parsePoint // point
      return { type: object };
    case 651: //parseStringArray // cidr[]
      return { type: array, items: { type: string } };
    case 718: //parseCircle // circle
      return { type: object };
    case 1000: //parseBoolArray
      return { type: array, items: { type: boolean } };
    case 1001: //parseByteAArray
      return { type: array, items: { type: object, buffer: true } };
    case 1005: //parseIntegerArray // _int2
    case 1007: //parseIntegerArray // _int4
    case 1028: //parseIntegerArray // oid[]
      return { type: array, items: { type: integer } };
    case 1016: //parseBigIntegerArray // _int8
      return { type: array, items: { type: string, bigint: true } };
    case 1017: //parsePointArray // point[]
      return { type: array, items: { type: object } };
    case 1021: //parseFloatArray // _float4
    case 1022: //parseFloatArray // _float8
    case 1231: //parseFloatArray // _numeric
      return { type: array, items: { type: number } };
    case 1014: //parseStringArray //char
    case 1015: //parseStringArray //varchar
    case 1008: //parseStringArray
    case 1009: //parseStringArray
    case 1040: //parseStringArray // macaddr[]
    case 1041: //parseStringArray // inet[]
      return { type: array, items: { type: string } };
    case 1115: //parseDateArray // timestamp without time zone[]
    case 1182: //parseDateArray // _date
    case 1185: //parseDateArray // timestamp with time zone[]
      return { type: array, items: { type: string, date: true } };
    case 1186: //parseInterval
      return { type: object };
    case 1187: //parseIntervalArray
      return { type: array, items: { type: object } };
    case 17: //parseByteA
      return { type: object, buffer: true };
    case 114: //JSON.parse.bind(JSON) // json
    case 3802: //JSON.parse.bind(JSON) // jsonb
      return { type: object };
    case 199: //parseJsonArray // json[]
    case 3807: //parseJsonArray // jsonb[]
      return { type: array, items: { type: object } };
    case 3907: //parseStringArray // numrange[]
    case 2951: //parseStringArray // uuid[]
    case 791: //parseStringArray // money[]
    case 1183: //parseStringArray // time[]
    case 1270: //parseStringArray // timetz[]
      return { type: array, items: { type: string } };
    // https://github.com/brianc/node-pg-types/blob/master/lib/binaryParsers.js#L236
    case 1700: //parseNumeric
      return { type: number };
    case 25: //parseText
    default:
      return { type: string };
  }
}

app.use(
  cors({
    origin: "*",
  })
);

app.post("/", (req, res) => {
  query(req, res);
});

app.listen(port, () => {
  console.log(`DB Proxy app listening on ${port}`);
});

package.json

{
  "name": "wishyoulization-database-proxy",
  "version": "0.0.1",
  "main": "index.js",
  "dependencies": {
    "JSONStream": "1.3.5",
    "cors": "^2.8.5",
    "express": "^4.17.1",
    "micro": "9.3.4",
    "pg": "8.5.1",
    "pg-cursor": "2.5.2",
    "pg-query-stream": "4.0.0",
    "url": "0.11.0"
  }
}