How to render a table with data from 2 databases

[uvinw]

As the title suggests I'd like to show data from 2 Postgres databases in a single application's "table" component.

The docs suggest this is possible but I'd couldn't find a detailed guide on how to approach this.

I suspect I cannot use a single Postgres Query but must instead use a JS/Python script that connects to both databases, runs the 2 queries and builds the result JSON.

I tried this from Windmill Hub but the query fails.

import {
  pgClient,
  type Sql,
} from "https://deno.land/x/windmill@v1.88.1/mod.ts";

type Postgresql = {
  host: string;
  port: number;
  user: string;
  dbname: string;
  sslmode: string;
  password: string;
};
export async function main(db: Postgresql, query: Sql = "SELECT * FROM test;") {
  if (!query) {
    throw Error("Query must not be empty.");
  }
  const { rows } = await pgClient(db).queryObject(query);
  return rows;
}

Error: The certificate used to secure the TLS connection is invalid.
    at Connection.#startup (https://deno.land/x/postgres@v0.17.0/connection/connection.ts:392:19)
    at eventLoopTick (ext:core/01_core.js:183:11)
    at async Connection.startup (https://deno.land/x/postgres@v0.17.0/connection/connection.ts:491:11)
    at async Connection.query (https://deno.land/x/postgres@v0.17.0/connection/connection.ts:943:7)
    at async Client.#executeQuery (https://deno.land/x/postgres@v0.17.0/client.ts:245:12)
    at async Client.queryObject (https://deno.land/x/postgres@v0.17.0/client.ts:433:12)
    at async main (file:///tmp/windmill/wk-default-2jl9h-adu7o/018d553b-5bf9-2806-9f3a-323e9e266837/main.ts:18:20)
    at async run (file:///tmp/windmill/wk-default-2jl9h-adu7o/018d553b-5bf9-2806-9f3a-323e9e266837/wrapper.ts:14:20)

I have tested the DB connection with a simple query on a table component and had no issues so the connection issue is due to this script.
I see that the Postgresql type on the deno pgClient offered by Windmill does not have a field for the ca-cert. Perhaps there is a newer version? Or should I not use this pgClient at all?

Thanks!

[andrewlreeve]

@uvinw if you need to use a ca-cert the below script works with the postgres resource.

import { Client } from "https://deno.land/x/postgres@v0.17.0/mod.ts";
import * as wmill from "npm:windmill-client@1";

export async function main(sql:string) {
  return await pgQuery(sql);
}

export async function pgQuery(sql: string) {
  const dbConfig = await wmill.getResource("<RESOURCE_FOLDER>");

  const clientOptions = {
    hostname: dbConfig.host,
    port: dbConfig.port,
    user: dbConfig.user,
    database: dbConfig.dbname,
    password: dbConfig.password,
    host_type: "tcp",
    tls: {
      enabled: true,
      enforce: true,
      caCertificates: [dbConfig.root_certificate_pem],
    },
  };

  const client = new Client(clientOptions);
  await client.connect();

  const res = await client.queryObject(sql);

  await client.end();

  return res.rows;
}

[uvinw]

Thanks @andrewlreeve. Exactly what I was looking for.