index.ts

import dayjs from 'dayjs';
import { AdminForthResource, IAdminForthSingleFilter, IAdminForthAndOrFilter, IAdminForthDataSourceConnector, AdminForthConfig, IAggregationRule, IGroupByRule, IGroupByDateTrunc, IGroupByField, AdminForthResourceColumn } from 'adminforth';
import { AdminForthDataTypes, AdminForthFilterOperators, AdminForthSortDirections, AdminForthBaseConnector} from 'adminforth';
import pkg from 'pg';
import { afLogger, dbLogger, checkIfFieldIsInsideResourceColumns } from 'adminforth';

const { Pool } = pkg;
const { Client, types } = pkg;

// postgres-date (used by pg for OID 1114/1082) parses no-TZ strings with new Date(y,m,d,...)
// which treats them as LOCAL server time. Return raw strings so getFieldValue can parse as UTC.
types.setTypeParser(1114, (val) => val); // TIMESTAMP WITHOUT TIME ZONE
types.setTypeParser(1082, (val) => val); // DATE

type QueryRow = Record<string, any>;


class PostgresConnector extends AdminForthBaseConnector implements IAdminForthDataSourceConnector {

    // connector builds WHERE clause from pkValues, so resources with composite primary key are supported 
    supportsCompositePrimaryKey = true;

    /**
     * Returns [column, value] pairs identifying single record.
     * pkValues is passed by base connector, fallback keeps method usable when it is called directly.
     */
    pkEntries(resource: AdminForthResource, recordId: any, pkValues?: Record<string, any>): [string, any][] {
        return Object.entries(pkValues ?? this.getPrimaryKeyValues(resource, recordId));
    }

    async setupClient(url: string, options?: { recovery?: boolean }): Promise<void> {
        this.client = new Pool({
            connectionString: url
        });

        const selfHeal = options?.recovery !== false;

        if (selfHeal) {
            this.client.on('error', (err: Error) => {
                afLogger.error(`Postgres pool idle client error (pool self-heals on next query): ${err.message} ${err.stack}`);
            });
            try {
                const client = await this.client.connect();
                client.release();
            } catch (e) {
                afLogger.error(`Failed to connect to Postgres ${e}`);
            }
        } else {
            try {
                await this.client.connect();
                this.client.on('error', async (err: Error) => {
                    afLogger.error(`Postgres error: ${err.message} ${err.stack}`);
                    this.client.end();
                    await new Promise((resolve) => { setTimeout(resolve, 1000) });
                    this.setupClient(url, options);
                });
            } catch (e) {
                afLogger.error(`Failed to connect to Postgres ${e}`);
            }
        }
    }

    OperatorsMap = {
        [AdminForthFilterOperators.EQ]: '=',
        [AdminForthFilterOperators.NE]: 'IS DISTINCT FROM',
        [AdminForthFilterOperators.GT]: '>',
        [AdminForthFilterOperators.LT]: '<',
        [AdminForthFilterOperators.GTE]: '>=',
        [AdminForthFilterOperators.LTE]: '<=',
        [AdminForthFilterOperators.LIKE]: 'LIKE',
        [AdminForthFilterOperators.ILIKE]: 'ILIKE',
        [AdminForthFilterOperators.IN]: 'IN',
        [AdminForthFilterOperators.NIN]: 'NOT IN',
        [AdminForthFilterOperators.AND]: 'AND',
        [AdminForthFilterOperators.OR]: 'OR',
        [AdminForthFilterOperators.IS_EMPTY]: 'IS NULL',
        [AdminForthFilterOperators.IS_NOT_EMPTY]: 'IS NOT NULL',
    };

    SortDirectionsMap = {
        [AdminForthSortDirections.asc]: 'ASC',
        [AdminForthSortDirections.desc]: 'DESC',
    };

    async getAllTables(): Promise<Array<string>> {
        const res = await this.client.query(`
            SELECT table_name
            FROM information_schema.tables
            WHERE table_schema = 'public' AND table_type = 'BASE TABLE';
        `);
        return res.rows.map((row: { table_name: string }) => row.table_name);
    }
    
