SQL to TypeInterface

sql_typeInterface.js

const fs = require('fs');

// Read the SQL file
const sql = fs.readFileSync('structure.sql', 'utf8');

// Helper function to map SQL types to TypeScript types
const mapSQLTypeToTSType = (sqlType) => {
  if (/tinyint\(1\)/i.test(sqlType)) return 'boolean'; // Map tinyint(1) to boolean
  if (/int|decimal|float|double/i.test(sqlType)) return 'number';
  if (/varchar|text/i.test(sqlType)) return 'string';
  if (/timestamp|date|datetime/i.test(sqlType)) return 'Date';
  return 'any';
};

// Convert table names to PascalCase
const toPascalCase = (name) => {
  return name
    .replace(/[_-](\w)/g, (_, letter) => letter.toUpperCase())  // handle snake_case and kebab-case
    .replace(/^\w/, (letter) => letter.toUpperCase());          // capitalize the first letter
};

// Parse each column within the table definition
const parseColumns = (columnsText) => {
  return columnsText
    .split(/\r?\n/)  // Split by line to process each column separately
    .map(column => {
      // Match the column name, type, and constraints (like DEFAULT NULL, NOT NULL)
      const match = column.trim().match(/`(\w+)`\s+(\w+(\(\d+\))?)(.*)/i);
      if (!match) return null; // Skip if no match found

      const [, name, type, , constraints] = match;
      const tsType = mapSQLTypeToTSType(type);
      const isNotNull = constraints.includes('NOT NULL');
      const hasDefault = constraints.includes('DEFAULT');
      const isNullable = constraints.includes('DEFAULT NULL') || constraints.includes('NULL');
      const isPrimaryKey = /PRIMARY KEY/i.test(constraints);
      const isKey = /KEY/i.test(constraints);

      // Determine if the TypeScript property should be optional or required
      const optionalFlag = (isNullable || (isNotNull && hasDefault)) ? '?' : '';
      const requiredFlag = isNotNull || isPrimaryKey || isKey ? '' : optionalFlag;

      return `  ${name}${requiredFlag}: ${tsType};`;
    })
    .filter(Boolean)
    .join('\n');
};

// Parse each table in the SQL file
const parseTable = (tableSQL) => {
  const tableMatch = tableSQL.match(/`(\w+)`\s*\(([\s\S]+?)\)\s*ENGINE/i); // Match table name and columns
  if (!tableMatch) return null;

  const tableName = toPascalCase(tableMatch[1].trim()); // Convert to PascalCase
  const columnsText = tableMatch[2].trim();
  const columns = parseColumns(columnsText);
  return `interface ${tableName} {\n${columns}\n}`;
};

// Split by `CREATE TABLE` statements and parse each table
const tables = sql.split(/CREATE TABLE/).slice(1);
const interfaces = tables.map(parseTable).filter(Boolean).join('\n\n');

// Write the generated TypeScript interfaces to a file
fs.writeFileSync('types.ts', interfaces);

console.log('TypeScript interfaces generated in types.ts');