上回说到，我们通过docker容器进行数据库的部署，这篇文章将介绍如何通过网站程序对数据库进行初始化。
编写初始化数据库文件
我们用一些初始数据来填充数据库。在 /app 目录中，创建seed文件夹，并且在这个文件夹创建一个名为 route.ts 的 Next.js路由处理程序，它将用于填充您的数据库。这创建了一个服务器端端点，您可以在浏览器中访问它来开始填充您的数据库。
编辑/app/seed/route.ts 导入两个组件import bcrypt from 'bcryptjs'; ，import { Pool } from 'pg';，一个组件用于加密明文密码变成密文密码，一个组件用于操作数据库文件。
由于这两个组件都不是默认的，因此需要使用pnpm安装pnpm install bcryptjspnpm install pg
安装完成之后，如果编辑器还是出现红色波浪线，说明编辑器还是无法找到这两个模块，这样需要进一步操作pnpm i --save-dev @types/bcryptjspnpm i --save-dev @types/pg
安装 PostgreSQL 客户端库pg的TypeScript类型定义，并将其保存为项目的开发依赖。这样做可以让TypeScript编译器和你的IDE理解pg库的结构，提供更好的类型检查和代码补全功能，但这些类型定义不会影响你的生产代码或增加生产构建的大小。
依赖都安装完成之后，我们开始编码啦，首先需要初始化我们的数据库连接池
接下来我们要准备一些测试的数据，用于填充至数据库，创建lib文件夹和placeholder-data.tsx文件 路径：/app/lib/placeholder-data.tsx
const users = [
{
id: '410544b2-4001-4271-9855-fec4b6a6442a',
name: 'User',
email: '[email protected]',
password: '123456',
},
];
const customers = [
{
id: 'd6e15727-9fe1-4961-8c5b-ea44a9bd81aa',
name: 'Evil Rabbit',
email: '[email protected]',
image_url: '/customers/evil-rabbit.png',
},
{
id: '3958dc9e-712f-4377-85e9-fec4b6a6442a',
name: 'Delba de Oliveira',
email: '[email protected]',
image_url: '/customers/delba-de-oliveira.png',
},
{
id: '3958dc9e-742f-4377-85e9-fec4b6a6442a',
name: 'Lee Robinson',
email: '[email protected]',
image_url: '/customers/lee-robinson.png',
},
{
id: '76d65c26-f784-44a2-ac19-586678f7c2f2',
name: 'Michael Novotny',
email: '[email protected]',
image_url: '/customers/michael-novotny.png',
},
{
id: 'CC27C14A-0ACF-4F4A-A6C9-D45682C144B9',
name: 'Amy Burns',
email: '[email protected]',
image_url: '/customers/amy-burns.png',
},
{
id: '13D07535-C59E-4157-A011-F8D2EF4E0CBB',
name: 'Balazs Orban',
email: '[email protected]',
image_url: '/customers/balazs-orban.png',
},
];
const invoices = [
{
customer_id: customers[0].id,
amount: 15795,
status: 'pending',
date: '2022-12-06',
},
{
customer_id: customers[1].id,
amount: 20348,
status: 'pending',
date: '2022-11-14',
},
{
customer_id: customers[4].id,
amount: 3040,
status: 'paid',
date: '2022-10-29',
},
{
customer_id: customers[3].id,
amount: 44800,
status: 'paid',
date: '2023-09-10',
},
{
customer_id: customers[5].id,
amount: 34577,
status: 'pending',
date: '2023-08-05',
},
{
customer_id: customers[2].id,
amount: 54246,
status: 'pending',
date: '2023-07-16',
},
{
customer_id: customers[0].id,
amount: 666,
status: 'pending',
date: '2023-06-27',
},
{
customer_id: customers[3].id,
amount: 32545,
status: 'paid',
date: '2023-06-09',
},
{
customer_id: customers[4].id,
amount: 1250,
status: 'paid',
date: '2023-06-17',
},
{
customer_id: customers[5].id,
amount: 8546,
status: 'paid',
date: '2023-06-07',
},
{
customer_id: customers[1].id,
amount: 500,
status: 'paid',
date: '2023-08-19',
},
{
customer_id: customers[5].id,
amount: 8945,
status: 'paid',
date: '2023-06-03',
},
{
customer_id: customers[2].id,
amount: 1000,
status: 'paid',
date: '2022-06-05',
},
];
const revenue = [
{ month: 'Jan', revenue: 2000 },
{ month: 'Feb', revenue: 1800 },
{ month: 'Mar', revenue: 2200 },
{ month: 'Apr', revenue: 2500 },
{ month: 'May', revenue: 2300 },
{ month: 'Jun', revenue: 3200 },
{ month: 'Jul', revenue: 3500 },
{ month: 'Aug', revenue: 3700 },
{ month: 'Sep', revenue: 2500 },
{ month: 'Oct', revenue: 2800 },
{ month: 'Nov', revenue: 3000 },
{ month: 'Dec', revenue: 4800 },
];
export { users, customers, invoices, revenue };
有了这些测试数据之后，我们在route.ts上面引入import { invoices, customers, revenue, users } from '../lib/placeholder-data';，invoices（发票）、customers（客户）、revenue（收入）、users（用户）测试数据。
然后根据不同的数据结构类型，创建数据库表结构
import bcrypt from 'bcryptjs';
import { Pool } from 'pg';
import { invoices, customers, revenue, users } from '../lib/placeholder-data';
const pool = new Pool({
user: 'postgres',
host: 'localhost',
database: 'nextjsdbs',
password: '12345678',
port: 5432,
});
async function createTables() {
try {
await pool.query('CREATE EXTENSION IF NOT EXISTS "uuid-ossp"');
await pool.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
);
`);
console.log('Users table created or already exists');
await pool.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
);
`);
console.log('Customers table created or already exists');
await pool.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
);
`);
console.log('Invoices table created or already exists');
await pool.query(`
CREATE TABLE IF NOT EXISTS revenue (
month VARCHAR(4) NOT NULL UNIQUE,
revenue INT NOT NULL
);
`);
console.log('Revenue table created or already exists');
} catch (error) {
console.error('Error creating tables:', error);
throw error;
}
}
async function seedUsers() {
console.log('Starting seedUsers function');
try {
const insertedUsers = await Promise.all(
users.map(async (user) => {
const hashedPassword = await bcrypt.hash(user.password, 10);
console.log(`Attempting to insert user: ${user.id}`);
const result = await pool.query(`
INSERT INTO users (id, name, email, password)
VALUES ($1, $2, $3, $4)
ON CONFLICT (id) DO NOTHING
RETURNING *;
`, [user.id, user.name, user.email, hashedPassword]);
console.log(`Insert result for user ${user.id}:`, result.rowCount, result.rows);
return result;
})
);
console.log(`Attempted to insert ${users.length} users`);
return insertedUsers;
} catch (error) {
console.error('Error in seedUsers:', error);
throw error;
}
}
async function seedCustomers() {
const insertedCustomers = await Promise.all(
customers.map(
(customer) => pool.query(`
INSERT INTO customers (id, name, email, image_url)
VALUES ($1, $2, $3, $4)
ON CONFLICT (id) DO NOTHING;
`, [customer.id, customer.name, customer.email, customer.image_url]),
),
);
return insertedCustomers;
}
async function seedInvoices() {
const insertedInvoices = await Promise.all(
invoices.map(
(invoice) => pool.query(`
INSERT INTO invoices (customer_id, amount, status, date)
VALUES ($1, $2, $3, $4)
ON CONFLICT (id) DO NOTHING;
`, [invoice.customer_id, invoice.amount, invoice.status, invoice.date]),
),
);
return insertedInvoices;
}
async function seedRevenue() {
const insertedRevenue = await Promise.all(
revenue.map(
(rev) => pool.query(`
INSERT INTO revenue (month, revenue)
VALUES ($1, $2)
ON CONFLICT (month) DO NOTHING;
`, [rev.month, rev.revenue]),
),
);
return insertedRevenue;
}
export async function GET() {
try {
console.log('Starting database seeding...');
// 检查数据库连接
const testConnection = await pool.query('SELECT current_database(), current_user');
console.log('Connected to database:', testConnection.rows[0].current_database);
console.log('Connected as user:', testConnection.rows[0].current_user);
// 创建表（在事务外）
await createTables();
await pool.query('BEGIN');
await seedUsers();
await pool.query('COMMIT');
await pool.query('BEGIN');
console.log('Transaction begun');
await seedCustomers();
await seedInvoices();
await seedRevenue();
await pool.query('COMMIT');
console.log('Transaction committed');
return Response.json({ message: 'Database seeded successfully' });
} catch (error) {
console.error('Error seeding database:', error);
await pool.query('ROLLBACK');
if (error instanceof Error) {
return Response.json({ error: error.message, stack: error.stack }, { status: 500 });
} else {
return Response.json({ error: 'An unknown error occurred' }, { status: 500 });
}
} finally {
await pool.end();
console.log('Pool ended');
}
}
完成这一切之后，我们访问/seed 目录即可初始化数据库
通过数据库链接工具可以看到数据已经成功被写入数据库
好了，这部分到此结束啦，更新文章正在更新中，喜欢的帮忙点个赞和转发给身边人呗，感激不尽。
更详细内容查看
独立博客 https://www.dataeast.cn/
CSDN博客 https://blog.csdn.net/siberiaWarpDrive
B站视频空间 https://space.bilibili.com/25871614?spm_id_from=333.1007.0.0
关注 “曲速引擎 Warp Drive” 微信公众号
