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

[jschuur]

NOTE FROM MAINTAINERS

The way to use @vercel/postgres locally is documented here: https://vercel.com/docs/storage/vercel-postgres/local-development

---

I thought I could use Vercel Postgres with a local DB for offline development with a custom connection string that points to a local DB...

import { createPool, sql } from '@vercel/postgres';
import { drizzle } from 'drizzle-orm/vercel-postgres';

export const db = drizzle(
  process.env.NODE_ENV === 'production'
    ? sql
    : createPool({
        connectionString: process.env.POSTGRES_URL,
      }),
  { logger: true }
);

...but this doesn't work:

[VercelPostgresError]: VercelPostgresError - 'invalid_connection_string': This connection string is meant to be used with a direct connection. Make sure to use a pooled connection string or try `createClient()` instead.

The initial issue seems to be that the error is triggered if you don't provide a pooled URL, and that check is hardcoded for the presence of -pooler. in the connection string, due to the URLs Vercel uses:

export function isPooledConnectionString(connectionString: string): boolean {
  return connectionString.includes('-pooler.');
}

If I try and bypass this with a local URL like 'postgresql://jschuur:@localhost:5432/learnchineseclub?foo=-pooler.' then I get a new error:

The database host is 'localhost', which is the default host when none is set. If that's intentional, please ignore this warning. If not, perhaps an environment variable has not been set, or has not been passed to the library?
- error uncaughtException: Error: connect ECONNREFUSED ::1:443
    at TCPConnectWrap.afterConnect [as oncomplete] (node:net:1494:16)
    at TCPConnectWrap.callbackTrampoline (node:internal/async_hooks:130:17) {
  digest: undefined
}

Is it currently possible to do local development without using a cloud hosted Vercel Postgres DB in both locations this way? Considering the 1 database limit on the free tier (and low other limits), this makes development rather difficult.

[elliott-with-the-longest-name-on-github]

There's currently a PR open (#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.

[jschuur]

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);
}

[elliott-with-the-longest-name-on-github]

@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?

[jschuur]

@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?

Would love to see this post too, @Schniz!

[klaussner]

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.

[elliott-with-the-longest-name-on-github]

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

[dferber90]

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

[vvo]

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!

[webhype]

Is there a tutorial WITHOUT using Docker?

[prplecake]

@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

[murderteeth]

@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?

[GustavoOS]

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