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

10.9 KB
Raw Blame
import { AppConnectionValueForAuthProperty, isNil } from '@activepieces/pieces-framework';
import sql from 'mssql';
import type { mssqlAuth } from '../auth';

const DEFAULT_PORT = 1433;
const TIMEOUT_MS = 30000;
const OUTPUT_BLOCKED_BY_TRIGGER = 334;

function isOutputBlockedByTrigger(e: unknown): boolean {
  if (typeof e !== 'object' || isNil(e) || !('number' in e)) return false;
  const { number } = e;
  return number === OUTPUT_BLOCKED_BY_TRIGGER;
}

function quoteId(identifier: string): string {
  if (identifier.includes('\0')) {
    throw new Error(`Invalid identifier: ${JSON.stringify(identifier)}`);
  }
  return `[${identifier.replace(/]/g, ']]')}]`;
}

function quoteTable(table: MssqlTable): string {
  return `${quoteId(table.table_schema)}.${quoteId(table.table_name)}`;
}

function buildConfig({
  auth,
  requestTimeoutMs,
}: {
  auth: MssqlAuth;
  requestTimeoutMs?: number;
}): sql.config {
  const {
    connection_string,
    host,
    port,
    database,
    user,
    password,
    tenant_id,
    client_id,
    client_secret,
    encrypt,
    trust_server_certificate,
    certificate,
    min_tls_version,
  } = auth.props;

  const requestTimeout = isNil(requestTimeoutMs)
    ? TIMEOUT_MS
    : Number(requestTimeoutMs);
  const cryptoCredentialsDetails: Record<string, string> = {};
  if (certificate && certificate.trim().length > 0) {
    cryptoCredentialsDetails['ca'] = certificate.trim();
  }
  if (min_tls_version) {
    cryptoCredentialsDetails['minVersion'] = min_tls_version;
  }

  if (connection_string && connection_string.trim().length > 0) {
    const trimmed = connection_string.trim();
    if (/Password\s*=\s*\{?your_password\}?/i.test(trimmed)) {
      throw new Error(
        'The connection string still contains the {your_password} placeholder from the Azure portal. Replace it with your actual password.'
      );
    }
    const entra = trimmed.match(/Authentication\s*=\s*(Active Directory[^;]*)/i);
    if (entra) {
      throw new Error(
        `The Connection String field does not support "${entra[1].trim()}". Copy the ADO.NET (SQL authentication) string from the Azure portal instead and replace the credentials inside that string, including the {your_password} placeholder, or clear the Connection String and fill in Host, Tenant ID, Client ID and Client Secret to authenticate with Microsoft Entra ID.`
      );
    }
    const parsed = sql.ConnectionPool.parseConnectionString(trimmed);
    parsed.requestTimeout = requestTimeout;
    if (Object.keys(cryptoCredentialsDetails).length > 0) {
      parsed.options = {
        ...parsed.options,
        cryptoCredentialsDetails: {
          ...(parsed.options?.cryptoCredentialsDetails ?? {}),
          ...cryptoCredentialsDetails,
        },
      };
    }
    return parsed;
  }

  if (tenant_id || client_id || client_secret) {
    if (!tenant_id || !client_id || !client_secret) {
      throw new Error(
        'Tenant ID, Client ID and Client Secret are all required to authenticate with Microsoft Entra ID.'
      );
    }
    if (!host) {
      throw new Error(
        'Host is required to authenticate with Microsoft Entra ID.'
      );
    }
    return {
      server: host,
      port: port ? Number(port) : DEFAULT_PORT,
      database: database || undefined,
      connectionTimeout: TIMEOUT_MS,
      requestTimeout,
      authentication: {
        type: 'azure-active-directory-service-principal-secret',
        options: {
          clientId: client_id,
          clientSecret: client_secret,
          tenantId: tenant_id,
        },
      },
      options: {
        encrypt: encrypt ?? true,
        trustServerCertificate: trust_server_certificate ?? false,
        cryptoCredentialsDetails,
      },
    };
  }

  if (!host || !user || !password) {
    throw new Error(
      'Host, Username and Password are required unless a Connection String is provided.'
    );
  }

  return {
    server: host,
    port: port ? Number(port) : DEFAULT_PORT,
    database: database || undefined,
    user,
    password,
    connectionTimeout: TIMEOUT_MS,
    requestTimeout,
    options: {
      encrypt: encrypt ?? true,
      trustServerCertificate: trust_server_certificate ?? false,
      cryptoCredentialsDetails,
    },
  };
}

