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

[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

[nkpgardose]

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

2. Run the docker

   $ docker-compose up

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

4. 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()
  }
}

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

[v1s10n-4]

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

@nkpgardose really interested in a solution if you found out

[reyx]

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;

[Dakuan]

seriously? just to get postgres running locally?

[dudil]

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 :car:

[Kagami]

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.

[dudil]

Nice. The only major difference is that you need to specify array types and don't use .rows.

Absolutely right - I didn't mentioned that since it seems like the thread here is stale. BTW - I find that API (of the Postgres package) much more natural and I think the the amazing guys at @Vercel should consider to adapt it 🙏

[awongh]

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 🚗

fyi this didn't work for me because the postgres package has a different API. It doesn't return a rows key in the results, so can't be used as a drop-in replacement where you expect a rows array.

[Nitestack]

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;

This solution works fine for me except that it crashes because the get and apply are async functions. When used with drizzle, this didnt work

instead, I did

client.connect((err) => {
  if (err) {
    console.error(err);
  }
});

I looked into the source code of client.connect and with a callback provided, it doesn't need to be executed asynchronously. This removes the need for async get and apply in the proxy and works perfectly with drizzle

[steveninety]

I looked into the source code of client.connect and with a callback provided, it doesn't need to be executed asynchronously. This removes the need for async get and apply in the proxy and works perfectly with drizzle

I keep getting "Connection terminated unexpectedly" with this workaround...

Happens whenever I start the dev server, and then whenever auth redirects me back to localhost.

[nathanredblur]

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

[gsidsid]

^ +1 Using an extremely basic implementation on vercel connecting to a remote supabase instance and also getting the above error.

Uncaught Exception: TypeError: w.getRandomValues is not a function
at Object.randomBytes (file:///var/task/node_modules/@neondatabase/serverless/index.mjs:556:10)
at Object.startSession (file:///var/task/node_modules/@neondatabase/serverless/index.mjs:736:29)
at file:///var/task/node_modules/@neondatabase/serverless/index.mjs:1273:21
at VercelClient._checkPgPass (file:///var/task/node_modules/@neondatabase/serverless/index.mjs:1268:58)
at VercelClient._handleAuthSASL (file:///var/task/node_modules/@neondatabase/serverless/index.mjs:1272:63)

[GustavoOS]

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

[shawnmclean]

Can anyone explain to me why I am getting this sort of error locally when production is using a regular supabase db?

I even used the docker-compose with the supabase image:

version: "3.8"

services:
  db:
    image: supabase/postgres:15.1.1.61
    ports:
      - "5432:5432"
    environment:
      POSTGRES_PASSWORD: postgres

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

import { sql } from "@vercel/postgres";
import { drizzle } from "drizzle-orm/vercel-postgres";

import * as schema from "./schema";

export const db = drizzle(sql, { schema });

From what I understand, by using @vercel/postgres and @drizzle-orm/vercel-postgres, it allows the client side code to run in edge runtime, but allows it to connect to any database?

So why does it connect to a regular postgres db on vercel but not locally?

[shawnmclean]

My current state of learning:

From what I understand, the pooler is a web app placed Infront of the database.

They use something called supavisor.

This communication medium is http, I'm not sure what the requests look like but apparently this is different from talking straight to Postgres via TCP?

The edge runtimes are supposed to be small with fast startup time, so they don't contain the network libraries to do TCP connections. PG only supports TCP.

So the pooler is one way for edge code to proxy to the db, along with handling the connection pooling behavior.

So if I want to do local dev, I prefer to have something close to the production environment working, which is edge code talking to the pooler.

I still can't get a local db working as I couldn't figure out how to configure the pooler. I played with docker-compose and the supabase cli and ran into some tenant error.

Apparently there is some configuration required for the pooler to know what section of the db to talk to based on some tenant configuration.

So I'm doing local development with a hosted supabase db.

So that's my lead. Get supavisor working locally with nextjs functions talking to it.

[Maelstromeous]

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.

[vvo]

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.

[Maelstromeous]

Hi @vvo,

Thank you for providing a comprehensive and detailed response to this. I can confirm that this method works, and works very well! Adding your example repo to the Vercel Docs would likely be very welcome. 🙏

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

[vvo]

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 🙏

[vvo]

@Maelstromeous There's now a documentation page for this: https://vercel.com/docs/storage/vercel-postgres/local-development

[Maelstromeous]

@vvo most excellent stuff, thank you! This issue is well and truly closed now! 👏

[shawnmclean]

Someone ping his manager, guy deserve a raise.

[yokaimeow]

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

[very-meanly]

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.

[yokaimeow]

@very-meanly Replace localhost with your IP address