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){
export default conn;
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})
}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(){
return NextResponse.json({ err: `There was an error ${error}`})
}}
import { NextResponse } from 'next/server'
import conn from '../../../lib/db
export async function GET(){
try {
return NextResponse.json({ res })}
} catch (error){const res await conn.query('SELECT * from table where index = 1')
return NextResponse.json({ res })}
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