Skip to content

A native macOS / windows / Linux database IDE built with Swift and AppKit. Connect to PostgreSQL, MySQL, SQLite, and Redis from a single app with a fast, keyboard-driven interface.

C++
43.0 KB
Raw Blame
// PostgreSQLAdapter.swift
// Gridex
//
// PostgreSQL database adapter using PostgresNIO.

import Foundation
import PostgresNIO
import NIOCore
import NIOPosix
import NIOSSL
import Logging

final class PostgreSQLAdapter: DatabaseAdapter, SchemaInspectable, @unchecked Sendable {
    let databaseType: DatabaseType = .postgresql
    private(set) var isConnected: Bool = false
    private var client: PostgresClient?
    private var clientTask: Task<Void, Never>?
    private let eventLoopGroup = MultiThreadedEventLoopGroup(numberOfThreads: 2)

    deinit {
        clientTask?.cancel()
    }

    // MARK: - Connection

    func connect(config: ConnectionConfig, password: String?) async throws {
        let host = config.host ?? "localhost"
        let port = config.port ?? 5432
        let username = config.username ?? "postgres"
        let database = config.database ?? username

        let tlsConfig = try Self.makeTLSConfig(for: config.effectiveSSLMode, config: config)

        let pgConfig = PostgresClient.Configuration(
            host: host,
            port: port,
            username: username,
            password: password,
            database: database,
            tls: tlsConfig
        )

        let newClient = PostgresClient(configuration: pgConfig)
        self.client = newClient

        // Querying before PostgresClient.run() is scheduled surfaces a bare
        // `_ConnectionPoolModule.ConnectionPoolError` that hides the real cause;
        // reproduces on slow handshakes (e.g. HighGo Secure login-audit NOTICE).
        // The continuation guarantees the spawned Task body has been entered;
        // the trailing yield lets run() reach its first suspension point.
        await withCheckedContinuation { (cont: CheckedContinuation<Void, Never>) in
            self.clientTask = Task {
                cont.resume()
                await newClient.run()
            }
        }
        await Task.yield()

        _ = try await newClient.query("SELECT 1")
        isConnected = true
    }

    private static func makeTLSConfig(
        for mode: SSLMode,
        config: ConnectionConfig
    ) throws -> PostgresClient.Configuration.TLS {
        if mode == .disabled { return .disable }

        var tls = TLSConfiguration.makeClientConfiguration()

        // mTLS client cert (e.g. Teleport).
        if let certPath = config.sslCertPath, !certPath.isEmpty,
           let keyPath = config.sslKeyPath, !keyPath.isEmpty {
            let cert = try NIOSSLCertificate.fromPEMFile(certPath)
            let key = try NIOSSLPrivateKey(file: keyPath, format: .pem)
            tls.certificateChain = cert.map { .certificate($0) }
            tls.privateKey = .privateKey(key)
        }

        // libpq semantics: PREFERRED/REQUIRED encrypt only; VERIFY_CA verifies
        // the chain; VERIFY_IDENTITY also verifies the hostname.
        switch mode {
        case .verifyCA, .verifyIdentity:
            if let caPath = config.sslCACertPath, !caPath.isEmpty {
                tls.trustRoots = .file(caPath)
            }
            tls.certificateVerification = (mode == .verifyIdentity) ? .fullVerification : .noHostnameVerification
        default:
            tls.certificateVerification = .none
        }

        // .prefer falls back to plaintext when the server refuses TLS;
        // every stricter mode requires a successful TLS handshake.
        return (mode == .preferred) ? .prefer(tls) : .require(tls)
    }

    func disconnect() async throws {
        clientTask?.cancel()
        clientTask = nil
        client = nil
        isConnected = false
    }

    func testConnection(config: ConnectionConfig, password: String?) async throws -> Bool {
        let adapter = PostgreSQLAdapter()
        do {
            try await adapter.connect(config: config, password: password)
            try await adapter.disconnect()
            return true
        } catch {
            try? await adapter.disconnect()
            throw error
        }
    }

    // MARK: - Query Execution

    func execute(query: String, parameters: [QueryParameter]?) async throws -> QueryResult {
        try ensureConnected()
        let startTime = CFAbsoluteTimeGetCurrent()
        let upper = query.trimmingCharacters(in: .whitespacesAndNewlines).uppercased()
        let queryType = detectQueryType(upper)
        let isSelect = queryType == .select || upper.hasPrefix("SHOW") || upper.hasPrefix("EXPLAIN")

        guard let client else { throw GridexError.queryExecutionFailed("No connection") }

        do {
            let pgQuery = PostgresQuery(unsafeSQL: query)
            let rows = try await client.query(pgQuery)
            let columns = rows.columns.map { col in
                ColumnHeader(name: col.name, dataType: col.dataType.description)
            }

            if isSelect {
                var resultRows: [[RowValue]] = []
                for try await row in rows {
                    var rowValues: [RowValue] = []
                    for (cell, meta) in zip(row, rows.columns) {
                        rowValues.append(decodeCell(cell, dataType: meta.dataType))
                    }
                    resultRows.append(rowValues)
                }
                let duration = CFAbsoluteTimeGetCurrent() - startTime
                return QueryResult(columns: columns, rows: resultRows, rowsAffected: resultRows.count, executionTime: duration, queryType: queryType)
            } else {
                var count = 0
                for try await _ in rows { count += 1 }
                let duration = CFAbsoluteTimeGetCurrent() - startTime
                return QueryResult(columns: columns, rows: [], rowsAffected: count, executionTime: duration, queryType: queryType)
            }
        } catch {
            throw GridexError.queryExecutionFailed(Self.formatPostgresError(error))
        }
    }

