How to Integrate Lovable with PostgreSQL

Before writing any code, store your database connection string securely. In Lovable, click the '+' button next to the Preview panel to open the Cloud tab. Navigate to the Secrets section. Click 'Add Secret' and create a new secret named POSTGRES_URL. Paste your full connection string as the value — it should look like: postgresql://myuser:mypassword@myhost.example.com:5432/mydatabase?sslmode=require The ?sslmode=require parameter at the end is important. Most hosted PostgreSQL providers (Neon, Railway, RDS) require SSL connections, and omitting this flag can cause connection failures. If your provider gives you a connection string with ?sslmode=verify-full, keep that instead — it's more secure. Do not paste this connection string anywhere in the chat, in component code, or in any client-side file. Lovable's security scanner blocks approximately 1,200 hardcoded credentials per day, but it's still your responsibility to use the Secrets panel. Lovable's AI will reference this secret by name in the Edge Function code using Deno.env.get('POSTGRES_URL').

Open the Lovable chat panel and describe the Edge Function you need. Lovable will generate the TypeScript code, but for complex external database integrations, providing an explicit prompt helps ensure the generated code is correct. Ask Lovable to create a new Edge Function at supabase/functions/query-postgres/index.ts. The function needs to: import the pg client from npm:pg, read the connection string from Deno.env.get('POSTGRES_URL'), accept a POST request with a JSON body specifying the operation type and parameters, execute a parameterized query (never string interpolation), handle errors gracefully and return them as JSON, and close the database connection after each request. Parameterized queries are critical for security. Using string interpolation like `SELECT * FROM users WHERE id = ${userId}` creates SQL injection vulnerabilities. The correct pattern is `client.query('SELECT * FROM users WHERE id = $1', [userId])` — the pg driver handles escaping automatically. The Edge Function also needs CORS headers so your Lovable frontend can call it. Lovable adds these automatically when you ask for an Edge Function that the frontend will call.

After Lovable generates the Edge Function, open the Code panel and navigate to supabase/functions/query-postgres/index.ts. Read through the code to verify a few critical points: the connection string is read with Deno.env.get('POSTGRES_URL') and not hardcoded anywhere, queries use parameterized placeholders ($1, $2, etc.) rather than string concatenation, the pg client is created per request or a connection pool is used rather than a global singleton (global singletons can cause issues in Deno's serverless environment), and CORS headers include Access-Control-Allow-Origin, Access-Control-Allow-Headers, and Access-Control-Allow-Methods. If you need to test the function directly before wiring it to the frontend, you can use Lovable's built-in log viewer. Open Cloud → Logs, then trigger the function from your frontend or via the chat prompt. Any console.log output and errors from the Edge Function appear in real time. Common issues at this stage include SSL handshake failures (add ?sslmode=require to the connection string), timeouts on first cold start (Deno functions have a brief warm-up), and pg driver import errors (ensure you're using npm:pg syntax, not a bare pg import).

1// supabase/functions/query-postgres/index.ts
2import { serve } from "https://deno.land/std@0.168.0/http/server.ts";
3import { Client } from "npm:pg";
4
5const corsHeaders = {
6  "Access-Control-Allow-Origin": "*",
7  "Access-Control-Allow-Headers": "authorization, x-client-info, apikey, content-type",
8  "Access-Control-Allow-Methods": "POST, OPTIONS",
9};
10
11serve(async (req: Request) => {
12  if (req.method === "OPTIONS") {
13    return new Response("ok", { headers: corsHeaders });
14  }
15
16  try {
17    const { operation, table, filters, data, limit = 100 } = await req.json();
18
19    if (!operation || !table) {
20      return new Response(
21        JSON.stringify({ error: "operation and table are required" }),
22        { status: 400, headers: { ...corsHeaders, "Content-Type": "application/json" } }
23      );
24    }
25
26    const client = new Client({ connectionString: Deno.env.get("POSTGRES_URL") });
27    await client.connect();
28
29    let result;
30    try {
31      if (operation === "select") {
32        const conditions = filters
33          ? Object.entries(filters).map(([k, _], i) => `${k} = $${i + 1}`).join(" AND ")
34          : null;
35        const values = filters ? Object.values(filters) : [];
36        const sql = conditions
37          ? `SELECT * FROM ${table} WHERE ${conditions} LIMIT $${values.length + 1}`
38          : `SELECT * FROM ${table} LIMIT $1`;
39        result = await client.query(sql, [...values, limit]);
40      } else if (operation === "insert") {
41        const cols = Object.keys(data).join(", ");
42        const placeholders = Object.keys(data).map((_, i) => `$${i + 1}`).join(", ");
43        result = await client.query(
44          `INSERT INTO ${table} (${cols}) VALUES (${placeholders}) RETURNING *`,
45          Object.values(data)
46        );
47      } else {
48        return new Response(
49          JSON.stringify({ error: `Unsupported operation: ${operation}` }),
50          { status: 400, headers: { ...corsHeaders, "Content-Type": "application/json" } }
51        );
52      }
53    } finally {
54      await client.end();
55    }
56
57    return new Response(
58      JSON.stringify({ data: result.rows, count: result.rowCount }),
59      { status: 200, headers: { ...corsHeaders, "Content-Type": "application/json" } }
60    );
61  } catch (error) {
62    return new Response(
63      JSON.stringify({ error: error.message }),
64      { status: 500, headers: { ...corsHeaders, "Content-Type": "application/json" } }
65    );
66  }
67});

With the Edge Function deployed, you can now ask Lovable to build frontend components that call it. Open the chat and describe the UI you want — Lovable will generate React components that call the query-postgres Edge Function via fetch and display the results. For Lovable's AI to generate accurate query logic, tell it your schema explicitly: table names, column names, data types, and any foreign key relationships. Without this context, Lovable may guess column names incorrectly. A useful approach is to paste a simplified schema description into the chat: 'My PostgreSQL database has these tables: users (id uuid, name text, email text, created_at timestamptz), orders (id uuid, user_id uuid, total numeric, status text, created_at timestamptz).' The generated React code will use a pattern like: calling the Edge Function URL (which Lovable constructs automatically from your project's Supabase URL), passing the operation type and parameters as a JSON body, handling loading and error states in the component, and displaying results in a table, list, or chart. You will see the fetch call reference your Supabase project URL plus /functions/v1/query-postgres — this is the correct endpoint format for Lovable Cloud Edge Functions.

By default, Edge Functions in Lovable are publicly accessible — any caller with the function URL can invoke them. For a database proxy, you should add authentication to prevent unauthorized database access. The simplest method is to require the Supabase anon key in the Authorization header, which Lovable's frontend sends automatically when using the Supabase client. Ask Lovable to add authentication to your Edge Function by verifying the JWT from the Authorization header. This ensures only authenticated users from your Lovable app can trigger database queries. For read-only public data, you may choose to leave the function open, but for any write operations or user-specific data, authentication is essential. Also review your Edge Function's error handling: does it return user-friendly messages for expected errors (record not found, validation failure) versus generic 500 responses for unexpected errors? Detailed database error messages should not be returned to the frontend in production — they can leak schema information. Ask Lovable to filter error messages so only safe, generic descriptions reach the client. For production deployments with strict security requirements or complex multi-table schemas, RapidDev's team can help audit your Edge Function code and database access patterns.