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

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.

About this issue

  • Original URL
  • State: closed
  • Created 4 years ago
  • Reactions: 22
  • Comments: 52 (4 by maintainers)

Most upvoted comments

seriously? just to get postgres running locally?

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

@vvo would you please consider re-opening this issue as this was never properly solved?

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

Hi there. To follow up on the various ways to use @vercel/postgres pointing to a local Postgres server, I created a repository that shows how to do it: https://github.com/vvo/local-vercel-postgres-development.

The overview is to use Docker to start a local Postgres instance coupled with a WebSockets proxy and configure @vercel/postgres to use it. The WebSockets proxy is needed due to the nature of serverless databases and how clients will connect to them. To mimic most of the production it’s recommended to go this way.

Here is the minimal implementation required and one I recommend:

package.json

{
  "dependencies": {
    "@vercel/postgres": "^0.9.0",
  },
  "devDependencies": {
    "@neondatabase/serverless": "^0.9.3"
  }
}

docker-compose.yml

services:
  postgres:
    image: postgres:16.4
    environment:
      POSTGRES_PASSWORD: password
    # Expose the port to the host machine so you can use a local client to connect/inspect to the database
    ports:
      - "54320:5432"
  pg_proxy:
    image: ghcr.io/neondatabase/wsproxy:latest
    environment:
      APPEND_PORT: "postgres:5432"
      ALLOW_ADDR_REGEX: ".*"
      LOG_TRAFFIC: "true"
    ports:
      - "54330:80"
    depends_on:
      - postgres

src/db.ts

import { neonConfig } from "@neondatabase/serverless";

// This is the only code that will be different in development
if (process.env.VERCEL_ENV === "development") {
  neonConfig.wsProxy = (host) => `${host}:54330/v1`;
  neonConfig.useSecureWebSocket = false;
  neonConfig.pipelineTLS = false;
  neonConfig.pipelineConnect = false;
}

export * from "@vercel/postgres";

.env.development

VERCEL_ENV=development
POSTGRES_URL=postgresql://postgres:password@localhost:54320/postgres

src/app/api/insert/route.ts

import { sql } from "@/db";

export const dynamic = "force-dynamic";

export async function GET() {
  await sql.query(
    `INSERT INTO users (name) VALUES ('John Doe');`
  );

  return Response.json({ succes: true });
}

I hope this helps all of you to use @vercel/postgres locally without too much environment differences.

@vvo ,I am a novice learning nextjs. The above explanation seems to lack database pooling, and there are some spelling errors in the env file. This is the code I modified.

Learn Next.js

docker-compose.yml

services:
  postgres:
    image: postgres:16.4
    environment:
      POSTGRES_PASSWORD: password
    # Expose the port to the host machine so you can use a local client to connect/inspect to the database
    ports:
      - "54320:5432"
  pg_proxy:
    image: ghcr.io/neondatabase/wsproxy:latest
    environment:
      APPEND_PORT: "postgres:5432"
      ALLOW_ADDR_REGEX: ".*"
      LOG_TRAFFIC: "true"
    ports:
      - "54330:80"
    depends_on:
      - postgres

db.ts

import { neonConfig } from '@neondatabase/serverless';

// This is the only code that will be different in development
if (process.env.VERCEL_ENV === "development") {
  neonConfig.wsProxy = (host) => `${host}:54330/v1`;
  neonConfig.useSecureWebSocket = false;
  neonConfig.pipelineTLS = false;
  neonConfig.pipelineConnect = false;
}

export * from "@vercel/postgres";

app/seed/route.ts

import bcrypt from 'bcrypt';
// import { db } from '@vercel/postgres';
import { invoices, customers, revenue, users } from '../lib/placeholder-data';
import "@/db"
import { Pool, neonConfig } from '@neondatabase/serverless';
import ws from 'ws';
neonConfig.webSocketConstructor = ws;

const pool = new Pool({ connectionString: process.env.POSTGRES_URL });

const client = await pool.connect();

// const client = await db.connect();



async function seedUsers() {
  await client.query(`CREATE EXTENSION IF NOT EXISTS "uuid-ossp"`);
  await client.query(`
    CREATE TABLE IF NOT EXISTS users (
      id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
      name VARCHAR(255) NOT NULL,
      email TEXT NOT NULL UNIQUE,
      password TEXT NOT NULL
    );
  `);

  const insertedUsers = await Promise.all(
    users.map(async (user) => {
      const hashedPassword = await bcrypt.hash(user.password, 10);
      return client.query(`
        INSERT INTO users (id, name, email, password)
        VALUES ($1, $2, $3, $4)
        ON CONFLICT (id) DO NOTHING;
      `, [user.id, user.name, user.email, hashedPassword]);
    }),
  );

  return insertedUsers;
}

