Error: connect ECONNREFUSED ::1:443 with PostgreSQL and Next.js locally?

[lancejpollard]

I have this defined in .env locally:

DATABASE_URL=postgresql://localhost:5432/mydb
POSTGRES_URL=postgresql://localhost:5432/mydb

I then have this kysely config (where DB is from codgen-kysely):

import 'dotenv/config'
import { createKysely } from '@vercel/postgres-kysely'
import { DB } from 'kysely-codegen'

export const db = createKysely<DB>()
export { sql } from 'kysely'

I then have a seed file at seeds/start.ts:

yarn ts-node -P tsconfig.cli.json seeds/start

/* eslint-disable @typescript-eslint/no-unsafe-argument */
import { db } from '../configurations/kysely'

seed()

async function seed() {
  db.insertInto('foo').values(...)
}

What I am getting though is:

$ yarn ts-node -P tsconfig.cli.json seeds/start
$ ./node_modules/.bin/ts-node -P tsconfig.cli.json seeds/start
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: connect ECONNREFUSED ::1:443
    at TCPConnectWrap.afterConnect [as oncomplete] (node:net:1487:16) {
  errno: -61,
  code: 'ECONNREFUSED',
  syscall: 'connect',
  address: '::1',
  port: 443
}
error Command failed with exit code 1.
info Visit https://yarnpkg.com/en/docs/cli/run for documentation about this command.

Why is it trying to connect to port 443 when I specified 5432 in the .env? Do I need to open up port 443 locally? That seems weird. How do I get around this locally?

I was able to run the kysely migrations fine, but not the seeds now, using the @vercel/postgres-kysely package.

[Tchoupinax]

Hello, I meet exactly the same issue. According my investigation, it comes with localhost because if I put a Vercel postgres URL in the env variable, it works without any issue. For the moment I do not know how to solve it..

[lancejpollard]

I guess you aren't meant to use this locally, you are meant to use the production database?

[arxpoetica]

I've been running into this. Vercel might need to document what to do for a local setup. I've been reading this: https://neon.tech/blog/serverless-driver-for-postgres

If you scroll down to the bottom it talks about how to set things up locally under the section "Please do try this at home." But I've been wrestling with the setup long enough. Most of that documentation focuses on @neondatabase/serverless and not Vercel's integration which is very specific.

Setup w/ a local db is a common enough use case, that I think it'd be wise to lay this on Vercel's shoulders to document better or provide an easy way to do it.

FWIW, I can't move forward w/ this @vercel/postgres because I need to work locally, so just using postgres until I can figure out how to get it working.

[lancejpollard]

@arxpoetica yeah I'm in the same boat, I am just trying to have two databases in 2 separate vercel projects, one a playground and one production. Maybe you can have 2 postgres's in one vercel project, not sure yet. I then have to have a ENV flag to tell dotenv to load the .env.local (playground) instead of .env (production), but it's hard to remember for such a straightforward thing, and then on top of that the migrations are being manually run through kysely currently, which is a tad low-level for my taste. But it's working.

Hoping for a better local workflow for sure.

[Tyrrrz]

Having the same issue, @vercel/postgres completely ignores my localhost connection URI. I installed pg instead, and it works just fine.

Related issue: https://github.com/vercel/storage/issues/123

[jamezening]

I've been running into this. Vercel might need to document what to do for a local setup. I've been reading this: https://neon.tech/blog/serverless-driver-for-postgres

If you scroll down to the bottom it talks about how to set things up locally under the section "Please do try this at home." But I've been wrestling with the setup long enough. Most of that documentation focuses on @neondatabase/serverless and not Vercel's integration which is very specific.

Setup w/ a local db is a common enough use case, that I think it'd be wise to lay this on Vercel's shoulders to document better or provide an easy way to do it.

FWIW, I can't move forward w/ this @vercel/postgres because I need to work locally, so just using postgres until I can figure out how to get it working.

