How to Integrate Bolt.new with MySQL

1// lib/mysql.ts
2// ⚠️ MYSQL2 TCP LIMITATION IN BOLT WEBCONTAINER
3// mysql2 uses TCP protocol (port 3306) which is not supported in
4// Bolt's WebContainer browser-based runtime environment.
5//
6// DEVELOPMENT OPTIONS:
7// 1. PlanetScale: Use @planetscale/database (HTTP-based) — works in WebContainer
8//    Install: npm install @planetscale/database
9//    See lib/planetscale.ts for the HTTP-based alternative
10//
11// 2. Deploy First: Deploy to Netlify (30 seconds), add env vars,
12//    test API routes at deployed URL where TCP works normally
13//
14// 3. New Project: Use Supabase (PostgreSQL) — Bolt's native database,
15//    HTTP-based, zero configuration, works in WebContainer immediately
16
17import mysql from 'mysql2/promise';
18
19let pool: mysql.Pool | null = null;
20
21export function getPool(): mysql.Pool {
22  if (pool) return pool;
23
24  const host = process.env.MYSQL_HOST;
25  if (!host) {
26    throw new Error(
27      'MySQL is not configured. Set MYSQL_HOST, MYSQL_DATABASE, MYSQL_USER, MYSQL_PASSWORD in .env. ' +
28      'Note: mysql2 requires TCP — it cannot connect from Bolt\'s WebContainer. Deploy to Netlify first or use PlanetScale\'s HTTP client.'
29    );
30  }
31
32  pool = mysql.createPool({
33    host,
34    port: parseInt(process.env.MYSQL_PORT || '3306', 10),
35    database: process.env.MYSQL_DATABASE,
36    user: process.env.MYSQL_USER,
37    password: process.env.MYSQL_PASSWORD,
38    ssl: process.env.MYSQL_SSL !== 'false' ? { rejectUnauthorized: true } : undefined,
39    waitForConnections: true,
40    connectionLimit: 10,
41    queueLimit: 0,
42    enableKeepAlive: true,
43    keepAliveInitialDelay: 0,
44  });
45
46  return pool;
47}
48
49export async function mysqlQuery<T = mysql.RowDataPacket[]>(
50  sql: string,
51  params?: unknown[]
52): Promise<T> {
53  const connection = getPool();
54  const [rows] = await connection.execute<mysql.RowDataPacket[]>(sql, params);
55  return rows as T;
56}

1// lib/planetscale.ts
2// PlanetScale uses HTTP (not TCP) — works in Bolt's WebContainer during development
3import { Client } from '@planetscale/database';
4
5let client: Client | null = null;
6
7export function getPlanetScaleClient(): Client {
8  if (client) return client;
9
10  const host = process.env.PLANETSCALE_HOST;
11  const username = process.env.PLANETSCALE_USERNAME;
12  const password = process.env.PLANETSCALE_PASSWORD;
13
14  if (!host || !username || !password) {
15    throw new Error(
16      'PlanetScale credentials not configured. Set PLANETSCALE_HOST, PLANETSCALE_USERNAME, PLANETSCALE_PASSWORD in .env. ' +
17      'Create credentials at app.planetscale.com → Database → Passwords.'
18    );
19  }
20
21  client = new Client({ host, username, password });
22  return client;
23}
24
25export async function psQuery<T = Record<string, unknown>>(
26  sql: string,
27  params?: unknown[]
28): Promise<T[]> {
29  const db = getPlanetScaleClient();
30  const result = await db.execute(sql, params);
31  return result.rows as T[];
32}
33
34export async function psQueryOne<T = Record<string, unknown>>(
35  sql: string,
36  params?: unknown[]
37): Promise<T | null> {
38  const rows = await psQuery<T>(sql, params);
39  return rows[0] || null;
40}
41
42export async function psExecute(
43  sql: string,
44  params?: unknown[]
45): Promise<{ rowsAffected: number; insertId: string | null }> {
46  const db = getPlanetScaleClient();
47  const result = await db.execute(sql, params);
48  return {
49    rowsAffected: result.rowsAffected,
50    insertId: result.insertId || null,
51  };
52}