    /// Extract a human-readable message from a PSQLError (which doesn't implement LocalizedError well).
    static func formatPostgresError(_ error: Error) -> String {
        if let transactionError = error as? PostgresTransactionError {
            let primaryError = transactionError.beginError
                ?? transactionError.closureError
                ?? transactionError.commitError

            var message: String
            if let primaryError {
                message = formatPostgresError(primaryError)
            } else if let rollbackError = transactionError.rollbackError {
                return "Rollback failed: \(formatPostgresError(rollbackError))"
            } else {
                return error.localizedDescription
            }

            if let rollbackError = transactionError.rollbackError {
                message += "\nRollback failed: \(formatPostgresError(rollbackError))"
            }
            return message
        }

        if let pgError = error as? PSQLError {
            var parts: [String] = []
            if let msg = pgError.serverInfo?[.message] {
                parts.append(msg)
            }
            if let detail = pgError.serverInfo?[.detail] {
                parts.append("Detail: \(detail)")
            }
            if let hint = pgError.serverInfo?[.hint] {
                parts.append("Hint: \(hint)")
            }
            if let position = pgError.serverInfo?[.position] {
                parts.append("Position: \(position)")
            }
            if let sqlState = pgError.serverInfo?[.sqlState] {
                parts.append("SQLSTATE: \(sqlState)")
            }
            if !parts.isEmpty {
                return parts.joined(separator: "\n")
            }
            return String(describing: pgError)
        }
        return error.localizedDescription
    }

    func executeRaw(sql: String) async throws -> QueryResult {
        try await execute(query: sql, parameters: nil)
    }

    func executeWithRowValues(sql: String, parameters: [RowValue]) async throws -> QueryResult {
        try ensureConnected()
        guard let client else { throw GridexError.queryExecutionFailed("No connection") }

        let startTime = CFAbsoluteTimeGetCurrent()
        let upper = sql.trimmingCharacters(in: .whitespacesAndNewlines).uppercased()
        let queryType = detectQueryType(upper)

        var bindings = PostgresBindings(capacity: parameters.count)
        for param in parameters {
            appendBinding(param, to: &bindings)
        }

        return try await Self.withFormattedQueryErrors {
            let pgQuery = PostgresQuery(unsafeSQL: sql, binds: bindings)
            let rows = try await client.query(pgQuery)
            let columns = rows.columns.map { col in
                ColumnHeader(name: col.name, dataType: col.dataType.description)
            }

            var resultRows: [[RowValue]] = []
            for try await row in rows {
                var rowValues: [RowValue] = []
                for (cell, meta) in zip(row, rows.columns) {
                    rowValues.append(self.decodeCell(cell, dataType: meta.dataType))
                }
                resultRows.append(rowValues)
            }

            let duration = CFAbsoluteTimeGetCurrent() - startTime
            return QueryResult(columns: columns, rows: resultRows, rowsAffected: resultRows.count, executionTime: duration, queryType: queryType)
        }
    }

    static func withFormattedQueryErrors<T>(
        _ operation: () async throws -> T
    ) async throws -> T {
        do {
            return try await operation()
        } catch let error as GridexError {
            throw error
        } catch {
            throw GridexError.queryExecutionFailed(formatPostgresError(error))
        }
    }

    private func appendBinding(_ value: RowValue, to bindings: inout PostgresBindings) {
        switch value {
        case .null:
            bindings.appendNull()
        case .string(let v):
            bindings.append(v)
        case .integer(let v):
            bindings.append(v)
        case .double(let v):
            bindings.append(v)
        case .boolean(let v):
            bindings.append(v)
        case .date(let v):
            bindings.append(v)
        case .uuid(let v):
            bindings.append(v)
        case .json(let v):
            bindings.append(v)
        case .data(let v):
            var buf = ByteBuffer()
            buf.writeBytes(v)
            bindings.append(buf)
        case .array(let v):
            bindings.append(v.map(\.description).joined(separator: ","))
        }
    }

    // MARK: - Schema Inspection

    func listDatabases() async throws -> [String] {
        let result = try await executeRaw(sql: "SELECT datname FROM pg_database WHERE datistemplate = false ORDER BY datname")
        return result.rows.compactMap { $0.first?.stringValue }
    }

