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

The SQL export is the one that surprises people most. Paste in a messy CSV, clean it up visually, click Export, and you get a CREATE TABLE statement followed by one INSERT INTO per row. Ready to run against any database.

No backend. No schema tool. No manual SQL writing. The browser does it.

Here's how.

The DOM as a Data Source

TAFNE's export pipeline doesn't work on the raw HTML string. It works on live DOM elements. The first step in every export is a call to getTableData(), a helper that extracts headers and rows by walking the table DOM.

function getTableData(tableEl) {
    const $table = $(tableEl);
    const headers = [];
    const rows = [];
$table.find('tr').each(function(rowIdx) {         const cells = [];         $(this).find('th, td').each(function() {             cells.push($(this).text().trim());         });         if (rowIdx === 0 && $(this).find('th').length > 0) {             headers.push(...cells);         } else {             if (cells.length > 0) rows.push(cells);         }     });
if (headers.length === 0 && rows.length > 0) {         headers.push(...rows.shift());     }
return { headers, rows }; }

It identifies headers by checking whether the first row contains <th> elements. If the table has no <th> at all, which happens with imported data that didn't distinguish headers from data, the first row is promoted to headers anyway. That fallback covers the common case of CSV files where the user didn't use a header row.

The output is a plain { headers, rows } object. Both are arrays of strings. At this point the data has left the DOM entirely.

Sanitizing Column Names

SQL column names have rules. Spaces are illegal in most databases without quoting. Special characters break parsers. TAFNE handles this before the CREATE TABLE statement is built:

const cols = headers.map((h, idx) =>
    h ? h.replace(/\s+/g, '_').replace(/[^a-zA-Z0-9_]/g, '') || col_${idx + 1} : col_${idx + 1}
);

Two passes. First: replace any whitespace sequences with underscores. Second: strip anything that's not a letter, digit, or underscore. If the result is an empty string, which happens if the header contained only special characters, the column falls back to col_N where N is the 1-based index.

This is conservative sanitization. It doesn't quote column names, which means headers like First Name become First_Name. If someone needs exact preservation of column names with spaces, they'd need to add quoting logic. But for the common case of developer-friendly column names, this covers it.

Generating the Schema

The CREATE TABLE statement uses TEXT for every column. No type inference currently.

const colDefs = cols.map(c =>   ${c} TEXT).join(',\n');
let sql = CREATE TABLE ${tableName} (\n${colDefs}\n);\n;

The tableName is table_1, table_2, etc. When a single table is exported, it's just table_1. When multiple tables are exported at once, each gets a sequential number.

Every column is TEXT. This is an intentional choice. Type inference from cell content is possible, you could scan each column, try to parse values as integers or floats, and assign the appropriate type. But that inference can be wrong, and a wrong column type in a CREATE TABLE statement can silently truncate data or cause insertion failures.

Declaring everything as TEXT is always correct. If the downstream database has specific type requirements, the developer adjusts the schema after examining it. That's one edit. Debugging a wrong type inference is much more expensive.

Generating the Inserts

The INSERT statements are one per row:

rows.forEach(row => {
    const values = cols.map((_, idx) => {
        const v = row[idx] !== undefined ? row[idx] : '';
        return '${v.replace(/'/g, "''")}';
    });
    sql += INSERT INTO ${tableName} (${cols.join(', ')}) VALUES (${values.join(', ')});\n;
});

Each value is wrapped in single quotes and any single quotes within the value are doubled, '' is the standard SQL escape for a literal single quote. Missing values (row shorter than header count) default to empty string.

The column list is repeated in every INSERT statement. This is more verbose than a multi-value INSERT, but it's safer: each statement is self-contained and will succeed or fail independently. Multi-value INSERTs fail as a unit if any row has a problem.

The Full Output

For a 4-row table with columns Quarter, Revenue, Profit:

CREATE TABLE table_1 (
  Quarter TEXT,
  Revenue TEXT,
  Profit TEXT
);
INSERT INTO table_1 (Quarter, Revenue, Profit) VALUES ('Q1 2024', '1200000', '220000');
INSERT INTO table_1 (Quarter, Revenue, Profit) VALUES ('Q2 2024', '1450000', '430000');
INSERT INTO table_1 (Quarter, Revenue, Profit) VALUES ('Q3 2024', '1320000', '330000');
INSERT INTO table_1 (Quarter, Revenue, Profit) VALUES ('Q4 2024', '1980000', '830000');

Paste that into any SQL client. Done.

Where It Fits in the Export Suite

The SQL emitter is one of six exporters in generateFunctions.js. The others produce HTML, Markdown, JSON, CSV, and ASCII. All of them call getTableData() first and then format the output differently. The shared data extraction function means any improvement to header detection benefits every export format simultaneously.

The same table. Six different outputs. No reformatting by the user.

Source: github.com/carnworkstudios/TAFNE