1// app/api/mysql/records/route.ts
2import { NextResponse } from 'next/server';
3
4// Use PlanetScale HTTP client if configured (works in WebContainer)
5// Fall back to mysql2 if MYSQL_HOST is configured (requires deployment)
6async function executeQuery<T>(sql: string, params: unknown[]): Promise<T[]> {
7  if (process.env.PLANETSCALE_HOST) {
8    const { psQuery } = await import('@/lib/planetscale');
9    return psQuery<T>(sql, params);
10  } else if (process.env.MYSQL_HOST) {
11    const { mysqlQuery } = await import('@/lib/mysql');
12    return mysqlQuery<T>(sql, params);
13  } else {
14    throw new Error(
15      'No database configured. Set either PLANETSCALE_HOST (HTTP, works in WebContainer) ' +
16      'or MYSQL_HOST (TCP, requires deployment) in .env'
17    );
18  }
19}
20
21export async function GET(request: Request) {
22  const { searchParams } = new URL(request.url);
23  const table = searchParams.get('table');
24  const search = searchParams.get('search') || '';
25  const page = parseInt(searchParams.get('page') || '1', 10);
26  const pageSize = Math.min(parseInt(searchParams.get('pageSize') || '20', 10), 100);
27  const offset = (page - 1) * pageSize;
28
29  if (!table || !/^[a-zA-Z_][a-zA-Z0-9_]*$/.test(table)) {
30    return NextResponse.json({ error: 'Invalid table name' }, { status: 400 });
31  }
32
33  try {
34    // Table names cannot be parameterized — validate via regex above
35    const searchClause = search ? 'WHERE name LIKE ?' : '';
36    const searchParam = search ? [`%${search}%`] : [];
37
38    const [records, countResult] = await Promise.all([
39      executeQuery<Record<string, unknown>>(
40        `SELECT * FROM \`${table}\` ${searchClause} LIMIT ? OFFSET ?`,
41        [...searchParam, pageSize, offset]
42      ),
43      executeQuery<[{ total: number }]>(
44        `SELECT COUNT(*) AS total FROM \`${table}\` ${searchClause}`,
45        searchParam
46      ),
47    ]);
48
49    return NextResponse.json({
50      records,
51      total: (countResult as unknown as Array<{ total: number }>)[0]?.total || 0,
52      page,
53      pageSize,
54    });
55  } catch (err) {
56    const message = err instanceof Error ? err.message : 'Query failed';
57    const isTcpError = message.includes('ECONNREFUSED') || message.includes('Failed to connect');
58    return NextResponse.json(
59      {
60        error: message,
61        hint: isTcpError
62          ? 'mysql2 uses TCP which is blocked in Bolt WebContainer. Use PlanetScale HTTP client or deploy first.'
63          : undefined,
64      },
65      { status: 500 }
66    );
67  }
68}

1// lib/prisma.ts
2// Note: Prisma with MySQL uses mysql2 (TCP) and requires deployment to connect.
3// For WebContainer development, configure DATABASE_URL to use PlanetScale with
4// the Prisma PlanetScale adapter, or use the psQuery helper from lib/planetscale.ts
5import { PrismaClient } from '@prisma/client';
6
7const globalForPrisma = globalThis as unknown as { prisma: PrismaClient | undefined };
8
9export const prisma =
10  globalForPrisma.prisma ??
11  new PrismaClient({
12    log: process.env.NODE_ENV === 'development' ? ['query', 'error', 'warn'] : ['error'],
13  });
14
15if (process.env.NODE_ENV !== 'production') {
16  globalForPrisma.prisma = prisma;
17}
18
19// prisma/schema.prisma:
20// generator client {
21//   provider = "prisma-client-js"
22// }
23// datasource db {
24//   provider = "mysql"
25//   url      = env("DATABASE_URL")
26//   relationMode = "prisma" // Use this for PlanetScale (disables FK constraints)
27// }
28// model User {
29//   id        Int      @id @default(autoincrement())
30//   email     String   @unique
31//   name      String
32//   createdAt DateTime @default(now())
33// }
34
35// .env DATABASE_URL format:
36// DATABASE_URL="mysql://user:password@host:3306/database?ssl={rejectUnauthorized:true}"
37// For PlanetScale:
38// DATABASE_URL="mysql://username:password@host.us-east.psdb.cloud/database?sslaccept=strict"