    func listSchemas(database: String?) async throws -> [String] {
        // Query pg_namespace directly rather than information_schema.schemata.
        // The latter is defined (per SQL standard) to filter rows by
        // `pg_has_role(owner, 'USAGE') OR has_schema_privilege('USAGE, CREATE')`,
        // so on Supabase — where the `postgres` role is NOSUPERUSER and schemas
        // like `auth`, `storage`, `realtime`, `extensions`, `graphql` are owned
        // by `supabase_admin` without a USAGE grant — everything except `public`
        // silently disappears. pg_namespace is readable by all roles and returns
        // every physical schema, matching what pgAdmin/TablePlus/DBeaver show.
        let result = try await executeRaw(sql: """
            SELECT nspname FROM pg_namespace
            WHERE nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast')
              AND nspname !~ '^pg_(temp|toast_temp)_'
            ORDER BY nspname
            """)
        return result.rows.compactMap { $0.first?.stringValue }
    }

    func listTables(schema: String?) async throws -> [TableInfo] {
        let schemaFilter = schema ?? "public"
        // relkind 'r' = ordinary table, 'p' = partitioned table parent.
        // `NOT relispartition` excludes partition children — they show up as
        // ordinary 'r' rows but the parent already represents them in the UI;
        // including them would double-count rows (regression vs the original
        // information_schema query, which only returned 'BASE TABLE' parents).
        let result = try await executeParameterized(sql: """
            SELECT c.relname, NULLIF(c.reltuples::bigint, -1)
            FROM pg_class c
            JOIN pg_namespace n ON n.oid = c.relnamespace
            WHERE n.nspname = $1
              AND c.relkind IN ('r', 'p')
              AND NOT c.relispartition
            ORDER BY c.relname
            """, params: [schemaFilter])
        return result.rows.compactMap { row -> TableInfo? in
            guard let name = row.first?.stringValue else { return nil }
            let count = row.count > 1 ? row[1].intValue : nil
            return TableInfo(name: name, schema: schemaFilter, type: .table, estimatedRowCount: count)
        }
    }

    func listViews(schema: String?) async throws -> [ViewInfo] {
        let schemaFilter = schema ?? "public"
        let result = try await executeParameterized(sql: """
            SELECT table_name, view_definition, false AS is_materialized
            FROM information_schema.views
            WHERE table_schema = $1
            UNION ALL
            SELECT matviewname, definition, true
            FROM pg_matviews
            WHERE schemaname = $1
            ORDER BY table_name
            """, params: [schemaFilter])

        return result.rows.compactMap { row -> ViewInfo? in
            guard let name = row[0].stringValue else { return nil }
            let def = row[1].stringValue
            let isMat = row[2].stringValue == "true" || row[2].stringValue == "t"
            return ViewInfo(name: name, schema: schemaFilter, definition: def, isMaterialized: isMat)
        }
    }

    func describeTable(name: String, schema: String?) async throws -> TableDescription {
        let schemaFilter = schema ?? "public"

        async let colsTask = describeColumns(table: name, schema: schemaFilter)
        async let constraintsTask = listAllConstraints(table: name, schema: schemaFilter)
        async let indexesTask = listIndexes(table: name, schema: schemaFilter)

        let cols = try await colsTask
        let constraints = try await constraintsTask
        let indexes = try await indexesTask

        // Merge check constraints into columns
        var columns = cols.columns
        for (colName, checkExpr) in constraints.checks {
            if let idx = columns.firstIndex(where: { $0.name == colName }) {
                columns[idx].checkConstraint = checkExpr
            }
        }

        return TableDescription(
            name: name, schema: schemaFilter, columns: columns,
            indexes: indexes, foreignKeys: constraints.foreignKeys, constraints: [],
            comment: cols.tableComment, estimatedRowCount: cols.estimatedRowCount
        )
    }

    static func shortenPgType(_ raw: String) -> String {
        switch raw {
        case "timestamp without time zone": return "timestamp"
        case "timestamp with time zone": return "timestamptz"
        case "time without time zone": return "time"
        case "time with time zone": return "timetz"
        case "integer": return "int4"
        case "smallint": return "int2"
        case "bigint": return "int8"
        case "real": return "float4"
        case "double precision": return "float8"
        case "boolean": return "bool"
        default: break
        }
        if raw.hasPrefix("character varying") {
            return raw.replacingOccurrences(of: "character varying", with: "varchar")
        }
        if raw.hasPrefix("character") {
            return raw.replacingOccurrences(of: "character", with: "char")
        }
        return raw
    }

    // MARK: - Query 1: Columns + PK + defaults + comments + meta (no FK/check joins)

