Microsoft SQL Server

Before building anything new, investigate whether your organization already has a REST layer in front of the SQL Server database. Ask the development team or check these common locations: 1. **Swagger/OpenAPI documentation**: Many ASP.NET Web API services auto-generate Swagger docs at `/swagger` or `/api/docs`. If this exists, you already have a REST API — note the base URL and auth mechanism. 2. **Azure API Management**: If your organization uses Azure, check the Azure Portal for an API Management instance that may already proxy the SQL Server application. 3. **OData endpoints**: .NET Entity Framework applications frequently expose OData v4 endpoints at `/odata/{Entity}`. These work with Bubble's API Connector — use `$filter`, `$select`, and `$top` query parameters for filtering. 4. **WCF or SOAP services**: If the only existing API is a WCF service or SOAP endpoint, Bubble's API Connector can call SOAP endpoints but the setup is complex. Consider wrapping the SOAP service in a thin Azure Function that converts it to REST/JSON. If an existing REST API exists: skip to Step 4 and configure Bubble's API Connector to point at that endpoint with the existing auth mechanism. If no REST API exists: continue with Steps 2 and 3 to deploy an Azure Function.

If you need to build a new REST layer, Azure Functions is the recommended platform because it integrates natively with Azure SQL, supports Managed Identity for passwordless auth, and scales automatically. In the Azure Portal, go to Create a resource → Function App. Configure: - **Runtime**: Node.js 20 LTS - **Plan**: Consumption (serverless, pay per execution — free tier covers 1M invocations/month) - **Region**: same region as your SQL Server instance (reduces latency) - **Operating System**: Linux After deployment, go to the Function App → Configuration → Application settings. Add these settings: - `SQL_CONNECTION_STRING`: your SQL Server connection string — for SQL Server Auth: `Server=your-server.database.windows.net;Database=your-db;User Id=your-user;Password=your-password;Encrypt=true;` - `BUBBLE_API_KEY`: a randomly generated API key string (you'll use this in Bubble's Private header to authenticate requests to the Function) For Azure SQL with Managed Identity (the more secure option for Azure-hosted databases): - Enable System-assigned Managed Identity on the Function App (Identity tab → System assigned → On) - In Azure SQL, run: `CREATE USER [your-function-app-name] FROM EXTERNAL PROVIDER; ALTER ROLE db_datareader ADD MEMBER [your-function-app-name];` - Update the connection string to: `Server=your-server.database.windows.net;Database=your-db;Authentication=Active Directory Managed Identity;Encrypt=true;` Managed Identity eliminates the SQL Server password from your configuration entirely — the Function authenticates to SQL Server using its Azure identity.

1{
2  "SQL_CONNECTION_STRING": "Server=your-server.database.windows.net;Database=your-db;User Id=your-user;Password=your-password;Encrypt=true;TrustServerCertificate=False;",
3  "BUBBLE_API_KEY": "your-random-api-key-string"
4}

In the Azure Function App, create HTTP-triggered functions for each data operation your Bubble app needs. The key design principle: each function endpoint should correspond to a specific business operation, not generic SQL pass-through. Call stored procedures where they exist. Here's a sample Function that gets orders by status and creates new orders — using the `mssql` library with parameterized queries: ```javascript import sql from 'mssql'; const API_KEY = process.env.BUBBLE_API_KEY; const config = { connectionString: process.env.SQL_CONNECTION_STRING }; let pool; async function getPool() { if (!pool) pool = await sql.connect(config); return pool; } export default async function handler(req, context) { // Authenticate Bubble requests if (req.headers.get('x-api-key') !== API_KEY) { return new Response(JSON.stringify({ error: 'Unauthorized' }), { status: 401 }); } const db = await getPool(); const url = new URL(req.url); const path = url.pathname; if (req.method === 'GET' && path.endsWith('/orders')) { const status = url.searchParams.get('status'); const request = db.request(); let query = 'SELECT OrderId, CustomerId, Status, TotalAmount, CreatedAt FROM Orders'; if (status) { request.input('status', sql.NVarChar, status); query += ' WHERE Status = @status'; } query += ' ORDER BY CreatedAt DESC'; const result = await request.query(query); return new Response(JSON.stringify({ rows: result.recordset }), { headers: { 'Content-Type': 'application/json' } }); } if (req.method === 'POST' && path.endsWith('/orders')) { const body = await req.json(); const request = db.request(); // Use a stored procedure for the insert request.input('CustomerId', sql.Int, body.customerId); request.input('ProductId', sql.Int, body.productId); request.input('Quantity', sql.Int, body.quantity); const result = await request.execute('sp_CreateOrder'); return new Response(JSON.stringify({ orderId: result.recordset[0]?.OrderId }), { headers: { 'Content-Type': 'application/json' } }); } return new Response(JSON.stringify({ error: 'Not found' }), { status: 404 }); } ``` Deploy the function. Test it from the Azure Portal's Function editor using the Test/Run panel, or with a REST client like Postman. Confirm it returns valid JSON before connecting Bubble.