I am not in the same situation, and my specific use case might not be within the scope of this gh repo (vercel/examples) but sharing as I ran into a similar error @arxpoetica did:

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 127.0.0.1:443
    at TCPConnectWrap.afterConnect [as oncomplete] (node:net:1278:16) {
  digest: undefined

My setup is with Drizzle, Neon, and Vercel Edge.

I took a hint from the error message stating that the database host is not being set, and also referenced this Neon article outlining the connection details.

The initial setup code for the Drizzle, Neon, and Vercel Edge setup was:

import { Pool } from "@neondatabase/serverless";
import type { NextRequest, NextFetchEvent } from "next/server";

export const config = { runtime: "edge" };

// eslint-disable-next-line import/no-anonymous-default-export
export default async (req: NextRequest, event: NextFetchEvent) => {
  const pool = new Pool({
    connectionString: process.env.DATABASE_URL,
  });

(where {DATABASE_URL} is the url Neon gives you in the dashboard)

so I just added additional details, including host, password, database, and user (fill in your values below) to the Pool setup as follows:

const pool = new Pool({
    connectionString: process.env.DATABASE_URL,
    host: {host},
    password: {password},
    database: {database},
    user: {user},
  });

and I was able to connect from localhost.

I am still organizing how I was able to connect in my head, and I hope this might help solve some of the issues mentioned above.

[jamezening]

I also tried Getting Started with Vercel Postgres

which generates the following env values: POSTGRES_URL POSTGRES_PRISMA_URL POSTGRES_URL_NON_POOLING POSTGRES_USER POSTGRES_HOST POSTGRES_PASSWORD POSTGRES_DATABASE

so I think configuring your local env with the value above will help you successfully connect from localhost.

[codecab]

I'm also facing this issue. Please provide an example of how to switch to a locally installed database when using this package.

[paulmwatson]

Same, am trying to setup a docker compose local dev setup with vercel/postgres but keep getting the VercelPostgresError - 'invalid_connection_string': This connection string is meant to be used with a direct connection. Make sure to use a pooled connection string error.

[verheyenkoen]

I got this working for Drizzle using a docker-compose setup and a tiny bit of extra config code described here: https://gal.hagever.com/posts/running-vercel-postgres-locally.

My db.ts file:

import { Pool, neonConfig } from '@neondatabase/serverless'
import { drizzle } from 'drizzle-orm/neon-serverless'

import * as schemas from './schema'

if (!process.env.VERCEL_ENV) {
  // Set the WebSocket proxy to work with the local instance
  neonConfig.wsProxy = (host) => `${host}:5433/v1`
  
  // Disable all authentication and encryption
  neonConfig.useSecureWebSocket = false
  neonConfig.pipelineTLS = false
  neonConfig.pipelineConnect = false
}

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
})

export const db = drizzle(pool, {
  schema: { ...schemas },
  logger: true,
})

So both postgres and the web socket proxy are running in a local container. It should also be possible to just have the web socket proxy in a docker container (or even both on your host machine) but I didn't try that.

So many thanks @Schniz for that article!

[Guito]

I tried to do what is said in the tutorial. The database is working correctly, however, when I try to use it in my nuxt 3 application I get the error [uncaughtException] TypeError: g.getRandomValues is not a function

I'm using node 18.14.2

[NeuroWhAI]

While I was following the Next.js dashboard-app tutorial, I had a similar problem connecting DB locally, so I came here while searching. After doing this and that, I found a solution and want to share it for the other people like me. I connected to DB using pg library instead @vercel/postgres library and made sql template literal work using code snippet of sql-pg library.

Here is a modified seed.js file snippet.

async function main() {
  const client = new Client({
    host: 'localhost',
    port: 5432,
    database: 'db name',
    user: 'db user',
    password: 'user pwd',
  });
  await client.connect();

  const values = (values, { columns = Object.keys(values) } = {}) => {
    if (!Array.isArray(values)) {
      values = columns.map(column => values[column]);
    }
    return valuePosition => ({
      text: Array.apply(null, { length: values.length }).map(() => '$' + (++valuePosition)).join(', '),
      values
    })
  };
  client.sql = (textFragments, ...valueFragments) => {
    const query = {
      text: textFragments[0],
      values: []
    };
    valueFragments.forEach((valueFragment, i) => {
      if (typeof valueFragment !== 'function') {
        valueFragment = values([valueFragment]);
      }
      valueFragment = valueFragment(query.values.length);
      query.text += valueFragment.text + textFragments[i + 1];
      query.values = query.values.concat(valueFragment.values);
    });
    return client.query(query.text, query.values);
  };

  await seedUsers(client);
  await seedCustomers(client);
  await seedInvoices(client);
  await seedRevenue(client);

  await client.end();
}

Don't forget to require Client from pg.

[aleksandr-vin]

From my experience, chapter 6 was the most time consuming, like 10:2 comparing to other chapters of the course. I think it would benefit from adding a small hint there about how to do local pg connection. I have no doubts that a real portion of users do go with local db setup in the course.

[Tobbe]

@NeuroWhAI Thank you so much for sharing that snippet! Only thing I changed was the connection parameters for creating the client to look like this:

const client = new Client({ connectionString: process.env.DATABASE_URL });