async function seedInvoices() {
  await client.query(`CREATE EXTENSION IF NOT EXISTS "uuid-ossp"`);

  await client.query(`
    CREATE TABLE IF NOT EXISTS invoices (
      id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
      customer_id UUID NOT NULL,
      amount INT NOT NULL,
      status VARCHAR(255) NOT NULL,
      date DATE NOT NULL
    );
  `);

  const insertedInvoices = await Promise.all(
    invoices.map(
      (invoice) => client.query(`
        INSERT INTO invoices (customer_id, amount, status, date)
        VALUES ($1, $2, $3, $4)
        ON CONFLICT (id) DO NOTHING;
      `, [invoice.customer_id, invoice.amount, invoice.status, invoice.date]),
    ),
  );

  return insertedInvoices;
}

async function seedCustomers() {
  await client.query(`CREATE EXTENSION IF NOT EXISTS "uuid-ossp"`);

  await client.query(`
    CREATE TABLE IF NOT EXISTS customers (
      id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
      name VARCHAR(255) NOT NULL,
      email VARCHAR(255) NOT NULL,
      image_url VARCHAR(255) NOT NULL
    );
  `);

  const insertedCustomers = await Promise.all(
    customers.map(
      (customer) => client.query(`
        INSERT INTO customers (id, name, email, image_url)
        VALUES ($1, $2, $3, $4)
        ON CONFLICT (id) DO NOTHING;
      `, [customer.id, customer.name, customer.email, customer.image_url]),
    ),
  );

  return insertedCustomers;
}

async function seedRevenue() {
  await client.query(`
    CREATE TABLE IF NOT EXISTS revenue (
      month VARCHAR(4) NOT NULL UNIQUE,
      revenue INT NOT NULL
    );
  `);

  const insertedRevenue = await Promise.all(
    revenue.map(
      (rev) => client.query(`
        INSERT INTO revenue (month, revenue)
        VALUES ($1, $2)
        ON CONFLICT (month) DO NOTHING;
      `, [rev.month, rev.revenue]),
    ),
  );

  return insertedRevenue;
}

export async function GET() {
  // return Response.json({
  //   message:
  //     'Uncomment this file and remove this line. You can delete this file when you are finished.',
  // });
  try {
    await client.query(`BEGIN`);
    await seedUsers();
    await seedCustomers();
    await seedInvoices();
    await seedRevenue();
    await client.query(`COMMIT`);

    return Response.json({ message: 'Database seeded successfully' });
  } catch (error) {
    await client.query(`ROLLBACK`);
    return Response.json({ error }, { status: 500 });
  }
}

.env

VERCEL_ENV="development"
POSTGRES_URL="postgres://postgres:password@localhost:54320/nextjs-dashboard-postgres"

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.

Apologies if my previous comment was a bit abrupt / rude, I was losing my mind over this one!

No I think I completely understand your position. As soon as I really tried (very hard!) to have a local Postgres setup I was very angry at our own tooling so I can definitely understand you got angry as well…

I’ll see how we can add this to the docs 🙏

Is there a tutorial WITHOUT using Docker?

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

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

What happens if you clone the repo and see whether this works for you?