    async getAllColumnsInTable(tableName: string): Promise<Array<{ name: string; sampleValue?: any }>> {
        const res = await this.client.query(`
            SELECT column_name
            FROM information_schema.columns
            WHERE table_name = $1 AND table_schema = 'public';
        `, [tableName]);
        const sampleRowRes = await this.client.query(`SELECT * FROM ${tableName} ORDER BY ctid DESC LIMIT 1`);
        const sampleRow = sampleRowRes.rows[0] ?? {};
        return res.rows.map((row: { column_name: string }) => ({ name: row.column_name, sampleValue: sampleRow[row.column_name] }));
      }

    async isDatabaseEmpty(): Promise<boolean> {
        const res = await this.client.query(`
            SELECT table_schema, table_name
            FROM information_schema.tables
            WHERE table_type = 'BASE TABLE'
              AND table_schema NOT IN ('pg_catalog', 'information_schema')
              AND table_schema NOT LIKE 'pg_toast%'
            LIMIT 1
        `);
        return res.rows.length === 0;
    }

    async checkForeignResourceCascade(resource: AdminForthResource, config: AdminForthConfig, schema = 'public'): Promise<void> {
        const cascadeColumn = resource.columns.find(c => c.foreignResource?.onDelete === 'cascade');
        if (!cascadeColumn) return;

        const parentResource = config.resources.find(r => r.resourceId === cascadeColumn.foreignResource!.resourceId);
        if (!parentResource) return;

        const res = await this.client.query(
            `
            SELECT 1
            FROM pg_constraint
            WHERE contype = 'f'
                AND confrelid = ($2 || '.' || $1)::regclass
                AND conrelid = ($2 || '.' || $3)::regclass
                AND confdeltype = 'c'
            LIMIT 1
                    `,
            [parentResource.table, schema, resource.table ]
        );

        const hasCascadeOnTable = res.rowCount > 0;
        const isUploadPluginInstalled = resource.plugins?.some(p => p.className === "UploadPlugin");

        if (hasCascadeOnTable && isUploadPluginInstalled) {
            afLogger.warn(`Table "${resource.table}" has ON DELETE CASCADE and installed upload plugin, which may conflict with adminForth cascade deletion`);
        }
    }

