CODEWITHSUNDEEP
node.jsreactjspostgresqlcurlnext.js
F.Yuya
F.Yuya

Reputation: 119

Cannot connect Postgresql with Next.js API Route

I'm a beginner of Next.js. I want to create API routes which connects Postgresql database as follows:

// /server.js
const { Pool } = require('pg');

const pool = new Pool({
  user: process.env.DB_USER,
  host: process.env.DB_HOST,
  database: process.env.DB_DATABASE,
  password: process.env.DB_PASSWORD,
  port: process.env.DB_PORT
});

module.exports = pool;

// /src/pages/api/users.ts
import type { NextApiRequest, NextApiResponse } from "next";
import pool from "server";

export default async function handler(req: NextApiRequest, res: NextApiResponse) {
  try {
    const data =  await pool.query('SELECT * FROM user');
    res.status(200).json(data);
  } catch (err) {
    res.status(500).json({ error: 'failed to load data' })
  }
};

After running next dev, I ran curl "http://localhost:3000/api/users". I got the following response:

$ curl "http://localhost:3000/api/users"
{"error":"failed to load data"}%

Why I couldn't get data from DB correctly?

NOTES

$ curl -v "http://localhost:3000/api/users"
*   Trying 127.0.0.1:3000...
* Connected to localhost (127.0.0.1) port 3000 (#0)
> GET /api/decks HTTP/1.1
> Host: localhost:3000
> User-Agent: curl/7.79.1
> Accept: */*
> 
* Mark bundle as not supporting multiuse
< HTTP/1.1 500 Internal Server Error
< Content-Type: application/json; charset=utf-8
< ETag: "1f-fks/WOXqDfcm7GRptyiB/Swu0Aw"
< Content-Length: 31
< Vary: Accept-Encoding
< Date: Thu, 23 Jun 2022 10:06:21 GMT
< Connection: keep-alive
< Keep-Alive: timeout=5
< 
* Connection #0 to host localhost left intact
{"error":"failed to load data"}%     

"next": "^12.1.6",
"pg": "^8.7.3",
"react": "^18.1.0",
"react-dom": "^18.1.0"
"@types/node": "^17.0.41",
"@types/react": "^18.0.12",
"@types/react-dom": "^18.0.5",
"sass": "^1.52.3",
"typescript": "^4.7.3"

Upvotes: 2

Views: 3189

Answers (1)

F.Yuya
F.Yuya

Reputation: 119

I solved this problem by myself.

The important point is that I use Heroku Postgres.

As mentioned in the docs, we should set ssl configure as follows:

const pool = new Pool({
  user: process.env.DB_USER,
  host: process.env.DB_HOST,
  database: process.env.DB_DATABASE,
  password: process.env.DB_PASSWORD,
  port: process.env.DB_PORT,
  ssl: {
    rejectUnauthorized: false //HERE!
  }
});

This fixes my error and I can get response correctly.

Upvotes: 2

Related Questions