ai agent --- postgreSQL 关系型数据库

本文目录

1.概念
2.postgreSQL 和 mysql 对比
3.运行原理
4.1 安装postgreSQL
4.2 安装 pgAdmin 4
4.3 在nodejs 里面使用
4.4 在nestjs里面使用
使用 Nest CLI 创建项目
安装 PostgreSQL 驱动和 TypeORM

一.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

官网 www.postgresql.org/

4.2 安装 pgAdmin 4

官网： www.pgadmin.org/download/pg…

pgAdmin 4图形界面的使用和 mysql workbetch 很相似，连接数据库后就能使用sql 语句进行增删改查了。

4.3 在nodejs 里面使用

4.3.1 初始化项目

mkdir node-pg-demo
cd node-pg-demo
npm init -y
npm install pg dotenv

4.3.2配置.env

<span>PGHOST</span>=localhost
<span>PGPORT</span>=<span>5432</span>
<span>PGUSER</span>=postgres
<span>PGPASSWORD</span>=你的密码
<span>PGDATABASE</span>=node_pg_demo

4.3.3连接数据库

<span>import</span> pg <span>from</span> <span>"pg"</span>;
<span>import</span> dotenv <span>from</span> <span>"dotenv"</span>;

dotenv.<span>config</span>();

<span>const</span> { <span>Pool</span> } = pg;

<span>const</span> pool = <span>new</span> <span>Pool</span>({
  <span>host</span>: process.<span>env</span>.<span>PGHOST</span>,
  <span>port</span>: process.<span>env</span>.<span>PGPORT</span>,
  <span>user</span>: process.<span>env</span>.<span>PGUSER</span>,
  <span>password</span>: process.<span>env</span>.<span>PGPASSWORD</span>,
  <span>database</span>: process.<span>env</span>.<span>PGDATABASE</span>,
});

<span>// 连接测试</span>
pool.<span>query</span>(<span>"SELECT NOW()"</span>, <span>(<span>err, res</span>) =></span> {
  <span>if</span> (err) {
    <span>console</span>.<span>error</span>(<span>"连接失败："</span>, err.<span>message</span>);
  } <span>else</span> {
    <span>console</span>.<span>log</span>(<span>"数据库连接成功，服务器时间："</span>, res.<span>rows</span>[<span>0</span>].<span>now</span>);
  }
});

<span>export</span> <span>default</span> pool;

4.3.4新建数据表

<span>import</span> pool <span>from</span> <span>"./db.js"</span>;

<span>const</span> createTable = <span>`
  CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    age INT,
    created_at TIMESTAMPTZ DEFAULT NOW()
  )
`</span>;

<span>const</span> <span>run</span> = <span>async</span> (<span></span>) => {
  <span>try</span> {
    <span>await</span> pool.<span>query</span>(createTable);
    <span>console</span>.<span>log</span>(<span>"users 表创建成功"</span>);
  } <span>catch</span> (err) {
    <span>console</span>.<span>error</span>(<span>"建表失败："</span>, err.<span>message</span>);
  } <span>finally</span> {
    <span>await</span> pool.<span>end</span>();
  }
};

<span>run</span>();

4.3.5增删改查

<span>import</span> pool <span>from</span> <span>"./db.js"</span>;

<span>// 1. 新增</span>
<span>const</span> <span>createUser</span> = <span>async</span> (<span>name, email, age</span>) => {
  <span>const</span> sql = <span>`
    INSERT INTO users (name, email, age)
    VALUES ($1, $2, $3)
    RETURNING *
  `</span>;
  <span>const</span> res = <span>await</span> pool.<span>query</span>(sql, [name, email, age]);
  <span>return</span> res.<span>rows</span>[<span>0</span>];
};

<span>// 2. 查询全部</span>
<span>const</span> <span>getAllUsers</span> = <span>async</span> (<span></span>) => {
  <span>const</span> res = <span>await</span> pool.<span>query</span>(
    <span>"SELECT * FROM users ORDER BY id DESC"</span>
  );
  <span>return</span> res.<span>rows</span>;
};