1import sql from 'mssql';
2
3const API_KEY = process.env.BUBBLE_API_KEY;
4const config = { connectionString: process.env.SQL_CONNECTION_STRING };
5
6let pool;
7async function getPool() {
8  if (!pool) pool = await sql.connect(config);
9  return pool;
10}
11
12export default async function handler(req, context) {
13  if (req.headers.get('x-api-key') !== API_KEY) {
14    return new Response(JSON.stringify({ error: 'Unauthorized' }), { status: 401 });
15  }
16
17  const db = await getPool();
18  const url = new URL(req.url);
19  const path = url.pathname;
20
21  if (req.method === 'GET' && path.endsWith('/orders')) {
22    const status = url.searchParams.get('status');
23    const request = db.request();
24    let query = 'SELECT OrderId, CustomerId, Status, TotalAmount, CreatedAt FROM Orders';
25    if (status) {
26      request.input('status', sql.NVarChar, status);
27      query += ' WHERE Status = @status';
28    }
29    query += ' ORDER BY CreatedAt DESC';
30    const result = await request.query(query);
31    return new Response(JSON.stringify({ rows: result.recordset }), {
32      headers: { 'Content-Type': 'application/json' }
33    });
34  }
35
36  if (req.method === 'POST' && path.endsWith('/orders')) {
37    const body = await req.json();
38    const request = db.request();
39    request.input('CustomerId', sql.Int, body.customerId);
40    request.input('ProductId', sql.Int, body.productId);
41    request.input('Quantity', sql.Int, body.quantity);
42    const result = await request.execute('sp_CreateOrder');
43    return new Response(JSON.stringify({ orderId: result.recordset[0]?.OrderId }), {
44      headers: { 'Content-Type': 'application/json' }
45    });
46  }
47
48  return new Response(JSON.stringify({ error: 'Not found' }), { status: 404 });
49}

In Bubble, go to Plugins tab → API Connector → 'Add another API'. Name it 'SQL Server API'. Set the base URL to your Azure Function URL: `https://your-function.azurewebsites.net/api` Add shared headers: - `x-api-key`: `{your-BUBBLE_API_KEY-value}` — check the **Private** checkbox - `Content-Type`: `application/json` Add your first call for reads: - **Name**: `Get Orders` - **Method**: GET - **URL**: `/orders` - **Parameters**: `status` (text, dynamic, optional) - **Use as**: Data Add a call for writes: - **Name**: `Create Order` - **Method**: POST - **URL**: `/orders` - **Body**: JSON `{"customerId": "{{customer_id}}", "productId": "{{product_id}}", "quantity": "{{quantity}}"}` - **Use as**: Action The full API Connector configuration: ```json { "base_url": "https://your-function.azurewebsites.net/api", "shared_headers": { "x-api-key": "<private>", "Content-Type": "application/json" }, "calls": [ { "name": "Get Orders", "method": "GET", "path": "/orders", "params": { "status": "<dynamic-optional>" }, "use_as": "Data" }, { "name": "Create Order", "method": "POST", "path": "/orders", "body": { "customerId": "<dynamic>", "productId": "<dynamic>", "quantity": "<dynamic>" }, "use_as": "Action" } ] } ``` For Initialize call on 'Get Orders': click Initialize call → Run. If your SQL Server Orders table has data, Bubble detects the field schema from the `rows` array. If Azure SQL Serverless is paused when you initialize, the first call may time out — go to the Azure Portal and trigger a query from the SQL Server Query editor to wake the database first. For Initialize call on 'Create Order': provide test values and run. The Function should return `{"orderId": 123}` (or whatever your stored procedure returns). Save.