async function connect({
  auth,
  requestTimeoutMs,
}: {
  auth: MssqlAuth;
  requestTimeoutMs?: number;
}): Promise<sql.ConnectionPool> {
  const config = buildConfig({ auth, requestTimeoutMs });
  const pool = new sql.ConnectionPool(config);
  try {
    await pool.connect();
  } catch (e) {
    await pool.close().catch(() => undefined);
    if (config.options?.instanceName) {
      const reason = e instanceof Error ? e.message : String(e);
      throw new Error(
        `${reason} Connecting by instance name ("${config.options.instanceName}") requires the SQL Server Browser service on UDP port 1434, which many networks block. Address the server by host and port instead, for example myhost,1433.`
      );
    }
    throw e;
  }
  return pool;
}

async function getTables(pool: sql.ConnectionPool): Promise<MssqlTable[]> {
  const result = await pool.request().query(
    `SELECT TABLE_SCHEMA, TABLE_NAME
     FROM INFORMATION_SCHEMA.TABLES
     WHERE TABLE_TYPE = 'BASE TABLE'
     ORDER BY TABLE_SCHEMA, TABLE_NAME`
  );
  return result.recordset.map((row) => ({
    table_schema: row['TABLE_SCHEMA'],
    table_name: row['TABLE_NAME'],
  }));
}

async function getTableMeta({
  pool,
  table,
}: {
  pool: sql.ConnectionPool;
  table: MssqlTable;
}): Promise<MssqlTableMeta> {
  const result = await pool
    .request()
    .input('schema', table.table_schema)
    .input('name', table.table_name)
    .query<Record<string, unknown>>(
      `DECLARE @object int = OBJECT_ID(QUOTENAME(@schema) + '.' + QUOTENAME(@name));

       SELECT c.name, TYPE_NAME(c.system_type_id) AS type_name, c.precision,
              c.scale, c.max_length, c.is_nullable, c.collation_name
       FROM sys.columns c
       WHERE c.object_id = @object
       ORDER BY c.column_id;

       SELECT i.index_id, i.is_primary_key, c.name AS column_name, c.is_nullable
       FROM sys.indexes i
       JOIN sys.index_columns ic
         ON ic.object_id = i.object_id AND ic.index_id = i.index_id
        AND ic.is_included_column = 0
       JOIN sys.columns c
         ON c.object_id = i.object_id AND c.column_id = ic.column_id
       WHERE i.object_id = @object
         AND i.is_unique = 1
         AND i.has_filter = 0
         AND i.is_disabled = 0
         AND i.is_hypothetical = 0
       ORDER BY i.index_id, ic.key_ordinal;

       SELECT name
       FROM sys.identity_columns
       WHERE object_id = @object AND increment_value > 0;`
    );

  const recordsets: Record<string, unknown>[][] = Array.isArray(
    result.recordsets
  )
    ? result.recordsets
    : [];
  const [columnRows, indexRows, identityRows] = recordsets;

  const columns: MssqlColumn[] = (columnRows ?? []).map((row) => ({
    name: String(row['name']),
    type: String(row['type_name']).toLowerCase(),
    precision: Number(row['precision']),
    scale: Number(row['scale']),
    maxLength: Number(row['max_length']),
    nullable: Boolean(row['is_nullable']),
    collation: isNil(row['collation_name'])
      ? undefined
      : String(row['collation_name']),
  }));

  const indexes = new Map<
    number,
    { isPrimary: boolean; nullable: boolean; columns: string[] }
  >();
  for (const row of indexRows ?? []) {
    const id = Number(row['index_id']);
    const entry = indexes.get(id) ?? {
      isPrimary: Boolean(row['is_primary_key']),
      nullable: false,
      columns: [],
    };
    entry.columns.push(String(row['column_name']));
    if (row['is_nullable']) entry.nullable = true;
    indexes.set(id, entry);
  }

  const candidates = [...indexes.values()]
    .filter((index) => !index.nullable)
    .sort((a, b) => {
      if (a.isPrimary !== b.isPrimary) return a.isPrimary ? -1 : 1;
      return a.columns.length - b.columns.length;
    });

  const ascending =
    (identityRows ?? []).length === 1
      ? String(identityRows[0]['name'])
      : undefined;
  const identity =
    ascending !== undefined &&
    candidates.some(
      (index) => index.columns.length === 1 && index.columns[0] === ascending
    )
      ? ascending
      : undefined;

  return {
    columns,
    identity,
    keyColumns: candidates[0]?.columns ?? [],
  };
}

