Skip to content

AI Agents & MCPs & AI Workflow Automation • (~400 MCP servers for AI agents) • AI Automation / AI Agent with MCPs • AI Workflows & AI Agents • MCPs for AI Agents

6.3 KB
Raw Blame
import { MarkdownVariant, Property } from "@activepieces/pieces-framework";
import { postgresAuth } from "./auth";
import { Client } from "pg";
import { pgClient } from "./client";
import format from "pg-format";


const listTables = async (client: Client): Promise<PostgresTable[]> => {
    const result = await client.query<PostgresTable>(
        `SELECT table_schema, table_name FROM information_schema.tables WHERE table_type = 'BASE TABLE' AND table_schema NOT IN ('pg_catalog', 'information_schema') ORDER BY table_schema, table_name`
    );
    return result.rows;
}

const listColumns = async ({ client, table }: { client: Client, table: PostgresTable }): Promise<string[]> => {
    const result = await client.query<{ column_name: string }>(
        `SELECT column_name FROM information_schema.columns WHERE table_schema = $1 AND table_name = $2 ORDER BY ordinal_position`,
        [table.table_schema, table.table_name]
    );
    return result.rows.map((row) => row.column_name);
}

const listColumnDetails = async ({ client, table }: { client: Client, table: PostgresTable }): Promise<PostgresColumn[]> => {
    const result = await client.query<PostgresColumn>(
        `SELECT column_name, data_type, is_nullable, column_default, is_identity, is_generated FROM information_schema.columns WHERE table_schema = $1 AND table_name = $2 ORDER BY ordinal_position`,
        [table.table_schema, table.table_name]
    );
    return result.rows;
}

const isPostgresTable = (value: unknown): value is PostgresTable => {
    if (typeof value !== 'object' || value === null) {
        return false;
    }
    const candidate: Record<string, unknown> = { ...value };
    return typeof candidate['table_schema'] === 'string' && typeof candidate['table_name'] === 'string';
}

const qualifiedName = (table: PostgresTable): string => format('%I.%I', table.table_schema, table.table_name);

const quoteIdentifier = (name: string): string => name === '*' ? name : format.ident(name);

export { pgClient };

export const warningMarkdown = Property.MarkDown({
    value: 'Never type dynamic values into the condition. Write $1, $2 and put the values in Arguments to prevent SQL injection.',
    variant: MarkdownVariant.WARNING,
});

export const postgresCommon = {
    table: Property.Dropdown<PostgresTable, true, typeof postgresAuth>({
        auth: postgresAuth,
        displayName: 'Table',
        required: true,
        refreshers: [],
        refreshOnSearch: false,
        options: async ({ auth }) => {
            if (!auth) {
                return {
                    disabled: true,
                    options: [],
                    placeholder: 'Please authenticate first',
                };
            }
            const client = await pgClient(auth);
            try {
                const tables = await listTables(client);
                return {
                    disabled: false,
                    options: tables.map((table) => ({
                        label: `${table.table_schema}.${table.table_name}`,
                        value: {
                            table_schema: table.table_schema,
                            table_name: table.table_name,
                        },
                    })),
                };
            } finally {
                await client.end();
            }
        },
    }),
    column: ({ displayName, description }: { displayName: string, description?: string }) =>
        Property.Dropdown<string, true, typeof postgresAuth>({
            auth: postgresAuth,
            displayName,
            description,
            required: true,
            refreshers: ['table'],
            refreshOnSearch: false,
            options: async ({ auth, table }) => {
                if (!auth) {
                    return {
                        disabled: true,
                        options: [],
                        placeholder: 'Please authenticate first',
                    };
                }
                if (!isPostgresTable(table)) {
                    return {
                        disabled: true,
                        options: [],
                        placeholder: 'Please select a table',
                    };
                }
                const client = await pgClient(auth);
                try {
                    const columns = await listColumns({ client, table });
                    return {
                        disabled: false,
                        options: columns.map((column) => ({
                            label: column,
                            value: column,
                        })),
                    };
                } finally {
                    await client.end();
                }
            },
        }),
    columns: Property.MultiSelectDropdown<string, false, typeof postgresAuth>({
        auth: postgresAuth,
        displayName: 'Columns',
        description: 'Leave empty to return every column.',
        required: false,
        refreshers: ['table'],
        options: async ({ auth, table }) => {
            if (!auth) {
                return {
                    disabled: true,
                    options: [],
                    placeholder: 'Please authenticate first',
                };
            }
            if (!isPostgresTable(table)) {
                return {
                    disabled: true,
                    options: [],
                    placeholder: 'Please select a table',
                };
            }
            const client = await pgClient(auth);
            try {
                const columns = await listColumns({ client, table });
                return {
                    disabled: false,
                    options: columns.map((column) => ({
                        label: column,
                        value: column,
                    })),
                };
            } finally {
                await client.end();
            }
        },
    }),
};

export const postgresUtils = {
    listTables,
    listColumns,
    listColumnDetails,
    isPostgresTable,
    qualifiedName,
    quoteIdentifier,
};

export type PostgresTable = {
    table_schema: string;
    table_name: string;
};

export type PostgresColumn = {
    column_name: string;
    data_type: string;
    is_nullable: string;
    column_default: string | null;
    is_identity: string;
    is_generated: string;
};