    async discoverFields(resource: AdminForthResource, config: AdminForthConfig) {
        await this.checkForeignResourceCascade(resource, config);

        const tableName = resource.table;
        const stmt = await this.client.query(`
        SELECT
            a.attname AS name,
            pg_catalog.format_type(a.atttypid, a.atttypmod) AS type,
            a.attnotnull AS notnull,
            COALESCE(pg_get_expr(d.adbin, d.adrelid), '') AS dflt_value,
            CASE
                WHEN ct.contype = 'p' THEN 1
                ELSE 0
            END AS pk
        FROM
            pg_catalog.pg_attribute a
        LEFT JOIN pg_catalog.pg_attrdef d ON a.attrelid = d.adrelid AND a.attnum = d.adnum
        LEFT JOIN pg_catalog.pg_constraint ct ON a.attnum = ANY (ct.conkey) AND a.attrelid = ct.conrelid
        LEFT JOIN pg_catalog.pg_class c ON a.attrelid = c.oid
        LEFT JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
        WHERE
            c.relname = $1
            AND a.attnum > 0
            AND NOT a.attisdropped
        ORDER BY
            a.attnum;
    `, [tableName]);
        const rows = stmt.rows;
        const fieldTypes: Record<string, any> = {};

        rows.forEach((row: any) => {
            const field: any = {};
            const baseType = row.type.toLowerCase();
            const isPgArray = baseType.endsWith('[]');
            const normalizedBaseType = isPgArray ? baseType.slice(0, -2) : baseType;
            if (normalizedBaseType == 'int') {
                field.type = AdminForthDataTypes.INTEGER;
                field._underlineType = 'int';

            } else if (normalizedBaseType.includes('float') || normalizedBaseType.includes('double')) {
                field.type = AdminForthDataTypes.FLOAT;
                field._underlineType = 'float';

            } else if (normalizedBaseType.includes('bool')) {
                field.type = AdminForthDataTypes.BOOLEAN;
                field._underlineType = 'bool';

            } else if (normalizedBaseType == 'uuid') {
                field.type = AdminForthDataTypes.STRING;
                field._underlineType = 'uuid';

            } else if (normalizedBaseType.includes('character varying')) {
                field.type = AdminForthDataTypes.STRING;
                field._underlineType = 'varchar';
                const length = normalizedBaseType.match(/\d+/);
                field.maxLength = length ? parseInt(length[0]) : null;

            } else if (normalizedBaseType == 'text') {
                field.type = AdminForthDataTypes.TEXT;
                field._underlineType = 'text';

            } else if (normalizedBaseType.includes('decimal(') || normalizedBaseType.includes('numeric(')) {
                field.type = AdminForthDataTypes.DECIMAL;
                field._underlineType = 'decimal';
                const [precision, scale] = normalizedBaseType.match(/\d+/g);
                field.precision = parseInt(precision);
                field.scale = parseInt(scale);

            } else if (normalizedBaseType == 'real') {
                field.type = AdminForthDataTypes.FLOAT;
                field._underlineType = 'real';

            } else if (normalizedBaseType == 'date') {
                field.type = AdminForthDataTypes.DATE;
                field._underlineType = 'timestamp';
            } else if (normalizedBaseType.includes('date') || normalizedBaseType.includes('time')) {
                field.type = AdminForthDataTypes.DATETIME;
                field._underlineType = 'timestamp';
            } else if (normalizedBaseType == 'json' || normalizedBaseType == 'jsonb') {
                field.type = AdminForthDataTypes.JSON;
                field._underlineType = 'json';
            } else {
                field.type = 'unknown'
            }
            field._baseTypeDebug = baseType;
            if (isPgArray) {
                field._isPgArray = true;
            }
            field.primaryKey = row.pk == 1;
            field.default = row.dflt_value;
            field.required = row.notnull && !row.dflt_value;
            fieldTypes[row.name] = field
        });
        return fieldTypes;
    }

    getFieldValue(field: any, value: any) {
        if (field.type == AdminForthDataTypes.DATETIME) {
            if (!value) {
                return null;
            }
            if (field._underlineType == 'timestamp') {
                if (typeof value == 'string') {
                    const normalizedValue = value.includes(' ') ? `${value.replace(' ', 'T')}Z` : value;
                    return dayjs(normalizedValue).toISOString();
                }
                return dayjs(value).toISOString();
            } else if (field._underlineType == 'int') {
                return dayjs.unix(+value).toISOString();
            } else if (field._underlineType == 'varchar') {
                return dayjs(value).toISOString();
            } else {
                throw new Error(`AdminForth does not support row type: ${field._underlineType} for timestamps, use VARCHAR (with iso strings) or TIMESTAMP/INT (with unix timestamps). Issue in field: ${field.name} in table: ${field.table}`);
            }
        }

        if (field.type == AdminForthDataTypes.DATE) {
            if (!value) {
                return null;
            }
            return value;
        }

        if (field.type == AdminForthDataTypes.BOOLEAN) {
            return value === null ? null : !!value;
        } 

        if (field.type == AdminForthDataTypes.JSON) {
            if (typeof value == 'string') {
                try {
                    return JSON.parse(value);
                } catch (e: any) {
                    return { 'error': `Failed to parse JSON: ${e.message}` }
                }
            } else if (typeof value == 'object') {
                return value;
            } else {
                afLogger.error(`JSON field value is not string or object, but has type: ${typeof value}`);
                afLogger.error(`Field:, ${field}`);
                return {}
            }
        }

        return value;
    }


