How to Integrate Lovable with MySQL

Before writing any Edge Function code, you need to choose the right connection method for your MySQL host. The choice matters because Deno's runtime environment handles networking differently from Node.js. Option A — PlanetScale serverless driver: If your MySQL database is hosted on PlanetScale, you can use the @planetscale/database package, which is specifically designed for HTTP-based environments like Deno and Cloudflare Workers. It communicates with PlanetScale's HTTP API rather than over TCP, making it the cleanest integration for Deno Edge Functions. To get started, log in to app.planetscale.com, select your database, go to Settings → Passwords, and create a new password. Note your host, username, and password. Option B — REST proxy for other MySQL hosts: If your MySQL is on AWS RDS, Google Cloud SQL, a self-hosted server, or any other provider that requires TCP connections, you need a REST proxy in front of MySQL. This is a small Node.js Express application that accepts HTTP requests and translates them to MySQL queries using the mysql2 package. Deploy this proxy on any Node.js hosting (Railway, Render, Fly.io, or Azure App Service) and note its public URL and API key. Option C — PlanetScale import: If your current MySQL is on another host, consider importing it to PlanetScale. PlanetScale offers a free import wizard that copies your existing MySQL data and sets up ongoing replication. This is the path of least resistance for getting a Deno-compatible MySQL connection. This guide focuses on Option A (PlanetScale) for the code examples, with Option B noted where the approach differs. Choose based on where your data currently lives.

Store your MySQL access credentials in Lovable's Cloud Secrets panel. Whether you are using PlanetScale directly or a REST proxy, the credentials must never appear in your frontend code, Git repository, or Lovable chat history. Lovable's security infrastructure blocks approximately 1,200 hardcoded API keys per day and holds SOC 2 Type II certification — but the safest practice is always using Secrets from the start. To access the Secrets panel, click the '+' icon at the top of the Lovable editor next to the Preview label. In the Cloud panel, click the 'Secrets' tab. Click 'Add new secret' and add your credentials. For PlanetScale (Option A), add: - Name: MYSQL_HOST — Value: your PlanetScale database host (e.g., aws.connect.psdb.cloud) - Name: MYSQL_USERNAME — Value: your service token username or database username - Name: MYSQL_PASSWORD — Value: your service token or database password - Name: MYSQL_DATABASE — Value: your database name For a REST proxy (Option B), add: - Name: MYSQL_API_URL — Value: your proxy base URL (e.g., https://mysql-proxy.yourapp.railway.app) - Name: MYSQL_API_KEY — Value: your proxy API key After adding each secret, it appears in the list with its value masked. Do not add your full MySQL connection string as a single secret — splitting it into host, username, and password gives you more flexibility to rotate individual credentials without updating every secret.

For PlanetScale-hosted MySQL, the Edge Function uses the @planetscale/database package imported directly from esm.sh. This package handles all HTTP communication with PlanetScale's serverless API, executes parameterized queries, and returns results in a familiar format. The import syntax works in Deno without any package.json configuration — Deno's module resolution fetches the package directly from the CDN URL at runtime. The Edge Function below implements a flexible query handler that accepts a named SQL query and parameter values. Like the SQL Server and MongoDB patterns, it uses an allowlist of named queries rather than accepting raw SQL strings from the frontend — this is a critical security pattern that prevents SQL injection even if a malicious actor crafts a specially formed frontend request. For non-PlanetScale MySQL (Option B), the Edge Function body is nearly identical to the SQL Server proxy pattern — replace the PlanetScale client code with a fetch call to your REST proxy using MYSQL_API_URL and MYSQL_API_KEY. Paste the prompt below into Lovable's chat to create the Edge Function, or create the file manually in the Code panel under supabase/functions/.

1// supabase/functions/mysql-read/index.ts
2import { connect } from 'https://esm.sh/@planetscale/database@1';
3
4const corsHeaders = {
5  'Access-Control-Allow-Origin': '*',
6  'Access-Control-Allow-Headers': 'authorization, x-client-info, apikey, content-type',
7};
8
9// Allowlist of named queries — never accept raw SQL from the frontend
10const QUERIES: Record<string, { sql: string; paramKeys: string[] }> = {
11  get_customers: {
12    sql: 'SELECT id, name, email, company, created_at FROM customers ORDER BY created_at DESC LIMIT 50',
13    paramKeys: [],
14  },
15  get_customer_by_id: {
16    sql: 'SELECT * FROM customers WHERE id = ?',
17    paramKeys: ['id'],
18  },
19  get_orders: {
20    sql: 'SELECT o.id, o.total, o.status, o.created_at, c.name AS customer_name FROM orders o JOIN customers c ON o.customer_id = c.id WHERE o.status = ? ORDER BY o.created_at DESC LIMIT 100',
21    paramKeys: ['status'],
22  },
23  search_products: {
24    sql: 'SELECT id, sku, name, price, stock FROM products WHERE name LIKE ? LIMIT 20',
25    paramKeys: ['search'],
26  },
27};
28
29Deno.serve(async (req) => {
30  if (req.method === 'OPTIONS') {
31    return new Response('ok', { headers: corsHeaders });
32  }
33
34  try {
35    const { queryName, params = {} } = await req.json();
36    const queryDef = QUERIES[queryName];
37
38    if (!queryDef) {
39      return new Response(JSON.stringify({ error: `Unknown query: ${queryName}` }), {
40        status: 400,
41        headers: { ...corsHeaders, 'Content-Type': 'application/json' },
42      });
43    }
44
45    const conn = connect({
46      host: Deno.env.get('MYSQL_HOST')!,
47      username: Deno.env.get('MYSQL_USERNAME')!,
48      password: Deno.env.get('MYSQL_PASSWORD')!,
49    });
50
51    const database = Deno.env.get('MYSQL_DATABASE')!;
52    const paramValues = queryDef.paramKeys.map((key) => params[key] ?? null);
53
54    // Add wildcard for LIKE queries
55    const processedParams = paramValues.map((v, i) => {
56      const sql = queryDef.sql;
57      return sql.includes('LIKE') && queryDef.paramKeys[i] === 'search' ? `%${v}%` : v;
58    });
59
60    const results = await conn.execute(
61      `USE ${database}; ${queryDef.sql}`,
62      processedParams
63    );
64
65    return new Response(JSON.stringify({ rows: results.rows }), {
66      headers: { ...corsHeaders, 'Content-Type': 'application/json' },
67    });
68  } catch (error) {
69    console.error('MySQL query error:', error);
70    return new Response(JSON.stringify({ error: 'Query failed', details: String(error) }), {
71      status: 500,
72      headers: { ...corsHeaders, 'Content-Type': 'application/json' },
73    });
74  }
75});

For operations that modify MySQL data — INSERT, UPDATE, DELETE — create a separate Edge Function that verifies the user's Supabase JWT before executing any write. This ensures only authenticated users from your Lovable app can mutate your MySQL database, even if someone discovers the Edge Function URL. The write function mirrors the read function's structure but adds auth verification first. It uses Supabase's client library to validate the JWT from the Authorization header and then calls the PlanetScale connection for the write operation. For the write Edge Function, define your write operations in a similar allowlist pattern. Map named operation identifiers to parameterized SQL statements. Never accept raw INSERT or UPDATE SQL from the frontend. If the user's role needs to be checked (for example, only admins can delete records), add a role check after the JWT verification by querying your Supabase database for the user's role. For complex multi-table write operations that need transaction support, consider using MySQL stored procedures callable by name — the REST proxy pattern (Option B) is better suited for transaction-heavy writes than the PlanetScale serverless driver, which does not support interactive transactions in its HTTP API.

1// supabase/functions/mysql-write/index.ts
2import { connect } from 'https://esm.sh/@planetscale/database@1';
3import { createClient } from 'https://esm.sh/@supabase/supabase-js@2';
4
5const corsHeaders = {
6  'Access-Control-Allow-Origin': '*',
7  'Access-Control-Allow-Headers': 'authorization, x-client-info, apikey, content-type',
8};
9
10const WRITE_OPERATIONS: Record<string, { sql: string; paramKeys: string[] }> = {
11  insert_customer: {
12    sql: 'INSERT INTO customers (name, email, company, created_at) VALUES (?, ?, ?, NOW())',
13    paramKeys: ['name', 'email', 'company'],
14  },
15  update_order_status: {
16    sql: 'UPDATE orders SET status = ?, updated_at = NOW() WHERE id = ?',
17    paramKeys: ['status', 'id'],
18  },
19};
20
21Deno.serve(async (req) => {
22  if (req.method === 'OPTIONS') return new Response('ok', { headers: corsHeaders });
23
24  const authHeader = req.headers.get('Authorization');
25  if (!authHeader) {
26    return new Response(JSON.stringify({ error: 'Authentication required' }), {
27      status: 401,
28      headers: { ...corsHeaders, 'Content-Type': 'application/json' },
29    });
30  }
31
32  const supabase = createClient(
33    Deno.env.get('SUPABASE_URL')!,
34    Deno.env.get('SUPABASE_ANON_KEY')!,
35    { global: { headers: { Authorization: authHeader } } }
36  );
37
38  const { data: { user }, error: authError } = await supabase.auth.getUser();
39  if (authError || !user) {
40    return new Response(JSON.stringify({ error: 'Unauthorized' }), {
41      status: 401,
42      headers: { ...corsHeaders, 'Content-Type': 'application/json' },
43    });
44  }
45
46  try {
47    const { operation, data = {} } = await req.json();
48    const opDef = WRITE_OPERATIONS[operation];
49    if (!opDef) {
50      return new Response(JSON.stringify({ error: `Unknown operation: ${operation}` }), {
51        status: 400,
52        headers: { ...corsHeaders, 'Content-Type': 'application/json' },
53      });
54    }
55
56    const conn = connect({
57      host: Deno.env.get('MYSQL_HOST')!,
58      username: Deno.env.get('MYSQL_USERNAME')!,
59      password: Deno.env.get('MYSQL_PASSWORD')!,
60    });
61
62    const database = Deno.env.get('MYSQL_DATABASE')!;
63    const paramValues = opDef.paramKeys.map((k) => data[k] ?? null);
64
65    const result = await conn.execute(
66      `USE ${database}; ${opDef.sql}`,
67      paramValues
68    );
69
70    return new Response(JSON.stringify({ affectedRows: result.rowsAffected, insertId: result.insertId }), {
71      headers: { ...corsHeaders, 'Content-Type': 'application/json' },
72    });
73  } catch (error) {
74    console.error('MySQL write error:', error);
75    return new Response(JSON.stringify({ error: String(error) }), {
76      status: 500,
77      headers: { ...corsHeaders, 'Content-Type': 'application/json' },
78    });
79  }
80});

With the Edge Functions deployed, build your Lovable frontend components that display and interact with MySQL data. Open the Lovable chat and describe the data you need displayed — Lovable will generate React components that call your Edge Functions using supabase.functions.invoke(). For the migration path consideration: if you plan to eventually move from MySQL to Supabase PostgreSQL — which is recommended for maximizing Lovable's AI-assisted development capabilities — design your Edge Functions as a compatibility layer rather than a permanent architecture. Use the same data shape and naming conventions in your MySQL Edge Functions that you intend to use in Supabase later. When you migrate a table to Supabase, you replace the Edge Function calls with direct Supabase queries in your components — the rest of the UI remains unchanged. To track which tables are still on MySQL versus Supabase, ask Lovable to create a simple admin indicator: 'Add a small database badge to the customer list page showing whether the data is coming from MySQL or Supabase so I can track my migration progress.' This is a simple state variable in the component but keeps you aware of the architecture during the transition period. For complex MySQL-to-Supabase migration strategies including data mapping, RLS policy generation for migrated tables, and zero-downtime cutover planning, RapidDev's team specializes in this transition architecture and can help structure the migration in phases.