async function getColumnDetails({
  pool,
  table,
}: {
  pool: sql.ConnectionPool;
  table: MssqlTable;
}): Promise<MssqlColumnDetail[]> {
  const result = await pool
    .request()
    .input('schema', table.table_schema)
    .input('name', table.table_name)
    .query<Record<string, unknown>>(
      `SELECT c.name, TYPE_NAME(c.system_type_id) AS type_name, c.is_nullable,
              c.is_identity, c.is_computed,
              CASE WHEN c.default_object_id <> 0 THEN 1 ELSE 0 END AS has_default
       FROM sys.columns c
       WHERE c.object_id = OBJECT_ID(QUOTENAME(@schema) + '.' + QUOTENAME(@name))
       ORDER BY c.column_id`
    );
  return result.recordset.map((row) => ({
    name: String(row['name']),
    typeName: String(row['type_name']).toLowerCase(),
    isNullable: Boolean(row['is_nullable']),
    isIdentity: Boolean(row['is_identity']),
    isComputed: Boolean(row['is_computed']),
    hasDefault: Boolean(row['has_default']),
  }));
}

function bindParameters({
  request,
  parameters,
}: {
  request: sql.Request;
  parameters: Record<string, unknown> | undefined;
}): sql.Request {
  for (const [name, value] of Object.entries(parameters ?? {})) {
    request.input(name.replace(/^@/, ''), value ?? null);
  }
  return request;
}

async function writeReturningRows({
  bind,
  withOutput,
  withoutOutput,
}: {
  bind: () => sql.Request;
  withOutput: string;
  withoutOutput: string;
}): Promise<{ rows: Record<string, unknown>[]; rows_affected: number }> {
  const total = (counts: number[]) => counts.reduce((a, b) => a + b, 0);
  try {
    const result = await bind().query<Record<string, unknown>>(withOutput);
    return {
      rows: result.recordset ?? [],
      rows_affected: total(result.rowsAffected ?? []),
    };
  } catch (e) {
    if (!isOutputBlockedByTrigger(e)) throw e;
    const result = await bind().query<Record<string, unknown>>(withoutOutput);
    return { rows: [], rows_affected: total(result.rowsAffected ?? []) };
  }
}

export const mssqlCommon = {
  isOutputBlockedByTrigger,
  quoteId,
  quoteTable,
  buildConfig,
  connect,
  getTables,
  getTableMeta,
  getColumnDetails,
  bindParameters,
  writeReturningRows,
};

export type MssqlAuth = AppConnectionValueForAuthProperty<typeof mssqlAuth>;

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

export type MssqlColumn = {
  name: string;
  type: string;
  precision: number;
  scale: number;
  maxLength: number;
  nullable: boolean;
  collation?: string;
};

export type MssqlTableMeta = {
  columns: MssqlColumn[];
  identity?: string;
  keyColumns: string[];
};

export type MssqlColumnDetail = {
  name: string;
  typeName: string;
  isNullable: boolean;
  isIdentity: boolean;
  isComputed: boolean;
  hasDefault: boolean;
};