How to set up Next.js with Node Postgres

This guide will walk you through setting up the pg package with Next.js.

ORMs are great, but there are several reasons why you might not want to use one.

• You might not want the complexity the ORM brings
• You might be familiar with the driver already and just want to use it
• You might love artisanally crafted SQL statements

If you want to use Prisma, check out this guide instead!

Start Postgres

To start, have an instance of Postgres you can connect to. I like using Docker for a quick and easy locally running database.

docker run --rm --publish 5432:5432 -e POSTGRES_HOST_AUTH_METHOD=trust -e postgres

This will set up the database to run without needing a password to connect.

Install Node Postgres

yarn add -SE pg
yarn add -SED @types/pg

Node Postgres still does not have TypeScript types built in, so you need to install the separate types package.

Create a database file

The node Postgres package has connecting pooling built in, so you should only have a single instance of the pool created.

Create a file db/index.ts and add the following code to it:

import { Pool, QueryResultRow } from 'pg'

// Creates a global connection pool
const pool = new Pool()

export const query = <Result extends QueryResultRow>(
  text: string,
  params: any[] = []
) => {
  return pool.query<Result>(text, params)
}

This creates the connection pool that will use environment variables to connect. We then export a query function that you can use throughout your application to use that pool to fetch results. The function can be used for both selecting as well as inserting, and updating data.

Use the connection

For an example of how to use this, go to a page in the pages directory, and you can do the following:

type Result = {
  // Your database type here
}

export const getServerSideProps = async () => {
  const { rows } = await query<Result>('SELECT * FROM table')
  return {
    props: {
      data: rows
    }
  }
}

That's all there is to it!