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

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

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.

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

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.

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