Error: connect ECONNREFUSED ::1:443 with PostgreSQL and Next.js locally? #701
Comments
|
Hello, |
|
I guess you aren't meant to use this locally, you are meant to use the production database? |
|
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 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 |
|
@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 Hoping for a better local workflow for sure. |
|
Having the same issue, Related issue: vercel/storage#123 |
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: 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: (where {DATABASE_URL} is the url Neon gives you in the dashboard) so I just added additional details, including 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. |
|
I also tried Getting Started with Vercel Postgres which generates the following env values: so I think configuring your local env with the value above will help you successfully connect from localhost. |
|
I'm also facing this issue. Please provide an example of how to switch to a locally installed database when using this package. |
|
Same, am trying to setup a docker compose local dev setup with vercel/postgres but keep getting the |
|
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 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 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 I'm using node 18.14.2 |
|
While I was following the Next.js dashboard-app tutorial, I had a similar problem connecting DB locally, so I came here while searching. 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 |
|
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. |
|
@NeuroWhAI Thank you so much for sharing that snippet! const client = new Client({ connectionString: process.env.DATABASE_URL }); |
|
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 npm install pg
npm install dotenvAnd this is my complete // 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 # 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 |
|
What changes we would have to make to the @gnomefin @NeuroWhAI I copied your snippet and wanted would the |
|
@i-naeem 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 //actions.ts
import { sql } from '@/app/lib/data';
//...
await sql`DELETE FROM invoices WHERE id = ${id}`; |
|
I copied the code from here: https://github.com/vercel/storage/blob/main/packages/postgres/src/sql-template.ts And that gave me a 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 // ./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.');
}
} |
|
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! :) |
I have this defined in
.envlocally:I then have this kysely config (where
DBis fromcodgen-kysely):I then have a seed file at
seeds/start.ts:What I am getting though is:
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-kyselypackage.The text was updated successfully, but these errors were encountered: