Postgre-Kit无法完成PostgreSQL数据库代码访问问题求助

目标 #

连接PostgreSQL数据库并向表中添加简单数据

环境配置 #

// swift-tools-version:5.9
import PackageDescription

let package = Package(
    name: "Server_Todo",
    platforms: [
       .macOS(.v13)
    ],
    dependencies: [
        // 💧 A server-side Swift web framework.
        .package(url: "https://github.com/vapor/vapor.git", from: "4.77.1"),
        .package(url: "https://github.com/vapor/multipart-kit.git", from: "4.5.4"),
        .package(url: "https://github.com/vapor/postgres-kit.git", from: "2.12.2"),
    ],
    targets: [
        .executableTarget(
            name: "App",
            dependencies: [
                .product(name: "Vapor", package: "vapor"),
                .product(name: "MultipartKit", package: "multipart-kit"),
                .product(name: "PostgresKit", package: "postgres-kit"),
            ]
        ),
        .testTarget(name: "AppTests", dependencies: [
            .target(name: "App"),
            .product(name: "XCTVapor", package: "vapor"),
        ])
    ]
)

问题分析 #

当前代码存在几个关键问题：

• 未定义Realtor结构体，无法完成请求内容解码
• 手动创建数据库连接，未利用Vapor内置连接池，低效且易出错
• 强制解包可选值（description!、info!），会触发程序崩溃
• execute方法参数传递格式不符合当前PostgresKit版本要求

修复后的完整代码 #

import Logging
import MultipartKit
import PostgresKit
import Vapor

// 定义Realtor结构体，符合Content协议用于解码请求体
struct Realtor: Content {
    let id: UUID?
    let title: String
    let description: String?
    let info: String?
}

private enum Config {
    static let hostname = "localhost"
    static let port = 5432
    static let user = "Ric"
    static let password = "ThisSucks!"
    static let database = "db_realtor"
}

struct RealtorController: RouteCollection {
    func boot(routes: RoutesBuilder) throws {
        routes.get(use: getRealtor)

        let realtor = routes.grouped("realtor")
        realtor.post(use: postRealtor)
    }

    private func getRealtor(req _: Request) async throws -> String {
        "GET Realtor"
    }

    private func postRealtor(req: Request) async throws -> String {
        let realtor = try req.content.decode(Realtor.self)
        
        // 使用Vapor内置数据库连接池，无需手动管理连接生命周期
        let db = req.db as! SQLPostgresDatabase
        
        let id = realtor.id ?? UUID()
        let title = realtor.title
        let description = realtor.description
        let info = realtor.info

        // 安全处理可选值，PostgresKit会自动将nil转为SQL的NULL
        let sql = """
            INSERT INTO table_realtor (
                id, title, description, info
            ) VALUES (
                $1, $2, $3, $4
            )
        """
        
        // 直接传入Swift原生类型，无需手动封装PostgresData
        try await db.execute(sql, [
            id,
            title,
            description,
            info
        ])

        return "Realtor added successfully"
    }
}

// 全局配置数据库连接
extension Application {
    func configureDatabase() throws {
        let config = SQLPostgresConfiguration(
            hostname: Config.hostname,
            port: Config.port,
            username: Config.user,
            password: Config.password,
            database: Config.database,
            tls: .disable
        )
        self.databases.use(.postgres(configuration: config), as: .psql)
    }
}

// 程序入口配置
@main
struct App {
    static func main() async throws {
        var env = try Environment.detect()
        try LoggingSystem.bootstrap(from: &env)
        let app = Application(env)
        defer { app.shutdown() }
        
        try app.configureDatabase()
        try app.register(collection: RealtorController())
        
        try await app.run()
    }
}

关键修复说明 #

• 定义Realtor结构体：必须遵循Content协议，Vapor才能自动解码JSON请求体
• 使用内置连接池：通过req.db获取连接，Vapor自动处理连接的创建、复用和关闭，性能更优
• 避免强制解包：直接传递可选值，PostgresKit会自动将nil转换为SQL的NULL，防止崩溃
• 统一数据库配置：将配置逻辑抽离到全局，避免在每个请求中重复创建配置
• 适配参数传递：当前版本支持直接传入Swift原生类型，无需手动封装为PostgresData

内容的提问来源于stack exchange，提问作者Frederick C. Lee