From DOM to Database: How TAFNE Generates SQL From a Visual Table

TLDR

Generating executable SQL (CREATE TABLE and INSERT INTO) from browser HTML tables requires sanitizing raw text headers and escaping quote characters. Sanitizing column names to valid identifiers and defaulting column types to TEXT produces client-side DDL queries ready for immediate database execution.

SQL Generation Phase	Input Data Source	Transformation Strategy	Output Format
Header Sanitization	<th> / <td> Text strings	Regex replace spaces & special chars	CREATE TABLE table_1 (...)
Row Serialization	<td> Cell contents	Double-single quote escaping ('')	INSERT INTO table_1 VALUES (...)

---

Arbitrary text strings break database execution unless sanitized into valid identifiers

Exporting edited browser tables into SQL statements requires converting arbitrary HTML text into database-valid identifiers. Unsanitized headers containing spaces, punctuation, or SQL reserved words cause execution errors when imported into database clients.

To ensure client-side exports remain valid across MySQL, PostgreSQL, and SQLite, we implemented an automated header sanitization and string escaping pipeline.

---

Client-side string escaping generates safe, executable DDL and DML queries

The export process extracts table data, sanitizes headers, and formats SQL queries directly inside the browser:

   SQL EMISSION PIPELINE:
   
   [Raw Table Headers] ──> "User Name", "Email!"
            │
            ▼
   ┌────────────────────────────────┐
   │ 1. Header Sanitization         │ 
   │    Regex: s/\s+/_/g            │
   └────────────────┬───────────────┘
                    │
                    ▼
   [Sanitized Cols] ──> "User_Name", "Email"
                    │
                    ├─► [Emit DDL] ──> CREATE TABLE t1 (User_Name TEXT, Email TEXT);
                    │
                    ▼
   ┌────────────────────────────────┐
   │ 2. Row Iteration & Escaping    │ 
   │    Replace: ' with ''          │
   └────────────────┬───────────────┘
                    │
                    ▼
         [Emit DML] ──> INSERT INTO t1 (User_Name, Email) VALUES ('O''Brian', 'x@y');

Here is the implementation for client-side SQL generation:

// Extract DOM data and generate sanitized SQL schema and insert queries
export function generateSqlFromTable(tableEl, tableName = 'table_1') {
  const { headers, rows } = extractTableData(tableEl);
// 1. Sanitize column headers into valid SQL identifiers   const sanitizedCols = headers.map((h, idx) => {     const clean = h.replace(/\s+/g, '_').replace(/[^a-zA-Z0-9_]/g, '');     return clean.length > 0 ? clean : col_${idx + 1};   });
// 2. Build DDL CREATE TABLE statement (defaulting columns to TEXT)   const colDefs = sanitizedCols.map(col =>   ${col} TEXT).join(',\n');   let sqlOutput = CREATE TABLE ${tableName} (\n${colDefs}\n);\n\n;
// 3. Emit DML INSERT queries with single-quote escaping   rows.forEach(row => {     const escapedValues = sanitizedCols.map((_, idx) => {       const val = row[idx] !== undefined ? String(row[idx]) : '';       return '${val.replace(/'/g, "''")}';     });     sqlOutput += INSERT INTO ${tableName} (${sanitizedCols.join(', ')}) VALUES (${escapedValues.join(', ')});\n;   });
return sqlOutput; }

Defaulting column types to TEXT avoids risky client-side type inference errors while preserving numeric and string precision during import.

Rule of thumb: Sanitize header identifiers aggressively and escape single quotes when generating SQL statements client-side.