Adding a tutorial for seeding a local postgres database

[aidinio]

Hello.

There is no tutorial about seeding a local database in "Chapter 6: Setting Up Your Database". I changed the route.ts file so it would seed the local postgres database. I would be happy to make a tutorial for it, but I'm not sure how can I contribute to the dashboard app's tutorial. Neither I did find any guides on contributing to the tutorial. Is there a way to edit the existing chapter 6, or to add a new chapter about seeding a local postgres database?

[NicoleCoding]

A tutorial would be great. I followed all steps, but it seems that something is missing because I'm not getting the data into the database.

[felixmakinda]

return Response.json({ message: '..., });

You need to delete the file above, and you may consider using bcryptjs instead of bcrypt. Here is how you go about it:

1. pnpm install bcryptjs
2. Replace import for bcrypt with bcryptjs -> import bcrypt from 'bcryptjs';
3. Adjust the code to use bcryptjs -> const hashedPassword = await bcrypt.hash(user.password, 10);

That should fix it.

[wangyilan98]

return Response.json({ message: '..., });

You need to delete the file above, and you may consider using bcryptjs instead of bcrypt. Here is how you go about it:

1. pnpm install bcryptjs
2. Replace import for bcrypt with bcryptjs -> import bcrypt from 'bcryptjs';
3. Adjust the code to use bcryptjs -> const hashedPassword = await bcrypt.hash(user.password, 10);

That should fix it.

This method still failed, and the console shows a 500 error along with an Uncaught Error: [object Object]

[vineetsarpal]

What worked for me is setting the seed/route.ts like below:

import bcrypt from 'bcrypt';
import { db } from '@vercel/postgres';
import { invoices, customers, revenue, users } from '../lib/placeholder-data';

const client = await db.connect();

async function seedUsers() {
  await client.sql`CREATE EXTENSION IF NOT EXISTS "uuid-ossp"`;
  await client.sql`
    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.sql`
        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.sql`CREATE EXTENSION IF NOT EXISTS "uuid-ossp"`;

  await client.sql`
    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.sql`
        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.sql`CREATE EXTENSION IF NOT EXISTS "uuid-ossp"`;

  await client.sql`
    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.sql`
        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.sql`
    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.sql`
        INSERT INTO revenue (month, revenue)
        VALUES (${rev.month}, ${rev.revenue})
        ON CONFLICT (month) DO NOTHING;
      `,
    ),
  );

  return insertedRevenue;
}

export async function GET() {
  // return Response.json({
  //   message:
  //     'Uncomment this file and remove this line. You can delete this file when you are finished.',
  // });
  try {
    await client.sql`BEGIN`;
    await seedUsers();
    await seedCustomers();
    await seedInvoices();
    await seedRevenue();
    await client.sql`COMMIT`;

    return Response.json({ message: 'Database seeded successfully' });
  } catch (error) {
    await client.sql`ROLLBACK`;
    return Response.json({ error }, { status: 500 });
  }
}

The last try catch block was not getting executed. After commenting like shown above, I got it working!

[fuerst]

That issue helped me getting it running locally with @vercel/postgres: https://github.com/vercel/storage/issues/123

To summarize:

1. Follow steps 1 to 4 in https://vercel.com/docs/storage/vercel-postgres/local-development. Save the db.ts (step 3) in app/lib/db.ts
2. The sql function from @vercel/postgres doesn't pick up the neonConfig. Add a sql implementation in db.ts based on db.connect() which uses the neonConfig:

import {
  neonConfig,
  QueryResult,
  QueryResultRow,
} from '@neondatabase/serverless';
import { db } from '@vercel/postgres';

neonConfig.wsProxy = (host) => `${host}:54330/v1`;
neonConfig.useSecureWebSocket = false;
neonConfig.pipelineTLS = false;
neonConfig.pipelineConnect = false;

type Primitive = string | number | boolean | undefined | null;

const client = await db.connect();

export function sql<O extends QueryResultRow>(
  strings: TemplateStringsArray,
  ...values: Primitive[]
): Promise<QueryResult<O>> {
  return client.sql(strings, ...values);
}

3. Make sure the same version of @neondatabase/serverless will be used across all packages. Add to package.json:

"overrides": {
  "@neondatabase/serverless": "0.10.4"
}

1. Remove node_modules and pnpm-lock.json - and re-run pnpm install
2. Replace imports from @vercel/postgres with @/app/lib/db in both route.ts.
3. Load our sql implementation in app/lib/data.ts:

// import { sql } from '@vercel/postgres'; 
import { sql } from '@/app/lib/db';

Updated 2025-01-06: less changes in the tutorial code base necessary.

[connor-john-x]

I can use local postgres without using docker in the Chapter 6. but you need another middle-ware. check it out here. https://github.com/vercel/storage/issues/123#issuecomment-2598300413

[ArthaZeng]

Thank you so much ! @vineetsarpal Your codes work for me. Do you want to contribute to this repo? I guess other people also meet same problem.