How to Integrate Bolt.new with Microsoft SQL Server

1// lib/sqlserver.ts
2// ⚠️ IMPORTANT: mssql uses TCP (TDS protocol) and CANNOT connect from Bolt's
3// WebContainer preview environment. TCP connections are not supported in
4// browser-based runtimes. This file only works after deploying to Netlify,
5// Bolt Cloud, or another serverless hosting environment.
6//
7// For development testing, either:
8// 1. Deploy first, then test API routes at your deployed URL
9// 2. Use Azure SQL Database REST API (HTTPS-based, works in WebContainer)
10// 3. Use Supabase (PostgreSQL) for new projects — native Bolt support, no TCP
11
12import sql from 'mssql';
13
14const config: sql.config = {
15  server: process.env.MSSQL_SERVER || '',
16  database: process.env.MSSQL_DATABASE || '',
17  user: process.env.MSSQL_USER || '',
18  password: process.env.MSSQL_PASSWORD || '',
19  port: parseInt(process.env.MSSQL_PORT || '1433', 10),
20  options: {
21    encrypt: true, // Required for Azure SQL, recommended for all
22    trustServerCertificate: process.env.NODE_ENV === 'development', // Only for dev with self-signed certs
23    enableArithAbort: true,
24  },
25  pool: {
26    max: 10,
27    min: 0,
28    idleTimeoutMilliseconds: 30000,
29  },
30  connectionTimeout: 15000,
31  requestTimeout: 30000,
32};
33
34let pool: sql.ConnectionPool | null = null;
35
36export async function getPool(): Promise<sql.ConnectionPool> {
37  if (pool && pool.connected) return pool;
38
39  if (!config.server || !config.database || !config.user) {
40    throw new Error(
41      'SQL Server is not configured. Set MSSQL_SERVER, MSSQL_DATABASE, MSSQL_USER, and MSSQL_PASSWORD in .env. ' +
42      'Note: mssql requires a deployed environment — it cannot connect from Bolt\'s WebContainer preview.'
43    );
44  }
45
46  pool = await sql.connect(config);
47  return pool;
48}
49
50export async function sqlQuery<T = sql.IRecordSet<Record<string, unknown>>>(
51  queryString: string,
52  params?: Record<string, unknown>
53): Promise<T> {
54  const connection = await getPool();
55  const request = connection.request();
56
57  if (params) {
58    Object.entries(params).forEach(([key, value]) => {
59      request.input(key, value);
60    });
61  }
62
63  const result = await request.query(queryString);
64  return result.recordset as T;
65}

1// app/api/sqlserver/products/route.ts
2// NOTE: This route requires a deployed environment to function.
3// The mssql driver uses TCP protocol which is not supported in Bolt's WebContainer preview.
4// Deploy to Netlify or Bolt Cloud, add environment variables, then test this route.
5import { NextResponse } from 'next/server';
6import { sqlQuery } from '@/lib/sqlserver';
7
8interface Product {
9  id: number;
10  name: string;
11  category: string;
12  price: number;
13  stock: number;
14  description: string;
15  createdAt: Date;
16}
17
18export async function GET(request: Request) {
19  const { searchParams } = new URL(request.url);
20  const search = searchParams.get('search') || '';
21  const category = searchParams.get('category') || '';
22  const page = parseInt(searchParams.get('page') || '1', 10);
23  const pageSize = parseInt(searchParams.get('pageSize') || '20', 10);
24  const offset = (page - 1) * pageSize;
25
26  try {
27    // Build WHERE clause based on provided filters
28    const whereClauses: string[] = ['1=1'];
29    const params: Record<string, unknown> = { offset, pageSize };
30
31    if (search) {
32      whereClauses.push('(p.name LIKE @search OR p.description LIKE @search)');
33      params.search = `%${search}%`;
34    }
35
36    if (category) {
37      whereClauses.push('p.category = @category');
38      params.category = category;
39    }
40
41    const whereClause = whereClauses.join(' AND ');
42
43    // Use parameterized queries — never interpolate user input
44    const products = await sqlQuery<Product[]>(
45      `SELECT
46        p.id, p.name, p.category, p.price, p.stock, p.description, p.created_at AS createdAt
47       FROM Products p
48       WHERE ${whereClause}
49       ORDER BY p.name
50       OFFSET @offset ROWS FETCH NEXT @pageSize ROWS ONLY`,
51      params
52    );
53
54    // Get total count for pagination
55    const countResult = await sqlQuery<Array<{ total: number }>>(
56      `SELECT COUNT(*) AS total FROM Products p WHERE ${whereClause}`,
57      search ? { search: `%${search}%` } : category ? { category } : {}
58    );
59
60    return NextResponse.json({
61      products,
62      total: countResult[0]?.total || 0,
63      page,
64      pageSize,
65    });
66  } catch (err) {
67    const message = err instanceof Error ? err.message : 'Database query failed';
68    const isTcpError = message.includes('Failed to connect') || message.includes('ECONNREFUSED');
69    return NextResponse.json(
70      {
71        error: message,
72        hint: isTcpError
73          ? 'mssql requires TCP which is not supported in Bolt WebContainer. Deploy to Netlify or Bolt Cloud to test SQL Server connections.'
74          : undefined,
75      },
76      { status: 500 }
77    );
78  }
79}