[gnomefin]

Just to give my complete snippets that is worked like a charm, thanks @NeuroWhAI and @Tobbe for the reference! 🚀

Since I want to use my .env, then I need to install dotenv as well:

npm install pg
npm install dotenv

And this is my complete seed.js:

// const { db } = require('@vercel/postgres'); // I tried to comment this
require('dotenv').config();
const { Client } = require('pg');
const {
  invoices,
  customers,
  revenue,
  users,
} = require('../app/lib/placeholder-data.js');
const bcrypt = require('bcrypt');

async function seedUsers(client) {
  try {
    await client.sql`CREATE EXTENSION IF NOT EXISTS "uuid-ossp"`;
    // Create the "users" table if it doesn't exist
    const createTable = await client.sql`
      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
      );
    `;

    console.log(`Created "users" table`);

    // Insert data into the "users" table
    const insertedUsers = await Promise.all(
      users.map(async (user) => {
        const hashedPassword = await bcrypt.hash(user.password, 10);
        return client.sql`
        INSERT INTO users (id, name, email, password)
        VALUES (${user.id}, ${user.name}, ${user.email}, ${hashedPassword})
        ON CONFLICT (id) DO NOTHING;
      `;
      }),
    );

    console.log(`Seeded ${insertedUsers.length} users`);

    return {
      createTable,
      users: insertedUsers,
    };
  } catch (error) {
    console.error('Error seeding users:', error);
    throw error;
  }
}

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

    // Create the "invoices" table if it doesn't exist
    const createTable = await client.sql`
    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
  );
`;

    console.log(`Created "invoices" table`);

    // Insert data into the "invoices" table
    const insertedInvoices = await Promise.all(
      invoices.map(
        (invoice) => client.sql`
        INSERT INTO invoices (customer_id, amount, status, date)
        VALUES (${invoice.customer_id}, ${invoice.amount}, ${invoice.status}, ${invoice.date})
        ON CONFLICT (id) DO NOTHING;
      `,
      ),
    );

    console.log(`Seeded ${insertedInvoices.length} invoices`);

    return {
      createTable,
      invoices: insertedInvoices,
    };
  } catch (error) {
    console.error('Error seeding invoices:', error);
    throw error;
  }
}

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

    // Create the "customers" table if it doesn't exist
    const createTable = await client.sql`
      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
      );
    `;

    console.log(`Created "customers" table`);

    // Insert data into the "customers" table
    const insertedCustomers = await Promise.all(
      customers.map(
        (customer) => client.sql`
        INSERT INTO customers (id, name, email, image_url)
        VALUES (${customer.id}, ${customer.name}, ${customer.email}, ${customer.image_url})
        ON CONFLICT (id) DO NOTHING;
      `,
      ),
    );

    console.log(`Seeded ${insertedCustomers.length} customers`);

    return {
      createTable,
      customers: insertedCustomers,
    };
  } catch (error) {
    console.error('Error seeding customers:', error);
    throw error;
  }
}

async function seedRevenue(client) {
  try {
    // Create the "revenue" table if it doesn't exist
    const createTable = await client.sql`
      CREATE TABLE IF NOT EXISTS revenue (
        month VARCHAR(4) NOT NULL UNIQUE,
        revenue INT NOT NULL
      );
    `;

    console.log(`Created "revenue" table`);

    // Insert data into the "revenue" table
    const insertedRevenue = await Promise.all(
      revenue.map(
        (rev) => client.sql`
        INSERT INTO revenue (month, revenue)
        VALUES (${rev.month}, ${rev.revenue})
        ON CONFLICT (month) DO NOTHING;
      `,
      ),
    );

    console.log(`Seeded ${insertedRevenue.length} revenue`);

    return {
      createTable,
      revenue: insertedRevenue,
    };
  } catch (error) {
    console.error('Error seeding revenue:', error);
    throw error;
  }
}

async function main() {
  const client = new Client({
    connectionString: process.env.DATABASE_URL,
    user: process.env.POSTGRES_USER,
    host: process.env.POSTGRES_HOST,
    database: process.env.POSTGRES_DATABASE,
    password: process.env.POSTGRES_PASSWORD,
    port: 5432,
  });
  await client.connect();

  const values = (values, { columns = Object.keys(values) } = {}) => {
    if (!Array.isArray(values)) {
      values = columns.map(column => values[column]);
    }
    return valuePosition => ({
      text: Array.apply(null, { length: values.length }).map(() => '$' + (++valuePosition)).join(', '),
      values
    })
  };
  client.sql = (textFragments, ...valueFragments) => {
    const query = {
      text: textFragments[0],
      values: []
    };
    valueFragments.forEach((valueFragment, i) => {
      if (typeof valueFragment !== 'function') {
        valueFragment = values([valueFragment]);
      }
      valueFragment = valueFragment(query.values.length);
      query.text += valueFragment.text + textFragments[i + 1];
      query.values = query.values.concat(valueFragment.values);
    });
    return client.query(query.text, query.values);
  };

  await seedUsers(client);
  await seedCustomers(client);
  await seedInvoices(client);
  await seedRevenue(client);

  await client.end();
}

