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

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.

About this issue

  • Original URL
  • State: open
  • Created 4 years ago
  • Reactions: 16
  • Comments: 26

Most upvoted comments

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.

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

image

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

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.

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

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.

@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}`;

For the people that come from the NextJS Dashboard tutorial that want to use a PostgreSQL local container in a typescript project, here’s my take on replacing @vercel/postgres with the pg lib in the app/seed/route.ts file

import bcrypt from 'bcrypt';
import pg from 'pg';
const { Client } = pg;
import { invoices, customers, revenue, users } from '../lib/placeholder-data';

const client = new Client({
  connectionString: process.env.POSTGRES_URL
});
await client.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 ('${user.id}', '${user.name}', '${user.email}', '${hashedPassword}')
        ON CONFLICT (id) DO NOTHING;
      `);
    }),
  );

  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 ('${invoice.customer_id}', '${invoice.amount}', '${invoice.status}', '${invoice.date}')
        ON CONFLICT (id) DO NOTHING;
      `),
    ),
  );

  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 ('${customer.id}', '${customer.name}', '${customer.email}', '${customer.image_url}')
        ON CONFLICT (id) DO NOTHING;
      `),
    ),
  );

  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 ('${rev.month}', '${rev.revenue}')
        ON CONFLICT (month) DO NOTHING;
      `),
    ),
  );

  return insertedRevenue;
}

export async function GET() {
  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 });
  }
}

This replaces the client.sql notation with the client.query() method.

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

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!

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

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

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

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…

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

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.