Next.js Tutorial – Setting up your database in your local development environment

While I was following with Next.js tutorial and reached this step to set up the database – https://nextjs.org/learn/dashboard-app/setting-up-your-database

I didn’t want to create a repository to deploy into Vercel; hence, I decided to run Postgres in my local development environment. However, it was not that easy to set it up.

The errors I got were the below:

Error [VercelPostgresError]: VercelPostgresError - 'invalid_connection_string': This connection string is meant to be used with a direct connection. Make sure to use a pooled connection string or try createClient() instead.

The database host is 'localhost', which is the default host when none is set. If that's intentional, please ignore this warning. If not, perhaps an environment variable has not been set, or has not been passed to the library?
- error uncaughtException: Error: connect ECONNREFUSED ::1:443
    at TCPConnectWrap.afterConnect [as oncomplete] (node:net:1494:16)
    at TCPConnectWrap.callbackTrampoline (node:internal/async_hooks:130:17) {
  digest: undefined
}

$ npm run seed                                                                                                                                       

> seed
> node -r dotenv/config ./scripts/seed.js

nextjs-dashboard/node_modules/@neondatabase/serverless/index.js:981
      return w.getRandomValues(d.alloc(r))
               ^

TypeError: w.getRandomValues is not a function
 

In the end, I was able to seed the data as below;

$ npm run seed                                                                                                                                       

> seed
> node -r dotenv/config ./scripts/seed.js

Created "users" table
Seeded 1 users
Created "customers" table
Seeded 10 customers
Created "invoices" table
Seeded 15 invoices
Created "revenue" table
Seeded 12 revenue

Here are the changes I made to make it work.

1. Update .nvmrc to use Node 20

Open .nvmrc and update as below:

20

And run nvm use to make sure you are using Node 20

$ nvm use

2. Create docker-compose.yml

Add docker-compose.yml as below:

Reference: https://gal.hagever.com/posts/running-vercel-postgres-locally

services:
  postgres:
    image: postgres:15
    container_name: postgres
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: password
      POSTGRES_DB: postgres
    ports:
      - '5432:5432'
    volumes:
      - postgres_data:/var/lib/postgresql/data    
    healthcheck:
      test: ['CMD-SHELL', 'pg_isready -U postgres']
      interval: 10s
      timeout: 5s
      retries: 5

  pg_proxy:
    container_name: pg_proxy
    image: ghcr.io/neondatabase/wsproxy:latest
    environment:
      APPEND_PORT: 'postgres:5432'
      ALLOW_ADDR_REGEX: '.*'
      LOG_TRAFFIC: 'true'
      USE_HOST_HEADER: 'false'
    ports:
      - '5433:80'
    depends_on:
      postgres:
        condition: service_healthy

  pgbouncer:
    image: edoburu/pgbouncer:1.22.1-p0
    container_name: pgbouncer
    environment:
      - DB_USER=postgres
      - DB_PASSWORD=password
      - DB_HOST=postgres
      - DB_NAME=postgres
      - AUTH_TYPE=scram-sha-256 # remove/comment this line if using postgres:13 and lower
      - POOL_MODE=transaction
      - ADMIN_USERS=postgres
    ports:
      - '5434:5432'
    depends_on:
      postgres:
        condition: service_healthy
volumes:
  postgres_data:

And launch docker containers.

$ docker-compose up -d

3. Add seed script into package.json as instructed by the tutorial

Open package.json and add the following script:

  "scripts": {
    ...
    "prettier:check": "prettier --check --ignore-unknown .",
    "seed": "node -r dotenv/config ./scripts/seed.js",
    ...
  },

4. Update scripts/seed.js

Open scripts/seed.js and update as below:

const { neonConfig } = require('@neondatabase/serverless');
const { db } = require('@vercel/postgres');

const {
  invoices,
  customers,
  revenue,
  users,
} = require('../app/lib/placeholder-data.js');
const bcrypt = require('bcrypt');

// if we're running locally
if (!process.env.VERCEL_ENV) {
  // Set the WebSocket proxy to work with the local instance
  neonConfig.wsProxy = (host) => `${host}:5433/v1`;
  neonConfig.fetchEndpoint = (host) => `http://${host}:5433/sql`;
  // Disable all authentication and encryption
  neonConfig.useSecureWebSocket = false;
  neonConfig.pipelineTLS = false;
  neonConfig.pipelineConnect = false;
}

...

5. Run npm run seed

Now, all ready.

Update your .env file as the below:

# Copy from .env.local on the Vercel dashboard
# https://nextjs.org/learn/dashboard-app/setting-up-your-database#create-a-postgres-database
POSTGRES_URL="postgres://postgres:password@127.0.0.1:5433/postgres?-pooler."
POSTGRES_PRISMA_URL="postgres://postgres:password@127.0.0.1:5434/postgres?pgbouncer=true&connection_timeout=15"
# URL of your Postgres database without pooling. Direct connection
POSTGRES_URL_NON_POOLING="postgres://postgres:password@127.0.0.1:5432/postgres"
POSTGRES_USER=postgres
POSTGRES_HOST=127.0.0.1
POSTGRES_PASSWORD=password
POSTGRES_DATABASE=postgres

# `openssl rand -base64 32`
AUTH_SECRET=
AUTH_URL=http://localhost:3000/api/auth

And run npm run seed, then you will see the output as below:

$ npm run seed                                                                                                                                       

> seed
> node -r dotenv/config ./scripts/seed.js

Created "users" table
Seeded 1 users
Created "customers" table
Seeded 10 customers
Created "invoices" table
Seeded 15 invoices
Created "revenue" table
Seeded 12 revenue

Hope it helps.