    setFieldValue(field: any, value: any) {
        if (field.type == AdminForthDataTypes.DATETIME) {
            if (!value) {
                return null;
            }
            if (field._underlineType == 'timestamp' || field._underlineType == 'int') {
                return dayjs(value);
            } else if (field._underlineType == 'varchar') {
                return dayjs(value).toISOString();
            }
        } else if (field.isArray?.enabled) {
            if (value === null || value === undefined) {
                return null;
            }
            if (field._isPgArray) {
                return value;
            }
            if (field._underlineType == 'json') {
                return JSON.stringify(value);
            }
            return JSON.stringify(value);
        } else if (field.type == AdminForthDataTypes.BOOLEAN) {
            return value === null ? null : (value ? true : false);
        } else if (field.type == AdminForthDataTypes.JSON) {
            if (field._underlineType == 'json') {
                return typeof value === 'string' || value === null ? value : JSON.stringify(value);
            } else {
                return JSON.stringify(value);
            }
        }
        return value;
    }

    getFilterString(resource: AdminForthResource, filter: IAdminForthSingleFilter | IAdminForthAndOrFilter): string {
        if ((filter as IAdminForthSingleFilter).field) {
            // Field-to-field comparison support
            if ((filter as IAdminForthSingleFilter).rightField) {
                const left = `"${(filter as IAdminForthSingleFilter).field}"`;
                const right = `"${(filter as IAdminForthSingleFilter).rightField}"`;
                const operator = this.OperatorsMap[filter.operator as keyof typeof this.OperatorsMap] as string;
                return `${left} ${operator} ${right}`;
            }
            let placeholder = '$?';
            let field = (filter as IAdminForthSingleFilter).field;
            const fieldData = resource.dataSourceColumns.find((col) => col.name == field) as any;
            let operator = this.OperatorsMap[filter.operator as keyof typeof this.OperatorsMap] as string;
            
            // Handle IS_EMPTY and IS_NOT_EMPTY operators
            if (filter.operator == AdminForthFilterOperators.IS_EMPTY || filter.operator == AdminForthFilterOperators.IS_NOT_EMPTY) {
                return `"${field}" ${operator}`;
            } else if (filter.operator == AdminForthFilterOperators.IN || filter.operator == AdminForthFilterOperators.NIN) {
                placeholder = `(${filter.value.map(() => placeholder).join(', ')})`;
            }

            if (fieldData._underlineType == 'uuid' &&
                (filter.operator == AdminForthFilterOperators.ILIKE || filter.operator == AdminForthFilterOperators.LIKE)
            ) {
                field = `cast("${field}" as text)`
            } else if (filter.operator == AdminForthFilterOperators.EQ && filter.value === null) {
                operator = 'IS';
                placeholder = 'NULL';
            } else {
                field = `"${field}"`
            }
            return `${field} ${operator} ${placeholder}`;
        }

        // filter is a single insecure raw sql
        if ((filter as IAdminForthSingleFilter).insecureRawSQL) {
            return (filter as IAdminForthSingleFilter).insecureRawSQL as string;
        }

        // filter is a AndOr filter
        return (filter as IAdminForthAndOrFilter).subFilters.map((f) => {
            if ((f as IAdminForthSingleFilter).field || (f as IAdminForthSingleFilter).insecureRawSQL) {
                // subFilter is a Single filter
                return this.getFilterString(resource, f);
            }

            // subFilter is a AndOr filter - add parentheses
            return `(${this.getFilterString(resource, f)})`;
        }).join(` ${this.OperatorsMap[filter.operator as keyof typeof this.OperatorsMap] as string} `);
    }

