pgsql-module-skill

PostgreSQL Module Skill

面向已有后端项目的开发者，快速集成 PostgreSQL 能力。

能力清单

能力	说明
连接配置	JDBC/连接池/环境变量配置
表结构设计	DDL 建表/字段类型/约束
ORM 映射	实体类/Repository/Service
JSON/JSONB	JSON 文档存储与查询
数组类型	PostgreSQL 原生数组支持
索引优化	索引设计/慢查询优化
事务处理	声明式/编程式事务
主从复制	读写分离/流复制

触发场景

用户说"帮我加 PostgreSQL"或"集成 PostgreSQL"时触发。

核心配置

Java (Spring Boot)

# application.yml
spring:
  datasource:
    url: jdbc:postgresql://${PG_HOST:localhost}:${PG_PORT:5432}/${PG_DATABASE:myapp}
    username: ${PG_USER:postgres}
    password: ${PG_PASSWORD:}
    driver-class-name: org.postgresql.Driver
    hikari:
      maximum-pool-size: 10
      minimum-idle: 5
      connection-timeout: 30000

Python (FastAPI)

# config.py
from pydantic_settings import BaseSettings

class Settings(BaseSettings):
    pg_host: str = "localhost"
    pg_port: int = 5432
    pg_user: str = "postgres"
    pg_password: str = ""
    pg_database: str = "myapp"

# database.py
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

engine = create_engine(
    f"postgresql+psycopg2://{user}:{password}@{host}:{port}/{database}",
    pool_size=10,
    max_overflow=20,
    pool_pre_ping=True
)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

表结构设计

基础表模板

CREATE TABLE "wg_user" (
  "id" BIGSERIAL PRIMARY KEY,
  "username" VARCHAR(50) NOT NULL UNIQUE,
  "password" VARCHAR(255) NOT NULL,
  "nickname" VARCHAR(100),
  "avatar" VARCHAR(500),
  "email" VARCHAR(100),
  "phone" VARCHAR(20),
  "status" SMALLINT NOT NULL DEFAULT 1 CHECK (status IN (0, 1)),
  "extra" JSONB DEFAULT '{}',
  "tags" TEXT[] DEFAULT '{}',
  "created_at" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updated_at" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "deleted_at" TIMESTAMP
);

CREATE INDEX "idx_wg_user_status" ON "wg_user" ("status");
CREATE INDEX "idx_wg_user_created_at" ON "wg_user" ("created_at");
CREATE INDEX "idx_wg_user_extra" ON "wg_user" USING GIN ("extra");

COMMENT ON TABLE "wg_user" IS '用户表';
COMMENT ON COLUMN "wg_user"."username" IS '用户名';