    private func describeColumns(table: String, schema: String) async throws -> (columns: [ColumnInfo], tableComment: String?, estimatedRowCount: Int?) {
        let result = try await executeParameterized(sql: """
            SELECT a.attname,
                   format_type(a.atttypid, a.atttypmod),
                   NOT a.attnotnull,
                   pg_get_expr(d.adbin, d.adrelid),
                   a.attnum,
                   CASE WHEN a.atttypid IN (1042, 1043) AND a.atttypmod > 0 THEN a.atttypmod - 4 END,
                   CASE WHEN pk.conkey @> ARRAY[a.attnum] THEN true ELSE false END,
                   col_description(cls.oid, a.attnum),
                   cls.reltuples::bigint,
                   obj_description(cls.oid)
            FROM pg_attribute a
            JOIN pg_class cls ON cls.oid = a.attrelid
            JOIN pg_namespace ns ON ns.oid = cls.relnamespace
            LEFT JOIN pg_attrdef d ON d.adrelid = a.attrelid AND d.adnum = a.attnum
            LEFT JOIN pg_constraint pk ON pk.conrelid = cls.oid AND pk.contype = 'p'
            WHERE cls.relname = $1 AND ns.nspname = $2
              AND a.attnum > 0 AND NOT a.attisdropped
            ORDER BY a.attnum
            """, params: [table, schema])

        var columns: [ColumnInfo] = []
        var rowCount: Int?
        var tableComment: String?

        for row in result.rows {
            let colName = row[0].stringValue ?? ""
            if columns.contains(where: { $0.name == colName }) { continue }

            let dataType = Self.shortenPgType(row[1].stringValue ?? "text")
            let nullable = row[2].stringValue == "t" || row[2].stringValue == "true"
            let defVal = row[3].stringValue
            let ordinal = row[4].intValue ?? 0
            let maxLen = row[5].intValue
            let isPK = row[6].stringValue == "t" || row[6].stringValue == "true"
            let comment = row[7].stringValue

            columns.append(ColumnInfo(
                name: colName, dataType: dataType, isNullable: nullable, defaultValue: defVal,
                isPrimaryKey: isPK, isAutoIncrement: defVal?.hasPrefix("nextval(") == true,
                comment: comment, ordinalPosition: ordinal, characterMaxLength: maxLen
            ))

            if rowCount == nil { rowCount = row[8].intValue }
            if tableComment == nil { tableComment = row[9].stringValue }
        }

        return (columns, tableComment, rowCount)
    }

    // MARK: - Query 2: All constraints (FK + check) in one simple query

    private func listAllConstraints(table: String, schema: String) async throws -> (foreignKeys: [ForeignKeyInfo], checks: [(String, String)]) {
        let result = try await executeParameterized(sql: """
            SELECT con.contype,
                   con.conname,
                   con.conkey,
                   pg_get_constraintdef(con.oid),
                   ref_cls.relname,
                   (SELECT string_agg(a.attname, ',' ORDER BY array_position(con.confkey, a.attnum))
                    FROM pg_attribute a WHERE a.attrelid = con.confrelid AND a.attnum = ANY(con.confkey)),
                   (SELECT string_agg(a.attname, ',' ORDER BY array_position(con.conkey, a.attnum))
                    FROM pg_attribute a WHERE a.attrelid = con.conrelid AND a.attnum = ANY(con.conkey)),
                   con.confdeltype,
                   con.confupdtype
            FROM pg_constraint con
            JOIN pg_class cls ON cls.oid = con.conrelid
            JOIN pg_namespace ns ON ns.oid = cls.relnamespace
            LEFT JOIN pg_class ref_cls ON ref_cls.oid = con.confrelid
            WHERE cls.relname = $1 AND ns.nspname = $2
              AND con.contype IN ('f', 'c')
            ORDER BY con.conname
            """, params: [table, schema])

        var fks: [ForeignKeyInfo] = []
        var checks: [(String, String)] = []

        for row in result.rows {
            let contype = row[0].stringValue ?? ""
            let conname = row[1].stringValue ?? ""

            if contype == "f" {
                let refTable = row[4].stringValue ?? ""
                let refCols = splitCols(row[5].stringValue)
                let localCols = splitCols(row[6].stringValue)
                let onDelete = pgFKAction(row[7].stringValue)
                let onUpdate = pgFKAction(row[8].stringValue)
                fks.append(ForeignKeyInfo(name: conname, columns: localCols, referencedTable: refTable, referencedColumns: refCols, onDelete: onDelete, onUpdate: onUpdate))
            } else if contype == "c" {
                let conDef = row[3].stringValue ?? ""
                let localCols = splitCols(row[6].stringValue)
                // Strip "CHECK (...)" wrapper
                let expr: String
                if conDef.hasPrefix("CHECK (") && conDef.hasSuffix(")") {
                    expr = String(conDef.dropFirst(7).dropLast(1))
                } else {
                    expr = conDef
                }
                for col in localCols {
                    checks.append((col, expr))
                }
            }
        }

        return (fks, checks)
    }

    /// Parse PostgreSQL array string like "{a,b,c}" into [String]
    private func parseArrayString(_ raw: String?) -> [String] {
        guard let raw, raw.hasPrefix("{") && raw.hasSuffix("}") else {
            return raw?.split(separator: ",").map(String.init) ?? []
        }
        return String(raw.dropFirst().dropLast()).split(separator: ",").map { String($0) }
    }

    /// Split comma-separated column names from string_agg result
    private func splitCols(_ raw: String?) -> [String] {
        guard let raw, !raw.isEmpty else { return [] }
        return raw.split(separator: ",").map { String($0).trimmingCharacters(in: .whitespaces) }
    }

    func listIndexes(table: String, schema: String?) async throws -> [IndexInfo] {
        let schemaFilter = schema ?? "public"
        // Use pg_get_indexdef + regex parsing (faster, no subqueries)
        let result = try await executeParameterized(sql: """
            SELECT ix.relname,
                   upper(am.amname),
                   i.indisunique,
                   pg_get_indexdef(i.indexrelid),
                   obj_description(ix.oid)
            FROM pg_index i
            JOIN pg_class t ON t.oid = i.indrelid
            JOIN pg_class ix ON ix.oid = i.indexrelid
            JOIN pg_namespace n ON t.relnamespace = n.oid
            JOIN pg_am am ON ix.relam = am.oid
            WHERE t.relname = $1 AND n.nspname = $2
            ORDER BY ix.relname
            """, params: [table, schemaFilter])

        return result.rows.compactMap { row -> IndexInfo? in
            guard let name = row[0].stringValue else { return nil }
            let type = row[1].stringValue
            let unique = row[2].stringValue == "t" || row[2].stringValue == "true"
            let indexDef = row[3].stringValue ?? ""
            let comment = row[4].stringValue

            // Parse columns from indexdef: "CREATE INDEX ... ON ... USING btree (col1, col2)"
            let cols: [String]
            if let openParen = indexDef.range(of: "("),
               let closeParen = indexDef.range(of: ")", options: .backwards) {
                let colStr = indexDef[openParen.upperBound..<closeParen.lowerBound]
                cols = colStr.split(separator: ",").map { String($0.trimmingCharacters(in: .whitespaces)) }
            } else {
                cols = []
            }

            // Parse WHERE condition
            let condition: String?
            if let whereRange = indexDef.range(of: " WHERE ", options: .caseInsensitive) {
                condition = String(indexDef[whereRange.upperBound...])
            } else {
                condition = nil
            }

            // Parse INCLUDE
            let include: String?
            if let includeRange = indexDef.range(of: " INCLUDE ", options: .caseInsensitive) {
                let afterInclude = indexDef[includeRange.upperBound...]
                if let iParen = afterInclude.range(of: "("),
                   let iClose = afterInclude.range(of: ")") {
                    include = String(afterInclude[iParen.upperBound..<iClose.lowerBound])
                } else {
                    include = nil
                }
            } else {
                include = nil
            }

            return IndexInfo(name: name, columns: cols, isUnique: unique, type: type, tableName: table, condition: condition, include: include, comment: comment)
        }
    }

    func listForeignKeys(table: String, schema: String?) async throws -> [ForeignKeyInfo] {
        let schemaFilter = schema ?? "public"
        let result = try await executeParameterized(sql: """
            SELECT con.conname,
                   att.attname AS column_name,
                   ref_cls.relname AS foreign_table,
                   ref_att.attname AS foreign_column,
                   con.confdeltype,
                   con.confupdtype
            FROM pg_constraint con
            JOIN pg_class cls ON cls.oid = con.conrelid
            JOIN pg_namespace ns ON ns.oid = cls.relnamespace
            JOIN pg_attribute att ON att.attrelid = con.conrelid AND att.attnum = ANY(con.conkey)
            JOIN pg_class ref_cls ON ref_cls.oid = con.confrelid
            JOIN pg_attribute ref_att ON ref_att.attrelid = con.confrelid AND ref_att.attnum = ANY(con.confkey)
            WHERE con.contype = 'f'
              AND ns.nspname = $1 AND cls.relname = $2
            ORDER BY con.conname
            """, params: [schemaFilter, table])

        return result.rows.compactMap { row -> ForeignKeyInfo? in
            let name = row[0].stringValue
            guard let col = row[1].stringValue,
                  let refTable = row[2].stringValue,
                  let refCol = row[3].stringValue else { return nil }
            let onDelete = pgFKAction(row[4].stringValue)
            let onUpdate = pgFKAction(row[5].stringValue)
            return ForeignKeyInfo(name: name, columns: [col], referencedTable: refTable, referencedColumns: [refCol], onDelete: onDelete, onUpdate: onUpdate)
        }
    }

    private func pgFKAction(_ code: String?) -> ForeignKeyAction {
        switch code {
        case "c": return .cascade
        case "n": return .setNull
        case "d": return .setDefault
        case "r": return .restrict
        default: return .noAction
        }
    }

    func listFunctions(schema: String?) async throws -> [String] {
        let schemaFilter = schema ?? "public"
        let result = try await executeParameterized(sql: """
            SELECT p.proname || '(' || pg_get_function_identity_arguments(p.oid) || ')'
            FROM pg_proc p
            JOIN pg_namespace n ON p.pronamespace = n.oid
            WHERE n.nspname = $1
              AND p.prokind IN ('f', 'p')
            ORDER BY p.proname
            """, params: [schemaFilter])
        return result.rows.compactMap { $0[0].stringValue }
    }

    func getFunctionSource(name: String, schema: String?) async throws -> String {
        let schemaFilter = schema ?? "public"
        // name may be "funcname(arg_types)" or just "funcname"
        let result: QueryResult
        if name.contains("(") {
            // Match exact overload by full signature
            result = try await executeParameterized(sql: """
                SELECT pg_get_functiondef(p.oid)
                FROM pg_proc p
                JOIN pg_namespace n ON p.pronamespace = n.oid
                WHERE n.nspname = $1
                  AND p.proname || '(' || pg_get_function_identity_arguments(p.oid) || ')' = $2
                LIMIT 1
                """, params: [schemaFilter, name])
        } else {
            result = try await executeParameterized(sql: """
                SELECT pg_get_functiondef(p.oid)
                FROM pg_proc p
                JOIN pg_namespace n ON p.pronamespace = n.oid
                WHERE n.nspname = $1 AND p.proname = $2
                LIMIT 1
                """, params: [schemaFilter, name])
        }
        guard let source = result.rows.first?[0].stringValue else {
            throw GridexError.queryExecutionFailed("Function '\(name)' not found")
        }
        return source
    }

    // MARK: - Data Manipulation

    func insertRow(table: String, schema: String?, values: [String: RowValue]) async throws -> QueryResult {
        let d = SQLDialect.postgresql
        let schemaPrefix = schema.map { d.quoteIdentifier($0) + "." } ?? ""
        let cols = values.keys.map { d.quoteIdentifier($0) }.joined(separator: ", ")
        let placeholders = (1...values.count).map { "$\($0)" }.joined(separator: ", ")
        let sql = "INSERT INTO \(schemaPrefix)\(d.quoteIdentifier(table)) (\(cols)) VALUES (\(placeholders))"
        return try await executeRaw(sql: buildInlineSQL(sql: sql, values: Array(values.values)))
    }

    func updateRow(table: String, schema: String?, set values: [String: RowValue], where conditions: [String: RowValue]) async throws -> QueryResult {
        let d = SQLDialect.postgresql
        let schemaPrefix = schema.map { d.quoteIdentifier($0) + "." } ?? ""
        let setClauses = values.compactMap { k, v in "\(d.quoteIdentifier(k)) = \(inlineValue(k, v))" }.joined(separator: ", ")
        let whereClauses = conditions.compactMap { k, v in "\(d.quoteIdentifier(k)) = \(inlineValue(k, v))" }.joined(separator: " AND ")
        return try await executeRaw(sql: "UPDATE \(schemaPrefix)\(d.quoteIdentifier(table)) SET \(setClauses) WHERE \(whereClauses)")
    }

    func deleteRow(table: String, schema: String?, where conditions: [String: RowValue]) async throws -> QueryResult {
        let d = SQLDialect.postgresql
        let schemaPrefix = schema.map { d.quoteIdentifier($0) + "." } ?? ""
        let whereClauses = conditions.compactMap { k, v in "\(d.quoteIdentifier(k)) = \(inlineValue(k, v))" }.joined(separator: " AND ")
        return try await executeRaw(sql: "DELETE FROM \(schemaPrefix)\(d.quoteIdentifier(table)) WHERE \(whereClauses)")
    }

    func beginTransaction() async throws { _ = try await executeRaw(sql: "BEGIN") }
    func commitTransaction() async throws { _ = try await executeRaw(sql: "COMMIT") }
    func rollbackTransaction() async throws { _ = try await executeRaw(sql: "ROLLBACK") }

    func executeStatements(_ statements: [String], inSchemaTransaction schema: String) async throws {
        try ensureConnected()
        guard let client else { throw GridexError.queryExecutionFailed("No connection") }

        let logger = Logger(label: "gridex.postgresql.sql-dump")
        let quotedSchema = databaseType.sqlDialect.quoteIdentifier(schema)

        do {
            try await client.withTransaction(logger: logger) { connection in
                let setupRows = try await connection.query(
                    PostgresQuery(unsafeSQL: "SET LOCAL search_path TO \(quotedSchema)"),
                    logger: logger
                )
                for try await _ in setupRows {}

                for statement in statements {
                    let rows = try await connection.query(
                        PostgresQuery(unsafeSQL: statement),
                        logger: logger
                    )
                    for try await _ in rows {}
                }
            }
        } catch {
            throw GridexError.queryExecutionFailed(Self.formatPostgresError(error))
        }
    }

    // MARK: - Pagination

    func fetchRows(table: String, schema: String?, columns: [String]?, where filter: FilterExpression?, orderBy: [QuerySortDescriptor]?, limit: Int, offset: Int) async throws -> QueryResult {
        let d = SQLDialect.postgresql
        let schemaPrefix = schema.map { d.quoteIdentifier($0) + "." } ?? ""
        let colList = columns?.map { d.quoteIdentifier($0) }.joined(separator: ", ") ?? "*"
        var sql = "SELECT \(colList) FROM \(schemaPrefix)\(d.quoteIdentifier(table))"
        if let filter, !filter.conditions.isEmpty {
            sql += " WHERE \(filter.toSQL(dialect: d))"
        }
        if let orderBy, !orderBy.isEmpty {
            sql += " ORDER BY " + orderBy.map { $0.toSQL(dialect: d) }.joined(separator: ", ")
        }
        sql += " LIMIT \(limit) OFFSET \(offset)"
        return try await executeRaw(sql: sql)
    }

    func serverVersion() async throws -> String {
        let r = try await executeRaw(sql: "SELECT version()")
        return r.rows.first?.first?.stringValue ?? "PostgreSQL"
    }

    func currentDatabase() async throws -> String? {
        let r = try await executeRaw(sql: "SELECT current_database()")
        return r.rows.first?.first?.stringValue
    }

    // MARK: - SchemaInspectable

    func fullSchemaSnapshot(database: String?) async throws -> SchemaSnapshot {
        let schemas = try await listSchemas(database: database)
        var schemaInfos: [SchemaInfo] = []
        for schemaName in schemas {
            let tables = try await listTables(schema: schemaName)
            let descs: [TableDescription] = try await withThrowingTaskGroup(of: TableDescription.self) { group in
                for t in tables {
                    let name = t.name
                    group.addTask { try await self.describeTable(name: name, schema: schemaName) }
                }
                var results: [TableDescription] = []
                for try await desc in group { results.append(desc) }
                return results
            }
            let views = try await listViews(schema: schemaName)
            schemaInfos.append(SchemaInfo(name: schemaName, tables: descs, views: views, functions: [], enums: []))
        }
        let dbName = try await currentDatabase() ?? "postgres"
        return SchemaSnapshot(databaseName: dbName, databaseType: .postgresql, schemas: schemaInfos, capturedAt: Date())
    }

    func columnStatistics(table: String, schema: String?, sampleSize: Int) async throws -> [ColumnStatistics] {
        let schemaFilter = schema ?? "public"
        let colResult = try await describeColumns(table: table, schema: schemaFilter)
        let cols = colResult.columns
        var stats: [ColumnStatistics] = []
        for col in cols {
            let q = SQLDialect.postgresql.quoteIdentifier(col.name)
            let tbl = "\(SQLDialect.postgresql.quoteIdentifier(schemaFilter)).\(SQLDialect.postgresql.quoteIdentifier(table))"
            let r = try await executeRaw(sql: """
                SELECT COUNT(DISTINCT \(q)),
                       CAST(SUM(CASE WHEN \(q) IS NULL THEN 1 ELSE 0 END) AS DOUBLE PRECISION) / GREATEST(COUNT(*), 1),
                       MIN(\(q)::text), MAX(\(q)::text)
                FROM \(tbl)
                """)
            if let row = r.rows.first {
                stats.append(ColumnStatistics(
                    columnName: col.name,
                    distinctCount: row[0].intValue,
                    nullRatio: row[1].doubleValue,
                    topValues: nil,
                    minValue: row[2].stringValue,
                    maxValue: row[3].stringValue
                ))
            }
        }
        return stats
    }

    func tableRowCount(table: String, schema: String?) async throws -> Int {
        let schemaFilter = schema ?? "public"
        // Use fast estimated count from pg_class instead of slow COUNT(*)
        let r = try await executeParameterized(sql: """
            SELECT reltuples::bigint FROM pg_class c
            JOIN pg_namespace n ON n.oid = c.relnamespace
            WHERE c.relname = $1 AND n.nspname = $2
            """, params: [table, schemaFilter])
        return r.rows.first?.first?.intValue ?? 0
    }

    func tableSizeBytes(table: String, schema: String?) async throws -> Int64? {
        let schemaFilter = schema ?? "public"
        let d = SQLDialect.postgresql
        let qualifiedName = "\(d.quoteIdentifier(schemaFilter)).\(d.quoteIdentifier(table))"
        let r = try await executeParameterized(sql: "SELECT pg_total_relation_size($1)", params: [qualifiedName])
        if let val = r.rows.first?.first?.intValue { return Int64(val) }
        return nil
    }

    func queryStatistics() async throws -> [QueryStatisticsEntry] { [] }

    func primaryKeyColumns(table: String, schema: String?) async throws -> [String] {
        let schemaFilter = schema ?? "public"
        let result = try await executeParameterized(sql: """
            SELECT kcu.column_name
            FROM information_schema.table_constraints tc
            JOIN information_schema.key_column_usage kcu
              ON tc.constraint_name = kcu.constraint_name AND tc.table_schema = kcu.table_schema
            WHERE tc.constraint_type = 'PRIMARY KEY'
              AND tc.table_schema = $1 AND tc.table_name = $2
            ORDER BY kcu.ordinal_position
            """, params: [schemaFilter, table])
        return result.rows.compactMap { $0.first?.stringValue }
    }

    // MARK: - Helpers

    /// Execute a parameterized query with `$1, $2, ...` placeholders and String bindings.
    /// Use this for metadata queries where values come from user input.
    private func executeParameterized(sql: String, params: [String]) async throws -> QueryResult {
        try ensureConnected()
        guard let client else { throw GridexError.queryExecutionFailed("No connection") }

        let startTime = CFAbsoluteTimeGetCurrent()
        var bindings = PostgresBindings(capacity: params.count)
        for param in params {
            bindings.append(param)
        }
        let pgQuery = PostgresQuery(unsafeSQL: sql, binds: bindings)
        let rows = try await client.query(pgQuery)
        let columns = rows.columns.map { col in
            ColumnHeader(name: col.name, dataType: col.dataType.description)
        }
        var resultRows: [[RowValue]] = []
        for try await row in rows {
            var rowValues: [RowValue] = []
            for (cell, meta) in zip(row, rows.columns) {
                rowValues.append(decodeCell(cell, dataType: meta.dataType))
            }
            resultRows.append(rowValues)
        }
        let duration = CFAbsoluteTimeGetCurrent() - startTime
        return QueryResult(columns: columns, rows: resultRows, rowsAffected: resultRows.count, executionTime: duration, queryType: .select)
    }

