Next.js Dashboard Tutorial: How to Use a Local Postgres Database

Next.js has an excellent free tutorial that walks you through building a dashboard web app. Part of the process involves exploring Vercel’s github integration by creating a github repo, pushing the changes, and linking it to a Vercel account for hosting. Along with that, it provides instruction for creating a postgres instance on Vercel and seeding it with data needed for the rest of the tutorial.

If you have reached Chapter 6: Setting Up Your Database, and like me, want to skip publishing the app and just use a local Postgres instance, then below are a few tweaks to the code to do just that.

Step 1. Local Postgres

In my case, I use Postgres.app on Mac. Any other flavor should work, just need to have it up and running. If you don’t want to use the default database, you can create a new one for the tutorial like:

createdb nextjs-dashboard -O yourUserName

If that command doesn’t work for you, verify that you have your path set up.

Step 2. Connection Strings

In your .env file, instead of what the tutorial says, paste the following:

PGUSER=yourUserName
PGPASSWORD=null
PGHOST=localhost
PGPORT=5432
PGDATABASE=nextjs-dashboard

Step 3. Install node-postgres

The tutorial has us installing @vercel/postgres but it didn’t work for me, so I stuck to gold old node-postgres:

pnpm i pg

Step 4. Modify the Seed Route Handler

The code to seed the database requirest a few tweaks in order to use pg. Paste this into route.ts and then proceed as normal in the tutorial by going to localhost:3000/seed

//route.ts
import bcrypt from 'bcrypt';
import { invoices, customers, revenue, users } from '../lib/placeholder-data';
import pg from 'pg'
const { Client } = pg
const client = new Client()

async function seedUsers() {
  await client.query(`CREATE EXTENSION IF NOT EXISTS "uuid-ossp"`);
  await client.query(`
    CREATE TABLE IF NOT EXISTS users (
      id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
      name VARCHAR(255) NOT NULL,
      email TEXT NOT NULL UNIQUE,
      password TEXT NOT NULL
    );
  `);

  const insertedUsers = await Promise.all(
    users.map(async (user) => {
      const hashedPassword = await bcrypt.hash(user.password, 10);
      return client.query(`
        INSERT INTO users (id, name, email, password)
        VALUES ('${user.id}', '${user.name}', '${user.email}', '${hashedPassword}')
        ON CONFLICT (id) DO NOTHING;
      `);
    }),
  );

  return insertedUsers;
}

async function seedInvoices() {
  await client.query(`CREATE EXTENSION IF NOT EXISTS "uuid-ossp"`);

  await client.query(`
    CREATE TABLE IF NOT EXISTS invoices (
      id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
      customer_id UUID NOT NULL,
      amount INT NOT NULL,
      status VARCHAR(255) NOT NULL,
      date DATE NOT NULL
    );
  `);

  const insertedInvoices = await Promise.all(
    invoices.map(
      (invoice) => client.query(`
        INSERT INTO invoices (customer_id, amount, status, date)
        VALUES ('${invoice.customer_id}', '${invoice.amount}', '${invoice.status}', '${invoice.date}')
        ON CONFLICT (id) DO NOTHING;
      `),
    ),
  );

  return insertedInvoices;
}

async function seedCustomers() {
  await client.query(`CREATE EXTENSION IF NOT EXISTS "uuid-ossp"`);

  await client.query(`
    CREATE TABLE IF NOT EXISTS customers (
      id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
      name VARCHAR(255) NOT NULL,
      email VARCHAR(255) NOT NULL,
      image_url VARCHAR(255) NOT NULL
    );
  `);

  const insertedCustomers = await Promise.all(
    customers.map(
      (customer) => client.query(`
        INSERT INTO customers (id, name, email, image_url)
        VALUES ('${customer.id}', '${customer.name}', '${customer.email}', '${customer.image_url}')
        ON CONFLICT (id) DO NOTHING;
      `),
    ),
  );

  return insertedCustomers;
}

async function seedRevenue() {
  await client.query(`
    CREATE TABLE IF NOT EXISTS revenue (
      month VARCHAR(4) NOT NULL UNIQUE,
      revenue INT NOT NULL
    );
  `);

  const insertedRevenue = await Promise.all(
    revenue.map(
      (rev) => client.query(`
        INSERT INTO revenue (month, revenue)
        VALUES ('${rev.month}', '${rev.revenue}')
        ON CONFLICT (month) DO NOTHING;
      `),
    ),
  );

  return insertedRevenue;
}

export async function GET() {

  try {
    await client.connect()

    await client.query(`BEGIN`);
    await seedUsers();
    await seedCustomers();
    await seedInvoices();
    await seedRevenue();
    await client.query(`COMMIT`);
    await client.end()

    return Response.json({ message: 'Database seeded successfully' });
  } catch (error) {
    await client.query(`ROLLBACK`);
    await client.end()

    return Response.json({ error }, { status: 500 });
  }
}

Next Steps

And that’s it! If you run into any problems, it will likely be a connectivity issue. Make sure you are able to connect to the local database in question outside of the dashboard code.

Next, check out Part 2 on how to make the changes needed for fetching data that will be needed for Chapter 7 of the Next.js tutorial.