main().catch((err) => {
  console.error(
    'An error occurred while attempting to seed the database:',
    err,
  );
});

My local .env (just do it for example for other folks):

# Copy from .env.local on the Vercel dashboard
# https://nextjs.org/learn/dashboard-app/setting-up-your-database#create-a-postgres-database
POSTGRES_URL="postgresql://nextjs:nextjs123@localhost:5432/open_commission"
POSTGRES_PRISMA_URL="postgresql://nextjs:nextjs123@localhost:5432/open_commission"
POSTGRES_URL_NON_POOLING="postgresql://nextjs:nextjs123@localhost:5432/open_commission"
POSTGRES_USER=nextjs
POSTGRES_HOST=localhost
POSTGRES_PASSWORD=nextjs123
POSTGRES_DATABASE=open_commission

# `openssl rand -base64 32`
AUTH_SECRET=
AUTH_URL=http://localhost:3000/api/auth

[i-naeem]

What changes we would have to make to the data.ts file?

@gnomefin @NeuroWhAI I copied your snippet and wanted would the data.ts files work without any changes?

[NeuroWhAI]

@i-naeem in case of data.ts file, put the below snippet on top of the file. Well, It's not a good code, but it will work well in the tutorial.

import { Client } from 'pg';

const client = new Client({
  host: 'localhost',
  port: 5432,
  database: 'db name',
  user: 'db user',
  password: 'user pwd',
});
client.connect();

type SqlResult<T> = { rows: T[] };
export async function sql<T = any>(textFragments: any, ...valueFragments: any[]): Promise<SqlResult<T>> {
  const values = (values: any, { columns = Object.keys(values) } = {}) => {
    if (!Array.isArray(values)) {
      values = columns.map(column => values[column]);
    }
    return (valuePosition: any) => ({
      text: Array.apply(null, { length: values.length } as any).map(() => '$' + (++valuePosition)).join(', '),
      values
    })
  };
  const query = {
    text: textFragments[0],
    values: []
  };
  valueFragments.forEach((valueFragment, i) => {
    if (typeof valueFragment !== 'function') {
      valueFragment = values([valueFragment]);
    }
    valueFragment = valueFragment(query.values.length);
    query.text += valueFragment.text + textFragments[i + 1];
    query.values = query.values.concat(valueFragment.values);
  });
  return client.query(query.text, query.values) as any;
}

You can also import this sql function and use in the same way as tutorial.

//actions.ts
import { sql } from '@/app/lib/data';
//...
await sql`DELETE FROM invoices WHERE id = ${id}`;

[Tobbe]

I copied the code from here: https://github.com/vercel/storage/blob/main/packages/postgres/src/sql-template.ts And then I combined it with the example from here: https://node-postgres.com/guides/project-structure#example

And that gave me a data.ts file that looks like the code below. I could successfully use this to complete the tutorial against my own local DB.

import { Pool, QueryResultRow } from 'pg';

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

type Primitive = string | number | boolean | undefined | null;

function sqlTemplate(
  strings: TemplateStringsArray,
  ...values: Primitive[]
): [string, Primitive[]] {
  if (!isTemplateStringsArray(strings) || !Array.isArray(values)) {
    throw new Error(
      'It looks like you tried to call `sql` as a function. Make sure to use it as a tagged template.\n' +
        "\tExample: sql`SELECT * FROM users`, not sql('SELECT * FROM users')",
    );
  }

  let result = strings[0] ?? '';

  for (let i = 1; i < strings.length; i++) {
    result += `$${i}${strings[i] ?? ''}`;
  }

  return [result, values];
}

function isTemplateStringsArray(
  strings: unknown,
): strings is TemplateStringsArray {
  return (
    Array.isArray(strings) && 'raw' in strings && Array.isArray(strings.raw)
  );
}

export async function sql<O extends QueryResultRow>(
  strings: TemplateStringsArray,
  ...values: Primitive[]
) {
  const [query, params] = sqlTemplate(strings, ...values);

  return pool.query<O>(query, params);
}