    getFilterParams(filter: IAdminForthSingleFilter | IAdminForthAndOrFilter): any[] {
        if ((filter as IAdminForthSingleFilter).field) {
            if ((filter as IAdminForthSingleFilter).rightField) {
                // No params for field-to-field comparisons
                return [];
            }
            // filter is a Single filter
            
            // Handle IS_EMPTY and IS_NOT_EMPTY operators - no params needed
            if (filter.operator == AdminForthFilterOperators.IS_EMPTY || filter.operator == AdminForthFilterOperators.IS_NOT_EMPTY) {
                return [];
            } else if (filter.operator == AdminForthFilterOperators.LIKE || filter.operator == AdminForthFilterOperators.ILIKE) {
                return [`%${filter.value}%`];
            } else if (filter.operator == AdminForthFilterOperators.IN || filter.operator == AdminForthFilterOperators.NIN) {
                return filter.value;
            } else if (filter.operator == AdminForthFilterOperators.EQ && filter.value === null) {
                return [];
            } else {
                return [(filter as IAdminForthSingleFilter).value];
            }
        }

        // filter is a single insecure raw sql
        if ((filter as IAdminForthSingleFilter).insecureRawSQL) {
            return [];
        }

        // filter is a AndOrFilter
        return (filter as IAdminForthAndOrFilter).subFilters.reduce((params: any[], f: IAdminForthSingleFilter | IAdminForthAndOrFilter) => {
            return params.concat(this.getFilterParams(f));
        }, []);
    }

    whereClauseAndValues(resource: AdminForthResource, filters: IAdminForthAndOrFilter): {
        sql: string,
        paramsCount: number,
        values: any[],
    } {
        let where = filters.subFilters.length ? `WHERE ${this.getFilterString(resource, filters)}` : '';
        const filterValues = filters.subFilters.length ? this.getFilterParams(filters) : [];
        filterValues.forEach((_, i) => where = where.replace('$?', `$${i + 1}`));
        return {
            sql: where,
            paramsCount: filterValues.length + 1,
            values: filterValues,
        };
    }


    async getAggregateWithOriginalTypes({ resource, filters, aggregations, groupBy }: {
        resource: AdminForthResource,
        filters: IAdminForthAndOrFilter,
        aggregations: { [alias: string]: IAggregationRule },
        groupBy?: IGroupByRule | IGroupByRule[],
    }): Promise<Array<{ group?: string, [key: string]: any }>> {
        const tableName = resource.table;
        const selectParts: string[] = [];
        const groupExprs: string[] = [];
        const groupByRules = this.normalizeGroupByRules(groupBy);

        for (const [index, groupByRule] of groupByRules.entries()) {
          let groupExpr: string;
          if (groupByRule.type === 'date_trunc') {
            const g = groupByRule as IGroupByDateTrunc;
            const tz = g.timezone ?? 'UTC';
            const col = resource.dataSourceColumns.find(c => c.name === g.field);
            const hasTZ = (col as any)?._baseTypeDebug?.includes('with time zone');
            const innerExpr = hasTZ
                ? `"${g.field}" AT TIME ZONE '${tz}'`
                : `"${g.field}" AT TIME ZONE 'UTC' AT TIME ZONE '${tz}'`;
            const fieldExpr = `DATE_TRUNC('${g.truncation}', ${innerExpr})`;
            groupExpr = `TO_CHAR(${fieldExpr}, 'YYYY-MM-DD')`;
          } else {
            const g = groupByRule as IGroupByField;
            groupExpr = `"${g.field}"`;
          }
          groupExprs.push(groupExpr);
          selectParts.push(`${groupExpr} AS "${this.getGroupByResultAlias(groupByRule, index, groupByRules.length)}"`);
        }

        for (const [alias, rule] of Object.entries(aggregations)) {
            switch (rule.operation) {
                case 'sum': selectParts.push(`SUM("${rule.field}") AS "${alias}"`); break;
                case 'count': selectParts.push(`COUNT(*) AS "${alias}"`); break;
                case 'count_distinct': selectParts.push(`COUNT(DISTINCT "${rule.field}") AS "${alias}"`); break;
                case 'avg': selectParts.push(`AVG("${rule.field}") AS "${alias}"`); break;
                case 'min': selectParts.push(`MIN("${rule.field}") AS "${alias}"`); break;
                case 'max': selectParts.push(`MAX("${rule.field}") AS "${alias}"`); break;
                case 'median': selectParts.push(`PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY "${rule.field}") AS "${alias}"`); break;
            }
        }

        const { sql: where, values: filterValues } = this.whereClauseAndValues(resource, filters);
        let query = `SELECT ${selectParts.join(', ')} FROM "${tableName}" ${where}`;

        if (groupExprs.length) {
            query += ` GROUP BY ${groupExprs.join(', ')} ORDER BY ${groupExprs.join(', ')} ASC`;
        }

        dbLogger.trace(`🪲📜 PG AGG Q: ${query}, params: ${JSON.stringify(filterValues)}`);
        const stmt = await this.client.query(query, filterValues);
        return stmt.rows;
    }

