How to Integrate Lovable with Microsoft SQL Server

Because Deno's runtime does not support the TCP-based SQL Server protocol (TDS) and lacks a compatible mssql driver, you need an HTTP REST layer between your Edge Functions and SQL Server. This is the most technically demanding step of the integration, but it is a one-time setup that then serves all your Lovable Edge Functions. The simplest option for Azure SQL Database users is to deploy an Azure Function with an HTTP trigger and SQL input binding. The Azure Function connects to SQL Server using its native connection string, runs a parameterized query from the HTTP request body, and returns JSON. You then expose this Azure Function's URL with a function key as the API endpoint your Lovable Edge Functions call. For on-premises SQL Server or more complex scenarios, deploy a Node.js Express API to Azure App Service (or any cloud platform). Install the `mssql` package, configure a connection pool with your SQL Server connection string, and expose POST endpoints that accept query parameters and return JSON results. Protect the API with an API key header that you generate and store. The REST API should expose at minimum two endpoints: GET /query for parameterized SELECT queries and POST /execute for parameterized INSERT, UPDATE, and DELETE operations. For a reporting-only dashboard, GET /query alone is sufficient. Restrict the API to only accept queries against specific pre-approved tables or named stored procedures — do not build a generic SQL execution endpoint, as that creates a SQL injection risk if the Edge Function ever passes untrusted user input. Once deployed, note your API's base URL and API key. You will store these in Lovable Cloud → Secrets in the next step.

1// Example: Minimal Express REST proxy for SQL Server
2// Deploy this on Azure App Service or any Node.js host
3// This code runs on YOUR server, not in Lovable
4const express = require('express');
5const sql = require('mssql');
6
7const app = express();
8app.use(express.json());
9
10const pool = new sql.ConnectionPool(process.env.SQL_CONNECTION_STRING);
11const poolConnect = pool.connect();
12
13// Simple API key auth middleware
14app.use((req, res, next) => {
15  if (req.headers['x-api-key'] !== process.env.API_KEY) {
16    return res.status(401).json({ error: 'Unauthorized' });
17  }
18  next();
19});
20
21// Approved queries map - never allow arbitrary SQL from clients
22const APPROVED_QUERIES = {
23  get_customers: 'SELECT TOP 100 id, name, email, company FROM Customers ORDER BY created_at DESC',
24  get_orders: 'SELECT o.id, o.total, o.status, c.name as customer FROM Orders o JOIN Customers c ON o.customer_id = c.id WHERE o.created_at > @since',
25  get_inventory: 'SELECT sku, name, stock_level, warehouse FROM Products WHERE (@search IS NULL OR name LIKE @search)',
26};
27
28app.post('/query', async (req, res) => {
29  const { queryName, params = {} } = req.body;
30  const queryTemplate = APPROVED_QUERIES[queryName];
31  if (!queryTemplate) return res.status(400).json({ error: 'Unknown query' });
32
33  try {
34    await poolConnect;
35    const request = pool.request();
36    Object.entries(params).forEach(([key, value]) => request.input(key, value));
37    const result = await request.query(queryTemplate);
38    res.json({ rows: result.recordset });
39  } catch (err) {
40    res.status(500).json({ error: err.message });
41  }
42});
43
44app.listen(3000);

With your REST proxy deployed and accessible, store its credentials in Lovable's Cloud Secrets panel. These credentials must never appear in frontend code — even though you are calling a REST proxy rather than SQL Server directly, the API key grants database query access and must be treated with the same security care as a direct database password. Lovable's security infrastructure blocks approximately 1,200 hardcoded API keys per day, and Lovable holds SOC 2 Type II certification. The Secrets panel is the correct place for all credentials — values stored here are encrypted at rest and only accessible from server-side Edge Functions via Deno.env.get(). To access the Secrets panel, click the '+' icon at the top of the Lovable editor next to the Preview label. Click the 'Cloud' tab, then the 'Secrets' tab. Add the following secrets: - Name: SQLSERVER_API_URL — Value: your REST proxy base URL (e.g., https://your-api.azurewebsites.net) - Name: SQLSERVER_API_KEY — Value: the API key or bearer token for your REST proxy You do not need to store the SQL Server connection string itself in Lovable Secrets — that lives in your REST proxy's environment configuration, never in Lovable. The only credentials Lovable needs are those for calling the REST proxy. If your organization uses Azure API Management or another API gateway, you may also need to store an additional subscription key. Store it as SQLSERVER_SUBSCRIPTION_KEY and reference it separately in your Edge Function headers.

With credentials stored, create the Lovable Edge Function that accepts requests from the frontend and forwards them to your SQL Server REST proxy. This Edge Function is the sole bridge between your Lovable React app and the SQL Server data — keeping all authentication logic server-side. The Edge Function should validate the request (checking for required parameters, restricting which query names are allowed), add the API key to the outbound request headers, call your REST proxy, and return the results. Adding a second validation layer in the Edge Function — even though the proxy already has one — provides defense in depth: a bug in the proxy configuration cannot be exploited by manipulating Edge Function requests. For read-only dashboards, a single 'sqlserver-read' Edge Function is sufficient. For applications that also write data back to SQL Server, create a separate 'sqlserver-write' Edge Function with Supabase JWT verification, ensuring only authenticated users can trigger write operations. In Lovable's chat, paste the prompt below to generate the Edge Function. Lovable will deploy it to your Supabase project's Edge Functions automatically.

1// supabase/functions/sqlserver-read/index.ts
2const corsHeaders = {
3  'Access-Control-Allow-Origin': '*',
4  'Access-Control-Allow-Headers': 'authorization, x-client-info, apikey, content-type',
5};
6
7// Allowlist of query names your frontend can invoke
8const ALLOWED_QUERIES = new Set([
9  'get_customers',
10  'get_orders',
11  'get_inventory',
12  'get_revenue_report',
13]);
14
15Deno.serve(async (req) => {
16  if (req.method === 'OPTIONS') {
17    return new Response('ok', { headers: corsHeaders });
18  }
19
20  try {
21    const { queryName, params = {} } = await req.json();
22
23    if (!ALLOWED_QUERIES.has(queryName)) {
24      return new Response(JSON.stringify({ error: `Query '${queryName}' is not allowed` }), {
25        status: 400,
26        headers: { ...corsHeaders, 'Content-Type': 'application/json' },
27      });
28    }
29
30    const apiUrl = Deno.env.get('SQLSERVER_API_URL')!;
31    const apiKey = Deno.env.get('SQLSERVER_API_KEY')!;
32
33    const response = await fetch(`${apiUrl}/query`, {
34      method: 'POST',
35      headers: {
36        'Content-Type': 'application/json',
37        'x-api-key': apiKey,
38      },
39      body: JSON.stringify({ queryName, params }),
40    });
41
42    if (!response.ok) {
43      const errorText = await response.text();
44      console.error(`SQL proxy error (${response.status}):`, errorText);
45      return new Response(JSON.stringify({ error: 'Query failed', status: response.status }), {
46        status: response.status,
47        headers: { ...corsHeaders, 'Content-Type': 'application/json' },
48      });
49    }
50
51    const data = await response.json();
52    return new Response(JSON.stringify(data), {
53      headers: { ...corsHeaders, 'Content-Type': 'application/json' },
54    });
55  } catch (error) {
56    console.error('Edge Function error:', error);
57    return new Response(JSON.stringify({ error: 'Internal server error' }), {
58      status: 500,
59      headers: { ...corsHeaders, 'Content-Type': 'application/json' },
60    });
61  }
62});

If your Lovable app needs to write data back to SQL Server — submitting forms, updating records, creating new entries — you need a write Edge Function that verifies the user is authenticated before executing any database mutation. This prevents anonymous users from modifying your SQL Server data even if they discover the Edge Function URL. The write function uses Supabase JWT verification: it reads the Authorization header from the incoming request, validates the JWT using the Supabase client, and only proceeds if the user is authenticated. This works because Lovable's frontend automatically attaches the user's Supabase session token to Edge Function calls made via supabase.functions.invoke(). For enterprise applications where you need more granular authorization — for example, only users with the 'admin' role should be able to write to the Customers table — you can add a role check after verifying the JWT. Store user roles in your Supabase database and check them in the Edge Function before forwarding the write request to the SQL proxy. For complex role-based access patterns connecting Supabase Auth roles to SQL Server write permissions, RapidDev's team can help design the authorization architecture across both databases.

1// supabase/functions/sqlserver-write/index.ts
2import { createClient } from 'https://esm.sh/@supabase/supabase-js@2';
3
4const corsHeaders = {
5  'Access-Control-Allow-Origin': '*',
6  'Access-Control-Allow-Headers': 'authorization, x-client-info, apikey, content-type',
7};
8
9const ALLOWED_WRITE_OPS = new Set(['insert_customer', 'update_order_status', 'insert_support_ticket']);
10
11Deno.serve(async (req) => {
12  if (req.method === 'OPTIONS') return new Response('ok', { headers: corsHeaders });
13
14  const authHeader = req.headers.get('Authorization');
15  if (!authHeader) {
16    return new Response(JSON.stringify({ error: 'Authentication required' }), {
17      status: 401,
18      headers: { ...corsHeaders, 'Content-Type': 'application/json' },
19    });
20  }
21
22  const supabase = createClient(
23    Deno.env.get('SUPABASE_URL')!,
24    Deno.env.get('SUPABASE_ANON_KEY')!,
25    { global: { headers: { Authorization: authHeader } } }
26  );
27
28  const { data: { user }, error: authError } = await supabase.auth.getUser();
29  if (authError || !user) {
30    return new Response(JSON.stringify({ error: 'Invalid session' }), {
31      status: 401,
32      headers: { ...corsHeaders, 'Content-Type': 'application/json' },
33    });
34  }
35
36  try {
37    const { operation, data } = await req.json();
38    if (!ALLOWED_WRITE_OPS.has(operation)) {
39      return new Response(JSON.stringify({ error: `Operation '${operation}' not allowed` }), {
40        status: 400,
41        headers: { ...corsHeaders, 'Content-Type': 'application/json' },
42      });
43    }
44
45    const apiUrl = Deno.env.get('SQLSERVER_API_URL')!;
46    const apiKey = Deno.env.get('SQLSERVER_API_KEY')!;
47
48    const response = await fetch(`${apiUrl}/execute`, {
49      method: 'POST',
50      headers: { 'Content-Type': 'application/json', 'x-api-key': apiKey },
51      body: JSON.stringify({ operation, data, userId: user.id }),
52    });
53
54    const result = await response.json();
55    return new Response(JSON.stringify(result), {
56      status: response.ok ? 200 : response.status,
57      headers: { ...corsHeaders, 'Content-Type': 'application/json' },
58    });
59  } catch (error) {
60    return new Response(JSON.stringify({ error: String(error) }), {
61      status: 500,
62      headers: { ...corsHeaders, 'Content-Type': 'application/json' },
63    });
64  }
65});

With both Edge Functions deployed, you can build Lovable frontend components that display and interact with your SQL Server data. Open the Lovable chat and describe the data display you need. Lovable will generate React components that call your Edge Functions, handle loading states, and render the returned data. The most effective approach is to describe exactly what data you need displayed and what the page should look like. Lovable's AI understands the Edge Function pattern and will generate the correct supabase.functions.invoke() calls with appropriate error handling. For a customer list dashboard, you might prompt: 'Build a customer list page that calls the sqlserver-read Edge Function with queryName get_customers. Display results in a table with columns for name, email, company, and last order date. Add a search field that filters by name or email. Show a loading skeleton while data is loading and a friendly error message if the query fails.' For write operations like a customer intake form, describe the form fields and the write operation: 'Create a new customer form with name, email, company, and phone fields. On submit, call the sqlserver-write Edge Function with operation insert_customer and the form data. Show a success message with the new customer ID returned from SQL Server, or an inline error if validation fails.' After Lovable generates the components, test them in the preview panel. SQL Server read queries work in the preview because they go through Edge Functions that can reach your REST proxy. If you see errors, check Cloud → Logs for the Edge Function execution output.