Next.js fullstack app connect to Cloud SQL PostgreSQL on Google Cloud Run

Question

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..

Answer 1

This could be one possible scenario and/or solution.

The PostgreSQL standard requires a .s.PGSQL.5432 suffix in the socket path as documented in the official public doc here. Some libraries apply this suffix automatically, but others require you to specify the socket path as follows:

/cloudsql/INSTANCE_CONNECTION_NAME/.s.PGSQL.5432

This seems to be missing in case of yours:

INSTANCE_UNIX_SOCKET=/cloudsql/my-project:europe-north1:my-db

So, adding the suffix .s.PGSQL.5432 for your Cloud SQL instance unix socket path as below may resolve the issue for you.

INSTANCE_UNIX_SOCKET=/cloudsql/my-project:europe-north1:my-db/.s.PGSQL.5432