一.postgreSQL 是什么?
1.概念
PostgreSQL 是一个免费、开源、功能完善的对象关系型数据库。他和mysql 一样,都是关系型数据库。
2.postgreSQL 和 mysql 对比
在安装方面
我们使用mysql的时候需要安装 mysql服务端用来存取数据;然后再安装 MYSQL Workbench来图形化查看服务端的数据;如果你在nodejs代码里面操作数据库,你需要安装mysql2包,用代码的方式操作数据库,操作完以后可以在 MYSQL Workbench里面清晰看到数据变化。
和mysql一样,postgreSQL也需要安装三个包,安装 psql 服务端用来存取数据;然后安装 pgAdmin4 来图形化查询服务端的数据;你在nodejs项目里面要用代码操作psql数据库里面的数据,就要安装pg包进行增删改查,然后在pgAdmin4软件里面查看最终数据。
在项目架构方面
在安装方面他们是极其相似的,但是对于aiAgent项目而言,mysql 和 postgresql 之间最大的区别是: postgresql可以存储向量,它还有一个插件pgvector,可以对问题向量和文档向量做余弦相似度检索,找到相关文档,给大模型使用。
mysql虽然也能存储向量,可是他却没有做余弦相似度检索的工具,所以一般在企业里面要是有mysql存数文档,那么就会选择用milvus做向量存储。
当然最新版的milvus也能存储一部分文档块,但是他的能力有限,如果你存的太多,就会将整个milvus的检索向量的能力拉低,所以在企业里面,milvus 里面一般不会放文档块。milvus的优势就是向量相似度对比,那么文档存储就会交给mysql做。所以之前的样例都是mysql + milvus的实现方案。
如果你的项目是一个新项目,没有使用mysql,那么你就可以直接用postgreSQL,他内置的插件pgvector可以快速解决向量相似度检索,从而直接拿到这条数据对应的文本块。而不像 mysql + milvus 一样,先从milvus里面拿到id 然后用这个id 去mysql 里面拿文本块。
在sql语句方面
PostgreSQL 和 MySQL 共享标准 SQL 基础,基本增删改查差异不大;真正明显的差异集中在自增主键、分页简写、日期函数、布尔值、冲突处理、JSON、存储过程、DDL 事务、引号和特有系统函数。
3.运行原理
首先将一个长文本按照某种规则,剪切成文本块,然后由嵌入模型把这个文本块转化成向量,利用
sql语句将 文本块 和 向量 插入到postgreSQL数据库里面。
当用户问问题的时候,嵌入模型会将用户问题转化成向量,然后通过
sql语句发送给postgreSQL数据库,再由PostgreSQL配合pgvector计算向量距离并返回最相似的文本块。
pgvector支持多种距离算法,而不仅是余弦相似度,并且检索到的文本还需要拼接到prompt里面交给大模型生成最终回答。
注意:问题向量和文档向量必须使用同一个嵌入模型生成,否则维度不同、语义空间不同,无法直接比较。
4.安装
4.1 安装postgreSQL
4.2 安装 pgAdmin 4
官网: www.pgadmin.org/download/pg…
pgAdmin 4图形界面的使用和 mysql workbetch 很相似,连接数据库后就能使用sql 语句进行增删改查了。
4.3 在nodejs 里面使用
4.3.1 初始化项目
1mkdir node-pg-demo 2cd node-pg-demo 3npm init -y 4npm install pg dotenv 5
4.3.2配置.env
1PGHOST=localhost 2PGPORT=5432 3PGUSER=postgres 4PGPASSWORD=你的密码 5PGDATABASE=node_pg_demo 6
4.3.3连接数据库
1import pg from "pg"; 2import dotenv from "dotenv"; 3 4dotenv.config(); 5 6const { Pool } = pg; 7 8const pool = new Pool({ 9 host: process.env.PGHOST, 10 port: process.env.PGPORT, 11 user: process.env.PGUSER, 12 password: process.env.PGPASSWORD, 13 database: process.env.PGDATABASE, 14}); 15 16// 连接测试 17pool.query("SELECT NOW()", (err, res) => { 18 if (err) { 19 console.error("连接失败:", err.message); 20 } else { 21 console.log("数据库连接成功,服务器时间:", res.rows[0].now); 22 } 23}); 24 25export default pool; 26
4.3.4新建数据表
1import pool from "./db.js"; 2 3const createTable = ` 4 CREATE TABLE IF NOT EXISTS users ( 5 id SERIAL PRIMARY KEY, 6 name VARCHAR(100) NOT NULL, 7 email VARCHAR(255) UNIQUE NOT NULL, 8 age INT, 9 created_at TIMESTAMPTZ DEFAULT NOW() 10 ) 11`; 12 13const run = async () => { 14 try { 15 await pool.query(createTable); 16 console.log("users 表创建成功"); 17 } catch (err) { 18 console.error("建表失败:", err.message); 19 } finally { 20 await pool.end(); 21 } 22}; 23 24run(); 25
4.3.5增删改查
1import pool from "./db.js"; 2 3// 1. 新增 4const createUser = async (name, email, age) => { 5 const sql = ` 6 INSERT INTO users (name, email, age) 7 VALUES ($1, $2, $3) 8 RETURNING * 9 `; 10 const res = await pool.query(sql, [name, email, age]); 11 return res.rows[0]; 12}; 13 14// 2. 查询全部 15const getAllUsers = async () => { 16 const res = await pool.query( 17 "SELECT * FROM users ORDER BY id DESC" 18 ); 19 return res.rows; 20}; 21 22// 3. 按 ID 查询 23const getUserById = async (id) => { 24 const res = await pool.query( 25 "SELECT * FROM users WHERE id = $1", 26 [id] 27 ); 28 return res.rows[0]; 29}; 30 31// 4. 修改 32const updateUser = async (id, name, age) => { 33 const sql = ` 34 UPDATE users 35 SET name = $1, age = $2 36 WHERE id = $3 37 RETURNING * 38 `; 39 const res = await pool.query(sql, [name, age, id]); 40 return res.rows[0]; 41}; 42 43// 5. 删除 44const deleteUser = async (id) => { 45 const res = await pool.query( 46 "DELETE FROM users WHERE id = $1 RETURNING *", 47 [id] 48 ); 49 return res.rows[0]; 50}; 51 52// 演示完整流程 53const run = async () => { 54 try { 55 // 新增 56 const user = await createUser("张三", "zhangsan@example.com", 28); 57 console.log("新增:", user); 58 59 // 查询全部 60 const list = await getAllUsers(); 61 console.log("全部用户:", list); 62 63 // 按 ID 查询 64 const one = await getUserById(user.id); 65 console.log("单条查询:", one); 66 67 // 修改 68 const updated = await updateUser(user.id, "李四", 30); 69 console.log("修改后:", updated); 70 71 // 删除 72 const deleted = await deleteUser(user.id); 73 console.log("已删除:", deleted); 74 } catch (err) { 75 console.error("操作失败:", err.message); 76 } finally { 77 await pool.end(); 78 } 79}; 80 81run(); 82
4.4 在nestjs里面使用
4.4.1 初始化项目
1# 使用 Nest CLI 创建项目 2npm i -g @nestjs/cli 3nest new nest-pg-demo 4cd nest-pg-demo 5 6# 安装 PostgreSQL 驱动和 TypeORM 7npm install @nestjs/typeorm typeorm pg 8npm install @nestjs/mapped-types 9
4.4.2 连接数据库
1import { Module } from '@nestjs/common'; 2import { TypeOrmModule } from '@nestjs/typeorm'; 3import { UsersModule } from './users/users.module'; 4 5@Module({ 6 imports: [ 7 TypeOrmModule.forRoot({ 8 type: 'postgres', 9 host: 'localhost', 10 port: 5432, 11 username: 'postgres', 12 password: '你的密码', 13 database: 'nest_pg_demo', 14 autoLoadEntities: true, 15 synchronize: true, // 开发环境自动建表,生产环境建议关闭 16 }), 17 UsersModule, 18 ], 19}) 20export class AppModule {} 21
4.4.3创建 User 实体(Entity)
新建 src/users/entities/user.entity.ts
1import { 2 Column, 3 CreateDateColumn, 4 Entity, 5 PrimaryGeneratedColumn, 6} from 'typeorm'; 7 8@Entity('users') 9export class User { 10 @PrimaryGeneratedColumn() 11 id: number; 12 13 @Column({ length: 100 }) 14 name: string; 15 16 @Column({ unique: true, length: 255 }) 17 email: string; 18 19 @Column({ nullable: true }) 20 age: number; 21 22 @CreateDateColumn() 23 createdAt: Date; 24} 25
4.4.4创建 DTO(数据传输对象)
新建 src/users/dto/create-user.dto.ts:
1export class CreateUserDto { 2 name: string; 3 email: string; 4 age?: number; 5} 6
新建 src/users/dto/update-user.dto.ts:
1import { PartialType } from '@nestjs/mapped-types'; 2import { CreateUserDto } from './create-user.dto'; 3 4export class UpdateUserDto extends PartialType(CreateUserDto) {} 5
4.4.5创建 Service(业务逻辑 + CRUD)
新建 src/users/users.service.ts:
1import { Injectable, NotFoundException } from '@nestjs/common'; 2import { InjectRepository } from '@nestjs/typeorm'; 3import { Repository } from 'typeorm'; 4import { User } from './entities/user.entity'; 5import { CreateUserDto } from './dto/create-user.dto'; 6import { UpdateUserDto } from './dto/update-user.dto'; 7 8@Injectable() 9export class UsersService { 10 constructor( 11 @InjectRepository(User) 12 private readonly userRepository: Repository<User>, 13 ) {} 14 15 // 新增 16 async create(createUserDto: CreateUserDto): Promise<User> { 17 const user = this.userRepository.create(createUserDto); 18 return await this.userRepository.save(user); 19 } 20 21 // 查询全部 22 async findAll(): Promise<User[]> { 23 return await this.userRepository.find({ 24 order: { id: 'DESC' }, 25 }); 26 } 27 28 // 按 ID 查询 29 async findOne(id: number): Promise<User> { 30 const user = await this.userRepository.findOne({ where: { id } }); 31 if (!user) { 32 throw new NotFoundException(`ID 为 ${id} 的用户不存在`); 33 } 34 return user; 35 } 36 37 // 修改 38 async update(id: number, updateUserDto: UpdateUserDto): Promise<User> { 39 const user = await this.findOne(id); 40 Object.assign(user, updateUserDto); 41 return await this.userRepository.save(user); 42 } 43 44 // 删除 45 async remove(id: number): Promise<void> { 46 const user = await this.findOne(id); 47 await this.userRepository.remove(user); 48 } 49} 50
4.4.6创建 Controller(路由)
新建 src/users/users.controller.ts:
1import { 2 Body, 3 Controller, 4 Delete, 5 Get, 6 Param, 7 Patch, 8 Post, 9} from '@nestjs/common'; 10import { UsersService } from './users.service'; 11import { CreateUserDto } from './dto/create-user.dto'; 12import { UpdateUserDto } from './dto/update-user.dto'; 13 14@Controller('users') 15export class UsersController { 16 constructor(private readonly usersService: UsersService) {} 17 18 // POST /users 19 @Post() 20 create(@Body() createUserDto: CreateUserDto) { 21 return this.usersService.create(createUserDto); 22 } 23 24 // GET /users 25 @Get() 26 findAll() { 27 return this.usersService.findAll(); 28 } 29 30 // GET /users/:id 31 @Get(':id') 32 findOne(@Param('id') id: string) { 33 return this.usersService.findOne(Number(id)); 34 } 35 36 // PATCH /users/:id 37 @Patch(':id') 38 update(@Param('id') id: string, @Body() updateUserDto: UpdateUserDto) { 39 return this.usersService.update(Number(id), updateUserDto); 40 } 41 42 // DELETE /users/:id 43 @Delete(':id') 44 remove(@Param('id') id: string) { 45 return this.usersService.remove(Number(id)); 46 } 47} 48
4.4.7 创建 UsersModule
新建 src/users/users.module.ts:
1import { Module } from '@nestjs/common'; 2import { TypeOrmModule } from '@nestjs/typeorm'; 3import { UsersService } from './users.service'; 4import { UsersController } from './users.controller'; 5import { User } from './entities/user.entity'; 6 7@Module({ 8 imports: [TypeOrmModule.forFeature([User])], 9 controllers: [UsersController], 10 providers: [UsersService], 11 exports: [UsersService], 12}) 13export class UsersModule {} 14
参考文档
《ai agent --- postgreSQL 关系型数据库》 是转载文章,点击查看原文。