Deno DeployからSupabase(PostgreSQL)に接続する

URIを使う場合

パスワードは、encodeURIComponent('YOUR_PASSWORD_HERE')を通すこと

import { serve } from "https://deno.land/std@0.177.0/http/server.ts";
import { Client } from "https://deno.land/x/postgres@v0.17.0/mod.ts";

const client = new Client('postgresql://postgres:[e-mail]:5432/postgres')
await client.connect();

serve(async (req: Request) => {
    const result = await client.queryObject("SELECT * FROM item")
    return new Response(JSON.stringify(result, null, 2))
});

URIを使わない場合

import { serve } from "https://deno.land/std@0.177.0/http/server.ts";
import { Client } from "https://deno.land/x/postgres@v0.17.0/mod.ts";

const client = new Client({
  user: "postgres",
  password: "YOUR_PASSWORD_HERE",
  database: "postgres",
  hostname: "db.gnrghrfzxujxijnkdssb.supabase.co"
});
await client.connect();

serve(async (req: Request) => {
    const result = await client.queryObject("SELECT * FROM item")
    return new Response(JSON.stringify(result, null, 2))
});

TLS connection failed with message: invalid peer certificate contents: invalid peer certificate: UnknownIssuer

URIを使う場合

パスワードは、encodeURIComponent('YOUR_PASSWORD_HERE')を通すこと

import { serve } from "https://deno.land/std@0.177.0/http/server.ts";
import { Client } from "https://deno.land/x/postgres@v0.17.0/mod.ts";

const client = new Client('postgresql://postgres:[e-mail]:5432/postgres?sslmode=disable')
await client.connect();

serve(async (req: Request) => {
    const result = await client.queryObject("SELECT * FROM item")
    return new Response(JSON.stringify(result, null, 2))
});

URIを使わない場合

なんかエラーが出る場合。証明書はダウンロードしてね

import { serve } from "https://deno.land/std@0.177.0/http/server.ts";
import { Client } from "https://deno.land/x/postgres@v0.17.0/mod.ts";

const cert = `-----BEGIN CERTIFICATE-----
MIIDxDCCAqygAwIBAgIUbLxMod62P2ktCiAkxnKJwtE9VPYwDQYJKoZIhvcNAQEL
BQAwazELMAkGA1UEBhMCVVMxEDAOBgNVBAgMB0RlbHdhcmUxEzARBgNVBAcMCk5l
dyBDYXN0bGUxFTATBgNVBAoMDFN1cGFiYXNlIEluYzEeMBwGA1UEAwwVU3VwYWJh
c2UgUm9vdCAyMDIxIENBMB4XDTIxMDQyODEwNTY1M1oXDTMxMDQyNjEwNTY1M1ow
azELMAkGA1UEBhMCVVMxEDAOBgNVBAgMB0RlbHdhcmUxEzARBgNVBAcMCk5ldyBD
YXN0bGUxFTATBgNVBAoMDFN1cGFiYXNlIEluYzEeMBwGA1UEAwwVU3VwYWJhc2Ug
Um9vdCAyMDIxIENBMIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAqQXW
QyHOB+qR2GJobCq/CBmQ40G0oDmCC3mzVnn8sv4XNeWtE5XcEL0uVih7Jo4Dkx1Q
DmGHBH1zDfgs2qXiLb6xpw/CKQPypZW1JssOTMIfQppNQ87K75Ya0p25Y3ePS2t2
GtvHxNjUV6kjOZjEn2yWEcBdpOVCUYBVFBNMB4YBHkNRDa/+S4uywAoaTWnCJLUi
cvTlHmMw6xSQQn1UfRQHk50DMCEJ7Cy1RxrZJrkXXRP3LqQL2ijJ6F4yMfh+Gyb4
O4XajoVj/+R4GwywKYrrS8PrSNtwxr5StlQO8zIQUSMiq26wM8mgELFlS/32Uclt
NaQ1xBRizkzpZct9DwIDAQABo2AwXjALBgNVHQ8EBAMCAQYwHQYDVR0OBBYEFKjX
uXY32CztkhImng4yJNUtaUYsMB8GA1UdIwQYMBaAFKjXuXY32CztkhImng4yJNUt
aUYsMA8GA1UdEwEB/wQFMAMBAf8wDQYJKoZIhvcNAQELBQADggEBAB8spzNn+4VU
tVxbdMaX+39Z50sc7uATmus16jmmHjhIHz+l/9GlJ5KqAMOx26mPZgfzG7oneL2b
VW+WgYUkTT3XEPFWnTp2RJwQao8/tYPXWEJDc0WVQHrpmnWOFKU/d3MqBgBm5y+6
jB81TU/RG2rVerPDWP+1MMcNNy0491CTL5XQZ7JfDJJ9CCmXSdtTl4uUQnSuv/Qx
Cea13BX2ZgJc7Au30vihLhub52De4P/4gonKsNHYdbWjg7OWKwNv/zitGDVDB9Y2
CMTyZKG3XEu5Ghl1LEnI3QmEKsqaCLv12BnVjbkSeZsMnevJPs1Ye6TjjJwdik5P
o/bKiIz+Fq8=
-----END CERTIFICATE-----`

const client = new Client({
  user: "postgres",
  password: "YOUR_PASSWORD_HERE",
  database: "postgres",
  hostname: "db.gnrghrfzxujxijnkdssb.supabase.co",
  tls: { caCertificates: [cert] },
});
await client.connect();

serve(async (req: Request) => {
    const result = await client.queryObject("SELECT * FROM item")
    return new Response(JSON.stringify(result, null, 2))
});