joplin-notes/joplin notion extension/Web Development/nodejs/snippets/json to sqlite db.md

json to sqlite db

const jsonToSQL = (
  json = "",
  rootPath = ".",
  dbfilename = "db.db",
  tablename = "tablename",
  primaryKeyField,
  isAppendMode = false
) => {
  const originalSchemaPath = `${rootPath}/originalSchema.json`;
  const parsedSchemaPath = `${rootPath}/parsedSchema.json`;

  const jsonData = JSON.parse(json);
  const db = new Database(`${rootPath}/${dbfilename}`);
  // console.log(typeof [1, 2]);
  // console.log(jsonData);

  // db.exec(`drop table if exists ${tablename}`);
  // db.exec(`delete from ${tablename}`);

  const isArray = Array.isArray(jsonData);

  let finalData = jsonData;

  // if not an array then converting to array
  if (!isArray) {
    finalData = Object.values(finalData);
  }

  let originalSchemaWrapper = {};

  let parsedSchemaWrapper = {};

  const originalSchema = {};
  const parsedSchema = {};

  // retrieving schema stored earlier
  if (fs.existsSync(originalSchemaPath)) {
    const value = JSON.parse(fs.readFileSync(originalSchemaPath));
    originalSchemaWrapper = value;
    Object.assign(originalSchema, value[tablename] ?? {});
  }

  if (fs.existsSync(parsedSchemaPath)) {
    const value = JSON.parse(fs.readFileSync(parsedSchemaPath));
    parsedSchemaWrapper = value;
    Object.assign(parsedSchema, value[tablename] ?? {});
  }

  const createTable = () => {
    if (finalData.length == 0) {
      return;
    }

    const firstData = finalData[0];
    if (typeof firstData != "object" || firstData === null) {
      throw new Error("invalid format");
    }

    // key value pair of fieldname and type, both are strings

    for (let key in firstData) {
      originalSchema[key] = typeof firstData[key];
    }

    const sqlColumnsInfoArray = [];
    for (let colname in originalSchema) {
      // to check if current column name is of primary key or not
      const isPrimaryKey =
        primaryKeyField != undefined &&
        primaryKeyField != null &&
        primaryKeyField == colname;

      // console.log(colname, " isPrimaryKey: ", isPrimaryKey);
      if (originalSchema[colname] == "number") {
        parsedSchema[colname] = "numeric";
      } else if (originalSchema[colname] == "boolean") {
        parsedSchema[colname] = "integer";

        // everything else is text as text can even store numbers too after being parsed to string so useful for nulls
      } else {
        parsedSchema[colname] = "text";
      }

      sqlColumnsInfoArray.push(
        `${colname} ${parsedSchema[colname]} ${
          isPrimaryKey ? "primary key" : ""
        }`
      );
    }

    const sqlCreateQuery = `create table  if not exists ${tablename} (
    ${sqlColumnsInfoArray.join(",")}
  );`;

    // console.log(sqlCreateQuery);
    const result = db.exec(sqlCreateQuery);
  };

  const insertTable = (record) => {
    if (typeof record != "object" || record == null) {
      throw new Error("invalid format");
    }

    for (let key in record) {
      if (!Object.hasOwn(originalSchema, key)) {
        let newSqlColumnInfo = "";
        originalSchema[key] = typeof record[key];

        if (originalSchema[key] == "number") {
          parsedSchema[key] = "number";
        } else if (originalSchema[key] == "boolean") {
          parsedSchema[key] = "integer";
          // newSqlColumnsInfoArray.push(`${key} integer`);

          // everything else is text as text can even store numbers too after being parsed to string so useful for nulls
        } else {
          parsedSchema[key] = "text";
          // newSqlColumnsInfoArray.push(`${key} text `);
        }

        newSqlColumnInfo = `${key} ${parsedSchema[key]} `;

        db.exec(`alter table ${tablename} add column ${newSqlColumnInfo}`);
      }
    }

    const columns_string = Object.keys(record).join(",");

    const valuesArray = [];

    for (let column in record) {
      // if text
      if (parsedSchema[column] === "text") {
        // check if value is null or not
        if (record[column] === null) {
          valuesArray.push(null);
        }
        // if value is string then simply pushing it
        else if (typeof record[column] === "string") {
          valuesArray.push(record[column]);
        }
        // if its number or array or object it will be stringified accordingly
        else {
          valuesArray.push(JSON.stringify(record[column]));
        }
      }
      // if its boolean
      else if (parsedSchema[column] === "integer") {
        valuesArray.push(record[column] ? 1 : 0);

        // if its number
      } else {
        valuesArray.push(record[column]);
      }
    }

    // const valuesPlaceholderArray =
    const values_placeholders_string = new Array(valuesArray.length)
      .fill("?")
      .join(",");

    const insertStatement = db.prepare(
      `insert into ${tablename} (${columns_string}) values (${values_placeholders_string}) `
    );

    const info = insertStatement.run(...valuesArray);

    // console.log("info: ", info);
  };

  if (Object.keys(originalSchema).length === 0) {
    createTable();
  } else {
    if (!isAppendMode) {
      db.exec(`delete from ${tablename}`);
    }
  }

  for (let record of finalData) {
    insertTable(record);
  }

  // console.log("result: ", result);

  // const insertStatement = db.exec(
  //   "create table  if not exists channels (info text)"
  // );

  // console.log("insertStatement: ", insertStatement);

  originalSchemaWrapper[tablename] = originalSchema;
  parsedSchemaWrapper[tablename] = parsedSchema;

  fs.writeFileSync(originalSchemaPath, JSON.stringify(originalSchemaWrapper));
  fs.writeFileSync(parsedSchemaPath, JSON.stringify(parsedSchemaWrapper));

  // console.log("done");

  console.log(`converted json to table ${tablename} in db ${dbfilename}`);
};