How To Connect postgres with NextJS 13

I'm assuming that you have set up postgresql locally.

I had a difficult time finding something online on this topic.

Thought I would share it here.

Install this npm package.

npm install pg

Then create a lib/db.js

Inside this new file put this:

db/lib.js
import { Pool } from 'pg'
const dotenv = require(dotenv); // This is to load .env
dotenv(config)

let conn;
if (!conn){
conn = new Pool({
user:process.env.PGUSER
host:process.env.PGHOST
database:process.env.PGDATABASE
password:process.env.PGPASSWORD
port:process.env.PGPORT})
}

export default conn;

Make sure you have created your .env file and stored your environment variables there

Then create an api route by creating a folder api/endpoint/route.js

Inside that folder add this code

app/api/endpoint/route.js
import { NextResponse } from 'next/server'
import conn from '../../../lib/db
export async function GET(){
try {
const res await conn.query('SELECT * from table where index = 1')

return NextResponse.json({ res })}
} catch (error){
return NextResponse.json({ err: `There was an error ${error}`})
}}

Then call this from your component using axios as documented in their github page

Use the endpoint /api/endpoint

Make sure you use the 'use client' in the calling component