// src/snowflake/serverless-connection.ts
import snowflake from 'snowflake-sdk';

let cachedConnection: snowflake.Connection | null = null;

/**
 * Reuse connection across Lambda/Cloud Function invocations.
 * Serverless containers may be reused — avoid reconnecting every call.
 */
export async function getConnection(): Promise<snowflake.Connection> {
  if (cachedConnection?.isUp()) {
    return cachedConnection;
  }

  const conn = snowflake.createConnection({
    account: process.env.SNOWFLAKE_ACCOUNT!,
    username: process.env.SNOWFLAKE_USER!,
    authenticator: 'SNOWFLAKE_JWT',
    privateKey: process.env.SNOWFLAKE_PRIVATE_KEY!,
    warehouse: process.env.SNOWFLAKE_WAREHOUSE!,
    database: process.env.SNOWFLAKE_DATABASE!,
    schema: process.env.SNOWFLAKE_SCHEMA || 'PUBLIC',
    clientSessionKeepAlive: true,  // Keep session alive between invocations
  });

  await new Promise<void>((resolve, reject) => {
    conn.connect((err) => (err ? reject(err) : resolve()));
  });

  cachedConnection = conn;
  return conn;
}

# Dockerfile
FROM node:20-slim
WORKDIR /app
COPY package*.json ./
RUN npm ci --only=production
COPY dist/ ./dist/
ENV NODE_ENV=production
CMD ["node", "dist/index.js"]

#!/bin/bash
# deploy-cloud-run.sh
PROJECT_ID="${GCP_PROJECT_ID}"
SERVICE_NAME="snowflake-api"
REGION="us-central1"

# Build and push
gcloud builds submit --tag gcr.io/$PROJECT_ID/$SERVICE_NAME

# Deploy with Snowflake credentials from Secret Manager
gcloud run deploy $SERVICE_NAME \
  --image gcr.io/$PROJECT_ID/$SERVICE_NAME \
  --region $REGION \
  --platform managed \
  --set-secrets="SNOWFLAKE_ACCOUNT=snowflake-account:latest,\
SNOWFLAKE_USER=snowflake-user:latest,\
SNOWFLAKE_PRIVATE_KEY=snowflake-private-key:latest" \
  --set-env-vars="SNOWFLAKE_WAREHOUSE=PROD_ANALYTICS_WH,\
SNOWFLAKE_DATABASE=PROD_DB,\
SNOWFLAKE_SCHEMA=PUBLIC" \
  --min-instances=1 \
  --max-instances=10 \
  --timeout=300

// lambda/handler.ts
import { getConnection } from './snowflake/serverless-connection';

export async function handler(event: any) {
  try {
    const conn = await getConnection();
    const rows = await new Promise<any[]>((resolve, reject) => {
      conn.execute({
        sqlText: 'SELECT COUNT(*) AS total FROM orders WHERE order_date = CURRENT_DATE()',
        complete: (err, stmt, rows) => (err ? reject(err) : resolve(rows || [])),
      });
    });
    return { statusCode: 200, body: JSON.stringify(rows[0]) };
  } catch (error: any) {
    return { statusCode: 500, body: JSON.stringify({ error: error.message }) };
  }
}

# Store Snowflake private key in AWS Secrets Manager
aws secretsmanager create-secret \
  --name snowflake/prod/private-key \
  --secret-string "$(cat rsa_key.p8)"

# Lambda environment variables
aws lambda update-function-configuration \
  --function-name snowflake-api \
  --environment "Variables={
    SNOWFLAKE_ACCOUNT=myorg-myaccount,
    SNOWFLAKE_USER=svc_lambda,
    SNOWFLAKE_WAREHOUSE=PROD_ANALYTICS_WH,
    SNOWFLAKE_DATABASE=PROD_DB
  }" \
  --timeout 300

# docker-compose.yml
services:
  snowflake-app:
    build: .
    environment:
      - SNOWFLAKE_ACCOUNT=${SNOWFLAKE_ACCOUNT}
      - SNOWFLAKE_USER=${SNOWFLAKE_USER}
      - SNOWFLAKE_PRIVATE_KEY_PATH=/run/secrets/snowflake_key
      - SNOWFLAKE_WAREHOUSE=PROD_ANALYTICS_WH
      - SNOWFLAKE_DATABASE=PROD_DB
    secrets:
      - snowflake_key
    healthcheck:
      test: ["CMD", "curl", "-f", "http://localhost:3000/health"]
      interval: 30s
      timeout: 10s
      retries: 3

secrets:
  snowflake_key:
    file: ./rsa_key.p8  # Never commit this file

// src/health.ts
import { getConnection } from './snowflake/serverless-connection';

export async function healthCheck() {
  const start = Date.now();
  try {
    const conn = await getConnection();
    const rows = await new Promise<any[]>((resolve, reject) => {
      conn.execute({
        sqlText: 'SELECT 1 AS health_check',
        complete: (err, _, rows) => (err ? reject(err) : resolve(rows || [])),
      });
    });
    return {
      status: 'healthy',
      snowflake: { connected: true, latencyMs: Date.now() - start },
    };
  } catch (error: any) {
    return {
      status: 'degraded',
      snowflake: { connected: false, error: error.message, latencyMs: Date.now() - start },
    };
  }
}