Local Pooler DB in supabase not working

[15077693d]

This is my local POSTGRES_URL:
POSTGRES_URL='postgresql://postgres.pooler-dev:postgres@localhost:54329/postgres?workaround=supabase-pooler.vercel'

Port 54329 is Pooler DB:

1. pnpm db:push is loading to long
2. Cannot open app with the error below

The error message:
>>> tRPC Error on 'post.all' TRPCError at getTRPCErrorFromUnknown (webpack-internal:///(rsc)/../../node_modules/@trpc/server/dist/unstable-core-do-not-import/error/TRPCError.mjs:55:23) ... 7 lines matching cause stack trace ... at async eval (webpack-internal:///(rsc)/../../node_modules/@trpc/server/dist/unstable-core-do-not-import/stream/jsonl.mjs:145:30) { cause: UnknownCauseError at getCauseFromUnknown (webpack-internal:///(rsc)/../../node_modules/@trpc/server/dist/unstable-core-do-not-import/error/TRPCError.mjs:39:21) at new TRPCError (webpack-internal:///(rsc)/../../node_modules/@trpc/server/dist/unstable-core-do-not-import/error/TRPCError.mjs:67:23) at getTRPCErrorFromUnknown (webpack-internal:///(rsc)/../../node_modules/@trpc/server/dist/unstable-core-do-not-import/error/TRPCError.mjs:55:23) at callRecursive (webpack-internal:///(rsc)/../../node_modules/@trpc/server/dist/unstable-core-do-not-import/procedureBuilder.mjs:172:97) at process.processTicksAndRejections (node:internal/process/task_queues:105:5) at async eval (webpack-internal:///(rsc)/../../packages/api/src/trpc.ts:97:20) at async callRecursive (webpack-internal:///(rsc)/../../node_modules/@trpc/server/dist/unstable-core-do-not-import/procedureBuilder.mjs:151:24) at async procedure (webpack-internal:///(rsc)/../../node_modules/@trpc/server/dist/unstable-core-do-not-import/procedureBuilder.mjs:184:24) at async eval (webpack-internal:///(rsc)/../../node_modules/@trpc/server/dist/unstable-core-do-not-import/http/resolveResponse.mjs:262:30) at async eval (webpack-internal:///(rsc)/../../node_modules/@trpc/server/dist/unstable-core-do-not-import/http/resolveResponse.mjs:437:45) at async eval (webpack-internal:///(rsc)/../../node_modules/@trpc/server/dist/unstable-core-do-not-import/stream/jsonl.mjs:145:30) { error: AggregateError [ECONNREFUSED]: at internalConnectMultiple (node:net:1139:18) at afterConnectMultiple (node:net:1712:7) at TCPConnectWrap.callbackTrampoline (node:internal/async_hooks:130:17) { code: 'ECONNREFUSED', [errors]: [Array] }, target: WebSocket { _events: [Object: null prototype], _eventsCount: 4, _maxListeners: undefined, _binaryType: 'arraybuffer', _closeCode: 1006, _closeFrameReceived: false, _closeFrameSent: false, _closeMessage: <Buffer >, _closeTimer: null, _errorEmitted: true, _extensions: {}, _paused: false, _protocol: '', _readyState: 3, _receiver: null, _sender: null, _socket: null, _bufferedAmount: 0, _isServer: false, _redirects: 0, _autoPong: true, _url: 'wss://localhost/v2', _req: null, [Symbol(shapeMode)]: false, [Symbol(kCapture)]: false }, type: 'error' }, code: 'INTERNAL_SERVER_ERROR' }

[Clueed]

I wanted to set up a local database to avoid relying on Supabase. I got burnt on a previous project after it became inactive on supabase and I can't get it running again because I never set up a local environment.

I've tried various tutorials (Supabase CLI, @neondatabase/serverless, pg_proxy, etc.), but nothing has worked. I suspect the issues stem from the pooling setup and the workaround=supabase-pooler.vercel configuration.

This tutorial looks the most promising: https://www.thisdot.co/blog/configure-your-project-with-drizzle-for-local-and-deployed-databases — it suggests using a different driver and a standard local Postgres instance. Unfortunately, the setup results in type errors with the latest version of Drizzle.

I would love for there to be a 'blessed' local DB setup as part of this repo.

Here's the messy workaround I've landed on. It works for now, but I haven't tested it in a production setting.

@acme/db/src/index.ts

import { sql } from "@vercel/postgres";
import { drizzle as drizzlePostgres } from "drizzle-orm/postgres-js";
import {
  drizzle as drizzleVercel,
  type VercelPgDatabase,
} from "drizzle-orm/vercel-postgres";
import postgres from "postgres";
import * as schema from "./schema";

const isDev = process.env.NODE_ENV === "development";

const createDBClient = (): VercelPgDatabase<typeof schema> => {
  if (isDev) {
    const client = postgres(process.env.POSTGRES_URL!);
    return drizzlePostgres({
      client,
      schema,
      casing: "snake_case",
    }) as unknown as VercelPgDatabase<typeof schema>;
  }

  return drizzleVercel({
    client: sql,
    schema,
    casing: "snake_case",
  });
};

export const db = createDBClient();

docker-compose.yml

services:
  db:
    image: postgres
    restart: always
    environment:
      POSTGRES_PASSWORD: postgres
      POSTGRES_USER: postgres
      POSTGRES_DB: postgres
    volumes:
      - pgdata:/var/lib/postgresql/data
    ports:
      - "5432:5432"
      
volumes:
  pgdata: