How to execute dynamic DDL statements?

[chenmonster]

import { SQL } from "bun";

// Assuming the REST API returns data.
const datas = [
  {
    syncState: "BaseIndirect",
    messageType: "PositionReportClassAScheduled",
    repeatIndicator: "0",
    transponderClass: "B",
    valid: "true",
    second: "39",
    navigationStatus: "UnderwayUsingEngine",
    mmsi: "413825213",
    courseOverGround: "107.5",
    speedOverGround: "6.400000095367432",
    rateOfTurn: "0",
    trueHeading: "511",
    longitude: "139.839637",
    latitude: "35.721631",
    shipName: "",
    shipType: "Cargo",
    draught: "0",
    callsign: "",
    eta: "0",
    imo: "0",
    destination: "0",
    shipWidth: "13",
    shipLength: "119",
    collectTime: "2026-01-17 14:59:40",
  },
  {
    syncState: "UTCDirect",
    messageType: "ExtendedClassBEquipmentPositionReport",
    repeatIndicator: "0",
    transponderClass: "A",
    valid: "true",
    second: "60",
    navigationStatus: "UnderwayUsingEngine",
    mmsi: "413773734",
    courseOverGround: "229.89999389648438",
    speedOverGround: "0.0",
    rateOfTurn: "-128",
    trueHeading: "511",
    longitude: "139.847856",
    latitude: "35.675789",
    shipName: "RUILIN",
    shipType: "Cargo",
    draught: "0",
    callsign: "0",
    eta: "0",
    imo: "0",
    destination: "0",
    shipWidth: "15",
    shipLength: "92",
    collectTime: "2026-01-17 14:59:41",
  },
];
const mysql = new SQL("mysql://xxx:xxx@mysql.sqlpub.com:3306/test");
const ddl = Object.keys(datas[0])
  .reduce(
    (sql, key) => sql.concat(`  \`${key}\` VARCHAR(255), \n`),
    "CREATE TABLE IF NOT EXISTS `ais_data` ( \n"
  )
  .slice(0, -3)
  .concat("\n) ");
// Here's an error.
await mysql`${ddl}`; 
// // It's OK here.
// await mysql`CREATE TABLE IF NOT EXISTS \`ais_data\` ( \`syncState\` VARCHAR(255), \`messageType\` VARCHAR(255), \`repeatIndicator\` VARCHAR(255), \`transponderClass\` VARCHAR(255), \`valid\` VARCHAR(255), \`second\` VARCHAR(255), \`navigationStatus\` VARCHAR(255), \`mmsi\` VARCHAR(255), \`courseOverGround\` VARCHAR(255), \`speedOverGround\` VARCHAR(255), \`rateOfTurn\` VARCHAR(255), \`trueHeading\` VARCHAR(255), \`longitude\` VARCHAR(255), \`latitude\` VARCHAR(255), \`shipName\` VARCHAR(255), \`shipType\` VARCHAR(255), \`draught\` VARCHAR(255), \`callsign\` VARCHAR(255), \`eta\` VARCHAR(255), \`imo\` VARCHAR(255), \`destination\` VARCHAR(255), \`shipWidth\` VARCHAR(255), \`shipLength\` VARCHAR(255) )`;
await mysql`INSERT INTO \`ais_data\` ${mysql(datas)}`;

I want to dynamically create a table in MySQL and store data based on the data returned from the REST API. The following error occurred:

 9 |   createQuery: createMySQLQuery,
10 |   init: initMySQL
11 | } = @lazy(16);
12 | function wrapError(error) {
13 |   if (Error.isError(error))
14 |   return new MySQLError(error.message, error);
              ^
MySQLError: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?' at line 1      
    errno: 1064,
 sqlState: "42000",
     code: "ERR_MYSQL_SYNTAX_ERROR"

      at wrapError (internal:sql/mysql:14:10)
      at onRejectMySQLQuery (internal:sql/mysql:47:25)

Bun v1.3.6 (Windows x64)

[sunnypatell]

@chenmonster the ? in the error is the giveaway. when you write await mysql\${ddl}``, bun's tagged template sends the entire DDL string as a bind parameter via mysql's prepared statement protocol. mysql can't parameterize DDL statements (table names, column definitions, etc.), only DML values.

fix: use sql.unsafe() for DDL, keep parameterized queries for DML:

const mysql = new SQL("mysql://xxx:xxx@mysql.sqlpub.com:3306/test");

// DDL -- use .unsafe() since table/column names can't be parameterized
const ddl = Object.keys(datas[0])
  .reduce((sql, key) => sql.concat(`  \`${key}\` VARCHAR(255),\n`), "CREATE TABLE IF NOT EXISTS `ais_data` (\n")
  .slice(0, -2)
  .concat("\n)");

await mysql.unsafe(ddl);

// DML -- tagged template is fine here, values get properly parameterized
await mysql`INSERT INTO \`ais_data\` ${mysql(datas)}`;

the distinction is in src/sql/postgres.zig (same codepath for mysql): tagged templates set SQLQueryFlags.prepared which wraps everything as bind params, while .unsafe() sets SQLQueryFlags.simple which sends raw SQL text.

one warning: .unsafe() does NOT escape input. since you're building column names from Object.keys(datas[0]), make sure those keys are trusted. a malicious key like foo` VARCHAR(1)); DROP TABLE ais_data; -- would execute. sanitize with a whitelist if the data comes from user input.

[chenmonster]

Thank you, Big Brother.