Connecting Lovable Apps to External Databases Safely

// DANGEROUS: Connection string in frontend code — visible to everyone
const DB_URL = "postgresql://admin:secretpass@db.example.com:5432/myapp";
// Or worse: in a VITE_ env variable (also shipped to browser)
const DB_URL = import.meta.env.VITE_DATABASE_URL;

// SAFE: Connection string stored in Cloud tab → Secrets
// Name: EXTERNAL_DB_URL
// Value: postgresql://admin:secretpass@db.example.com:5432/myapp
// This secret is only accessible from Edge Functions
// It never appears in frontend code or browser source

// No server-side proxy exists — frontend tries to connect directly
import { Client } from "pg";
async function fetchData() {
  const client = new Client("postgresql://admin:secretpass@db.example.com:5432/myapp");
  await client.connect();
  const result = await client.query("SELECT * FROM products");
  return result.rows;
}

// Edge Function: supabase/functions/external-db-query/index.ts
import { serve } from "https://deno.land/std@0.168.0/http/server.ts";
import { Client } from "https://deno.land/x/postgres@v0.17.0/mod.ts";
serve(async (req) => {
  try {
    // Connection string comes from Lovable Secrets — never hardcoded
    const dbUrl = Deno.env.get("EXTERNAL_DB_URL");
    if (!dbUrl) throw new Error("EXTERNAL_DB_URL secret is not set");
    const client = new Client(dbUrl);
    await client.connect();
    const { table, limit } = await req.json();
    // Use parameterized query to prevent SQL injection
    const result = await client.queryObject(
      `SELECT * FROM ${table} LIMIT $1`,
      [limit || 100]
    );
    await client.end();
    return new Response(JSON.stringify({ data: result.rows }), {
      headers: { "Content-Type": "application/json" },
    });
  } catch (error) {
    console.error("Database query error:", error.message);
    return new Response(JSON.stringify({ error: error.message }), {
      status: 500,
      headers: { "Content-Type": "application/json" },
    });
  }
});

// Direct database access from frontend — UNSAFE
const fetchProducts = async () => {
  const client = new Client(DB_URL);
  await client.connect();
  const result = await client.query("SELECT * FROM products");
  return result.rows;
};

import { supabase } from "@/integrations/supabase/client";
const fetchProducts = async () => {
  // Call Edge Function — credentials stay on server
  const { data, error } = await supabase.functions.invoke(
    "external-db-query",
    {
      body: { table: "products", limit: 50 },
    }
  );
  if (error) {
    console.error("Failed to fetch products:", error.message);
    return [];
  }
  return data.data;
};

// DANGEROUS: Table name comes directly from user input
const { table, limit } = await req.json();
const result = await client.queryObject(
  `SELECT * FROM ${table} LIMIT $1`,
  [limit]
);

// SAFE: Whitelist allowed tables and validate input
const ALLOWED_TABLES = ["products", "categories", "orders"];
const { table, limit } = await req.json();
if (!ALLOWED_TABLES.includes(table)) {
  return new Response(
    JSON.stringify({ error: "Invalid table name" }),
    { status: 400, headers: { "Content-Type": "application/json" } }
  );
}
const safeLimit = Math.min(Math.max(Number(limit) || 100, 1), 1000);
const result = await client.queryObject(
  `SELECT * FROM ${table} LIMIT $1`,
  [safeLimit]
);