storage: Trying to use a local DB with Vercel Postgres fails due to strict pool URL check

@klaussner Yeah, but then you have to conditionally use a different client for dev and prod, which is bound to introduce issues.

I feel relying on the solution in https://gal.hagever.com/posts/running-vercel-postgres-locally is bound to introduce issues… is there a plan\possibility for better localhost support?

seriously? just to get postgres running locally?

The localhost PR was merged and there’s a blogpost explaining how to use it: https://gal.hagever.com/posts/running-vercel-postgres-locally

Open a new issue if you’re still having problems with localhost postgres, thanks!

This fix does not apply to Drizzle using @vercel/postgres

However, there’s some additional setup required even after that PR is merged – Neon’s client (the client we use internally) uses websockets instead of regular tcp sockets, so a websocket proxy is required

Perhaps it would be better to use pg directly instead of @neondatabase/serverless when connecting to a local database. As I understand it, the WebSocket connection is only required for compatibility with edge runtimes, and setting up a local WebSocket proxy seems unnecessarily complicated.

For everyone here, I made it work using only @vercel/postgres. I’ve done the following:

1. I’ve followed the steps up until the docker creation: https://gal.hagever.com/posts/running-vercel-postgres-locally

services:
  postgres:
    image: "postgres:latest"
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
      POSTGRES_DB: postgres
    ports:
      - "5432:5432"
  pg_proxy:
    image: ghcr.io/neondatabase/wsproxy:latest
    environment:
      APPEND_PORT: "postgres:5432"
      ALLOW_ADDR_REGEX: ".*"
      LOG_TRAFFIC: "true"
    ports:
      - "5433:80"
    depends_on:
      - postgres

1. Run the docker

$ docker-compose up

1. Make sure your env is set, I use .env.local

# postgress OFFLINE
POSTGRES_URL="postgresql://postgres:postgres@localhost:5432/postgres"
POSTGRES_URL_NON_POOLING="postgresql://postgres:postgres@localhost:5432/postgres"
POSTGRES_PRISMA_URL="postgresql://postgres:postgres@localhost:5432/postgres?pgbouncer=true&connect_timeout=15"
POSTGRES_HOST="localhost"
POSTGRES_USER="postgres"
POSTGRES_PASSWORD="postgres"
POSTGRES_DATABASE="postgres"

1. I have a command db:migrate:dev that basically run a script to generate tables and db columns.

$ npm run db:migrate:dev

and let’s say you have scripts/migrate.ts, In my code I make sure after i run createClient, I override Socket config.

/**
 * @description
 * Main function to execute the migration.
 */
async function main() {
  /* ... */
  if (!isEnvLikeProduction) {
    dotenv.config()
    dotenv.config({ path: '.env.local' })
  }

  const client = createClient()

  // NOTE: The part where I make `vercel/postgres` work.
  if (!isEnvLikeProduction) {
    // Set the WebSocket proxy to work with the local instance.
    client.neonConfig.wsProxy = (host) => `${host}:5433/v1`
    // Disable all authentication and encryption.
    client.neonConfig.useSecureWebSocket = false
    client.neonConfig.pipelineTLS = false
    client.neonConfig.pipelineConnect = false
  }

  await client.connect()
  try {
    await createUserTable(client)
  } finally {
    await client.end()
  }
}

1. running the migrate command and it works on my side.

postgres=# \dt
         List of relations
 Schema | Name  | Type  |  Owner   
--------+-------+-------+----------
 public | users | table | postgres
(1 row)

Currently, I’m still figuring out how sql would run locally as well.

I believe this is the blog post that was mentioned earlier https://gal.hagever.com/posts/running-vercel-postgres-locally

For my Drizzle specific use case, I ended up just initialising based on NODE_ENV quick and dirty like this:

import { sql as sqlVercel } from '@vercel/postgres';
import { eq, sql as sqlDrizzle } from 'drizzle-orm';
import { drizzle as drizzleNode } from 'drizzle-orm/node-postgres';
import { migrate as migrateNode } from 'drizzle-orm/node-postgres/migrator';
import { drizzle as drizzleVercel } from 'drizzle-orm/vercel-postgres';
import { migrate as migrateVercel } from 'drizzle-orm/vercel-postgres/migrator';

import { Pool } from 'pg';

import { languageCard, NewLanguageCard } from './schema';

const db =
  process.env.NODE_ENV === 'production'
    ? drizzleVercel(sqlVercel)
    : drizzleNode(new Pool({ connectionString: process.env.POSTGRES_URL }));

export async function dbMigrate() {
  if (process.env.NODE_ENV === 'production')
    await migrateVercel(db, { migrationsFolder: './drizzle' });
  else await migrateNode(db, { migrationsFolder: './drizzle' });
}

No doubt this will affect my bundle size, but the import costs don’t seem to be too high.

I had to use environment specific methods for migrate in the code above, but Drizzle’s sql seemed to work in both environments when I needed to add randomisations e.g., so it looks like it might turn into a bit more overhead as my current project grows:

export function getRandomCards() {
  return db
    .select()
    .from(languageCard)
    .orderBy(sqlDrizzle`random()`)
    .limit(5);
}

@webhype assuming you already have a local postgres installation, just run wsproxy without docker:

git pull https://github.com/neondatabase/wsproxy.git
cd wsproxy
APPEND_PORT=:5432 LISTEN_PORT=:5433 LOG_TRAFFIC=true ALLOW_ADDR_REGEX=".*" go run main.go

The localhost PR was merged and there’s a blogpost explaining how to use it: https://gal.hagever.com/posts/running-vercel-postgres-locally

Open a new issue if you’re still having problems with localhost postgres, thanks!

@Schniz

The localhost PR has merged – I know you were writing a blog post about how to set all of that up. Can you share it here and close the issue when you do?

<picture data-single-emoji=":thanku:" title=":thanku:"></picture>

There’s currently a PR open (https://github.com/vercel/storage/pull/118) for this! However, there’s some additional setup required even after that PR is merged – Neon’s client (the client we use internally) uses websockets instead of regular tcp sockets, so a websocket proxy is required (here’s theirs: https://github.com/neondatabase/wsproxy).

I’m not sure how to set it up (haven’t needed to), but I know @Schniz has, so maybe he can help out.