And that last exported function can now be used as a taged template literal function, just like the tutorial is using sql from the Vercel Postgres package.

// ./auth.ts
import { sql } from '@/app/lib/data';

async function getUser(email: string): Promise<User | undefined> {
  try {
    const user = await sql<User>`SELECT * FROM users WHERE email=${email}`;
    return user.rows[0];
  } catch (error) {
    console.error('Failed to fetch user:', error);
    throw new Error('Failed to fetch user.');
  }
}

[Shtekata]

Thanks Tobbe! This is workaround for now to work as expected with 'sql' and use storage pool. May be one day Vercel will fix 'node_modules' and there won't be such a problem anymore! :)

[matt-long-92]

VERCEL - YOU NEED TO CALL OUT THAT THIS DOES NOT RUN USING LOCALHOST FOR DEV SETUP. WASTED MY TIME NOW. 🤦

[kjorand]

I copied the code from here: https://github.com/vercel/storage/blob/main/packages/postgres/src/sql-template.ts And then I combined it with the example from here: https://node-postgres.com/guides/project-structure#example

And that gave me a data.ts file that looks like the code below. I could successfully use this to complete the tutorial against my own local DB.

import { Pool, QueryResultRow } from 'pg';

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

type Primitive = string | number | boolean | undefined | null;

function sqlTemplate(
  strings: TemplateStringsArray,
  ...values: Primitive[]
): [string, Primitive[]] {
  if (!isTemplateStringsArray(strings) || !Array.isArray(values)) {
    throw new Error(
      'It looks like you tried to call `sql` as a function. Make sure to use it as a tagged template.\n' +
        "\tExample: sql`SELECT * FROM users`, not sql('SELECT * FROM users')",
    );
  }

  let result = strings[0] ?? '';

  for (let i = 1; i < strings.length; i++) {
    result += `$${i}${strings[i] ?? ''}`;
  }

  return [result, values];
}

function isTemplateStringsArray(
  strings: unknown,
): strings is TemplateStringsArray {
  return (
    Array.isArray(strings) && 'raw' in strings && Array.isArray(strings.raw)
  );
}

export async function sql<O extends QueryResultRow>(
  strings: TemplateStringsArray,
  ...values: Primitive[]
) {
  const [query, params] = sqlTemplate(strings, ...values);

  return pool.query<O>(query, params);
}

And that last exported function can now be used as a taged template literal function, just like the tutorial is using sql from the Vercel Postgres package.

// ./auth.ts
import { sql } from '@/app/lib/data';

async function getUser(email: string): Promise<User | undefined> {
  try {
    const user = await sql<User>`SELECT * FROM users WHERE email=${email}`;
    return user.rows[0];
  } catch (error) {
    console.error('Failed to fetch user:', error);
    throw new Error('Failed to fetch user.');
  }
}

Worked great for me (note : I changed the env variable being used to match with the naming from the tutorial) ... here's the docker-compose.yml I'm using ... in case someone is wondering ...

# Use postgres/example user/password credentials
version: '3.9'

services:

  db:
    image: postgres
    restart: always
    # set shared memory limit when using docker-compose
    shm_size: 128mb
    # or set shared memory limit when deploy via swarm stack
    #volumes:
    #  - type: tmpfs
    #    target: /dev/shm
    #    tmpfs:
    #      size: 134217728 # 128*2^20 bytes = 128Mb
    environment:
      POSTGRES_PASSWORD: example
      PGDATA: '/var/lib/postgresql/data/pgdata'
    ports:
      - 6543:5432
    volumes:
      - ./db/data:/var/lib/postgresql/data

  adminer:
    image: adminer
    restart: always
    ports:
      - 9080:8080

And here's my .env :

# Copy from .env.local on the Vercel dashboard
# https://nextjs.org/learn/dashboard-app/setting-up-your-database#create-a-postgres-database
POSTGRES_URL="postgresql://postgres:example@localhost:6543/nextjs-dashboard-postgres"
# POSTGRES_PRISMA_URL="postgresql://postgres:example@localhost:5432/postgres"
# POSTGRES_URL_NON_POOLING="postgresql://postgres:example@localhost:5432/postgres"
# POSTGRES_USER="postgres"
# POSTGRES_HOST="localhost"
# POSTGRES_PASSWORD="example"
# POSTGRES_DATABASE="nextjs-dashboard-postgres"

# `openssl rand -base64 32`
AUTH_SECRET=
AUTH_URL=http://localhost:3000/api/auth

[yoni-segal]

Thank you for the solution guys!