<span>// 3. 按 ID 查询</span>
<span>const</span> <span>getUserById</span> = <span>async</span> (<span>id</span>) => {
  <span>const</span> res = <span>await</span> pool.<span>query</span>(
    <span>"SELECT * FROM users WHERE id = $1"</span>,
    [id]
  );
  <span>return</span> res.<span>rows</span>[<span>0</span>];
};

<span>// 4. 修改</span>
<span>const</span> <span>updateUser</span> = <span>async</span> (<span>id, name, age</span>) => {
  <span>const</span> sql = <span>`
    UPDATE users
    SET name = $1, age = $2
    WHERE id = $3
    RETURNING *
  `</span>;
  <span>const</span> res = <span>await</span> pool.<span>query</span>(sql, [name, age, id]);
  <span>return</span> res.<span>rows</span>[<span>0</span>];
};

<span>// 5. 删除</span>
<span>const</span> <span>deleteUser</span> = <span>async</span> (<span>id</span>) => {
  <span>const</span> res = <span>await</span> pool.<span>query</span>(
    <span>"DELETE FROM users WHERE id = $1 RETURNING *"</span>,
    [id]
  );
  <span>return</span> res.<span>rows</span>[<span>0</span>];
};

<span>// 演示完整流程</span>
<span>const</span> <span>run</span> = <span>async</span> (<span></span>) => {
  <span>try</span> {
    <span>// 新增</span>
    <span>const</span> user = <span>await</span> <span>createUser</span>(<span>"张三"</span>, <span>"zhangsan@example.com"</span>, <span>28</span>);
    <span>console</span>.<span>log</span>(<span>"新增："</span>, user);

    <span>// 查询全部</span>
    <span>const</span> list = <span>await</span> <span>getAllUsers</span>();
    <span>console</span>.<span>log</span>(<span>"全部用户："</span>, list);

    <span>// 按 ID 查询</span>
    <span>const</span> one = <span>await</span> <span>getUserById</span>(user.<span>id</span>);
    <span>console</span>.<span>log</span>(<span>"单条查询："</span>, one);

    <span>// 修改</span>
    <span>const</span> updated = <span>await</span> <span>updateUser</span>(user.<span>id</span>, <span>"李四"</span>, <span>30</span>);
    <span>console</span>.<span>log</span>(<span>"修改后："</span>, updated);

    <span>// 删除</span>
    <span>const</span> deleted = <span>await</span> <span>deleteUser</span>(user.<span>id</span>);
    <span>console</span>.<span>log</span>(<span>"已删除："</span>, deleted);
  } <span>catch</span> (err) {
    <span>console</span>.<span>error</span>(<span>"操作失败："</span>, err.<span>message</span>);
  } <span>finally</span> {
    <span>await</span> pool.<span>end</span>();
  }
};

<span>run</span>();

4.4 在nestjs里面使用

4.4.1 初始化项目

# 使用 <span>Nest</span> <span>CLI</span> 创建项目
npm i -g @nestjs/cli
nest <span>new</span> nest-pg-demo
cd nest-pg-demo

# 安装 <span>PostgreSQL</span> 驱动和 <span>TypeORM</span>
npm install @nestjs/typeorm typeorm pg
npm install @nestjs/mapped-types

4.4.2 连接数据库

<span>import</span> { <span>Module</span> } <span>from</span> <span>'@nestjs/common'</span>;
<span>import</span> { <span>TypeOrmModule</span> } <span>from</span> <span>'@nestjs/typeorm'</span>;
<span>import</span> { <span>UsersModule</span> } <span>from</span> <span>'./users/users.module'</span>;

@<span>Module</span>({
  <span>imports</span>: [
    <span>TypeOrmModule</span>.<span>forRoot</span>({
      <span>type</span>: <span>'postgres'</span>,
      <span>host</span>: <span>'localhost'</span>,
      <span>port</span>: <span>5432</span>,
      <span>username</span>: <span>'postgres'</span>,
      <span>password</span>: <span>'你的密码'</span>,
      <span>database</span>: <span>'nest_pg_demo'</span>,
      <span>autoLoadEntities</span>: <span>true</span>,
      <span>synchronize</span>: <span>true</span>, <span>// 开发环境自动建表，生产环境建议关闭</span>
    }),
    <span>UsersModule</span>,
  ],
})
<span>export</span> <span>class</span> <span>AppModule</span> {}

