NEXT.js 创建postgres数据库-关联github项目-连接数据库-在项目初始化数据库的数据

1. github创建项目仓库
2. 创建Vercel账号
3. 选择hobby
4. 连接github仓库
5. install - deploy
6. 创建postgres数据库（等待deploy完成）
   • Continue to Dashboard
   • Storage（头部nav哪里）
   • create Postgres
   • connect
   • 连接完后，切换到.env.local（Quickstart那里）
   • Copy Snippet
7. 项目根目录新增.env文件，把复制的东西，粘贴进去
8. .gitignore 新增.env，避免暴露数据库密钥
9. npm i @vercel/postgres
10. 创建文件
    • your project root/src/app/seed/route.ts
    • your project root/src/lib/placeholder-data.ts
11. 查看route.ts说明，安装依赖，打开sql语句的注释
12. 访问http://localhost:3000/seed
13. 浏览器显示{"message":"Database seeded successfully"}，数据库数据写入成功

/seed/route.ts

// todo npm install bcrypt || npm install bcryptjs
import bcrypt from 'bcrypt';
// import bcrypt from 'bcryptjs';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 });}
}

/lib/placeholder-data.ts

// This file contains placeholder data that you'll be replacing with real data in the Data Fetching chapter:
// https://nextjs.org/learn/dashboard-app/fetching-data
const users = [{id: '410544b2-4001-4271-9855-fec4b6a6442a',name: 'User',email: 'user@nextmail.com',password: '123456',},
];const customers = [{id: 'd6e15727-9fe1-4961-8c5b-ea44a9bd81aa',name: 'Evil Rabbit',email: 'evil@rabbit.com',image_url: '/customers/evil-rabbit.png',}
];const invoices = [{customer_id: customers[0].id,amount: 15795,status: 'pending',date: '2022-12-06',}
];const revenue = [{ month: 'Jan', revenue: 2000 }
];export { users, customers, invoices, revenue };