    async getDataWithOriginalTypes({ resource, limit, offset, sort, filters, columns }: {
        resource: AdminForthResource;
        limit: number;
        offset: number;
        sort: any[];
        filters: IAdminForthAndOrFilter;
        columns?: Array<{ name: string }>;
    }): Promise<any[]> {
        const selectedColumns = (columns ?? resource.dataSourceColumns).map((col: { name: string }) => `"${col.name}"`).join(', ');
        const tableName = resource.table;

        const { sql: where, paramsCount, values: filterValues } = this.whereClauseAndValues(resource, filters);

                if (sort.some(s => !checkIfFieldIsInsideResourceColumns(s.field, resource))) {
                    throw new Error(`Invalid sort field: ${sort.find(s => !checkIfFieldIsInsideResourceColumns(s.field, resource))?.field}`);
                }

        const limitOffset = `LIMIT $${paramsCount} OFFSET $${paramsCount + 1}`;
        const d = [...filterValues, limit, offset];
        const orderBy = sort.length ? `ORDER BY ${sort.map((s: any) => `"${s.field}" ${this.SortDirectionsMap[s.direction as keyof typeof this.SortDirectionsMap]}`).join(', ')}` : '';
        const selectQuery = `SELECT ${selectedColumns} FROM "${tableName}" ${where} ${orderBy} ${limitOffset}`;
        dbLogger.trace(`🪲📜 PG Q: ${selectQuery}, params: ${JSON.stringify(d)}`);
        const stmt = await this.client.query(selectQuery, d);
        const rows = stmt.rows as QueryRow[];
        return rows.map((row: QueryRow) => {
            const newRow: QueryRow = {};
            for (const [key, value] of Object.entries(row)) {
                newRow[key] = value;
            }
            return newRow;
        });
    }

    async getCount({ resource, filters }: { resource: AdminForthResource; filters: IAdminForthAndOrFilter; }): Promise<number> {
        const tableName = resource.table;
        let normalizedFilters = filters;

        // validate and normalize in case this method is called from dataAPI
        if (filters) {
            const filterValidation = this.validateAndNormalizeFilters(filters, resource);
            if (!filterValidation.ok) {
                throw new Error(filterValidation.error);
            }
            normalizedFilters = filterValidation.normalizedFilters as IAdminForthAndOrFilter;
        }
        const { sql: where, values: filterValues } = this.whereClauseAndValues(resource, normalizedFilters);
        const q = `SELECT COUNT(*) FROM "${tableName}" ${where}`;
        dbLogger.trace(`🪲📜 PG Q: ${q}, values: ${JSON.stringify(filterValues)}`);
        const stmt = await this.client.query(q, filterValues);
        return +stmt.rows[0].count;
    }

    async getMinMaxForColumnsWithOriginalTypes({ resource, columns }: { resource: AdminForthResource; columns: Array<{ name: string }> }) {
        const tableName = resource.table;
        const result: Record<string, { min: any; max: any }> = {};
        await Promise.all(columns.map(async (col: { name: string }) => {
            const q = `SELECT MIN("${col.name}") as min, MAX("${col.name}") as max FROM "${tableName}"`;
            dbLogger.trace(`🪲📜 PG Q: ${q}`);
            const stmt = await this.client.query(q);
            const { min, max } = stmt.rows[0];
            result[col.name] = {
                min, max,
            };
        }))
        return result;
    } 

