I am looking at the documentation for connecting an Next.JS application in Google Cloud Run to PostgreSQL database at https://github.com/GoogleCloudPlatform/nodejs-docs-samples/blob/main/cloud-sql/postgres/knex/README.md.

I have done the following:

• Created a PostgreSQL database
• Created an application in Next.js that I have lunched at Google Cloud Run
• In Google Cloud Run application I have added Cloud SQL Connections to my-project:europe-north1:my-db
• In Google Cloud Run application I have added Environment variables:
  • INSTANCE_UNIX_SOCKET=/cloudsql/my-project:europe-north1:my-db
  • INSTANCE_CONNECTION_NAME=my-project:europe-north1:my-db
  • DB_NAME=postgres
  • DB_USER=myuser
  • DB_PASS=my-user-secret-password
  • DB_PORT=5432
  • DB_HOST=IP_TO_DB

I am unsure how to use connect to the database as I get the following error in Cloud Run:

⨯ Error: connect ECONNREFUSED 127.0.0.1:5432
    at TCPConnectWrap.afterConnect [as oncomplete] (node:net:1607:16)
    at TCPConnectWrap.callbackTrampoline (node:internal/async_hooks:130:17) {
  errno: -111,
  code: 'ECONNREFUSED',
  syscall: 'connect',
  address: '127.0.0.1',
  port: 5432
}

My app consists of a page where users can sign up. I have tried to make it as simple as possible in order to get the connection to database fixed before I expand it.

Sign up page

sql/1u.sql

create extension if not exists citext;

create table if not exists public.users (
  id bigserial primary key,
  username citext unique not null,
  password text,
  created_at timestamp default now(),
  updated_at timestamp default now()
);

app/(public)/signup/page.tsx

import Form from "./form";

export default async function SignUp() {
  return (
    <div>
      <Form />
    </div>
  );
}

app/(public)/signup/form.tsx

"use client";
import React, { FormEvent, useState } from "react";

function Form() {
  const [username, setUsername] = useState<undefined | string>("");
  const [password, setPassword] = useState<undefined | string>("");
  const [confirmPassword, setConfirmPassword] = useState<undefined | string>(
    ""
  );
  const [errors, setErrors] = useState<string[]>([]);

  async function handleSubmit(e: FormEvent) {
    e.preventDefault();
    setErrors([]);

    if (password != confirmPassword) {
      const newErrors = [];
      newErrors.push("Passwords do not match.");
      setErrors(newErrors);
      return;
    }

    const res = await fetch("/api/signup", {
      method: "POST",
      body: JSON.stringify({ username, password }),
    });
    if (res.ok) {
      window.location.href = "/signin";
    } 
    else {
      alert("sign up failed");
    }
  }

  return (
    <form onSubmit={handleSubmit} className="flex flex-col gap-2 p-5 max-w-xs w-full bg-slate-800 rounded-lg">

      <div className="text-center">
        <h3 className="font-semibold">Sign Up</h3>
      </div>

      <div className="my-3">
        <hr />
      </div>

      <div>
        <div className="flex flex-col gap-2">
          <label>Username</label>
          <input className="text-black p-3 border border-slate-700 rounded-lg" type="text" onChange={(e) => setUsername(e.target.value)} value={username} id="username" placeholder="Username" required />
        </div>
      </div>

      <div className="flex flex-col gap-2 mt-4">
        <label>Password</label>
        <input className="text-black p-3 border border-slate-700 rounded-lg" type="password" onChange={(e) => setPassword(e.target.value)} value={password} id="password" placeholder="Password" required />
      </div>

      <div className="flex flex-col gap-2 mt-4">
        <label>Confirm Password</label>
        <input className="text-black p-3 border border-slate-700 rounded-lg" type="password" onChange={(e) => setConfirmPassword(e.target.value)} value={confirmPassword} id="confirm-password" placeholder="Confirm Password" required />
      </div>

      <button type="submit" className="mt-4 bg-slate-900 text-white p-3 rounded-lg">Sign Up</button>
      
      {errors.map((error) => {
        return (
          <div key={error} className="p-4 mb-4 text-sm text-red-800 rounded-lg bg-red-50 dark:bg-gray-800 dark:text-red-400 mt-4" role="alert">
            <span className="font-medium">{error}</span>
          </div>
        );
      })}
    </form>
  );
}

export default Form;

app/api/signup/route.tsx

import { sql } from "@/db";
import bcrypt from "bcrypt";
import { NextResponse } from "next/server";

export async function POST(request: Request){
    const json = await request.json();

    // Does the username exists?
    const res = await sql(
        "SELECT id, username FROM users WHERE username ILIKE $1",
        [json.username]
    );

    if (res.rowCount! > 0) {
        return NextResponse.json({ error: "user already exists" }, { status: 400 });
    }

    // Genereate user
    const saltRounds = 10;
    const hash = await bcrypt.hash(json.password, saltRounds);

    await sql("INSERT INTO users (username, password) VALUES ($1, $2)", [
            json.username,
            hash,
            ]);

    return NextResponse.json({ msg: "registration success" }, { status: 201 });
}

db.ts

import { Client, QueryResult } from "pg";
import { loadEnvConfig } from "@next/env";

const projectDir = process.cwd();
loadEnvConfig(projectDir)

// Generate a Postgres client
export async function getClient(): Promise<Client>{
    
    // Client with URL
    if (process.env.DB_URL) {
        const client = new Client({
          connectionString: process.env.DB_URL + "?sslmode=require",
        });
        return client;
    }

    // Client with username, host, database, password
    const client = new Client({
        user: process.env.DB_USER,
        host: process.env.DB_HOST,
        database: process.env.DB_NAME,
        password: process.env.DB_PASS,
        port: parseInt(process.env.DB_PORT!)
    });
    return client;
}

// Handle connection, SQL and end the connection
export async function sql(sql: string, values?: Array<any>): Promise<QueryResult<any>> {
    const client = await getClient();
    await client.connect();
    const res = await client.query(sql, values);
    await client.end();
    return res;
}

I think I need to change db.ts to connect-unix.js from the Google Example, however I am a bit lost on how to do this..