    private func ensureConnected() throws {
        guard isConnected, client != nil else {
            throw GridexError.queryExecutionFailed("Not connected to PostgreSQL")
        }
    }

    private func detectQueryType(_ upper: String) -> QueryType {
        if upper.hasPrefix("SELECT") || upper.hasPrefix("EXPLAIN") || upper.hasPrefix("WITH") { return .select }
        if upper.hasPrefix("INSERT") { return .insert }
        if upper.hasPrefix("UPDATE") { return .update }
        if upper.hasPrefix("DELETE") { return .delete }
        if upper.hasPrefix("CREATE") || upper.hasPrefix("ALTER") || upper.hasPrefix("DROP") { return .ddl }
        return .other
    }

    private func decodeCell(_ cell: PostgresCell, dataType: PostgresDataType) -> RowValue {
        if cell.bytes == nil { return .null }
        do {
            switch dataType {
            case .bool:
                return .boolean(try cell.decode(Bool.self))
            case .int2:
                return .integer(Int64(try cell.decode(Int16.self)))
            case .int4:
                return .integer(Int64(try cell.decode(Int32.self)))
            case .int8:
                return .integer(try cell.decode(Int64.self))
            case .float4:
                return .double(Double(try cell.decode(Float.self)))
            case .float8:
                return .double(try cell.decode(Double.self))
            case .numeric:
                // numeric binary format requires Decimal decoder, not String
                let decimal = try cell.decode(Decimal.self)
                let str = "\(decimal)"
                if let d = Double(str) { return .double(d) }
                return .string(str)
            case .money:
                // money is stored as Int64 (cents) in binary protocol
                if var bytes = cell.bytes, bytes.readableBytes == 8,
                   let cents = bytes.readInteger(as: Int64.self) {
                    return .double(Double(cents) / 100.0)
                }
                return .null
            case .text, .varchar, .bpchar, .name:
                return .string(try cell.decode(String.self))
            case .bytea:
                var buf = try cell.decode(ByteBuffer.self)
                let data = buf.readData(length: buf.readableBytes) ?? Data()
                return .data(data)
            case .timestamp, .timestamptz, .date:
                return .date(try cell.decode(Date.self))
            case .time:
                // time/timetz: decode as String via text representation
                if let s = try? cell.decode(String.self) { return .string(s) }
                return .null
            case .interval:
                if let s = try? cell.decode(String.self) { return .string(s) }
                return .null
            case .uuid:
                return .uuid(try cell.decode(UUID.self))
            case .json, .jsonb:
                return .json(try cell.decode(String.self))
            case .textArray, .varcharArray, .bpcharArray, .nameArray:
                return .array(try cell.decode([String].self).map { .string($0) })
            case .boolArray:
                return .array(try cell.decode([Bool].self).map { .boolean($0) })
            case .int2Array:
                return .array(try cell.decode([Int16].self).map { .integer(Int64($0)) })
            case .int4Array:
                return .array(try cell.decode([Int32].self).map { .integer(Int64($0)) })
            case .int8Array:
                return .array(try cell.decode([Int64].self).map { .integer($0) })
            case .float4Array:
                return .array(try cell.decode([Float].self).map { .double(Double($0)) })
            case .float8Array:
                return .array(try cell.decode([Double].self).map { .double($0) })
            case .uuidArray:
                return .array(try cell.decode([UUID].self).map { .uuid($0) })
            default:
                // Try String decoding, but validate it's not binary garbage
                if let s = try? cell.decode(String.self),
                   !s.unicodeScalars.contains(where: { $0.value == 0xFFFD }) {
                    return .string(s)
                }
                return .null
            }
        } catch {
            if let s = try? cell.decode(String.self),
               !s.unicodeScalars.contains(where: { $0.value == 0xFFFD }) {
                return .string(s)
            }
            return .null
        }
    }

    private func inlineValue(_ key: String, _ value: RowValue) -> String {
        switch value {
        case .null: return "NULL"
        case .string(let v): return "'\(v.replacingOccurrences(of: "'", with: "''"))'"
        case .integer(let v): return "\(v)"
        case .double(let v): return "\(v)"
        case .boolean(let v): return v ? "TRUE" : "FALSE"
        case .date(let v):
            let fmt = DateFormatter()
            fmt.dateFormat = "yyyy-MM-dd HH:mm:ss"
            fmt.timeZone = TimeZone(identifier: "UTC")
            return "'\(fmt.string(from: v))'"
        case .uuid(let v): return "'\(v.uuidString)'"
        case .json(let v): return "'\(v.replacingOccurrences(of: "'", with: "''"))'::jsonb"
        case .data: return "NULL"
        case .array: return "NULL"
        }
    }

    private func buildInlineSQL(sql: String, values: [RowValue]) -> String {
        var result = sql
        for (i, value) in values.enumerated() {
            result = result.replacingOccurrences(of: "$\(i + 1)", with: inlineValue("", value))
        }
        return result
    }
}