1# .env — SQL Server connection configuration
2# WARNING: Never commit this file to version control
3# Add these values to Netlify or Bolt Cloud environment variables after deploying
4
5# Azure SQL Database example:
6MSSQL_SERVER=your-server.database.windows.net
7MSSQL_DATABASE=your-database-name
8MSSQL_USER=your-sql-login
9MSSQL_PASSWORD=your-secure-password
10MSSQL_PORT=1433
11
12# On-premises / VM SQL Server example:
13# MSSQL_SERVER=192.168.1.100
14# MSSQL_DATABASE=ProductionDB
15# MSSQL_USER=appuser
16# MSSQL_PASSWORD=SecurePassword123!
17# MSSQL_PORT=1433
18
19# Azure SQL with named instance (use port instead of instance name):
20# MSSQL_SERVER=your-vm.eastus.cloudapp.azure.com
21# MSSQL_PORT=1434  # Named instances often use a different port

1// app/api/azure-sql/token/route.ts
2// This approach uses Azure AD OAuth2 tokens with Azure REST API
3// It works in Bolt's WebContainer (HTTPS-based, no TCP required)
4// Trade-off: more complex auth setup, but testable in the preview
5import { NextResponse } from 'next/server';
6
7let tokenCache: { token: string; expiresAt: number } | null = null;
8
9export async function getAzureSqlToken(): Promise<string> {
10  if (tokenCache && Date.now() < tokenCache.expiresAt - 60_000) {
11    return tokenCache.token;
12  }
13
14  const tenantId = process.env.AZURE_TENANT_ID;
15  const clientId = process.env.AZURE_CLIENT_ID;
16  const clientSecret = process.env.AZURE_CLIENT_SECRET;
17
18  if (!tenantId || !clientId || !clientSecret) {
19    throw new Error('Azure AD credentials not configured: AZURE_TENANT_ID, AZURE_CLIENT_ID, AZURE_CLIENT_SECRET required');
20  }
21
22  const response = await fetch(
23    `https://login.microsoftonline.com/${tenantId}/oauth2/v2.0/token`,
24    {
25      method: 'POST',
26      headers: { 'Content-Type': 'application/x-www-form-urlencoded' },
27      body: new URLSearchParams({
28        grant_type: 'client_credentials',
29        client_id: clientId,
30        client_secret: clientSecret,
31        scope: 'https://database.windows.net/.default',
32      }),
33    }
34  );
35
36  const data = await response.json() as { access_token?: string; error?: string; expires_in?: number };
37
38  if (!data.access_token) {
39    throw new Error(`Azure AD token failed: ${data.error || 'unknown error'}`);
40  }
41
42  tokenCache = {
43    token: data.access_token,
44    expiresAt: Date.now() + (data.expires_in || 3600) * 1000,
45  };
46
47  return data.access_token;
48}
49
50export async function GET() {
51  try {
52    const token = await getAzureSqlToken();
53    return NextResponse.json({ status: 'ok', tokenPreview: token.substring(0, 20) + '...' });
54  } catch (err) {
55    const message = err instanceof Error ? err.message : 'Token acquisition failed';
56    return NextResponse.json({ error: message }, { status: 500 });
57  }
58}