1{
2  "base_url": "https://your-function.azurewebsites.net/api",
3  "shared_headers": {
4    "x-api-key": "<private>",
5    "Content-Type": "application/json"
6  },
7  "calls": [
8    {
9      "name": "Get Orders",
10      "method": "GET",
11      "path": "/orders",
12      "params": { "status": "<dynamic-optional>" },
13      "use_as": "Data"
14    }
15  ]
16}

With initialized API calls, wire them to your Bubble UI elements. **Reads in a Repeating Group**: Set the Repeating Group's Data source to 'Get Orders' (or your specific GET call). Add a filter Dropdown above the repeating group for status filtering — set the `status` parameter in the Data source to `Dropdown Filter's value`. Bubble re-fetches the API when the dropdown selection changes. Each row displays `current cell's Get Orders result's OrderId`, `current cell's Get Orders result's Status`, etc. **Writes via Backend Workflow**: For create, update, and delete operations, route them through a Backend Workflow (paid plan). In the Backend Workflows section, create 'Create SQL Order' with parameters `customer_id` (number), `product_id` (number), `quantity` (number). Add action: Plugins → API Connector → Create Order. Map parameters from the workflow parameters. Return the `orderId` from the action result. Trigger from client: button click → Schedule API Workflow → 'Create SQL Order' → pass `customer_id = Customer Dropdown's value's id`, `product_id = Product Dropdown's value's id`, `quantity = Quantity Input's value`. After a successful write, refresh the repeating group data source by triggering the Get Orders call again (add a 'Display data' action or reload the data source). **Handling write results**: In the Backend Workflow, after the Create Order action, access the result with `Step 1's orderId`. If your Bubble app needs to track this order ID for follow-up actions (e.g. redirect to an order detail page), store it in a Bubble data type or pass it back to the page through a custom state. RapidDev's team has built Bubble integrations with SQL Server for enterprise ERP systems and Azure SQL data warehouses — if your SQL Server environment has specific authentication requirements (Windows Auth, Azure AD), book a free scoping call at rapidevelopers.com/contact.

Azure SQL Serverless tier's auto-pause is the most common operational issue for Bubble + SQL Server integrations. When the database hasn't been queried for the auto-pause threshold (default 1 hour minimum), the first request triggers a cold start. In Bubble's API Connector call settings, increase the timeout for SQL Server calls. In the API Connector, click the gear icon on the 'Get Orders' call → increase the timeout to 60 seconds. This gives Azure SQL time to resume from a paused state before Bubble declares the call timed out. Add a retry mechanism in your Bubble workflows: after a SQL Server API call action, add a conditional branch: 'If Step 1 failed → wait 5 seconds → try API call again (Step 3)'. This handles temporary cold-start failures without showing a user-facing error. For the long-term fix, either disable Azure SQL auto-pause for production: - Azure Portal → your Azure SQL database → Configure → Min vCores (set to 0.5 or higher) → Auto-pause delay (set to -1 to disable) Or add a health check call in Bubble: a lightweight API Connector call (GET /health that returns `{"status": "ok"}` from the Azure Function with a simple `SELECT 1` SQL) that fires on page load before the user sees any data fetch. This proactively wakes Azure SQL before the user interacts. Add Bubble privacy rules on any data types that cache SQL Server data in Bubble's native database: Data tab → [Data type] → Privacy → add a rule restricting reads to the data owner. This adds a Bubble-side access control layer on top of the SQL Server-side restrictions enforced in the Azure Function.