4.4.3创建 User 实体（Entity）

新建 src/users/entities/user.entity.ts

<span>import</span> {
  <span>Column</span>,
  <span>CreateDateColumn</span>,
  <span>Entity</span>,
  <span>PrimaryGeneratedColumn</span>,
} <span>from</span> <span>'typeorm'</span>;

@<span>Entity</span>(<span>'users'</span>)
<span>export</span> <span>class</span> <span>User</span> {
  @<span>PrimaryGeneratedColumn</span>()
  <span>id</span>: number;

  @<span>Column</span>({ <span>length</span>: <span>100</span> })
  <span>name</span>: string;

  @<span>Column</span>({ <span>unique</span>: <span>true</span>, <span>length</span>: <span>255</span> })
  <span>email</span>: string;

  @<span>Column</span>({ <span>nullable</span>: <span>true</span> })
  <span>age</span>: number;

  @<span>CreateDateColumn</span>()
  <span>createdAt</span>: <span>Date</span>;
}

4.4.4创建 DTO（数据传输对象）

新建 src/users/dto/create-user.dto.ts：

<span>export</span> <span>class</span> <span>CreateUserDto</span> {
  <span>name</span>: <span>string</span>;
  <span>email</span>: <span>string</span>;
  age?: <span>number</span>;
}

新建 src/users/dto/update-user.dto.ts：

<span>import</span> { <span>PartialType</span> } from '<span>@nestjs</span>/mapped-types';
<span>import</span> { <span>CreateUserDto</span> } from './create-user.dto';

<span>export</span> <span><span>class</span> <span>UpdateUserDto</span> <span>extends</span> <span>PartialType</span>(<span><span>CreateUserDto</span></span>) </span>{}

4.4.5创建 Service（业务逻辑 + CRUD）

新建 src/users/users.service.ts：

<span>import</span> { <span>Injectable</span>, <span>NotFoundException</span> } <span>from</span> <span>'@nestjs/common'</span>;
<span>import</span> { <span>InjectRepository</span> } <span>from</span> <span>'@nestjs/typeorm'</span>;
<span>import</span> { <span>Repository</span> } <span>from</span> <span>'typeorm'</span>;
<span>import</span> { <span>User</span> } <span>from</span> <span>'./entities/user.entity'</span>;
<span>import</span> { <span>CreateUserDto</span> } <span>from</span> <span>'./dto/create-user.dto'</span>;
<span>import</span> { <span>UpdateUserDto</span> } <span>from</span> <span>'./dto/update-user.dto'</span>;

<span>@Injectable</span>()
<span>export</span> <span>class</span> <span>UsersService</span> {
  <span>constructor</span>(<span>
    <span>@InjectRepository</span>(User)
    <span>private</span> <span>readonly</span> userRepository: Repository<User>,
  </span>) {}

  <span>// 新增</span>
  <span>async</span> <span>create</span>(<span>createUserDto</span>: <span>CreateUserDto</span>): <span>Promise</span><<span>User</span>> {
    <span>const</span> user = <span>this</span>.<span>userRepository</span>.<span>create</span>(createUserDto);
    <span>return</span> <span>await</span> <span>this</span>.<span>userRepository</span>.<span>save</span>(user);
  }

  <span>// 查询全部</span>
  <span>async</span> <span>findAll</span>(): <span>Promise</span><<span>User</span>[]> {
    <span>return</span> <span>await</span> <span>this</span>.<span>userRepository</span>.<span>find</span>({
      <span>order</span>: { <span>id</span>: <span>'DESC'</span> },
    });
  }

  <span>// 按 ID 查询</span>
  <span>async</span> <span>findOne</span>(<span>id</span>: <span>number</span>): <span>Promise</span><<span>User</span>> {
    <span>const</span> user = <span>await</span> <span>this</span>.<span>userRepository</span>.<span>findOne</span>({ <span>where</span>: { id } });
    <span>if</span> (!user) {
      <span>throw</span> <span>new</span> <span>NotFoundException</span>(<span>`ID 为 <span>${id}</span> 的用户不存在`</span>);
    }
    <span>return</span> user;
  }

  <span>// 修改</span>
  <span>async</span> <span>update</span>(<span>id</span>: <span>number</span>, <span>updateUserDto</span>: <span>UpdateUserDto</span>): <span>Promise</span><<span>User</span>> {
    <span>const</span> user = <span>await</span> <span>this</span>.<span>findOne</span>(id);
    <span>Object</span>.<span>assign</span>(user, updateUserDto);
    <span>return</span> <span>await</span> <span>this</span>.<span>userRepository</span>.<span>save</span>(user);
  }

  <span>// 删除</span>
  <span>async</span> <span>remove</span>(<span>id</span>: <span>number</span>): <span>Promise</span><<span>void</span>> {
    <span>const</span> user = <span>await</span> <span>this</span>.<span>findOne</span>(id);
    <span>await</span> <span>this</span>.<span>userRepository</span>.<span>remove</span>(user);
  }
}