Turns out, the problem is with the .evn setup. I used POSTGRES_URL='postgresql://root:123456@127.0.0.1:54320/propertyease' as the connection string, which would produced the said error. When I changed the host name from “127.0.0.1” to “localhost”, invalid_connection_string error had stopped from showing up. However, I’m getting a new error now.

 at async eval (webpack-internal:///(rsc)/../../node_modules/@trpc/server/dist/unstable-core-do-not-import/stream/jsonl.mjs:157:34) {
    error: AggregateError [ECONNREFUSED]: 
        at internalConnectMultiple (node:net:1122:18)
        at afterConnectMultiple (node:net:1689: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'

I suspect it has something to do with @neondatabase/serverless package.

I can confirm that [neondatabase/wsproxy:latest] is running with correct docker-compose environment varialbe. It even produced some log regarding to the refused conneciton.

2024-12-21 20:26:19 2024/12/21 12:26:19 Got request from 172.18.0.1:59588
2024-12-21 20:26:19 2024/12/21 12:26:19 Got request from 172.18.0.1:59588, proxying to postgres:5432, allowed=true, addressArg=, hostHeader=localhost:54330
2024-12-21 20:26:19 2024/12/21 12:26:19 Got 58 bytes client->pg: AAAAOgADAAB1c2VyAHJvb3QAZGF0YWJhc2UAcG9zdGdyZXMAY2xpZW50X2VuY29kaW5nAFVURjgAAA==
2024-12-21 20:26:19 2024/12/21 12:26:19 Got 24 bytes pg->client: UgAAABcAAAAKU0NSQU0tU0hBLTI1NgAA
2024-12-21 20:26:19 2024/12/21 12:26:19 Got 56 bytes client->pg: cAAAADdTQ1JBTS1TSEEtMjU2AAAAACFuLCxuPSoscj0yMjI5QnZma0huN09BYXhDVDZYRlYvWk0=
2024-12-21 20:26:19 2024/12/21 12:26:19 Got 93 bytes pg->client: UgAAAFwAAAALcj0yMjI5QnZma0huN09BYXhDVDZYRlYvWk15YWNIdHIrOGtZdEdWaWxLaEREOGRtMFAscz1DZDRCUDlDbmFkSVpIY1l5YWdGdEdBPT0saT00MDk2
2024-12-21 20:26:19 2024/12/21 12:26:19 Got 109 bytes client->pg: cAAAAGxjPWJpd3Mscj0yMjI5QnZma0huN09BYXhDVDZYRlYvWk15YWNIdHIrOGtZdEdWaWxLaEREOGRtMFAscD1TV3ZzOHhPOXRkc3NWKzQwazJQSUpuTW5SRktlTzA3YnM5M25LdjZ3Qk9JPQ==
2024-12-21 20:26:19 2024/12/21 12:26:19 Got 488 bytes pg->client: UgAAADYAAAAMdj02bUhnd2pBWHJGNzl1RFNRQzlhakVHQnlGNTZDK3pKNjQ5eGZldnh2SWFRPVIAAAAIAAAAAFMAAAAXaW5faG90X3N0YW5kYnkAb2ZmAFMAAAAZaW50ZWdlcl9kYXRldGltZXMAb24AUwAAABVUaW1lWm9uZQBFdGMvVVRDAFMAAAAbSW50ZXJ2YWxTdHlsZQBwb3N0Z3JlcwBTAAAAFGlzX3N1cGVydXNlcgBvbgBTAAAAFmFwcGxpY2F0aW9uX25hbWUAAFMAAAAmZGVmYXVsdF90cmFuc2FjdGlvbl9yZWFkX29ubHkAb2ZmAFMAAAAac2NyYW1faXRlcmF0aW9ucwA0MDk2AFMAAAAXRGF0ZVN0eWxlAElTTywgTURZAFMAAAAjc3RhbmRhcmRfY29uZm9ybWluZ19zdHJpbmdzAG9uAFMAAAAfc2Vzc2lvbl9hdXRob3JpemF0aW9uAHJvb3QAUwAAABljbGllbnRfZW5jb2RpbmcAVVRGOABTAAAAMnNlcnZlcl92ZXJzaW9uADE3LjAgKERlYmlhbiAxNy4wLTEucGdkZzEyMCsxKQBTAAAAGXNlcnZlcl9lbmNvZGluZwBVVEY4AEsAAAAMAAAMtwTrKPtaAAAABUk=
2024-12-21 20:26:19 2024/12/21 12:26:19 Got 26 bytes client->pg: UQAAABlTRUxFQ1QgKiBGUk9NIHVzZXJzOwA=
2024-12-21 20:26:19 2024/12/21 12:26:19 Got 111 bytes pg->client: RQAAAGhTRVJST1IAVkVSUk9SAEM0MlAwMQBNcmVsYXRpb24gInVzZXJzIiBkb2VzIG5vdCBleGlzdABQMTUARnBhcnNlX3JlbGF0aW9uLmMATDE0NDkAUnBhcnNlck9wZW5UYWJsZQAAWgAAAAVJ
2024-12-21 20:26:19 2024/12/21 12:26:19 Got 5 bytes client->pg: WAAAAAQ=
2024-12-21 20:26:19 2024/12/21 12:26:19 failed to read from socket: EOF
2024-12-21 20:26:19 2024/12/21 12:26:19 failed to handle websocket: websocket: close 1000 (normal): TCP connection is closed

This issue has been resolved.

For future references, if you have drizzle or db setup in a different package of a Turborepo project, make sure your pnpm overrides json object is in the root package.json file. I had wrongly put the overrides setup in my db package which was omitted by pnpm during install process.

However, the drizzle migration has stopped working. A error message had popped up when pnpm db:push command was triggered

 Warning  '@vercel/postgres' can only connect to remote Neon/Vercel Postgres/Supabase instances through a websocket
│ ErrorEvent {
│   [Symbol(kTarget)]: WebSocket {
│     _events: [Object: null prototype] {
│       error: [Function],
│       message: [Function],
│       close: [Function],
│       open: [Function]
│     },
│     _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
│   },
│   [Symbol(kType)]: 'error',
│   [Symbol(kError)]: AggregateError [ECONNREFUSED]: 
│       at internalConnectMultiple (node:net:1122:18)
│       at afterConnectMultiple (node:net:1689:7)
│       at TCPConnectWrap.callbackTrampoline (node:internal/async_hooks:130:17) {
│     code: 'ECONNREFUSED',
│     [errors]: [ [Error], [Error] ]
│   },
│   [Symbol(kMessage)]: ''
│ }

@mmikhan I encountered a similar problem and discovered that two different versions of @neondatabase/serverless were installed. This caused a conflict because configuration changes were applied to the newer version, but @vercel/postgres was using the older version, which didn’t reflect those changes.

When I ran npm ls @neondatabase/serverless, the output was as follows:

├── @neondatabase/serverless@0.10.4
├─┬ @vercel/postgres@0.10.0
│ └── @neondatabase/serverless@0.9.5
└─┬ drizzle-orm@0.36.4
  └── @neondatabase/serverless@0.10.4 deduped

To resolve the issue, I added the following to my package.json and re-ran npm i:

"overrides": {
  "@neondatabase/serverless": "0.10.4"
}

This forces all packages to use the defined version and ensures that the global neonConfig is consistent across all packages. After making this change, everything worked as expected.

Following the exact steps and tried everything suggested but did not get it working. I have to say this is bad dx wasting hours for a must have feature. I guess we should get forced in buying more dbs for developing but even if you willing to you cant develop without internet. This is not open source but a payed service (not cheap) so its ok to demand some tooling.

This issue is to resolved and shouldnt be closed.

Edit: Using sql.query() instead of sql`` leads at least to different errors. query() : Error: Connection terminated unexpectedly sql``: NeonDbError: Error connecting to database: fetch failed

Right, it doesn’t work if you update the @neondatabase/serverless to the latest version ^0.10. Not sure why but have opened an issue

Hi there. To follow up on the various ways to use @vercel/postgres pointing to a local Postgres server, I created a repository that shows how to do it: https://github.com/vvo/local-vercel-postgres-development.

The overview is to use Docker to start a local Postgres instance coupled with a WebSockets proxy and configure @vercel/postgres to use it. The WebSockets proxy is needed due to the nature of serverless databases and how clients will connect to them. To mimic most of the production it’s recommended to go this way.

Here is the minimal implementation required and one I recommend:

package.json

{
  "dependencies": {
    "@vercel/postgres": "^0.9.0",
  },
  "devDependencies": {
    "@neondatabase/serverless": "^0.9.3"
  }
}

docker-compose.yml

services:
  postgres:
    image: postgres:16.4
    environment:
      POSTGRES_PASSWORD: password
    # Expose the port to the host machine so you can use a local client to connect/inspect to the database
    ports:
      - "54320:5432"
  pg_proxy:
    image: ghcr.io/neondatabase/wsproxy:latest
    environment:
      APPEND_PORT: "postgres:5432"
      ALLOW_ADDR_REGEX: ".*"
      LOG_TRAFFIC: "true"
    ports:
      - "54330:80"
    depends_on:
      - postgres

src/db.ts

import { neonConfig } from "@neondatabase/serverless";

// This is the only code that will be different in development
if (process.env.VERCEL_ENV === "development") {
  neonConfig.wsProxy = (host) => `${host}:54330/v1`;
  neonConfig.useSecureWebSocket = false;
  neonConfig.pipelineTLS = false;
  neonConfig.pipelineConnect = false;
}

export * from "@vercel/postgres";

.env.development

VERCEL_ENV=development
POSTGRES_URL=postgresql://postgres:password@localhost:54320/postgres

src/app/api/insert/route.ts

import { sql } from "@/db";

export const dynamic = "force-dynamic";

export async function GET() {
  await sql.query(
    `INSERT INTO users (name) VALUES ('John Doe');`
  );

  return Response.json({ succes: true });
}

I hope this helps all of you to use @vercel/postgres locally without too much environment differences.

I had the same issue, I just changed my package.json with the same version numbers, and it worked. from “@vercel/postgres”: “^0.8.0”, to “@vercel/postgres”: “^0.9.0”, and “@neondatabase/serverless”: “^0.9.3”, ->moved to devDependencies

The updated docs for local development didn’t work for me - following the instructions, I still got the error message:

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.

Confirming that @yokaimeow’s instructions fixed the issues.

It is absolutely mental that Vercel do not offer an ability to develop locally. What about people with no stable internet connection trying to make apps or quickly prototype an app without being tied to a cloud DB? They cannot depend on Vercel always being alive. What if someone was trying to develop while on a long plane trip?

There needs to be a simple way for the driver to be put into local mode and proxy the queries off to a local postgres database instance.

Please re-open this issue @vvo, it is not resolved.

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

I’m getting this error using this approach, any idea?

dashboard/node_modules/.pnpm/@neondatabase+serverless@0.6.0/node_modules/@neondatabase/serverless/index.js:556
return w.getRandomValues(d.alloc(r))}function $o(r){if(r==="sha256")return{update:function(e){
         ^

TypeError: w.getRandomValues is not a function

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!

I’m having issues as well with version 0.10.4 of @neondatabase/serverless. As soon as I update I see these errors on my console

   _url: 'wss://localhost/v2',
    _req: null,
    [Symbol(shapeMode)]: false,
    [Symbol(kCapture)]: false
  },
  [Symbol(kType)]: 'error',
  [Symbol(kError)]: AggregateError [ECONNREFUSED]: 
      at internalConnectMultiple (node:net:1118:18)
      at afterConnectMultiple (node:net:1685:7)
      at TCPConnectWrap.callbackTrampoline (node:internal/async_hooks:130:17) {
    code: 'ECONNREFUSED',
    [errors]: [ [Error], [Error] ]

Somehow it’s ignoring the whole neonConfig, since it’s using the url wss://localhost/v2.

@vvo would it be possible to update the example?

Someone ping his manager, guy deserve a raise.

It is easy enough to create a drop-in replace to the vercel/postgres API

Nice. The only major difference is that you need to specify array types and don’t use .rows. E.g. from the next.js course, need to change from:

const data = await sql<Revenue>`SELECT * FROM revenue`;
return data.rows;

To:

const data = await sql<Revenue[]>`SELECT * FROM revenue`;
return data;

Sample docker-compose.yml:

services:
  postgres:
    image: 'postgres:latest'
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
      POSTGRES_DB: postgres
    ports:
      - '5432:5432'
    volumes:
      - nextjs:/var/lib/postgresql/data
volumes:
  nextjs:

Sample .env:

POSTGRES_URL="postgresql://postgres:postgres@localhost:5432/postgres"

Start with docker-compose up -d.

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.

@klaussner - Took me a while to figure up and found out that it is better to use the Postgres package.

It is easy enough to create a drop-in replace to the vercel/postgres API:

const postgres = require('postgres');
....
const client = { sql: postgres(process.env.POSTGRES_URL) };
....
client.sql.end();

It worked nice for me without too much changes but YMMV 🚗

Workaround to use the sql function:

import type { QueryResult, QueryResultRow } from '@neondatabase/serverless';
import {
  VercelClient,
  createClient,
  createPool,
  type VercelPool,
} from '@vercel/postgres';

export type Primitive = string | number | boolean | undefined | null;
export { types } from '@neondatabase/serverless';

let pool: VercelPool | undefined;
let client: VercelClient | undefined;

const initiateClient = async () => {
  const client = createClient();
  // Set the WebSocket proxy to work with the local instance
  client.neonConfig.wsProxy = (host) =>
    `${host}:${process.env.NEON_PROXY_PORT}/v1`;
  // Disable all authentication and encryption
  client.neonConfig.useSecureWebSocket = false;
  client.neonConfig.pipelineTLS = false;
  client.neonConfig.pipelineConnect = false;
  await client.connect();
  return client;
};

export const sql = new Proxy(() => {}, {
  async get(_, prop) {
    if (process.env.VERCEL_ENV) {
      if (!pool) {
        pool = createPool();
      }

      const val = Reflect.get(pool, prop);
      if (typeof val === 'function') {
        return val.bind(pool);
      }
      return val;
    } else {
      if (!client) {
        client = await initiateClient();
      }

      const val = Reflect.get(client, prop);
      if (typeof val === 'function') {
        return val.bind(client);
      }
      return val;
    }
  },
  async apply(_, __, argumentsList) {
    if (process.env.VERCEL_ENV) {
      if (!pool) {
        pool = createPool();
      }
      return pool.sql(...argumentsList);
    } else {
      if (!client) {
        client = await initiateClient();
      }
      return client.sql(...argumentsList);
    }
  },
}) as unknown as VercelPool &
  (<O extends QueryResultRow>(
    strings: TemplateStringsArray,
    ...values: Primitive[]
  ) => Promise<QueryResult<O>>);

export const db = sql;

@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

@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:">: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.