    async createRecordOriginalValues({ resource, record }: { resource: AdminForthResource; record: Record<string, any> }): Promise<string> {
        const tableName = resource.table;
        const columns = Object.keys(record);

        const knownColumns = new Set(resource.dataSourceColumns.map((col: AdminForthResourceColumn) => col.name));
        const unknownColumn = columns.find((colName) => !knownColumns.has(colName));
        if (unknownColumn) {
            throw new Error(`Invalid column name: ${unknownColumn}`);
        }

        const placeholders = columns.map((_, i) => `$${i + 1}`).join(', ');
        const values = columns.map((colName) => record[colName]);
        for (let i = 0; i < columns.length; i++) {
            columns[i] = `"${columns[i]}"`;
        }
        const primaryKeys = this.getPrimaryKeys(resource);
        const returning = primaryKeys.map((pk) => `"${pk}"`).join(', ');
        const q = `INSERT INTO "${tableName}" (${columns.join(', ')}) VALUES (${placeholders}) RETURNING ${returning}`;
        dbLogger.trace(`🪲📜 PG Q: ${q}, values: ${JSON.stringify(values)}`);
        const ret = await this.client.query(q, values);
        // for composite primary key base connector builds record id from all returned key columns itself
        return ret.rows[0][primaryKeys[0]];
    }

    async updateRecordOriginalValues({ resource, recordId, newValues, pkValues }: { resource: AdminForthResource; recordId: any; newValues: Record<string, any>; pkValues?: Record<string, any> }) {
        const pkEntries = this.pkEntries(resource, recordId, pkValues);
        const values = [...Object.values(newValues), ...pkEntries.map(([, value]) => value)];
        const columnsWithPlaceholders = Object.keys(newValues).map((col, i) => `"${col}" = $${i + 1}`).join(', ');
        const whereClause = pkEntries
            .map(([col], i) => `"${col}" = $${Object.keys(newValues).length + i + 1}`)
            .join(' AND ');
        const q = `UPDATE "${resource.table}" SET ${columnsWithPlaceholders} WHERE ${whereClause}`;
        dbLogger.trace(`🪲📜 PG Q: ${q}, values: ${JSON.stringify(values)}`);
        await this.client.query(q, values);
    }

    async deleteRecord({ resource, recordId, pkValues }: { resource: AdminForthResource; recordId: any; pkValues?: Record<string, any> }): Promise<boolean> {
        const pkEntries = this.pkEntries(resource, recordId, pkValues);
        const whereClause = pkEntries.map(([col], i) => `"${col}" = $${i + 1}`).join(' AND ');
        const values = pkEntries.map(([, value]) => value);
        const q = `DELETE FROM "${resource.table}" WHERE ${whereClause}`;
        dbLogger.trace(`🪲📜 PG Q: ${q}, values: ${JSON.stringify(values)}`);
        const res = await this.client.query(q, values);
        return res.rowCount > 0;
    }

    async deleteMany({ resource, recordIds }: { resource: AdminForthResource; recordIds: string[]}): Promise<number> {
        if (!recordIds || recordIds.length === 0) {
            return 0;
        }
        // (pk1 = $1 AND pk2 = $2) OR (pk1 = $3 AND pk2 = $4) ... , which is just "pk IN (...)" for single primary key
        const values: any[] = [];
        const whereClause = recordIds.map((recordId) => {
            const pkEntries = this.pkEntries(resource, recordId);
            const condition = pkEntries.map(([col], i) => `"${col}" = $${values.length + i + 1}`).join(' AND ');
            values.push(...pkEntries.map(([, value]) => value));
            return `(${condition})`;
        }).join(' OR ');
        const query = `DELETE FROM "${resource.table}" WHERE ${whereClause}`;
        dbLogger.trace(`🪲📜 PG Q: ${query}, values: ${JSON.stringify(values)}`);
        const res = await this.client.query(query, values);
        return res.rowCount ?? 0;
    }

    async close() {
        await this.client.end();
    }
}

export default PostgresConnector;