4.4.6创建 Controller（路由）

新建 src/users/users.controller.ts：

<span>import</span> {
  <span>Body</span>,
  <span>Controller</span>,
  <span>Delete</span>,
  <span>Get</span>,
  <span>Param</span>,
  <span>Patch</span>,
  <span>Post</span>,
} <span>from</span> '@<span>nestjs</span>/<span>common</span>';
<span>import</span> { <span>UsersService</span> } <span>from</span> './<span>users</span><span>.service</span>';
<span>import</span> { <span>CreateUserDto</span> } <span>from</span> './<span>dto</span>/<span>create-user</span><span>.dto</span>';
<span>import</span> { <span>UpdateUserDto</span> } <span>from</span> './<span>dto</span>/<span>update-user</span><span>.dto</span>';

@<span>Controller</span>(<span>'users'</span>)
<span>export</span> <span>class</span> <span>UsersController</span> {
  <span>constructor</span>(private readonly <span>usersService</span>: UsersService) {}

  <span>// POST /users</span>
  <span>@Post</span>()
  <span>create</span>(<span>@Body</span>() <span>createUserDto</span>: CreateUserDto) {
    <span>return</span> <span>this</span><span>.usersService</span><span>.create</span>(createUserDto);
  }

  <span>// GET /users</span>
  @<span>Get</span>()
  <span>findAll</span>() {
    <span>return</span> <span>this</span><span>.usersService</span><span>.findAll</span>();
  }

  <span>// GET /users/:id</span>
  @<span>Get</span>(<span>':id'</span>)
  <span>findOne</span>(<span>@Param</span>(<span>'id'</span>) <span>id</span>: string) {
    <span>return</span> <span>this</span><span>.usersService</span><span>.findOne</span>(<span>Number</span>(id));
  }

  <span>// PATCH /users/:id</span>
  @<span>Patch</span>(<span>':id'</span>)
  <span>update</span>(<span>@Param</span>(<span>'id'</span>) <span>id</span>: string, <span>@Body</span>() <span>updateUserDto</span>: UpdateUserDto) {
    <span>return</span> <span>this</span><span>.usersService</span><span>.update</span>(<span>Number</span>(id), updateUserDto);
  }

  <span>// DELETE /users/:id</span>
  @<span>Delete</span>(<span>':id'</span>)
  <span>remove</span>(<span>@Param</span>(<span>'id'</span>) <span>id</span>: string) {
    <span>return</span> <span>this</span><span>.usersService</span><span>.remove</span>(<span>Number</span>(id));
  }
}

4.4.7 创建 UsersModule

新建 src/users/users.module.ts：

<span>import</span> { Module } <span>from</span> <span>'@nestjs/common'</span>;
<span>import</span> { TypeOrmModule } <span>from</span> <span>'@nestjs/typeorm'</span>;
<span>import</span> { UsersService } <span>from</span> <span>'./users.service'</span>;
<span>import</span> { UsersController } <span>from</span> <span>'./users.controller'</span>;
<span>import</span> { User } <span>from</span> <span>'./entities/user.entity'</span>;

<span>@Module(<span>{
  imports: [TypeOrmModule.forFeature(<span>[User]</span>)],
  controllers: [UsersController],
  providers: [UsersService],
  exports: [UsersService],
}</span>)</span>
export <span>class</span> <span>UsersModule</span> {}